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;
�ㄦ�缁��涓��灏�负 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>