鍦ㄦ鏁欑▼涓偍灏嗗涔犲浣曚娇鐢� Oracle 鏁版嵁搴� 10g SQL 涓柊鐨� SQL 鑱旀帴璇硶濉ˉ绋€鐤忔暟鎹腑鐨勭┖闅欍€�
澶х害 30 鍒嗛挓
鏈暀绋嬩粙缁嶄簡浠ヤ笅涓婚锛�
![]() |
姒傝堪 |
![]() |
璇硶 |
![]() |
绋€鐤忔暟鎹ず渚� |
![]() |
濉ˉ鏁版嵁涓殑绌洪殭 |
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
|
![]() |
鎬荤粨 |
灏嗗厜鏍囩疆浜庢鍥炬爣涓婁互鍔犺浇鍜屾煡鐪嬫湰鏁欑▼鐨勬墍鏈夊睆骞曟埅鍥俱€傦紙璀﹀憡锛氭鎿嶄綔浼氬悓鏃跺姞杞芥墍鏈夊睆骞曟埅鍥撅紝鎵€浠ョ綉閫熻緝鎱㈡椂锛屽搷搴旀椂闂村彲鑳戒細姣旇緝闀裤€傦級
娉ㄦ剰锛�姝ゅ锛屾偍鍙互灏嗗厜鏍囨斁鍦ㄤ笅鍒楁楠ょ殑鍗曚釜鍥炬爣涓婏紝浠ヤ究浠呭姞杞藉拰鏌ョ湅涓庤姝ョ浉鍏崇殑灞忓箷鎴浘銆傛偍鍙互閫氳繃鍗曞嚮鍗曚釜灞忓箷鎴浘灏嗗叾闅愯棌銆�
鏁版嵁閫氬父浠ョ█鐤忓舰寮忚繘琛屽瓨鍌ㄣ€備篃灏辨槸璇达紝濡傛灉鍦ㄦ煇涓€缁欏畾鏃堕棿娌℃湁浠讳綍鍊煎瓨鍦紝鍒欏湪浜嬪疄琛ㄤ腑娌℃湁琛屽瓨鍦ㄣ€傜劧鑰岋紝濡傛灉鏃堕棿缁翠笂鐨勬暟鎹瘑闆嗭紝鍒欐墽琛屾椂闂村簭鍒楄绠楁渶涓虹畝鍗曘€傝繖鏄洜涓哄瘑闆嗘暟鎹浜庢瘡涓椂娈垫墍濉厖鐨勮鏁颁竴鑷达紝浠庤€屾洿鏄撲簬浣跨敤甯︽湁鐗╃悊鍋忕Щ鐨勫垎鏋愮獥鍙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 |
PRODUCT_NAME YEAR WEEK SALES 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 |
PRODUCT_NAME YEAR WEEK DENSE_SALES |
璇锋敞鎰忥紝鍦ㄤ互涓婃煡璇腑锛屾妸鐢ㄤ簬绗� 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銆�
鍦ㄤ笅涓€涓换鍔′腑锛屾偍灏嗕娇鐢ㄥ閮ㄨ仈鎺ョ壒鎬ц法鏃舵姣旇緝鍊笺€傜壒鍒槸锛屾偍灏嗘寜鍛ㄨ绠楀勾涓庡勾涔嬮棿鐨勯攢鍞瘮杈冦€傛煡璇㈠皢鍦ㄥ悓涓€琛屼腑鏄剧ず閽堝姣忕浜у搧杩斿洖鐨勫叾 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 -- 鍦ㄦ樉绀虹殑璇彞涓紝閫氳繃鎵ц鍓嶄緥涓畾涔夌殑 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 | |
![]() |
鎵ц鏃舵涓庢椂娈电殑姣旇緝 | |
![]() |
鍦ㄧ淮涓垱寤鸿嚜瀹氫箟鎴愬憳 |