原創文章，轉載請注明出處，謝謝合作，
https://blog.csdn.net/DarianMograine/article/details/108561662
日前，筆者在作業中被人問到這樣一個問題：
Oracle資料庫中，如何優化取每一行資料在當前表中同分組資料的匯總，
當前表取出資料量級1W+，原邏輯是先取每行后對表進行N次訪問每次讀取資料，雖然是主鍵回表匯總金額，由于資料條數過多，頁面回傳時間無法預期，希望能夠通過SQL層面優化，
如下圖所示，希望得到每日當月累計營業額：
以上是問題的背景，
實際上，類似場景有很多，如：
- 匯總部分門店每日當月累計營業額
- 按規則自動分組匹配（銀行對賬、核銷、單據對比等）
這里給大家推薦一個思路，利用遞回+分組開窗函式+OLAP分析函式一個SQL實作資料的抓取，
我們先來看下實作的代碼：
- 創建演示表
CREATE TABLE test_cb
(key NUMBER
,iodate DATE
,amount NUMBER);
- 初始化模擬資料
INSERT INTO test_cb
(key, iodate, amount)
SELECT 1 key
,DATE '2020-05-01' iodate
,100 amount
FROM dual
UNION ALL
SELECT 1 key
,DATE '2020-05-01' iodate
,150 amount
FROM dual
UNION ALL
SELECT 1 key
,DATE '2020-05-03' iodate
,120 amount
FROM dual
UNION ALL
SELECT 2 key
,DATE '2020-05-01' iodate
,80 amount
FROM dual
UNION ALL
SELECT 2 key
,DATE '2020-05-02' iodate
,230 amount
FROM dual
UNION ALL
SELECT 2 key
,DATE '2020-05-04' iodate
,710 amount
FROM dual;
- 抓取資料的SQL代碼
SELECT sub2.key
,sub2.iodate
,sub2.amount "當前行金額"
,dbms_aw.eval_number(substr(sys_connect_by_path(sub2.amount, '+'), 2)) "匯總到當前行金額"
,lag(dbms_aw.eval_number(substr(sys_connect_by_path(sub2.amount, '+')
,2))) over(PARTITION BY sub2.key ORDER BY sub2.iodate) "匯總到上一行金額"
FROM (SELECT sub1.key
,sub1.iodate
,SUM(sub1.amount) amount
,row_number() over(PARTITION BY sub1.key ORDER BY sub1.iodate) rn
FROM test_cb sub1
GROUP BY sub1.iodate
,sub1.key) sub2
CONNECT BY nocycle(PRIOR sub2.rn = sub2.rn - 1
AND PRIOR sub2.key = sub2.key)
START WITH rn = 1
接下來我們來看一下這段代碼是怎么解決的這個問題：
-
我們利用分析函式row_number對資料以欄位key為分組依據做分組牌序，得到每行資料展示的順序
-
利用遞回天然有序的特性，將之前行的金額形成形如“+200+100+150”這樣的字串并去掉第一個+，這樣我們就得到一個字串200+100+150，亦即代碼中**substr(sys_connect_by_path(sub2.amount, ‘+’), 2)**部分，
-
利用OLAP函式dbms_aw.eval_number計算200+100+150的值，類似excel里=200+100+150的功能得到450，
-
利用分析函式lag在以key為基準，以iodate為排序欄位的分組中獲取上一行的步驟3金額，即得到上一行的匯總金額，
優化后的SQL中由于使用的是Oracle的內置函式，速度基本可以控制在2s以內，
是不是很神奇？感興趣的筒子可以一試，
原創文章，轉載請注明出處，謝謝合作，
https://blog.csdn.net/DarianMograine/article/details/108561662
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/38166.html
標籤：其他
上一篇：MySQL之基礎總結部分