OBE 涓婚〉 > 10gR2 VMware > 浣跨敤鍒嗗尯澶栭儴鑱旀帴濉ˉ绋€鐤忔暟鎹腑鐨勭┖闅�

浣跨敤鍒嗗尯澶栭儴鑱旀帴濉ˉ绋€鐤忔暟鎹腑鐨勭┖闅�

鍦ㄦ鏁欑▼涓偍灏嗗涔犲浣曚娇鐢� Oracle 鏁版嵁搴� 10g SQL 涓柊鐨� SQL 鑱旀帴璇硶濉ˉ绋€鐤忔暟鎹腑鐨勭┖闅欍€�

澶х害 30 鍒嗛挓

鏈暀绋嬩粙缁嶄簡浠ヤ笅涓婚锛�

灏嗗厜鏍囩疆浜庢鍥炬爣涓婁互鍔犺浇鍜屾煡鐪嬫湰鏁欑▼鐨勬墍鏈夊睆骞曟埅鍥俱€傦紙璀﹀憡锛氭鎿嶄綔浼氬悓鏃跺姞杞芥墍鏈夊睆骞曟埅鍥撅紝鎵€浠ョ綉閫熻緝鎱㈡椂锛屽搷搴旀椂闂村彲鑳戒細姣旇緝闀裤€傦級

娉ㄦ剰锛�姝ゅ锛屾偍鍙互灏嗗厜鏍囨斁鍦ㄤ笅鍒楁楠ょ殑鍗曚釜鍥炬爣涓婏紝浠ヤ究浠呭姞杞藉拰鏌ョ湅涓庤姝ョ浉鍏崇殑灞忓箷鎴浘銆傛偍鍙互閫氳繃鍗曞嚮鍗曚釜灞忓箷鎴浘灏嗗叾闅愯棌銆�

Oracle 鏁版嵁搴� 10g 鍒嗗尯澶栭儴鑱旀帴瀛愬彞姒傝堪

鏁版嵁閫氬父浠ョ█鐤忓舰寮忚繘琛屽瓨鍌ㄣ€備篃灏辨槸璇达紝濡傛灉鍦ㄦ煇涓€缁欏畾鏃堕棿娌℃湁浠讳綍鍊煎瓨鍦紝鍒欏湪浜嬪疄琛ㄤ腑娌℃湁琛屽瓨鍦ㄣ€傜劧鑰岋紝濡傛灉鏃堕棿缁翠笂鐨勬暟鎹瘑闆嗭紝鍒欐墽琛屾椂闂村簭鍒楄绠楁渶涓虹畝鍗曘€傝繖鏄洜涓哄瘑闆嗘暟鎹浜庢瘡涓椂娈垫墍濉厖鐨勮鏁颁竴鑷达紝浠庤€屾洿鏄撲簬浣跨敤甯︽湁鐗╃悊鍋忕Щ鐨勫垎鏋愮獥鍙e嚱鏁般€傝鍙傝€�绗� 21 绔狅細鏁版嵁浠撳簱鎸囧崡 浠ヨ幏寰楁洿澶氫俊鎭€�

瑕佹兂鍏嬫湇绋€鐤忛棶棰橈紝鎮ㄥ彲浠ヤ娇鐢ㄥ垎鍖哄閮ㄨ仈鎺ュ~琛ユ椂闂村簭鍒椾腑鐨勭┖闅欍€傛绉嶈仈鎺ラ€氳繃灏嗗閮ㄨ仈鎺ュ簲鐢ㄤ簬鏌ヨ涓畾涔夌殑鍚勪釜閫昏緫鍒嗗尯锛屾墿灞曚簡甯歌澶栭儴鑱旀帴璇硶銆侽racle 鏁版嵁搴撴牴鎹� PARTITION BY 瀛愬彞涓寚瀹氱殑琛ㄨ揪寮忥紝瀵规煡璇腑鐨勮杩涜閫昏緫鍒嗗尯銆傚垎鍖哄閮ㄨ仈鎺ョ殑缁撴灉鏄€昏緫鍒嗗尯琛ㄤ腑鍚勪釜缁勭殑澶栭儴鑱旀帴涓庤仈鎺ュ彟涓€渚х殑琛ㄧ殑鑱斿悎銆�

璇锋敞鎰忥紝鎮ㄥ彲浠ヤ娇鐢ㄨ繖绉嶇被鍨嬬殑鑱旀帴濉ˉ浠讳綍缁达紙鑰屼笉浠呬粎鏄椂闂寸淮锛変腑鐨勭┖闅欍€傛湰鏁欑▼灏嗕笓娉ㄤ簬鏃堕棿缁达紝鍥犱负瀹冩槸鏈€甯哥敤浣滄瘮杈冨熀纭€鐨勭殑缁淬€�

鍒嗗尯澶栭儴鑱旀帴鐨勮娉曚负 PARTITION BY 鍔犺〃杈惧紡鍒楄〃锛屽畠鎵╁睍浜� ANSI SQL JOIN 瀛愬彞銆傛鍒楄〃涓殑琛ㄨ揪寮忔寚瀹氫簡瑕佸簲鐢ㄥ閮ㄨ仈鎺ョ殑缁勩€備互涓嬫槸閫氬父鐢ㄤ簬鍒嗗尯澶栭儴鑱旀帴鐨勪袱绉嶈娉曞舰寮忥細

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

璇锋敞鎰忥紝鍒嗗尯澶栭儴鑱旀帴涓嶆敮鎸� FULL OUTER JOIN锛堝畬鍏ㄥ閮ㄨ仈鎺ワ級銆傝鍙傞槄 Oracle 鏁版嵁搴� 10g SQL 鍙傝€� 浠ヨ幏鍙栨湁鍏宠娉曞拰闄愬埗鐨勮繘涓€姝ヤ俊鎭€�

浠ヤ笅绀轰緥鏄竴涓吀鍨嬬殑绋€鐤忕淮鎯呭舰锛屽叾涓皢璁$畻浜у搧鈥淏ounce鈥�2000 骞村拰 2001 骞翠腑绗� 20–30 鍛ㄧ殑鍛ㄩ攢鍞鍜屼粠骞村垵鑷充粖鐨勯攢鍞锛�

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 鍛ㄧ殑鏁版嵁銆�

鏃堕棿搴忓垪涓殑绌洪殭浼氫娇璇稿鎸夊勾搴﹁繘琛屾瘮杈冧箣绫荤殑璁$畻闅句互杩涜銆傚湪娌℃湁绌洪殭鏃讹紝鍙互閫氳繃浣跨敤鍒嗘瀽鍑芥暟 LEAD() 鍜� LAG()锛屼粠涓€琛屽埌鍥哄畾闂撮殧浠ュ鐨勫彟涓€琛岃繘琛屽弬鑰冩潵姣旇緝鏁版嵁銆備緥濡傦紝濡傛灉鎮ㄦ敹鍒颁簡鏈堝害鏁版嵁锛屽苟甯屾湜鍙傝€� 12 涓湀浠ュ墠鐨勬暟鎹紝閭d箞锛岃闂綋鍓嶅€� 12 琛屼箣鍓嶇殑鏁版嵁寰堟柟渚裤€傚鏋滄瘡涓椂娈碉紙鎴栧叾浠栦换浣曠敤浣滃垎闅旂殑缁达級鐨勮鏁颁笉涓€鑷达紝浣跨敤 LEAD() 鍜� LAG() 鍑芥暟鍒欎細涓嶅彲闈犮€�

濡備綍鐢ㄥ垎鍖哄閮ㄨ繛鎺ュ~琛ュ墠闈㈢ず渚嬩腑鐨勭┖闅欙紵

鍙互鐢ㄥ瘑闆嗙殑鏃堕棿鏁版嵁闆嗗鎴戜滑鏌ヨ涓殑绋€鐤忔暟鎹繘琛屼竴娆″垎鍖哄閮ㄨ仈鎺ャ€傚湪浠ヤ笅鎵€绀虹殑鏌ヨ涓紝鍘熷鏌ヨ浠� v 涓哄埆鍚嶏紝浠庢椂闂磋〃妫€绱㈠埌鐨勬暟鎹互 t 涓哄埆鍚嶃€傜幇鍦ㄦ偍灏嗙湅鍒� 22 琛屾暟鎹紝鍥犱负搴忓垪涓病鏈夌┖闅欍€傛墍娣诲姞鐨� 4 琛屼腑锛屾瘡琛岀殑閿€鍞€奸兘涓� 0 鈥� 杩欎簺鍊奸€氳繃浣跨敤 NVL() 鍑芥暟璁剧疆涓轰簡 0銆�

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 鍑芥暟鐩哥粨鍚堜互鑾峰緱鎵€闇€鍚勫懆鐨勭疮绉攢鍞锛�

1.

浠庣粓绔獥鍙d腑锛屾墽琛屼互涓嬪懡浠わ細

cd /home/oracle/wkdir
sqlplus sh/sh
@fg

fg.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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
)  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)
) t
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銆�

鍦ㄦ煇浜涙煡璇腑锛屽垎鍖哄閮ㄨ繛鎺ュ皢杩斿洖甯︽湁 NULL 鍊肩殑琛岋紝浣嗘偍鍙兘甯屾湜杩欎簺琛屼繚瀛樺簭鍒椾腑鏈€杩戠殑闈� NULL 鍊笺€傚嵆锛屾偍鏄惁甯屾湜鍦ㄥ垪涓繘琛屽悜涓婃壂鎻忔椂锛岀敤鐪嬪埌鐨勭涓€涓潪 NULL 鍊兼浛鎹� NULL銆�

璺熻釜涓嶅悓浜у搧鐨勫彲鐢ㄥ崟浣嶆暟閲忕殑搴撳瓨琛紝鏄渶瑕佹绫昏緭鍑虹殑涓€绉嶅父瑙佹儏鍐点€傚簱瀛樿〃鏄█鐤忕殑锛氫笌閿€鍞〃鐩稿悓锛屽畠浠彧闇€鍦ㄦ湁浜嬩欢鍙戠敓鏃堕拡瀵逛骇鍝佸瓨鍌ㄨ銆傚浜庨攢鍞〃鏉ヨ锛屼簨浠舵槸閿€鍞紱瀵逛簬搴撳瓨琛ㄦ潵璇达紝浜嬩欢鏄彲鐢ㄤ骇鍝佹暟閲忕殑鍙樺寲銆傚鏋滃浜庡簱瀛樹娇鐢ㄥ瘑闆嗙殑鏃堕棿缁达紝鍒欒〃绀虹敤鎴峰笇鏈涚湅鍒版瘡涓€澶╃殑鏁伴噺鍊笺€傝杈撳嚭鐨勫€兼槸鏈€杩戠殑闈� NULL 鍊笺€傝娉ㄦ剰锛岃繖涓庝箣鍓嶇殑绱Н閿€鍞ず渚嬩笉鍚屻€傚湪閭d釜鏌ヨ涓紝绱Н鎬诲拰璁$畻灏� NULL 閮借涓� 0锛屽洜姝ゆ樉绀轰簡姝g‘鍊笺€傞偅绉嶆柟娉曚笉閫傜敤浜庡簱瀛樿〃鍙婁笌鍏剁被浼肩殑琛紝鍥犱负鏀剧疆鍦ㄥ甫鏈� NULL 鐨勮涓殑鍊间笉鏄€诲拰銆�

浠ヤ笅绀轰緥涓殑搴撳瓨琛ㄥ甫鏈夊垎鍖哄閮ㄨ仈鎺ャ€傚畠灏嗙敤鏈€杩戠殑闈� NULL 鍊兼浛鎹� NULL銆�

1.

棣栧厛锛屽垱寤轰竴涓湁涓ょ浜у搧鐨勫皬搴撳瓨琛紝姣忕浜у搧鏈変袱澶╃殑甯愮洰銆備骇鍝佲€渂ottle鈥濆湪 4 鏈� 1 鏃ュ簱瀛樹负 10 涓崟浣嶏紝鍦� 4 鏈� 6 鏃� 涓� 8 涓崟浣嶃€備骇鍝佲€渃an鈥濆湪 4 鏈� 1 鏃ュ簱瀛樹负 15 涓崟浣嶏紝鍦� 4 鏈� 4 鏃� 涓� 11 涓崟浣嶃€傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@ci

ci.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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);

灏嗛紶鏍囩Щ鍒拌鍥炬爣涓婂彲浠ユ煡鐪嬭鍥惧儚

2.

鐜板湪锛屽皢浣跨敤鍒嗗尯澶栭儴鑱旀帴鏌ョ湅浠� 4 鏈� 1 鏃ヨ嚦 7 鏃ユ湡闂存瘡绉嶄骇鍝佺殑鍙敤鏁伴噺銆傚鏋滀笉鑰冭檻甯︽湁 NULL 鍊肩殑琛岋紝灏变娇鐢ㄥ垎鍖哄閮ㄨ仈鎺ユ煡璇㈣繖涓〃锛屽鏄撳緱鍒伴敊璇粨鏋溿€傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@nn

nn.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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銆�

3.

涓烘樉绀烘墍闇€缁撴灉锛岄渶鍒╃敤 Oracle 鏁版嵁搴� 10g 涓坊鍔犲埌 FIRST_VALUE 鍜� LAST_VALUE 鍑芥暟鐨勬柊鍏抽敭瀛椼€傚浜庤繖浜涘嚱鏁颁腑鐨勪换涓€鍑芥暟锛岄兘鍙互鍦ㄥ叾鍙傛暟鍒楄〃涓寚瀹� IGNORE NULLS锛屽畠浠皢杩斿洖鏈€杩戠殑闈� NULL 鍊笺€傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@nn2

nn2.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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;

灏嗛紶鏍囩Щ鍒拌鍥炬爣涓婂彲浠ユ煡鐪嬭鍥惧儚

鍦ㄤ笅涓€涓换鍔′腑锛屾偍灏嗕娇鐢ㄥ閮ㄨ仈鎺ョ壒鎬ц法鏃舵姣旇緝鍊笺€傜壒鍒槸锛屾偍灏嗘寜鍛ㄨ绠楀勾涓庡勾涔嬮棿鐨勯攢鍞瘮杈冦€傛煡璇㈠皢鍦ㄥ悓涓€琛屼腑鏄剧ず閽堝姣忕浜у搧杩斿洖鐨勫叾 2001 骞村拰 2000 骞存瘡鍛ㄥ勾鍒濊嚦浠婄殑閿€鍞銆�

1.

瑕佹彁楂樻煡璇㈢殑鍙鎬у苟涓撴敞浜庡垎鍖哄閮ㄨ仈鎺ワ紝浣跨敤涓€涓� WITH 瀛愬彞寮€濮嬫煡璇€€傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@pp

pp.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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)
) t
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 鏉ュ鐞嗗垎鍖哄閮ㄨ仈鎺ョ殑杈撳嚭锛屼互璁$畻姣忓懆骞村垵鑷充粖鐨勯攢鍞锛堚€渨eekly_ytd_sales鈥濆垪锛夈€傚洜姝わ紝瑙嗗浘 DENSE_SALES 灏嗚绠楀嚭姣忓懆鐨勫勾鍒濊嚦浠婇攢鍞暟鎹紙鍖呮嫭鍦ㄨ仛鍚堣鍥� s 涓己澶辩殑鏁版嵁锛夈€�

鐒跺悗锛屽唴鑱旇鍥� YEAR_OVER_YEAR_SALES 浣跨敤 LAG 鍑芥暟璁$畻涓€骞村墠鐨勬瘡鍛ㄥ勾鍒濊嚦浠婇攢鍞銆傛爣璁颁负鈥渨eekly_ytd_sales_prior_year鈥濈殑 LAG 鍑芥暟灏嗘寚瀹氫竴涓� PARTITION BY 瀛愬彞锛屼互灏� 2000 骞村拰 2001 骞村悓涓€鍛ㄧ殑琛岄厤瀵瑰埌鍗曚釜鍒嗗尯涓€傚苟灏嗗亸绉婚噺 1 浼犻€掔粰 LAG 鍑芥暟浠ヨ幏寰楀墠涓€骞寸殑姣忓懆骞村垵鑷充粖閿€鍞銆�

鏈€澶栭潰鐨勬煡璇㈠潡鐢� yr = 2001 鏉′欢浠� YEAR_OVER_YEAR_SALES 閫夋嫨鏁版嵁锛屽洜鑰屾煡璇㈣繑鍥炴瘡绉嶄骇鍝佸湪 2001 骞村拰 2000 骞存寚瀹氬懆涓殑姣忓懆骞村垵鑷充粖閿€鍞銆�

灏界鍓嶉潰鐨勭ず渚嬫紨绀轰簡鎸夊崟涓椂闂存爣鍑嗗垱寤烘瘮杈冪殑鏂规硶锛屼絾鏄紝鍦ㄥ崟涓煡璇腑澶勭悊澶氭椂闂寸骇鏇翠负瀹炵敤銆備緥濡傦紝鎮ㄥ彲浠ユ寜骞淬€佸搴︺€佹湀銆佸ぉ绛夌浉瀵逛簬鍓嶄竴鏃舵杩涜閿€鍞姣旇緝銆�

涓嬩竴涓换鍔′腑锛屽皢瑕佸垱寤轰竴涓煡璇紝鎸夋垜浠椂闂村眰娆$殑鎵€鏈夌骇鍒骞村垵鑷充粖閿€鍞鎵ц骞翠笌骞翠箣闂寸殑瀵规瘮銆�

鎵ц姝や换鍔¢渶瑕佽嫢骞叉楠ゃ€傜洰鏍囨槸鏋勫缓鍗曚竴鏌ヨ锛屼互鎸夊ぉ銆佸懆銆佹湀銆佸搴︺€佸勾鍚勭骇杩涜姣旇緝銆傛偍灏嗙敤鍒扮墿鍖栬鍥� MV_PROD_TIME锛岃瑙嗗浘鍏锋湁涓€涓法 TIMES 鍜� PRODUCTS 鑱氬悎鐨勯攢鍞灞傛绔嬫柟銆傚湪杩欎釜鐗╁寲瑙嗗浘涔嬩笂灏嗗垱寤哄彟涓€涓鍥俱€傛澶栵紝杩橀渶鍒涘缓涓€涓椂闂寸淮瑙嗗浘浠ョ敤浣滅珛鏂圭殑杈广€傛鏃堕棿杈瑰皢鎴愪负鍒扮墿鍖栬鍥句腑绋€鐤忔暟鎹殑鍒嗗尯澶栭儴鑱旀帴銆�

鍏充簬灞傛绔嬫柟鐨勬洿澶氫俊鎭紝璇峰弬闃� 鏁版嵁浠撳簱鍙傝€冩寚鍗� 涓€滃湪鏁版嵁浠撳簱涓€傜敤浜庤仛鍚堢殑 SQL鈥濅竴绔犮€�

1.

鍒涘缓鐗╁寲瑙嗗浘銆傝娉ㄦ剰锛屾煡璇粎闄愪簬涓ょ浜у搧浠ヤ究缂╃煭澶勭悊鏃堕棿銆傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@cm1

cm1.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

CREATE MATERIALIZED VIEW mv_prod_time
REFRESH COMPLETE ON DEMAND
AS
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 瀛愬彞纭畾灞傛绔嬫柟鍐呭銆�

2.

鍒涘缓涓庣墿鍖栬鍥� MV_PROD_TIME 鍏锋湁鐩稿悓瀹氫箟鐨勮鍥� CUBE_PROD_TIME銆傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@cv1

cv1.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

CREATE OR REPLACE VIEW cube_prod_time
AS
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);

灏嗛紶鏍囩Щ鍒拌鍥炬爣涓婂彲浠ユ煡鐪嬭鍥惧儚

3.

鍒涘缓 EDGE_TIME 瑙嗗浘锛屽畠鏄畬鏁寸殑鏃ユ湡鍊奸泦銆侲DGE_TIME 鏄敤鍒嗗尯澶栭儴鑱旀帴濉ˉ鏃堕棿绌洪殭鎵€鐢ㄧ殑婧愩€侲DGE_TIME 涓殑 HIERARCHICAL_TIME 鍒楀皢鐢ㄥ湪涓� CUBE_PROD_TIME 瑙嗗浘鐨� HIERARCHICAL_TIME 鍒楃殑鍒嗗尯杩炴帴涓€傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@cv2

cv2.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

CREATE OR REPLACE VIEW edge_time
AS
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);

灏嗛紶鏍囩Щ鍒拌鍥炬爣涓婂彲浠ユ煡鐪嬭鍥惧儚

4.

鐜板湪锛屽叿澶囦簡杩涜姣旇緝鏌ヨ鎵€闇€鐨勫厓绱犮€傚彲浠ユ寜浠讳竴鏃堕棿绾ц繘琛屾椂娈典笌鏃舵涔嬮棿鐨勬瘮杈冭绠椼€傚畠闇€瑕佸皢鍒嗘瀽鍑芥暟娌挎椂闂寸淮搴旂敤鍒板甫鏈夊瘑闆嗘暟鎹殑灞傛绔嬫柟銆傚浜庢瘡涓椂闂寸骇锛屽彲鎵ц鐨勮绠楀寘鎷細

  • 鍦ㄤ换涓€鏃堕棿绾т笂璁$畻鍓嶄竴鏃舵閿€鍞鎬诲拰
  • 璁$畻涓庡墠涓€鏃舵閿€鍞鐨勫樊寮�
  • 鍦ㄤ换涓€鏃堕棿绾т笂璁$畻涓€骞村墠鍚屼竴鏃舵閿€鍞鐨勬€诲拰
  • 璁$畻涓庝笂涓€骞村悓涓€鏃舵閿€鍞鐨勫樊寮�

浠ヤ笅绀轰緥灏嗘墽琛屼笂杩板洓绉嶈绠椼€傚畠浣跨敤 CUBE_PROD_TIME 鍜� EDGE_TIME 瑙嗗浘鐨勫垎鍖哄閮ㄨ仈鎺ュ垱寤哄悕涓� DENSE_CUBE_PROD_TIME 鐨勫瘑闆嗘暟鎹殑鍐呭祵瑙嗗浘銆傜劧鍚庯紝鎸変笌涔嬪墠鍗曚竴绾у埆绀轰緥涓浉鍚岀殑鏂规硶锛屾煡璇㈠苟浣跨敤 LAG 鍑芥暟銆傚閮� WHERE 瀛愬彞鎸囧畾浜� 3 涓骇鍒殑鏃堕棿锛�2001 骞� 8 鏈堜腑鐨勬煇浜涘ぉ锛屾暣涓� 8 鏈堬紝2001 骞存暣涓涓夊搴︺€傝娉ㄦ剰锛岀粨鏋滅殑鏈€鍚庝袱琛屽皢鍖呭惈鏈堢骇鍜屽搴︾骇鎬昏銆�

鎵ц涓嬮潰鐨� SQL*Plus 鑴氭湰锛�

@mt

mt.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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 瀛愬彞閫傜敤浜庡垱寤烘秹鍙婄淮涓柊鎴愬憳鐨勬洿澶嶆潅鏂规銆傚叧浜庢涓婚鐨勮缁嗕俊鎭紝璇峰弬闃�鏁版嵁浠撳簱鍙傝€冩寚鍗�涓殑鏁欑▼鈥滅敤浜庡缓妯$殑 SQL鈥濄€�

鍦ㄦ湰缁冧範涓紝灏嗕负 TIME 缁村畾涔変竴涓柊鎴愬憳銆傚湪 TIME 缁翠腑鍒涘缓绗� 13 涓湀绾ф垚鍛樸€傝绗� 13 涓湀琚畾涔変负 2001 骞存瘡瀛e害澶翠竴涓湀姣忕浜у搧閿€鍞鐨勬€诲拰銆傚皢浣跨敤鍓嶉潰绀轰緥涓垱寤虹殑瑙嗗浘鍜岃〃鏋勫缓杩欎釜瑙e喅鏂规銆�

1.

鍒涘缓涓€涓湁鏂版垚鍛樻坊鍔犲埌鐩稿簲缁寸殑瑙嗗浘銆傝瑙嗗浘浣跨敤 UNION ALL 鎿嶄綔娣诲姞鏂版垚鍛樸€傝浣跨敤鑷畾涔夋垚鍛樻煡璇紝浣跨敤 CASE 琛ㄨ揪寮忓拰鍒嗗尯澶栭儴鑱旀帴銆傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@cv3

cv3.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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,
8 -- 
FROM DUAL);

鍦ㄦ樉绀虹殑璇彞涓紝閫氳繃鎵ц鍓嶄緥涓畾涔夌殑 EDGE_TIME 瑙嗗浘鐨� UNION ALL 鍜岀敤鎴峰畾涔夌殑绗� 13 涓湀锛屽 TIME_C 瑙嗗浘杩涜浜嗗畾涔夈€俇NION ALL 閫氳繃浠� DUAL 琛ㄨ繘琛� SELECT 鎸囧畾鐢ㄤ簬绗� 13 涓湀鐨勫睘鎬с€傝娉ㄦ剰锛屽垎缁� id锛堝垪 gid_t锛夎瀹氫负 8锛屽搴︽暟璁惧畾涓� 5銆�

灏嗛紶鏍囩Щ鍒拌鍥炬爣涓婂彲浠ユ煡鐪嬭鍥惧儚

2.

鏌ヨ鐨勫唴鑱旇鍥撅紙濡備笅鎵€绀猴級鎵ц CUBE_PROD_TIME 涓� TIME_C 鐨勫垎鍖哄閮ㄨ仈鎺ャ€傝姝ラ灏嗗湪姣忎釜浜у搧姹囨€荤骇涓虹 13 涓湀鍒涘缓閿€鍞暟鎹€傚湪涓绘煡璇腑锛屽垎鏋愬嚱鏁� SUM 涓� CASE 琛ㄨ揪寮忎竴璧蜂娇鐢ㄤ互璁$畻绗� 13 涓湀鐨勬暟鎹紝鍗虫瘡涓搴﹂鏈堢殑閿€鍞€诲拰銆傛墽琛屼笅闈㈢殑 SQL*Plus 鑴氭湰锛�

@cv4

cv4.sql 鑴氭湰鍖呭惈浠ヤ笅鍐呭锛�

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
END)
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銆�4銆�7銆�10 鏈堛€傜敱浜庢暟鎹泦寰堝皬锛屽彧鏈変袱涓骇鍝侊紝缁撴灉鐨勫嵎绉€兼槸杈冧綆绾у埆鑱氬悎鐨勫繀瑕侀噸澶嶃€傝鑾峰緱鏇村疄闄呯殑鍗风Н鍊奸泦锛屾偍鍙互鍦ㄥ簳灞傜墿鍖栬鍥句腑鍖呭惈鏇村鈥淕ame Console鈥濆拰鈥淵 Box Games鈥濆瓙绫诲埆涓殑浜у搧銆�

鍦ㄦ湰鏁欑▼涓紝鎮ㄥ涔犱簡濡備綍鎵ц涓嬪垪浠诲姟锛�

浣跨敤鍒嗘瀽 SQL 鍑芥暟濉ˉ鏁版嵁涓殑绌洪殭
鐢ㄦ渶杩戠殑闈� NULL 鍊兼浛鎹� NULL
鎵ц鏃舵涓庢椂娈电殑姣旇緝
鍦ㄧ淮涓垱寤鸿嚜瀹氫箟鎴愬憳

杩斿洖涓婚鍒楄〃