理解 SQL 窗口函数: over()与 sum, AVG, MIN, MAX 的实战应用
1.使用over子句与rows_number()以及聚合函数进行使用,可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。
2.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:“所有订单的总和”、“每一位客户的所有订单的总和”、”每一单的金额“
关键点:使用了sum() over() 这个开窗函数
如图:
代码如下:
2 customerID,
3 SUM(totalPrice) over() as AllTotalPrice,
4 SUM(totalPrice) over(partition by customerID) as cusTotalPrice,
5 totalPrice
6 from OP_Order
3.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:“所有订单的总和(AllTotalPrice)”、“每一位客户的所有订单的总(cusTotalPrice)”、”每一单的金额(totalPrice)“,”每一个客户订单的平均金额(avgCusprice)“,”所有客户的所有订单的平均金额(avgTotalPrice)“,"客户所购的总额在所有的订单中总额的比例(CusAllPercent)","每一订单的金额在每一位客户总额中所占的比例(cusToPercent)"。
代码如下
2 (
3 select
4 customerID,
5 SUM(totalPrice) over() as AllTotalPrice,
6 SUM(totalPrice) over(partition by customerID) as cusTotalPrice,
7 AVG(totalPrice) over(partition by customerID) as avgCusprice,
8 AVG(totalPrice) over() as avgTotalPrice,
9 totalPrice
10 from OP_Order
11 )
12 select
13 customerID,
14 AllTotalPrice,
15 cusTotalPrice,
16 totalPrice,
17 avgCusprice,
18 avgTotalPrice,
19 cusTotalPrice / AllTotalPrice as CusAllPercent,
20 totalPrice / cusTotalPrice as cusToPercent
21 from tabs
22
4.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:“所有订单的总和(AllTotalPrice)”、“每一位客户的所有订单 的总(cusTotalPrice)”、”每一单的金额(totalPrice)“,”每一个客户订单的平均金额(avgCusprice)“,”所有客 户的所有订单的平均金额(avgTotalPrice)“,"订单金额最小值(MinTotalPrice)","客户订单金额最小值(MinCusPrice)","订单金额最大值(MaxTotalPrice)","客户订单金额最大值(MaxCusPrice)","客户所购的总额在所有的订单中总额的比例(CusAllPercent)","每一订单的金 额在每一位客户总额中所占的比例(cusToPercent)"。
关键:利用over子句进行操作。
如图:
具体代码如下:
2 (
3 select
4 customerID,
5 SUM(totalPrice) over() as AllTotalPrice,
6 SUM(totalPrice) over(partition by customerID) as cusTotalPrice,
7 AVG(totalPrice) over(partition by customerID) as avgCusprice,
8 AVG(totalPrice) over() as avgTotalPrice,
9 MIN(totalPrice) over() as MinTotalPrice,
10 MIN(totalPrice) over(partition by customerID) as MinCusPrice,
11 MAX(totalPrice) over() as MaxTotalPrice,
12 MAX(totalPrice) over(partition by customerID) as MaxCusPrice,
13 totalPrice
14 from OP_Order
15 )
16 select
17 customerID,
18 AllTotalPrice,
19 cusTotalPrice,
20 totalPrice,
21 avgCusprice,
22 avgTotalPrice,
23 MinTotalPrice,
24 MinCusPrice,
25 MaxTotalPrice,
26 MaxCusPrice,
27 cusTotalPrice / AllTotalPrice as CusAllPercent,
28 totalPrice / cusTotalPrice as cusToPercent
29 from tabs
30
推荐阅读
-
玩转 SQL 窗口函数 (Window Function):OVER 的关键作用与应用指南" - 分组小能手:理解 PARTITION BY,就像在数据分块后为每个组执行聚合操作,不同窗口函数的分组互不影响 - 排序达人:在 OVER 之后加入 ORDER BY,就像 SQL 中一样轻松设定行的排序顺序 - 计算范围说明:引入 ROWS 子句,就像给窗口函数设置边界条件,无论是从哪一行开始计算,到哪一行结束,都能精准控制,例如要求前5行和后5行的总和,只需用 ROWS BETWEEN...AND... 指定界限规则即可。
-
探索Hive中的窗口函数:理解SUM、AVG、COUNT、MAX和MIN的窗口聚合操作
-
理解 SQL 窗口函数: over()与 sum, AVG, MIN, MAX 的实战应用
-
SQL中的神奇窗口函数:深入理解over()选项与聚合函数应用进阶