分组求金额占比，以如下数据为例

tid为主订单号，oid为子订单号，每个子订单有自己的订单金额，字段为fee。那么我们要求出每个子单的金额在主单的总金额的占比。

可以使用 OVER开窗函数 配合SUM求和函数来处理

SUM(FEE) OVER (PARTITION BY TID) AS SUM_FEE

来根据TID求的每个主订单的金额总和，之后再用各自的子订单金额除以主订单金额得到占比就行

SELECT TID,
       OID,
       FEE,
       SUM(FEE) OVER (PARTITION BY TID) AS SUM_FEE,
       ROUND(FEE/SUM(FEE)) OVER (PARTITION BY TID) AS FEE_RATIO
FROM (
         SELECT '101' as tid, '10001' as oid, 33.20 as fee
         UNION ALL
         SELECT '101' as tid, '10002' as oid, 13.65 as fee
         UNION ALL
         SELECT '101' as tid, '10003' as oid, 16.10 as fee
         UNION ALL
         SELECT '201' as tid, '20001' as oid, 6.70 as fee
         UNION ALL
         SELECT '201' as tid, '20002' as oid, 1.21 as fee
         UNION ALL
         SELECT '301' as tid, '30001' as oid, 118.22 as fee
         UNION ALL
         SELECT '401' as tid, '40001' as oid, 208.03 as fee
         UNION ALL
         SELECT '401' as tid, '40002' as oid, 119.90 as fee
         UNION ALL
         SELECT '401' as tid, '40003' as oid, 5.50 as fee
         UNION ALL
         SELECT '401' as tid, '40004' as oid, 24.80 as fee
     ) A

其他可以配合OVER开窗函数使用的函数有 AVG，MIN，MAX

Continue reading