最近写HIVE SQL的一点笔记【4】

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

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

OVER开窗函数使用基本格式

over（order by salary range between unbounded preceding and unbounded following）或者
over(partition by buildingid,height)

PARTITION BY 是分组用的，按谁进行分组统计;
 ORDER BY 是排序用的，排序后再计算，如果不指定ORDER BY，则将分组内所有值计算,排序默认asc,可以指定;
 ROWS BETWEEN 或者 RANGE BETWEEN,叫做WINDOW子句
       CURRENT ROW：当前行
       PRECEDING：往前
       FOLLOWING：往后
       UNBOUNDED:起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点
       如果不指定ROWS BETWEEN,默认为从起点到当前行;
       RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING：窗口范围为当前行数据幅度减3加3后的范围内的。
       ROWS   BETWEEN 3 PRECEDING AND 3 FOLLOWING：窗口范围为当前行前后各移动3行。