相关文章推荐
有腹肌的油条  ·  Remplacer ...·  3 月前    · 
活泼的硬盘  ·  sys.dm_os_threads ...·  5 月前    · 
谦和的蘑菇  ·  ios extension log-掘金·  10 月前    · 

浣跨����澶�����濉�ˉ绋���版�涓��绌洪�

�ㄦ����涓��灏��涔��浣�娇��Oracle �版�搴�10 g SQL 涓����SQL ���璇��濉�ˉ绋���版�涓��绌洪���/p>

澶х害 30 ���

���绋��缁��浠ヤ�涓婚�锛�/p> 绋���版�绀轰� 濉�ˉ�版�涓��绌洪�

灏����疆浜���炬�涓�互��浇���������������骞���俱�锛�����姝ゆ�浣�������浇���灞�����锛��浠ョ�����㈡�锛��搴���村��戒�姣���裤�锛�

娉ㄦ�锛�/b>姝ゅ�锛����互灏������ㄤ����楠ょ���釜�炬�涓��浠ヤ究浠��杞藉��ョ�涓��姝ョ��崇�灞����������互��������釜灞�����灏�������/span>

Oracle �版�搴�10 g ���澶�����瀛��姒�堪

�版���父浠ョ���舰寮��琛���ㄣ�涔�氨���锛��������缁���堕�娌℃�浠讳��煎�������浜��琛ㄤ腑娌℃�琛���ㄣ��惰�锛������寸淮涓���版�瀵��锛���ц��堕�搴��璁$���负绠��������涓哄�������浜��涓��娈垫�濉������颁��达�浠���存�浜�娇�ㄥ甫������绉荤����绐���芥������� 绗�21 绔���版�浠����� 浠ヨ�寰��澶�俊���

瑕�����绋�����锛����互浣跨����澶�����濉�ˉ�堕�搴��涓��绌洪����绉���ラ�杩��澶�����搴��浜��璇�腑瀹�����涓��杈���猴��╁�浜�父瑙���ㄨ��ヨ�娉��Oracle �版�搴���� PARTITION BY 瀛��涓��瀹��琛ㄨ揪寮��瀵规�璇�腑���杩���昏��������哄��ㄨ��ョ�缁�����杈���鸿〃涓��涓������ㄨ��ヤ�������渚х�琛ㄧ� ��� ��/p>

璇锋敞����ㄥ�浠ヤ娇�ㄨ�绉�被������濉�ˉ浠讳�缁达����浠������寸淮锛�腑��┖������绋��涓�敞浜���寸淮锛��涓哄����甯哥�浣��杈��纭����淮��/p>

���澶��������娉�负 PARTITION BY ��〃杈惧���〃锛���╁�浜�ANSI SQL JOIN 瀛�������〃涓��琛ㄨ揪寮��瀹��瑕���ㄥ��ㄨ��ョ�缁��浠ヤ����甯哥�浜���哄��ㄨ��ョ�涓ょ�璇��褰㈠�锛�/p>

SELECT select_expression
FROM   table_reference
PARTITION BY (expr [, expr ]... )
RIGHT OUTER JOIN table_reference

璇锋敞������澶�����涓����FULL OUTER JOIN锛���ㄥ��ㄨ��ワ������� Oracle �版�搴�10g SQL ��� 浠ヨ�����宠�娉��������涓��淇℃���/p>

浠ヤ�绀轰����涓�����绋��缁存�褰���朵腑灏��绠�骇���Bounce��000 骞村� 2001 骞翠腑绗�20–30 �ㄧ��ㄩ�������骞村��充�������锛�/p>

SELECT
SUBSTR(p.Prod_Name,1,15) Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
SUM(Amount_Sold) Sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name IN ('Bounce') AND
t.Calendar_Year IN (2000,2001) AND
t.Calendar_Week_Number BETWEEN 20 AND 30
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number;
PRODUCT_NAME          YEAR       WEEK      SALES
--------------- ---------- ---------- ----------
Bounce 2000 20 801
Bounce 2000 21 4062.24
Bounce 2000 22 2043.16
Bounce 2000 23 2731.14
Bounce 2000 24 4419.36
Bounce 2000 27 2297.29
Bounce 2000 28 1443.13
Bounce 2000 29 1927.38
Bounce 2000 30 1927.38
Bounce 2001 20 1483.3
Bounce 2001 21 4184.49
Bounce 2001 22 2609.19
Bounce 2001 23 1416.95
Bounce 2001 24 3149.62
Bounce 2001 25 2645.98
Bounce 2001 27 2125.12
Bounce 2001 29 2467.92
Bounce 2001 30 2620.17
18 rows selected.

���涓��濡���版�瀵��锛�������22 琛�����2 骞达�姣�勾 11 ����������ㄥ�浼����18 琛�������负�朵腑缂哄け浜�2000 骞寸� 25 �ㄥ�绗�26 �ㄤ互��2001 骞寸� 26 �ㄥ�绗�28 �ㄧ��版���br>

�堕�搴��涓��绌洪�浼�娇璇稿���勾搴��琛��杈��绫荤�璁$��句互杩�����娌℃�绌洪��讹���互���浣跨�����芥� LEAD() �� LAG() 锛��涓���板�瀹����互澶�����琛��琛�����姣���版����濡��濡���ㄦ��颁���害�版�锛�苟甯����� 12 涓��浠ュ��������d�锛�������� 12 琛������版�寰��渚裤�濡��姣�釜�舵�锛���朵�浠讳��ㄤ������淮锛��琛��涓���达�浣跨� LEAD() �� LAG() �芥����涓�����

濡���ㄥ��哄��ㄨ��ュ~琛ュ��㈢ず渚�腑��┖���

��互�ㄥ�����堕��版������滑�ヨ�涓��绋���版�杩��涓�����澶��������浠ヤ���ず���璇�腑锛��濮��璇�互 v 涓哄����浠���磋〃妫�储�扮��版�浠� t 涓哄�����板��ㄥ���� 22 琛�������负搴��涓�病��┖�����坊��� 4 琛�腑锛��琛������奸�涓�0 ��杩���奸�杩�娇�� NVL() �芥�璁剧疆涓轰� 0��/span>

SELECT Product_Name, t.Year, t.Week, NVL(Sales,0) dense_sales
FROM
(SELECT
SUBSTR(p.Prod_Name,1,15) Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
SUM(Amount_Sold) Sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name IN ('Bounce') AND
t.Calendar_Year IN (2000,2001) AND
t.Calendar_Week_Number BETWEEN 20 AND 30
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number
) v
PARTITION BY (v.Product_Name)
RIGHT OUTER JOIN
(SELECT DISTINCT
Calendar_Week_Number Week,
Calendar_Year Year
FROM Times
WHERE Calendar_Year in (2000, 2001)
AND Calendar_Week_Number BETWEEN 20 AND 30
) t

ON (v.week = t.week AND v.Year = t.Year)
ORDER BY t.year, t.week;
PRODUCT_NAME          YEAR       WEEK DENSE_SALES
--------------- ---------- ---------- -----------
Bounce 2000 20 801
Bounce 2000 21 4062.24
Bounce 2000 22 2043.16
Bounce 2000 23 2731.14
Bounce 2000 24 4419.36
Bounce 2000 25 0
Bounce 2000 26 0
Bounce 2000 27 2297.29
Bounce 2000 28 1443.13
Bounce 2000 29 1927.38
Bounce 2000 30 1927.38
Bounce 2001 20 1483.3
Bounce 2001 21 4184.49
Bounce 2001 22 2609.19
Bounce 2001 23 1416.95
Bounce 2001 24 3149.62
Bounce 2001 25 2645.98
Bounce 2001 26 0
Bounce 2001 27 2125.12
Bounce 2001 28 0
Bounce 2001 29 2467.92
Bounce 2001 30 2620.17
22 rows selected.

璇锋敞����ㄤ互涓��璇�腑锛���ㄤ�绗�20-30 �ㄩ��� WHERE �′欢�剧疆�ㄤ��堕�缁寸��ц�瑙��涓��姝ゆ�楠ゅ�灏���卞��ㄨ��ュ����琛��锛����害浜������淬�

濡��灏�����涓����SQL �芥��哥���互�峰���������疮绉�����锛�br>

浠��绔���d腑锛��琛�互涓��浠わ�

cd /home/oracle/wkdir
sqlplus sh/sh

fg.sql ������浠ヤ����锛�font face="Courier New, Courier, mono">

SELECT Product_Name, t.Year, t.Week, Sales, Weekly_ytd_sales
FROM:
(SELECT
SUBSTR(p.Prod_Name,1,15) Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
NVL(SUM(Amount_Sold),0) Sales,
SUM(SUM(Amount_Sold)) OVER
(PARTITION BY p.Prod_Name, t.Calendar_Year
ORDER BY t.Calendar_Week_Number) Weekly_ytd_sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name IN ('Bounce') AND
t.Calendar_Year IN (2000,2001)  AND
      t.Calendar_Week_Number BETWEEN  20 AND 30
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number
PARTITION BY (v.Product_Name) 
RIGHT OUTER JOIN
(SELECT DISTINCT
Calendar_Week_Number Week,
Calendar_Year Year
FROM Times
WHERE Calendar_Year in (2000, 2001)
ON (v.week = t.week AND v.Year = t.Year)
WHERE t.Week BETWEEN 20 AND 30
ORDER BY 1, 2, 3;

�ㄨ�涓��璇�腑锛��姣�����棰����竟锛��绠��浜���ㄧ�骞村��充����棰���卞��哄��ㄨ��ユ��ヤ互浣挎��村������� NULL �兼���父�规�杩��浜�����SUM �芥�灏���借�涓�0��/p>

�ㄦ�浜��璇�腑锛���哄��ㄨ��ュ�杩��甯�� NULL �肩�琛��浣�����甯��杩��琛��瀛����腑������ NULL �笺��筹��ㄦ���������腑杩���������讹��ㄧ��扮�绗��涓�� NULL �兼���NULL��

璺�釜涓��浜у�����ㄥ�浣�����搴��琛�����瑕��绫昏��虹�涓��甯歌�������瀛�〃������锛�����琛ㄧ����瀹�滑����ㄦ�浜�欢����堕�瀵逛骇����ㄨ����浜����〃�ヨ�锛��浠舵����锛��浜��瀛�〃�ヨ�锛��浠舵����浜у��伴�������濡��瀵逛�搴��浣跨�瀵������寸淮锛��琛ㄧず�ㄦ�甯�����姣��澶╃��伴��笺�瑕���虹��兼������� NULL �笺�璇锋敞���杩��涔����疮绉����ず渚������ㄩ�涓��璇�腑锛�疮绉�����绠�� NULL �借�涓�0锛��姝ゆ�绀轰�姝g‘�笺��g��规�涓���ㄤ�搴��琛ㄥ�涓��绫讳技��〃锛��涓烘�缃��甯�� NULL ���涓���间�������

浠ヤ�绀轰�涓��搴��琛ㄥ甫����哄��ㄨ��ャ�瀹���ㄦ�杩����NULL �兼���NULL��/p>

棣��锛��寤轰�涓��涓ょ�浜у����搴��琛��姣��浜у���袱澶╃�甯����骇���bottle��� 4 ��1 �ュ�瀛�负 10 涓��浣����4 ��6 ��涓�8 涓��浣��浜у���an��� 4 ��1 �ュ�瀛�负 15 涓��浣����4 ��4 ��涓�11 涓��浣���ц�涓����SQL*Plus ���锛�/p>

ci.sql ������浠ヤ����锛�font face="Courier New, Courier, mono">

CREATE TABLE inventory (
time_id DATE,
product VARCHAR2(10),
quant NUMBER);
INSERT INTO inventory VALUES
(TO_DATE('01/04/01', 'DD/MM/YY'), 'bottle', 10);
INSERT INTO inventory VALUES
(TO_DATE('06/04/01', 'DD/MM/YY'), 'bottle', 8);
INSERT INTO inventory VALUES
(TO_DATE('01/04/01', 'DD/MM/YY'), 'can', 15);
INSERT INTO inventory VALUES
(TO_DATE('04/04/01', 'DD/MM/YY'), 'can', 11);

�板�锛��浣跨����澶������ョ�浠�4 ��1 �ヨ� 7 �ユ��存�绉�骇�������伴����������甯�� NULL �肩�琛��灏变娇�ㄥ��哄��ㄨ��ユ�璇㈣�涓�〃锛������伴�璇������ц�涓����SQL*Plus ���锛�/p>

nn.sql ������浠ヤ����锛�font face="Courier New, Courier, mono">

SELECT times.time_id, product, quant
FROM inventory
PARTITION BY (product)
RIGHT OUTER JOIN times
ON (times.time_id = inventory.time_id)
WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY')
AND TO_DATE('07/04/01', 'DD/MM/YY')
ORDER BY  2,1;

涓�����������棰����— �ㄧ���┖�艰�涓�� bottle ��can ����ㄦ���氨���杩����NULL �笺�渚��锛��浜�bottle锛�� 4 ��2 �ュ� 5 �ワ��ㄦ�瑕���扮��伴�涓�10��

涓烘�绀烘������������Oracle �版�搴�10g 涓�坊��� FIRST_VALUE ��LAST_VALUE �芥�����抽�瀛��瀵逛�杩���芥�涓��浠讳��芥�锛����互�ㄥ������〃涓��瀹�IGNORE NULLS锛��浠��杩�������� NULL �笺��ц�涓����SQL*Plus ���锛�/p>

nn2.sql ������浠ヤ����锛�br>

WITH v1 AS
(SELECT time_id
FROM times
WHERE times.time_id BETWEEN
TO_DATE('01/04/01', 'DD/MM/YY')
AND TO_DATE('07/04/01', 'DD/MM/YY'))
SELECT product, time_id, quant quantity,
LAST_VALUE(quant IGNORE NULLS)
OVER (PARTITION BY product ORDER BY time_id)
repeated_quantity
FROM:
(SELECT product, v1.time_id, quant
FROM inventory PARTITION BY (product)
RIGHT OUTER JOIN v1
ON (v1.time_id = inventory.time_id))
ORDER BY 1, 2;

瑕��楂��璇㈢�����у苟涓�敞浜���哄��ㄨ��ワ�浣跨�涓�釜 WITH 瀛��寮���ヨ����琛���㈢� SQL*Plus ���锛�/p>

pp.sql ������浠ヤ����锛�/p>

WITH v AS
(SELECT
p.Prod_Name Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
SUM(Amount_Sold) Sales
FROM Sales s, Times t, Products p
WHERE s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name in ('Y Box') AND
t.Calendar_Year in (2000,2001) AND
t.Calendar_Week_Number BETWEEN 30 AND 40
GROUP BY p.Prod_Name, t.Calendar_Year, t.Calendar_Week_Number
SELECT substr(Product_Name,1,12) Prod, 
Year, 
Week, 
Sales,
Weekly_ytd_sales,
Weekly_ytd_sales_prior_year
FROM:
(SELECT --Start of year_over_year sales
Product_Name, Year, Week, Sales, Weekly_ytd_sales,
LAG(Weekly_ytd_sales, 1) OVER
(PARTITION BY Product_Name, Week ORDER BY Year)
Weekly_ytd_sales_prior_year
FROM:
(SELECT --Start of dense_sales
v.Product_Name Product_Name,
t.Year Year,
t.Week Week,
NVL(v.Sales,0) Sales,
SUM(NVL(v.Sales,0)) OVER
(PARTITION BY v.Product_Name, t.Year
ORDER BY t.week) weekly_ytd_sales
FROM v 
PARTITION BY (v.Product_Name) 
RIGHT OUTER JOIN
(SELECT DISTINCT
Calendar_Week_Number Week,
Calendar_Year Year
FROM Times
WHERE Calendar_Year IN (2000, 2001)
ON (v.week = t.week AND v.Year = t.Year)
) dense_sales
) year_over_year_sales
WHERE Year = 2001 AND
Week BETWEEN 30 AND 40 
ORDER BY 1, 2, 3;

�ㄥ������DENSE_SALES ��FROM 瀛��涓��浣跨����瑙�� v ����磋���t ����哄��ㄨ��ユ部�堕�缁村~琛ラ������腑��┖����跺�锛������芥� SUM ...OVER �ュ�����哄��ㄨ��ョ�杈��锛�互璁$�姣��骞村��充�������锛��weekly_ytd_sales���锛�����锛����DENSE_SALES 灏��绠��姣����勾���浠������������ㄨ������s 涓�己澶辩��版�锛��

�跺�锛�������YEAR_OVER_YEAR_SALES 浣跨� LAG �芥�璁$�涓�勾���姣��骞村��充����棰�����涓衡�weekly_ytd_sales_prior_year��� LAG �芥�灏��瀹��涓�PARTITION BY 瀛��锛�互灏�2000 骞村� 2001 骞村�涓���������板�涓���轰腑��苟灏��绉婚� 1 浼��缁�LAG �芥�浠ヨ�寰��涓�勾����ㄥ勾���浠�������/p>

����㈢��ヨ���� yr = 2001 �′欢浠�YEAR_OVER_YEAR_SALES ����版�锛�����璇㈣����绉�骇��� 2001 骞村� 2000 骞存�瀹��涓��姣��骞村��充����棰��

灏界������ず渚��绀轰����涓���存����寤烘�杈���规�锛������ㄥ�涓��璇�腑澶��澶���寸骇�翠负瀹�����濡���ㄥ�浠ユ�骞淬�瀛e害�����ぉ绛��瀵逛�����舵�杩�����棰��杈��

涓��涓�换�′腑锛��瑕��寤轰�涓��璇�����浠���村�娆$����绾у�瀵瑰勾���浠������ц�骞翠�骞翠��寸�瀵规���/p>

�ц�姝や换�¢�瑕��骞叉�楠ゃ�������寤哄�涓��璇��浠ユ�澶┿��ㄣ����瀛e害��勾��骇杩��姣�����灏���扮������MV_PROD_TIME锛��瑙���锋�涓�釜璺�TIMES ��PRODUCTS ���������灞��绔�����杩�釜�╁�瑙��涔��灏��寤哄�涓�釜瑙�����澶��杩����缓涓�釜�堕�缁磋��句互�ㄤ�绔����竟����堕�杈瑰���负�扮�����句腑绋���版�����哄��ㄨ��ャ�

�充�灞��绔�����澶�俊���璇峰����版�浠�������� 涓���ㄦ����搴�腑���浜����� SQL���绔��

��缓�╁�瑙�����娉ㄦ�锛��璇�����涓ょ�浜у�浠ヤ究缂╃�澶���堕����琛���㈢� SQL*Plus ���锛�/p>

cm1.sql ������浠ヤ����锛�/p>

CREATE MATERIALIZED VIEW mv_prod_time
REFRESH COMPLETE ON DEMAND
SELECT
(CASE
WHEN ((GROUPING(calendar_year)=0 )
AND (GROUPING(calendar_quarter_desc)=1 ))
THEN (TO_CHAR(calendar_year) || '_0')
WHEN ((GROUPING(calendar_quarter_desc)=0 )
AND (GROUPING(calendar_month_desc)=1 ))
THEN (TO_CHAR(calendar_quarter_desc) || '_1')
WHEN ((GROUPING(calendar_month_desc)=0 )
AND (GROUPING(t.time_id)=1 ))
THEN (TO_CHAR(calendar_month_desc) || '_2')
ELSE (TO_CHAR(t.time_id) || '_3') END) Hierarchical_Time,
calendar_year year,
calendar_quarter_desc quarter,
calendar_month_desc month,
t.time_id day,
prod_category cat,
prod_subcategory subcat,
p.prod_id prod,
GROUPING_ID(prod_category, prod_subcategory, p.prod_id,
calendar_year, calendar_quarter_desc,
calendar_month_desc,t.time_id) gid,
GROUPING_ID(prod_category, prod_subcategory, p.prod_id) gid_p, 
GROUPING_ID(calendar_year, calendar_quarter_desc,
calendar_month_desc, t.time_id) gid_t,
SUM(amount_sold) s_sold,
COUNT(amount_sold) c_sold,
COUNT(*) cnt
FROM SALES s, TIMES t, PRODUCTS p
WHERE s.time_id = t.time_id AND
p.prod_name in ('Bounce', 'Y Box')   AND
s.prod_id = p.prod_id
GROUP BY 
ROLLUP(calendar_year, calendar_quarter_desc, 
calendar_month_desc, t.time_id),
ROLLUP(prod_category, prod_subcategory, p.prod_id);

�变�灏������鹃��跺�涓ょ�浜у�涓�������� 2200 澶�����娉ㄦ�锛�ierarchical_Time �������村�娆$����绾у��堕����绗�覆琛ㄧず褰㈠����浜�Hierarchical_Time ��� CASE 琛ㄨ揪寮����� (_0, _1, ...) 杩藉��版�涓�����绗�覆浠ヨ〃绀哄�����寸骇��0 琛ㄧず骞寸骇锛�1 琛ㄧず瀛e害锛�2 ���锛�3 ������娉ㄦ�锛�ROUP BY 瀛�����涓���ヨ���� ROLLUP锛��瀹���村�浜у�缁寸��风Н灞����ROUP BY 瀛��纭��灞��绔�������/p>

��缓涓�������MV_PROD_TIME �锋��稿�瀹�������CUBE_PROD_TIME���琛���㈢� SQL*Plus ���锛�/p>

cv1.sql ������浠ヤ����锛�/p>

CREATE OR REPLACE VIEW cube_prod_time
SELECT
(CASE
WHEN ((GROUPING(calendar_year)=0 )
AND (GROUPING(calendar_quarter_desc)=1 ))
THEN (TO_CHAR(calendar_year) || '_0')
WHEN ((GROUPING(calendar_quarter_desc)=0 )
AND (GROUPING(calendar_month_desc)=1 ))
THEN (TO_CHAR(calendar_quarter_desc) || '_1')
WHEN ((GROUPING(calendar_month_desc)=0 )
AND (GROUPING(t.time_id)=1 ))
THEN (TO_CHAR(calendar_month_desc) || '_2')
ELSE (TO_CHAR(t.time_id) || '_3') END) Hierarchical_Time,
calendar_year year,
calendar_quarter_desc quarter,
calendar_month_desc month,
t.time_id day,
prod_category cat,
prod_subcategory subcat,
p.prod_id prod,
GROUPING_ID(prod_category, prod_subcategory, p.prod_id,
calendar_year, calendar_quarter_desc, calendar_month_desc,
t.time_id) gid,
GROUPING_ID(prod_category, prod_subcategory, p.prod_id) gid_p, 
GROUPING_ID(calendar_year, calendar_quarter_desc,
calendar_month_desc, t.time_id) gid_t,
SUM(amount_sold) s_sold,
COUNT(amount_sold) c_sold,
COUNT(*) cnt
  FROM SALES s, TIMES t, PRODUCTS p
WHERE s.time_id = t.time_id AND
p.prod_name IN ('Bounce', 'Y Box') AND
s.prod_id = p.prod_id
GROUP BY 
ROLLUP(calendar_year, calendar_quarter_desc, 
calendar_month_desc, t.time_id),
ROLLUP(prod_category, prod_subcategory, p.prod_id);

��缓 EDGE_TIME 瑙��锛������寸��ユ��奸���DGE_TIME ������澶�����濉�ˉ�堕�绌洪���������DGE_TIME 涓�� HIERARCHICAL_TIME ����ㄥ�涓�CUBE_PROD_TIME 瑙����HIERARCHICAL_TIME ������杩��涓���ц�涓����SQL*Plus ���锛�/p>

cv2.sql ������浠ヤ����锛�/p>

CREATE OR REPLACE VIEW edge_time
SELECT
(CASE
WHEN ((GROUPING(calendar_year)=0 )
AND (GROUPING(calendar_quarter_desc)=1 ))
THEN (TO_CHAR(calendar_year) || '_0')
WHEN ((GROUPING(calendar_quarter_desc)=0 )
AND (GROUPING(calendar_month_desc)=1 ))
THEN (TO_CHAR(calendar_quarter_desc) || '_1')
WHEN ((GROUPING(calendar_month_desc)=0 )
AND (GROUPING(time_id)=1 ))
THEN (TO_CHAR(calendar_month_desc) || '_2')
ELSE (TO_CHAR(time_id) || '_3') END) Hierarchical_Time,
calendar_year yr,
calendar_quarter_number qtr_num,
calendar_quarter_desc qtr,
calendar_month_number mon_num,
calendar_month_desc mon,
time_id - TRUNC(time_id, 'YEAR') + 1 day_num,
time_id day,
GROUPING_ID(calendar_year, calendar_quarter_desc,
calendar_month_desc, time_id) gid_t
FROM TIMES
GROUP BY ROLLUP
(calendar_year,
(calendar_quarter_desc, calendar_quarter_number),
(calendar_month_desc, calendar_month_number), time_id);

�板�锛��澶��杩��姣���ヨ�������绱����互��换涓���寸骇杩���舵�涓��娈典��寸�姣��璁$�������灏������版部�堕�缁村��ㄥ�甯��瀵���版����娆$��广�瀵逛�姣�釜�堕�绾э����琛��璁$����锛�/p>

  • �ㄤ换涓���寸骇涓��绠��涓��娈甸�����诲�
  • 璁$�涓��涓��娈甸������樊寮�/li>
  • �ㄤ换涓���寸骇涓��绠��骞村�����舵����棰���诲�
  • 璁$�涓��涓�勾����舵����棰��宸��
  • 浠ヤ�绀轰�灏��琛��杩板�绉��绠��瀹�娇��CUBE_PROD_TIME ��EDGE_TIME 瑙������哄��ㄨ��ュ�寤哄�涓�DENSE_CUBE_PROD_TIME ������������瑙�����������涔�����绾у�绀轰�涓������规�锛��璇㈠苟浣跨� LAG �芥������WHERE 瀛�����浜�3 涓�骇����堕�锛�001 骞�8 ��腑���浜�ぉ锛��涓�8 ���2001 骞存�涓��涓��搴��璇锋敞���缁�������袱琛�������骇���搴�骇�昏���/p>

    �ц�涓����SQL*Plus ���锛�/p>

    mt.sql ������浠ヤ����锛�/p>

    SELECT
    substr(prod,1,4) prod, substr(Hierarchical_Time,1,12) ht, 
    sales,
    sales_prior_period,
    sales - sales_prior_period variance_prior_period,
    sales_same_period_prior_year,
    sales - sales_same_period_prior_year variance_same_period_p_year
    FROM:
    (SELECT cat, subcat, prod, gid_p, gid_t, Hierarchical_Time,
    yr, qtr, mon, day, sales,
    LAG(sales, 1) OVER (PARTITION BY gid_p, cat, subcat, prod,
    gid_t ORDER BY yr, qtr, mon, day) 
    sales_prior_period,
    LAG(sales, 1) OVER (PARTITION BY gid_p, cat, subcat, prod,
    gid_t, qtr_num, mon_num, day_num ORDER BY yr)
    sales_same_period_prior_year
    FROM:
    (SELECT c.gid, c.cat, c.subcat, c.prod, c.gid_p, 
    t.gid_t, t.yr, t.qtr, t.qtr_num, t.mon, t.mon_num,
    t.day, t.day_num, t.Hierarchical_Time, NVL(s_sold,0) sales
    FROM cube_prod_time c
    PARTITION BY (gid_p, cat, subcat, prod)
    RIGHT OUTER JOIN edge_time t
    ON ( c.gid_t = t.gid_t AND c.Hierarchical_Time = t.Hierarchical_Time)
    ) dense_cube_prod_time 
    )          -- side by side current,prior and prior year sales
    WHERE prod IN (139) AND gid_p=0 AND -- 1 product and product level data
    ( (mon IN ('2001-08' ) AND gid_t IN (0, 1) ) OR -- day and month data
    ( qtr IN ('2001-03' ) AND gid_t IN (3) ) ) -- quarter level data 
    ORDER BY day;

    瀵逛�寰�� OLAP 浠诲����锛��缁翠腑瀹�����涔��������������濡���ㄥ�浠ュ�涔��瀹���舵�浠ョ�浜�������互浣跨����澶�����灏����复�舵坊���缁翠腑���娉ㄦ�锛�racle �版�搴�10g 涓���ョ���SQL MODEL 瀛�����浜��寤烘���淮涓��������澶���规����浜��涓婚����缁�俊���璇峰���i>�版�浠��������涓��������浜�缓妯$� SQL���

    �ㄦ�缁��涓��灏�负 TIME 缁村�涔��涓�������� TIME 缁翠腑��缓绗�13 涓��绾ф����璇ョ� 13 涓��琚��涔�负 2001 骞存�瀛e害澶翠�涓��姣��浜у����棰���诲����浣跨����绀轰�涓��寤虹�瑙����〃��缓杩�釜瑙e��规���

    ��缓涓�釜������娣诲��扮�搴�淮����俱�璇ヨ��句娇��UNION ALL ���娣诲��版����瑕�娇�ㄨ�瀹������ヨ�锛�娇��CASE 琛ㄨ揪寮�����澶��������琛���㈢� SQL*Plus ���锛�/p>

    cv3.sql ������浠ヤ����锛�br>

    CREATE OR REPLACE VIEW time_c  AS
    (SELECT * 
    FROM edge_time
    UNION ALL
    SELECT '2001-13_2', 2001, 5, '2001-05', 13, '2001-13', null, null,
    FROM DUAL);

    �ㄦ�绀虹�璇��涓������ц����涓��涔�� EDGE_TIME 瑙����UNION ALL ����峰�涔��绗�13 涓��锛�� TIME_C 瑙��杩��浜��涔��UNION ALL ���浠�DUAL 琛ㄨ�琛�SELECT ����ㄤ�绗�13 涓������с�璇锋敞������ id锛�� gid_t锛��瀹�负 8锛��搴��璁惧�涓�5��

    �ヨ��������撅�濡����ず锛��琛�CUBE_PROD_TIME 涓�TIME_C ����哄��ㄨ��ャ�璇ユ�楠ゅ��ㄦ�涓�骇����荤骇涓虹� 13 涓����缓����版����涓绘�璇�腑锛�������SUM 涓�CASE 琛ㄨ揪寮��璧蜂娇�ㄤ互璁$�绗�13 涓���������虫�涓��搴���������诲����琛���㈢� SQL*Plus ���锛�/p>

    cv4.sql ������浠ヤ����锛�/p>

    SELECT * from
    SELECT substr(cat,1,12) cat, substr(subcat,1,12)  subcat,
    substr(prod,1,9) prod,  mon, mon_num,
    SUM(CASE WHEN mon_num IN (1, 4, 7, 10)
    THEN s_sold
    ELSE NULL
    OVER (PARTITION BY gid_p, prod, subcat, cat, yr) sales_month_13
    FROM:
    (SELECT c.gid, c.prod, c.subcat, c.cat,  gid_p,
    t.gid_t , t.day, t.mon, t.mon_num,
    t.qtr, t.yr, NVL(s_sold,0) s_sold
    FROM cube_prod_time c
    PARTITION BY   (gid_p, prod, subcat, cat)
    RIGHT OUTER JOIN time_c  t ON
    (c.gid_t = t.gid_t AND c.Hierarchical_Time = t.Hierarchical_Time)
    WHERE  mon_num=13;
    

    �ㄤ����杩��缁����SUM �芥����涓�CASE 璇��锛���ュ��ユ����涓烘�骞寸� 1������0 ����变��版����灏�����涓や釜浜у�锛������风Н�兼�杈��绾у�������瑕��澶��瑕��寰��瀹�����绉������ㄥ�浠ュ�搴���╁�瑙��涓�����澶��Game Console����� Box Games���绫诲�涓��浜у���/p>