PG窗口函数的应用实践
简述
窗口函数主要用于聚合计算，相当于我们的分组语句group by。
常用的sql语句往往只支持单一的数据范围统计，而窗口函数支持更加灵活更加多样的数据范围划定。
本质上，是否需要使用窗口函数，取决于你的需求场景是否涉及到同一种事物的不同数据范围的同时计算。
排名类
排名，普通的只涉及到单一数据范围的排名的话，我们只需要正常的排序就可以满足，当你需要同时计算每个单位在不同数据范围下的排名计算，就需要使用窗口函数，
比如各个店铺销售额在楼层中的排名、在所属业态中的排名、在整个商场中的排名。
源表结构示意
shop_code shop_name floor_code order_date order_amount market_code HT2021051200025 BSiEE本涩 L2 2022-05-20 6786 000035 HT2021051300007 ROOKIE L3 2022-05-20 98887 000035 HT2021051300013 剪花娘子 L5 2022-05-20 4567 000035 HT2021051300014 阿香米线 L5 2022-05-20 5845.23 000035 HT2021051700025 森林鸟 L2 2022-05-20 37463 000035 …… …… …… …… …… …… 分别统计每店铺每天的销售额 在所在楼层中的排名、全场中的排名.
SELECT sale.shop_code,
sale.shop_name ,
sale.order_date as 销售日期 ,
sale.order_amount as 销售额,
rank() OVER (PARTITION BY sale.market_code, sale.order_date ORDER BY sale.order_amount DESC) AS 全场范围排名,
rank() OVER (PARTITION BY sale.market_code, sale.floor_code, sale.order_date ORDER BY sale.order_amount DESC) AS 店铺在所在楼层中的排名
FROM sale_data sale查询结果 (示例)
shop_code shop_name floor_code 销售日期 销售额 全场范围排名 店铺在所在楼层中的排名 WHDYC(20)Z 汉黄山 L5 2022-05-20 53038 1 1 HT2021121300 老寺黄金 B1 2022-05-20 27799 2 1 HT2021052500 楚去菜 L4 2022-05-20 21433.41 3 1 HT2021121400 Cdddoo L1 2022-05-20 20531 4 1 HT2021052600037 火瀑麻 L4 2022-05-20 17260 5 2 WHDYC(20)Z 晓南福 L4 2022-05-20 16358.25 6 3 HT2021080500 小米家 L1 2022-05-20 13678.03 7 2 …… …… …… …… …… …… ……
聚合类
聚合类，或者说累加，包括sum、avg、max、count等聚合函数。
同样的，如果我们只涉及到一种数据范围的聚合计算，那么只需要正常的聚合语句就可以完成。
当你需要同时计算某个值在不同数据范围下的聚合计算，就需要使用窗口函数，比如同时计算某商场每日新增的会员数量，以及截止到当日累计的会员数量。
源数据表结构示意,注册记录表
id name register_date 115752 李某某 2022-04-16 110498 朱某某 2022-04-09 109282 熊某某 2022-04-03 109290 徐某某 2022-04-03 108456 周某某 2022-03-28 107925 殷某某 2022-03-24 107575 樱某某 2022-03-20 107595 万某某 2022-03-20 107597 郑某某 2022-03-20 …… …… …… 统计每日新增会员数及截至当日会员总数
SELECT m.register_date,
count(0) AS 当日新增会员数,
sum(count(0)) OVER (ORDER BY m.register_date) AS 截止当日总会员数
FROM bi_data_member m
group by register_date
ORDER BY m.register_date DESC查询结果 (示例)
register_date 当日新增会员数 截止当日总会员数 2022-05-26 2 120092 2022-05-25 7 120090 2022-05-07 62 120083 2022-05-06 98 120021 2022-05-05 76 119923 2022-05-04 270 119847 2022-05-03 273 119577 2022-05-02 289 119304 2022-05-01 308 119015 …… …… ……
总结
从窗口函数的结构上看, 它是这么一个结构组成: fun OVER (PARTITION BY... ORDER BY...)
fun表示最终的计算操作函数,可以是窗口函数专用函数,也支持sum、max、avg等通用聚合函数.OVER无实际意义.PARTITION BY标识计算的数据范围的划分,也就是分组.ORDER BY这个函数就有意思了,通常概念中它起到排序的作用,比如我们第一个销售排名示例.但大部分情况下,比如我们的会员新统计的示例,他也起到了数据范围圈定的作用.在示例中它的作用是把合计的数据范围截止在当前的日期.PARTITION BY和ORDER BY,至少使用其中一个,否则窗口无效.
其他运用
移动计算类,除了以上我们提到的示例外,窗口函数还支持移动计算的场景.
其实本质上还是数据范围的划定,移动计算的含义就是可以通过指定每一行的前后多少行来聚合计算.而不单单只是根据某些业务字段来分组计算.
这种类型我本人尚未在实际业务场景中用到过,所以本文不再赘述.
附录
一些窗口函数
函数名称 返回类型 描述 row_number() bigint 在其分区中的当前行号，从1开始计算 rank() bigint 有间隔的当前行排名；与它的第一个相同行的row_number相同 dense_rank() bigint 没有间隔的当前行排名；这个函数计数对等组 percent_rank double precision 当前行的相对排名：（rank-1）/(总行数-1) cume_dist() double precision 当前行的相对排名：（前面的行数或与当前行相同的行数）/（总行数） ntile(num_buckets integer) integer 从1到参数的整数范围，尽可能相等的划分分区 lag(value any [, offset integer [, default any ]]) 类型同value 计算分区当前行的前offset行，返回value。如果没有这样的行，返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1，default默认是null lead(value any [, offset integer [, default any ]]) 类型同value 计算分区当前行的后offset行，返回value。如果没有这样的行，返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1，default默认是null first_value(value any) 类型同value 返回窗口第一行的计算value值 last_value(value any) 类型同value 返回窗口最后一行的计算value值 nth_value(value any,nth integer) 类型同value 返回窗口的第nth行的计算value，从1开始计数，没有符合的行则返回null window子句使用(分组后的再次划分)：
关键字 描述 preceding 往前 following 往后 current row 当前行 unbounded 起点 unbounded preceding 表示从前面的起点 unbounded following 表示到后面的终点
本文作者：Lee
本文地址： leeblog.icu/2022/05/26/
版权声明：本博客所有文章除特别声明外，均采用 CC 4.0 BY-NC-SA 许可协议。转载请注明出处！