博客

PostGreSQL开窗函数

语法
<窗口函数>
over（partition by 分组列 order by 排序列）
order by 并非必要

over() 是开窗函数的关键词

窗口函数存在哪些 ？

聚合类：sum() avg() max() min() count()

内置函数：RANK DENSE_RANK ROW_NUMBER等等

聚合类的窗口函数使用方法和平时使用相同

以sum()为例

id	type	name	amount
1	 1		liliya	3600
2	 1		will	4500
3	 2		hell	2350

select 
id,
name,
amount,
sum（amount）over(partiton by type) as total_amount

结果
id 	name 	amount 	total_amount
1	liliya	3600	8100
2	will	4500	8100
3	hell	2350	2350

内置函数：RANK,DENSE_RANK,ROW_NUMBER

三者性质相同，都是对数据进行排序并且编号，不过结果存在些许的差异

以RANK为例

table name -> t1
id	type	name	amount
1	 1		liliya	3600
2	 1		will	4500
3	 2		hell	2350
4	 1		oldman	2300
5	 2		trueman	4610
6	 2		ora		2350

select 
rank()over(partition by type order by amount desc),* from t1

结果就是
rank	id	type	name	amount
1		2	 1		will	 4500
2		1	 1		liliya	 3600
3		4	 1		oldman	 2300
1		5	 2		trueman	 4610
2		3 	 2		hell	 2350
3		6	 2		ora		 2350

RANK 使用结果有一个特点，就是若是排序的数据相同，则标记为并列，并且下一条数据的标记会 跳过标记相同的个数，进行标记 就比如并列第一的存在3个，name这三个标记为1 从第4个标记开始，标记为4 而非2

DENSE_RANK 则是不会跳过重复的个数，而是顺延标记。

ROW_NUMBER 不管重复，直接从1开始进行标记

开窗函数的使用，是因为group by 使用过程中，所select 的列都必须在group by中，这个会在实际中存在诸多麻烦，而开窗函数可以很好的避免此类情况。

同时开窗函数适用于TOPN 十分的方便

本人实际应用开窗函数的情况

table_name -> t1
自增ID	订单号	流水号	流水金额	流水收款日期
id	order_id	fina_id	amount	date
1	123			 t1		 30		2020-05-30
2	123			 t2		 100	2020-05-31
3	123			 t3		 500	2020-06-05
4	123			 t4		 70		2020-07-01

需求：累计金额首次超过300 的那天标注为1 
需求的结果表
order_id	fina_id	amount	date			sum_amount		is_sign
123			 t1		 30		2020-05-30			30				0
123			 t2		 100	2020-05-31			130				0
123			 t3		 500	2020-06-05			630				1
123			 t4		 70		2020-07-01			700				0
SQL:
select 
order_id,
fina_id,
amount,
date,
sum(amount)over(partition by order_id order by date) as sum_amount
case when sum(amount)over(partition by order_id order by date) >300 then 1 else 0 end as is_sign
from t1
此时得到的结果是
order_id	fina_id	amount	date			sum_amount		is_sign
123			 t1		 30		2020-05-30			30				0
123			 t2		 100	2020-05-31			130				0
123			 t3		 500	2020-06-05			630				1
123			 t4		 70		2020-07-01			700				1
还未符合我们想要的结果
将上表 给个别名 t2
SQL:
select 
order_id,
fina_id,
amount,
date,
sum_amount,
sum(is_sign)over(partition by order_id order by date) as sum_sign
from t2
此时得到的结果是：
order_id	fina_id	amount	date			sum_amount		sum_sign
123			 t1		 30		2020-05-30			30				0
123			 t2		 100	2020-05-31			130				0
123			 t3		 500	2020-06-05			630				1
123			 t4		 70		2020-07-01			700				2
尚未符合要求 将上表命名别名 t3
select 
order_id,
fina_id,
amount,
date,
sum_amount,
case when sum_sign = 1 then 1 else 0 end as is_sign
from t3
自此得到最终的结果表
order_id	fina_id	amount	date			sum_amount		is_sign
123			 t1		 30		2020-05-30			30				0
123			 t2		 100	2020-05-31			130				0
123			 t3		 500	2020-06-05			630				1
123			 t4		 70		2020-07-01			700				0

之所以要这种形式，是因为要根据查询时间计算转化人数，当累计金额大于300时，才算上一次人数，而后其他则不再计算

这个方法是我个人所做，不知道有没有更加简便的方法，希望有更加简便方法的朋友，可以告知，谢谢