GaussDB(DWS)性能调优：窗口函数聚合慢分析及提前聚合改进案例
【摘要】 窗口函数是同时获取明细和汇总数据的利器，但是也是SQL中容易出现瓶颈的一类函数。本篇旨在从一典型案例入手，解析窗口函数瓶颈的场景下，通过调优聚合函数通用的提前聚合方法，减少窗口函数对SQL性能的影响。
1、问题描述
SQL语句存在窗口函数对数据进行聚合，出现聚合慢成为瓶颈的现象。
2、原始SQL
explain performance
WITH TMP AS ( SELECT PERIOD_ID
,TARGET AS LGST_CYCLE_GOAL
,ATTAIN_RATE_GOAL AS LGST_CYCLE_ATTAIN_RATE_GOAL
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_CURR
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_CYCLE_TARGET_DTL_F
WHERE PROJECT_PACKAGE = 'PKG_OM_LGST'
AND CYCLE_CODE = '1000'
AND (IS_OVERSEA = 'ALL' OR IS_OVERSEA = 'N')
AND (PERIOD_ID BETWEEN '202301' AND '202308')
UNION ALL
SELECT DTL.PERIOD_ID_M AS PERIOD_ID
,0 AS LGST_CYCLE_GOAL
,0 AS LGST_CYCLE_ATTAIN_RATE_GOAL
,1 AS TOTAL_PACK_CURR
,MAX(DECODE(DTL.IS_LOGISTICS_E2E_TIME, 'Y', DTL.LOGISTICS_E2E_TIME, 0)) AS LGST_CYCLE_VALUE_CURR
,NULL AS LGST_CYCLE_ATTAIN_CURR
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_LOGISTICS_CYCLE_DTL_F DTL
WHERE ((1=1))
AND 'N' = DTL.IS_OVERSEA
AND DTL.FG_MF_FLAG = ANY(STRING_TO_ARRAY('FG',',')) AND DTL.ERP_SHIP_METHOD_NAME_EN = ANY(STRING_TO_ARRAY('BY AIR,BY AIR SEA,BY AIR TRUCK,BY EXPRESS,BY SEA,BY SEA TRUCK,BY TRAIN,BY TRAIN TRUCK,BY TRUCK,BY TRUCK TRAIN,NONSPECIFIC',',')) AND DTL.INS_SOURCE_TYPE = ANY(STRING_TO_ARRAY('MO,SPA,DO,DS,RE,PW,HS,PE,SAME,DK,TRAN,MS,1C,GE,SZ1,DB',',')) AND DTL.POD_SIGN_DATE >= '2023-01-01' AND DTL.POD_SIGN_DATE < ADDDATE('2023-11-30',1) AND DTL.ATA >= '2023-01-01' AND DTL.ATA <= ADDDATE('2023-08-31',1) GROUP BY DTL.PERIOD_ID_M, IFNULL(DTL.PACKING_LIST_NO, DTL.DELIVERY_NUMBER)
UNION ALL
SELECT TO_CHAR(ADDDATE(TO_DATE(DTL.PERIOD_ID_M,'YYYYMMDD'),INTERVAL '1' YEAR), 'YYYYMM') AS PERIOD_ID
,0 AS LGST_CYCLE_GOAL
,0 AS LGST_CYCLE_ATTAIN_RATE_GOAL
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_CURR
,1 AS TOTAL_PACK_LAST
,MAX(DECODE(DTL.IS_LOGISTICS_E2E_TIME, 'Y', DTL.LOGISTICS_E2E_TIME, 0)) AS LGST_CYCLE_VALUE_LAST
,NULL AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_LOGISTICS_CYCLE_DTL_F DTL
WHERE ((1=1))
AND 'N' = DTL.IS_OVERSEA
AND DTL.FG_MF_FLAG = ANY(STRING_TO_ARRAY('FG',',')) AND DTL.ERP_SHIP_METHOD_NAME_EN = ANY(STRING_TO_ARRAY('BY AIR,BY AIR SEA,BY AIR TRUCK,BY EXPRESS,BY SEA,BY SEA TRUCK,BY TRAIN,BY TRAIN TRUCK,BY TRUCK,BY TRUCK TRAIN,NONSPECIFIC',',')) AND DTL.INS_SOURCE_TYPE = ANY(STRING_TO_ARRAY('MO,SPA,DO,DS,RE,PW,HS,PE,SAME,DK,TRAN,MS,1C,GE,SZ1,DB',',')) AND DTL.POD_SIGN_DATE >= 2022-01-01 AND DTL.POD_SIGN_DATE < 2022-12-01 AND DTL.ATA >= 2022-01-01 AND DTL.ATA <= 2022-09-01 GROUP BY DTL.PERIOD_ID_M, IFNULL(DTL.PACKING_LIST_NO, DTL.DELIVERY_NUMBER)
)
SELECT DISTINCT A.PERIOD_ID
,LGST_CYCLE_CURR
,LGST_CYCLE_CURR_ACC
,LGST_CYCLE_LAST
,LGST_CYCLE_LAST_ACC
,LGST_CYCLE_GOAL
,LGST_CYCLE_ATTAIN_RATE_GOAL
,LGST_CYCLE_ATTAIN_RATE
,LGST_CYCLE_ATTAIN_ACC
,LGST_CYCLE_ATTAIN_RATE_LAST
,LGST_CYCLE_ATTAIN_LAST_ACC
FROM (
SELECT DISTINCT PERIOD_ID
,ROUND(SUM(LGST_CYCLE_VALUE_CURR) OVER(ORDER BY PERIOD_ID)/NULLIF(SUM(TOTAL_PACK_CURR) OVER(ORDER BY PERIOD_ID),0), 4) AS LGST_CYCLE_CURR_ACC
,ROUND(SUM(LGST_CYCLE_VALUE_LAST) OVER(ORDER BY PERIOD_ID)/NULLIF(SUM(TOTAL_PACK_LAST) OVER(ORDER BY PERIOD_ID),0), 4) AS LGST_CYCLE_LAST_ACC
,AVG(LGST_CYCLE_ATTAIN_CURR) OVER(ORDER BY PERIOD_ID) AS LGST_CYCLE_ATTAIN_ACC
,AVG(LGST_CYCLE_ATTAIN_LAST) OVER(ORDER BY PERIOD_ID) AS LGST_CYCLE_ATTAIN_LAST_ACC
from tmp) a
left join (
SELECT DISTINCT PERIOD_ID
,ROUND(SUM(LGST_CYCLE_VALUE_CURR) /NULLIF(SUM(TOTAL_PACK_CURR) ,0), 4) AS LGST_CYCLE_CURR
,ROUND(SUM(LGST_CYCLE_VALUE_LAST) /NULLIF(SUM(TOTAL_PACK_LAST) ,0), 4) AS LGST_CYCLE_LAST
,MAX(LGST_CYCLE_GOAL) AS LGST_CYCLE_GOAL
,MAX(LGST_CYCLE_ATTAIN_RATE_GOAL) AS LGST_CYCLE_ATTAIN_RATE_GOAL
,AVG(LGST_CYCLE_ATTAIN_CURR) AS LGST_CYCLE_ATTAIN_RATE
,AVG(LGST_CYCLE_ATTAIN_LAST) AS LGST_CYCLE_ATTAIN_RATE_LAST
from tmp
group by PERIOD_ID) b
on a.PERIOD_ID=b.PERIOD_ID
ORDER BY PERIOD_ID3、性能分析
"QUERY PLAN"
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-------------------------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+---------------+-----------+---------+-----------
1 | -> Row Adapter | 3705.866 | 8 | 1152 | | 104KB | | | 352 | 536499.47
2 | -> Vector Streaming (type: GATHER) | 3705.857 | 8 | 1152 | | 9314KB | | | 352 | 536499.47
" 3 | -> Vector Sort | [3559.851, 3597.946] | 8 | 1152 | | [368KB, 448KB] | 16MB | [0,153] | 352 | 536398.41 "
" 4 | -> Vector Sonic Hash Aggregate | [3559.589, 3597.675] | 8 | 1152 | | [2MB, 2MB] | 16MB | [0,161] | 352 | 536397.80 "
" 5 | -> Vector CTE Append(6, 24) | [3558.220, 3596.367] | 8 | 97623 | | [8KB, 8KB] | 1MB | | 352 | 536341.62 "
" 6 | -> Vector Append(7, 8, 16) [5, CTE tmp(1)] | [777.574, 861.812] | 784701 | 421728 | | [3MB, 3MB] | 1MB | | 78 | 459850.81 "
" 7 | -> CStore Scan on dmisc.dm_om_cycle_target_dtl_f | [0.077, 0.500] | 8 | 36 | | [1MB, 1MB] | 1MB | | 17 | 5.08 "
" 8 | -> Vector Subquery Scan on ""*SELECT* 2"" | [753.284, 793.977] | 784693 | 273936 | | [376KB, 376KB] | 1MB | | 78 | 230137.80 "
" 9 | -> Vector Hash Aggregate | [750.959, 791.804] | 784693 | 273931 | | [3MB, 3MB] | 16MB | [81,82] | 78 | 230080.73 "
" 10 | -> Vector Streaming(type: REDISTRIBUTE) | [719.329, 760.001] | 2794439 | 273931 | | [1MB, 1MB] | 2MB | | 46 | 229966.59 "
" 11 | -> Vector Subquery Scan on dtl | [447.793, 729.302] | 2794439 | 273931 | | [120KB, 120KB] | 1MB | | 46 | 229610.12 "
" 12 | -> Vector Hash Semi Join (13, 14) | [445.254, 726.301] | 2794439 | 273931 | | [324KB, 324KB] | 16MB | | 46 | 229610.11 "
" 13 | -> CStore Scan on dmisc.dm_om_logistics_cycle_dtl_f dtl | [434.036, 713.347] | 2863585 | 1204463 | 1 | [6MB, 6MB] | 1MB | | 49 | 229361.72 "
" 14 | -> Vector Adapter | [0.008, 0.050] | 768 | 768 | 16 | [40KB, 40KB] | 1MB | [22,22] | 32 | 0.20 "
" 15 | -> Values Scan on ""*VALUES*"" | [0.003, 0.042] | 768 | 768 | | [16KB, 16KB] | 1MB | | 32 | 0.20 "
" 16 | -> Vector Subquery Scan on ""*SELECT* 3"" | [0.379, 6.439] | 0 | 147744 | | [248KB, 248KB] | 1MB | | 78 | 229707.92 "
" 17 | -> Vector Hash Aggregate | [0.376, 6.434] | 0 | 147721 | | [608KB, 608KB] | 16MB | | 78 | 229677.14 "
" 18 | -> Vector Streaming(type: REDISTRIBUTE) | [0.269, 1.534] | 0 | 147721 | | [520KB, 520KB] | 2MB | | 46 | 229584.80 "
" 19 | -> Vector Subquery Scan on dtl | [339.265, 562.094] | 0 | 147721 | | [120KB, 120KB] | 1MB | | 46 | 229392.55 "
" 20 | -> Vector Hash Semi Join (21, 22) | [339.260, 562.091] | 0 | 147721 | | [324KB, 324KB] | 16MB | | 46 | 229392.54 "
" 21 | -> CStore Scan on dmisc.dm_om_logistics_cycle_dtl_f dtl | [339.005, 561.851] | 0 | 147721 | 1 | [4MB, 4MB] | 1MB | | 49 | 229361.72 "
" 22 | -> Vector Adapter | [0.008, 0.068] | 768 | 768 | 16 | [40KB, 40KB] | 1MB | [22,22] | 32 | 0.20 "
" 23 | -> Values Scan on ""*VALUES*"" | [0.004, 0.032] | 768 | 768 | | [16KB, 16KB] | 1MB | | 32 | 0.20 "
" 24 | -> Vector Hash Left Join (25, 32) | [2728.035, 2787.641] | 8 | 97623 | | [580KB, 580KB] | 16MB | | 352 | 76490.81 "
" 25 | -> Vector Hash Aggregate | [2657.986, 2691.184] | 8 | 97623 | 407 | [2MB, 2MB] | 16MB | [0,100] | 224 | 76068.36 "
" 26 | -> Vector Streaming(type: REDISTRIBUTE) | [2654.856, 2687.814] | 8 | 97623 | | [136KB, 168KB] | 2MB | | 224 | 76022.60 "
" 27 | -> Vector Hash Aggregate | [2668.312, 2668.312] | 8 | 97623 | | [2MB, 2MB] | 27MB(16997MB) | [100,100] | 224 | 55582.78 "
" 28 | -> Vector WindowAgg | [2533.747, 2533.747] | 784701 | 421728 | | [19MB, 19MB] | 16MB | | 224 | 47870.61 "
" 29 | -> Vector Streaming(type: BROADCAST) | [846.397, 846.397] | 784701 | 421728 | | [14MB, 14MB] | 2MB | | 224 | 46899.71 "
" 30 | -> Vector Sort | [818.146, 909.882] | 784701 | 421728 | | [4MB, 4MB] | 16MB | [96,96] | 224 | 773.21 "
" 31 | -> Vector CTE Scan on tmp(1) | [798.687, 872.478] | 784701 | 421728 | | [328KB, 328KB] | 1MB | | 224 | 175.72 "
" 32 | -> Vector Streaming(type: BROADCAST) | [56.051, 120.203] | 384 | 240 | 5 | [584KB, 584KB] | 2MB | [156,156] | 224 | 396.89 "
" 33 | -> Vector Subquery Scan on b | [870.338, 905.047] | 8 | 5 | | [120KB, 120KB] | 1MB | | 224 | 375.09 "
" 34 | -> Vector Hash Aggregate | [869.879, 904.640] | 8 | 5 | | [576KB, 584KB] | 16MB | [0,156] | 544 | 375.08 "
" 35 | -> Vector Sonic Hash Aggregate | [869.722, 904.465] | 8 | 5 | | [2MB, 3MB] | 16MB | [0,107] | 544 | 375.04 "
" 36 | -> Vector Streaming(type: REDISTRIBUTE) | [868.009, 903.802] | 384 | 240 | | [1MB, 1MB] | 2MB | | 544 | 374.91 "
" 37 | -> Vector Sonic Hash Aggregate | [810.278, 900.500] | 384 | 240 | | [2MB, 2MB] | 16MB | [107,107] | 544 | 373.48 "
" 38 | -> Vector CTE Scan on tmp(1) | [805.272, 894.369] | 784701 | 421728 | | [328KB, 328KB] | 1MB | | 288 | 175.72 "
从SQL和计划中我们看出，瓶颈在于第28层的WindowAgg上。这层窗口聚合78万行数据使用了1.7s，这是很慢的。
28 --Vector WindowAgg
dn_6073_6074 (actual time=104.011..2533.747 rows=784701 loops=1) (projection time=604.622)
" dn_6073_6074 (Buffers: shared hit=7, temp read=3996 written=3998)"
" dn_6073_6074 (CPU: ex c/r=6451, ex row=784701, ex cyc=5062452360, inc cyc=7600612432)"
29 --Vector Streaming(type: BROADCAST)
dn_6073_6074 (actual time=0.624..846.397 rows=784701 loops=1)
dn_6073_6074 (Buffers: 0)
" dn_6073_6074 (CPU: ex c/r=3234, ex row=784701, ex cyc=2538160072, inc cyc=2538160072)"
30 --Vector Sort
dn_6001_6002 (actual time=821.904..826.393 rows=16154 loops=1)
dn_6003_6004 (actual time=905.478..909.882 rows=16293 loops=1)
dn_6005_6006 (actual time=842.772..846.718 rows=16370 loops=1)
dn_6007_6008 (actual time=880.630..884.343 rows=16224 loops=1)
dn_6009_6010 (actual time=846.926..850.299 rows=16311 loops=1)
dn_6011_6012 (actual time=836.361..839.869 rows=16332 loops=1)
dn_6013_6014 (actual time=835.854..840.312 rows=16466 loops=1)
dn_6015_6016 (actual time=835.631..839.240 rows=16492 loops=1)
dn_6017_6018 (actual time=858.869..863.223 rows=16424 loops=1)
dn_6019_6020 (actual time=820.697..824.219 rows=16349 loops=1)
dn_6021_6022 (actual time=856.341..860.269 rows=16541 loops=1)
dn_6023_6024 (actual time=831.537..835.069 rows=16197 loops=1)
dn_6025_6026 (actual time=846.482..850.809 rows=16309 loops=1)进一步查看这层算子的信息，我们发现该算子只在一个DN上运行，这正是性能瓶颈的原因。
查看对应原SQL，这里的窗口函数是子查询a里面的一些SUM() OVER(ORDER BY), AVG() OVER(ORDER BY)；这些语句改写成join形式性能更差，因此还是需要保留。
我们打印出部分数据，分析发现，尽管原SQL中子查询a对结果集使用了DISTINCT方法，DISTINCT的聚合列为a的所有列，但是实际上由于窗口聚合的OVER(ORDER BY PERIOD_ID)，实际上聚合的粒度就是PERIOD_ID一列，且只涉及8个PERIOD_ID。在原SQL中，子查询a先做窗口聚合，再做DISTINCT，窗口聚合做完排序后，每次都根据本行的PERIOD_ID字段做累加和，实际上是这没有必要的。可改为先以PERIOD_ID为粒度进行求和，然后再做累加，这样在整个过程中，会有不少于PERIOD_ID个数个的DN做聚合运算，之后只需要把PERIOD_ID个数个的行送到1个DN上做窗口聚合，效率得到了很大的提升。
4、改写SQL
explain performance
WITH TMP AS ( SELECT PERIOD_ID
,TARGET AS LGST_CYCLE_GOAL
,ATTAIN_RATE_GOAL AS LGST_CYCLE_ATTAIN_RATE_GOAL
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_CURR
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_CYCLE_TARGET_DTL_F
WHERE PROJECT_PACKAGE = 'PKG_OM_LGST'
AND CYCLE_CODE = '1000'
AND (IS_OVERSEA = 'ALL' OR IS_OVERSEA = 'N')
AND (PERIOD_ID BETWEEN '202301' AND '202308')
UNION ALL
SELECT DTL.PERIOD_ID_M AS PERIOD_ID
,0 AS LGST_CYCLE_GOAL
,0 AS LGST_CYCLE_ATTAIN_RATE_GOAL
,1 AS TOTAL_PACK_CURR
,MAX(DECODE(DTL.IS_LOGISTICS_E2E_TIME, 'Y', DTL.LOGISTICS_E2E_TIME, 0)) AS LGST_CYCLE_VALUE_CURR
,NULL AS LGST_CYCLE_ATTAIN_CURR
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_LAST
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_LOGISTICS_CYCLE_DTL_F DTL
WHERE ((1=1))
AND 'N' = DTL.IS_OVERSEA
AND DTL.FG_MF_FLAG = ANY(STRING_TO_ARRAY('FG',',')) AND DTL.ERP_SHIP_METHOD_NAME_EN = ANY(STRING_TO_ARRAY('BY AIR,BY AIR SEA,BY AIR TRUCK,BY EXPRESS,BY SEA,BY SEA TRUCK,BY TRAIN,BY TRAIN TRUCK,BY TRUCK,BY TRUCK TRAIN,NONSPECIFIC',',')) AND DTL.INS_SOURCE_TYPE = ANY(STRING_TO_ARRAY('MO,SPA,DO,DS,RE,PW,HS,PE,SAME,DK,TRAN,MS,1C,GE,SZ1,DB',',')) AND DTL.POD_SIGN_DATE >= '2023-01-01' AND DTL.POD_SIGN_DATE < ADDDATE('2023-11-30',1) AND DTL.ATA >= '2023-01-01' AND DTL.ATA <= ADDDATE('2023-08-31',1) GROUP BY DTL.PERIOD_ID_M, IFNULL(DTL.PACKING_LIST_NO, DTL.DELIVERY_NUMBER)
UNION ALL
SELECT TO_CHAR(ADDDATE(TO_DATE(DTL.PERIOD_ID_M,'YYYYMMDD'),INTERVAL '1' YEAR), 'YYYYMM') AS PERIOD_ID
,0 AS LGST_CYCLE_GOAL
,0 AS LGST_CYCLE_ATTAIN_RATE_GOAL
,CAST(NULL AS DECIMAL) AS TOTAL_PACK_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_VALUE_CURR
,CAST(NULL AS DECIMAL) AS LGST_CYCLE_ATTAIN_CURR
,1 AS TOTAL_PACK_LAST
,MAX(DECODE(DTL.IS_LOGISTICS_E2E_TIME, 'Y', DTL.LOGISTICS_E2E_TIME, 0)) AS LGST_CYCLE_VALUE_LAST
,NULL AS LGST_CYCLE_ATTAIN_LAST
FROM DMISC.DM_OM_LOGISTICS_CYCLE_DTL_F DTL
WHERE ((1=1))
AND 'N' = DTL.IS_OVERSEA
AND DTL.FG_MF_FLAG = ANY(STRING_TO_ARRAY('FG',',')) AND DTL.ERP_SHIP_METHOD_NAME_EN = ANY(STRING_TO_ARRAY('BY AIR,BY AIR SEA,BY AIR TRUCK,BY EXPRESS,BY SEA,BY SEA TRUCK,BY TRAIN,BY TRAIN TRUCK,BY TRUCK,BY TRUCK TRAIN,NONSPECIFIC',',')) AND DTL.INS_SOURCE_TYPE = ANY(STRING_TO_ARRAY('MO,SPA,DO,DS,RE,PW,HS,PE,SAME,DK,TRAN,MS,1C,GE,SZ1,DB',',')) AND DTL.POD_SIGN_DATE >= 2022-01-01 AND DTL.POD_SIGN_DATE < 2022-12-01 AND DTL.ATA >= 2022-01-01 AND DTL.ATA <= 2022-09-01 GROUP BY DTL.PERIOD_ID_M, IFNULL(DTL.PACKING_LIST_NO, DTL.DELIVERY_NUMBER)
),
tmp1 as (
SELECT PERIOD_ID
,SUM(LGST_CYCLE_VALUE_CURR) as sum_LGST_CYCLE_VALUE_CURR
,SUM(TOTAL_PACK_CURR) as sum_TOTAL_PACK_CURR
,SUM(LGST_CYCLE_VALUE_LAST) as sum_LGST_CYCLE_VALUE_LAST
,SUM(TOTAL_PACK_LAST) as sum_TOTAL_PACK_LAST
,AVG(LGST_CYCLE_ATTAIN_CURR) as sum_LGST_CYCLE_ATTAIN_CURR
,AVG(LGST_CYCLE_ATTAIN_LAST) as sum_LGST_CYCLE_ATTAIN_LAST
from tmp
GROUP BY
period_id
),
a as (
select period_id
,ROUND(SUM(sum_LGST_CYCLE_VALUE_CURR) OVER(ORDER BY PERIOD_ID)/NULLIF(SUM(sum_TOTAL_PACK_CURR) OVER(ORDER BY PERIOD_ID),0), 4) AS LGST_CYCLE_CURR_ACC
,ROUND(SUM(sum_LGST_CYCLE_VALUE_LAST) OVER(ORDER BY PERIOD_ID)/NULLIF(SUM(sum_TOTAL_PACK_LAST) OVER(ORDER BY PERIOD_ID),0), 4) AS LGST_CYCLE_LAST_ACC
,AVG(sum_LGST_CYCLE_ATTAIN_CURR) OVER(ORDER BY PERIOD_ID) AS LGST_CYCLE_ATTAIN_ACC
,AVG(sum_LGST_CYCLE_ATTAIN_LAST) OVER(ORDER BY PERIOD_ID) AS LGST_CYCLE_ATTAIN_LAST_ACC
from tmp1
)
SELECT DISTINCT A.PERIOD_ID
,LGST_CYCLE_CURR
,LGST_CYCLE_CURR_ACC
,LGST_CYCLE_LAST
,LGST_CYCLE_LAST_ACC
,LGST_CYCLE_GOAL
,LGST_CYCLE_ATTAIN_RATE_GOAL
,LGST_CYCLE_ATTAIN_RATE
,LGST_CYCLE_ATTAIN_ACC
,LGST_CYCLE_ATTAIN_RATE_LAST
,LGST_CYCLE_ATTAIN_LAST_ACC
FROM a
left join (
SELECT DISTINCT PERIOD_ID
,ROUND(SUM(LGST_CYCLE_VALUE_CURR) /NULLIF(SUM(TOTAL_PACK_CURR) ,0), 4) AS LGST_CYCLE_CURR
,ROUND(SUM(LGST_CYCLE_VALUE_LAST) /NULLIF(SUM(TOTAL_PACK_LAST) ,0), 4) AS LGST_CYCLE_LAST
,MAX(LGST_CYCLE_GOAL) AS LGST_CYCLE_GOAL
,MAX(LGST_CYCLE_ATTAIN_RATE_GOAL) AS LGST_CYCLE_ATTAIN_RATE_GOAL
,AVG(LGST_CYCLE_ATTAIN_CURR) AS LGST_CYCLE_ATTAIN_RATE
,AVG(LGST_CYCLE_ATTAIN_LAST) AS LGST_CYCLE_ATTAIN_RATE_LAST
from tmp
group by PERIOD_ID) b
on a.PERIOD_ID=b.PERIOD_ID
ORDER BY PERIOD_ID在改写的SQL中，先用子查询tmp1对子查询a中需要用到的聚合数据进行聚合计算，然后再在a中使用这些数据。子查询a需要处理的行数从78万行降低为8行。另外可以看到，解释器使用了best_agg_plan=3的聚合方式，进一步减少了计算倾斜。
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+-------------------------------------------------------------------------------------------+--------------------+---------+---------+------------+----------------+----------+-----------+---------+-----------
1 | -> Row Adapter | 376.371 | 8 | 48 | | 104KB | | | 352 | 461379.06
2 | -> Vector Streaming (type: GATHER) | 376.365 | 8 | 48 | | 9394KB | | | 352 | 461379.06
3 | -> Vector Unique | [307.033, 348.395] | 8 | 48 | | [167KB, 167KB] | 1MB | | 352 | 461281.06
4 | -> Vector Sort | [307.030, 348.393] | 8 | 48 | | [384KB, 464KB] | 16MB | [0,153] | 352 | 461281.03
5 | -> Vector CTE Append(6, 24) | [306.882, 348.272] | 8 | 48 | | [8KB, 8KB] | 1MB | | 352 | 461281.02
6 | -> Vector Append(7, 8, 16) [5, CTE tmp(1)] | [288.492, 330.753] | 784701 | 426960 | | [3MB, 3MB] | 1MB | | 79 | 460559.89
7 | -> CStore Scan on dmisc.dm_om_cycle_target_dtl_f | [0.043, 0.279] | 8 | 36 | | [1MB, 1MB] | 1MB | | 17 | 5.08
8 | -> Vector Subquery Scan on "*SELECT* 2" | [268.291, 310.418] | 784693 | 279168 | | [376KB, 376KB] | 1MB | | 79 | 230494.22
9 | -> Vector Hash Aggregate | [267.394, 309.495] | 784693 | 279161 | | [3MB, 3MB] | 16MB | [81,82] | 79 | 230436.06
10 | -> Vector Streaming(type: REDISTRIBUTE) | [252.968, 295.741] | 2794439 | 279161 | | [1MB, 1MB] | 2MB | | 47 | 230319.74
11 | -> Vector Subquery Scan on dtl | [182.003, 244.973] | 2794439 | 279161 | | [120KB, 120KB] | 1MB | | 47 | 229953.63
12 | -> Vector Hash Semi Join (13, 14) | [181.402, 244.114] | 2794439 | 279161 | | [324KB, 324KB] | 16MB | | 47 | 229953.62
13 | -> CStore Scan on dmisc.dm_om_logistics_cycle_dtl_f dtl | [178.341, 239.812] | 2863585 | 1196550 | 1 | [6MB, 6MB] | 1MB | | 50 | 229713.72
14 | -> Vector Adapter | [0.006, 0.029] | 768 | 768 | 16 | [40KB, 40KB] | 1MB | [22,22] | 32 | 0.20
15 | -> Values Scan on "*VALUES*" | [0.002, 0.024] | 768 | 768 | | [16KB, 16KB] | 1MB | | 32 | 0.20
16 | -> Vector Subquery Scan on "*SELECT* 3" | [0.533, 9.239] | 0 | 147744 | | [248KB, 248KB] | 1MB | | 79 | 230060.58
17 | -> Vector Hash Aggregate | [0.529, 9.236] | 0 | 147721 | | [608KB, 608KB] | 16MB | | 79 | 230029.80
18 | -> Vector Streaming(type: REDISTRIBUTE) | [0.400, 9.139] | 0 | 147721 | | [520KB, 520KB] | 2MB | | 47 | 229937.46
19 | -> Vector Subquery Scan on dtl | [193.801, 290.499] | 0 | 147721 | | [120KB, 120KB] | 1MB | | 47 | 229743.70
20 | -> Vector Hash Semi Join (21, 22) | [193.798, 290.497] | 0 | 147721 | | [324KB, 324KB] | 16MB | | 47 | 229743.69
21 | -> CStore Scan on dmisc.dm_om_logistics_cycle_dtl_f dtl | [193.610, 290.245] | 0 | 147721 | 1 | [4MB, 4MB] | 1MB | | 50 | 229713.72
22 | -> Vector Adapter | [0.006, 0.030] | 768 | 768 | 16 | [40KB, 40KB] | 1MB | [22,22] | 32 | 0.20
23 | -> Values Scan on "*VALUES*" | [0.001, 0.026] | 768 | 768 | | [16KB, 16KB] | 1MB | | 32 | 0.20
24 | -> Vector Hash Left Join (25, 33) | [15.206, 17.861] | 8 | 48 | | [580KB, 580KB] | 16MB | | 352 | 721.13
25 | -> Vector Streaming(type: REDISTRIBUTE) | [2.322, 3.168] | 8 | 48 | 1 | [136KB, 168KB] | 2MB | | 160 | 341.35
26 | -> Vector WindowAgg | [0.311, 0.311] | 8 | 48 | | [1MB, 1MB] | 16MB | | 224 | 340.19
27 | -> Vector Streaming(type: BROADCAST) | [0.012, 0.012] | 8 | 48 | | [5MB, 5MB] | 2MB | | 224 | 340.13
28 | -> Vector Sort | [306.557, 347.263] | 8 | 48 | | [368KB, 416KB] | 16MB | [0,99] | 224 | 334.88
29 | -> Vector Sonic Hash Aggregate | [306.326, 347.115] | 8 | 5 | | [2MB, 2MB] | 16MB | [0,91] | 416 | 334.86
30 | -> Vector Streaming(type: REDISTRIBUTE) | [305.226, 346.440] | 384 | 240 | | [520KB, 568KB] | 2MB | | 416 | 334.75
31 | -> Vector Sonic Hash Aggregate | [298.217, 342.014] | 384 | 240 | | [2MB, 2MB] | 16MB | [91,91] | 416 | 333.64
32 | -> Vector CTE Scan on tmp(1) | [295.247, 338.960] | 784701 | 426960 | | [328KB, 328KB] | 1MB | | 224 | 177.90
33 | -> Vector Subquery Scan on b | [12.058, 15.153] | 8 | 5 | 1 | [120KB, 120KB] | 1MB | [0,156] | 224 | 379.72
34 | -> Vector Hash Aggregate | [11.864, 14.771] | 8 | 5 | | [576KB, 584KB] | 16MB | [0,156] | 544 | 379.71
35 | -> Vector Sonic Hash Aggregate | [11.724, 14.642] | 8 | 5 | | [2MB, 3MB] | 16MB | [0,107] | 544 | 379.67
36 | -> Vector Streaming(type: REDISTRIBUTE) | [10.736, 13.602] | 384 | 240 | | [1MB, 1MB] | 2MB | | 544 | 379.54
37 | -> Vector Sonic Hash Aggregate | [298.732, 343.279] | 384 | 240 | | [2MB, 2MB] | 16MB | [107,107] | 544 | 378.11
38 | -> Vector CTE Scan on tmp(1) | [295.344, 339.063] | 784701 | 426960 | | [328KB, 328KB] | 1MB | | 288 | 177.90
本例中，单点运行耗时从3.7s优化到0.5s。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）