posgres窗口函数

已于 2023-08-08 14:45:07 修改
阅读量366 收藏 1
点赞数 1
文章标签： sql 数据库 postgresql
于 2023-08-04 19:39:12 首次发布
原文链接：https://blog.csdn.net/youzi85/article/details/128004059
版权

postgres窗口函数

postgresql之窗口函数
工作中可能会遇到按照部门业绩排名?找出前N的员工进行业绩奖励？两次消费时间隔了多久？等等这样的问题。
对于这样的问题，使用窗口函数能很好的简化sql。

窗口函数是sql中一类特别的函数，通过查询筛选出的行的某些部分，窗口调用函数实现了类似于聚合函数的功能。 但是两者又不同，通俗的讲，聚合函数是将结果合并成一行（每组一条数据），但是窗口函数是扫描所有的行，然后你的表有几行，结果就是有几行。

有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数的语法
窗口函数调用语法如下：

function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ([expression [, expression ... ]]) OVER window_name
function_name ( * ) OVER ( window_definition )
function_name ( * ) OVER window_name

这里的window_definiton语法如下：

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

window_name引用的是查询语句中WINDOW 子句定义的命名窗口规范。命名窗口规范通常只是用OVER window_name 来引用
PARTITION BY选项将查询的行分为一组进入partitions， 这些行在窗口函数中单独处理。PARTITION BY和查询级别GROUP BY 子句做相似的工作，除了它的表达式只能作为表达式不能作为输出列的名字或数。 没有PARTITION BY，所有由查询产生的行被视为一个单独的分区
ORDER BY 选项决定分区中的行被窗口函数处理的顺序。它和查询级别ORDER BY子句做相似的工作， 但是同样的它不能作为输出列的名字或数。没有ORDER BY，行以一个不被预知的顺序处理。
这里frame_clause用在动态窗口函数中，静态窗口函数哪怕用了也不生效， frame_clause可以是：

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

可以用RANGE 或 ROWS模式声明；不管哪种情况， 它的变化范围是从frame_start到frame_end。如果省略了frame_end 默认为CURRENT ROW。
frame_start和frame_end可以是：

UNBOUNDED PRECEDING  # 表示框架从分区的第一行开始
UNBOUNDED FOLLOWING # 表示框架以分区的最后一行结束
CURRENT ROW # 1.rowsm模式：意味着框架以当前行开始或结束
			# 2.在RANGE模式，意味着框架以当前行在 ORDER BY序列中的第一个或最后一个元素开始或结束
value PRECEDING # 边界是当前行减去value的值
value FOLLOWING # 边界是当前行加上value的值

注意：value PRECEDING和value FOLLOWING 当前只允许ROWS模式。这也就意味着，窗口范围从当前行之前或之后指定的行数启动或结束。 value必须是整型表达式，而不能包含变量，聚合函数，或者窗口函数。 该值不能为空或负，但可以是零，表示只选择当前行本身。
例如，以下写法都是可以的：

rows Between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING 窗口中当前分区的所有行，相当于不写
rows UNBOUNDED PRECEDING 表示窗口范围从分区的第一行到当前当前行
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示窗口范围包括前一行，当前行，下一行共3行的数据范围

窗口函数适用的函数

目前，窗口函数可以调用的不仅有内建的窗口函数，还有任何用户定义的聚合函数和内建聚合函数，常见的min(),max(),count(),sum(),avg()就是可用在窗口函数中的内建聚合函数，其余可用的内建函数见官网文档。
内建窗口函数：

函数返回类型描述
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

将内建窗口函数分类，可分为

序号函数：row_number() | rank() | dense_rank()
分布函数：percent_rank() | cume_dist()
前后函数：lag() | lead()
其他函数：ntile() | nth_value()
头尾函数：first_value() | last_value()
其中：nth_value(),first_value(),last_value()是可实现动态窗口的。

sql示例

接下来，给出sql实例，一起来看看这些窗口函数的用法。
插入测试数据：

create table window_test(
name varchar(255),
subjects varchar(255),
scores int4
);
insert into window_test values ('张三','语文',random()*100);
insert into window_test values ('张三','数学',random()*100);
insert into window_test values ('张三','英语',random()*100);
insert into window_test values ('张三','化学',random()*100);
insert into window_test values ('张三','物理',random()*100);
insert into window_test values ('张三','生物',random()*100);
insert into window_test values ('张三','历史',random()*100);
insert into window_test values ('张三','地理',random()*100);
insert into window_test values ('张三','政治',random()*100);
insert into window_test values ('李四','语文',random()*100);
insert into window_test values ('李四','数学',random()*100);
insert into window_test values ('李四','英语',random()*100);
insert into window_test values ('李四','化学',random()*100);
insert into window_test values ('李四','物理',random()*100);
insert into window_test values ('李四','生物',random()*100);
insert into window_test values ('李四','历史',random()*100);
insert into window_test values ('李四','地理',random()*100);
insert into window_test values ('李四','政治',random()*100);
insert into window_test values ('王五','语文',random()*100);
insert into window_test values ('王五','数学',random()*100);
insert into window_test values ('王五','英语',random()*100);
insert into window_test values ('王五','化学',random()*100);
insert into window_test values ('王五','物理',random()*100);
insert into window_test values ('王五','生物',random()*100);
insert into window_test values ('王五','历史',random()*100);
insert into window_test values ('王五','地理',random()*100);
insert into window_test values ('王五','政治',random()*100);
insert into window_test values ('赵六','语文',random()*100);
insert into window_test values ('赵六','数学',random()*100);
insert into window_test values ('赵六','英语',random()*100);
insert into window_test values ('赵六','化学',random()*100);
insert into window_test values ('赵六','物理',random()*100);
insert into window_test values ('赵六','生物',random()*100);
insert into window_test values ('赵六','历史',random()*100);
insert into window_test values ('赵六','地理',random()*100);
insert into window_test values ('赵六','政治',random()*100);

1. 序号函数：row_number() | rank() |dense_rank()

select name,subjects,scores,
row_number() over (partition by name order by scores desc) as row_number,
rank() over (partition by name order by scores desc) as rank,
dense_rank() over (partition by name order by scores desc) as drank
from window_test;

显示结果如下图。表示每个同学按照学习成绩降序显示结果。这三个函数都能得到序号结果。

wind=# select name,subjects,scores,
wind-# row_number() over (partition by name order by scores desc) as row_number,
wind-# rank() over (partition by name order by scores desc) as rank,
wind-# dense_rank() over (partition by name order by scores desc) as drank
wind-# from window_test;
 name | subjects | scores | row_number | rank | drank 
------+----------+--------+------------+------+-------
 张三 | 生物     |     92 |          1 |    1 |     1
 张三 | 数学     |     92 |          2 |    1 |     1
 张三 | 语文     |     75 |          3 |    3 |     2
 张三 | 历史     |     74 |          4 |    4 |     3
 张三 | 地理     |     71 |          5 |    5 |     4
 张三 | 政治     |     54 |          6 |    6 |     5
 张三 | 英语     |     46 |          7 |    7 |     6
 张三 | 物理     |     45 |          8 |    8 |     7
 张三 | 化学     |      6 |          9 |    9 |     8
 李四 | 英语     |     95 |          1 |    1 |     1
 李四 | 数学     |     85 |          2 |    2 |     2
 李四 | 地理     |     66 |          3 |    3 |     3
 李四 | 语文     |     59 |          4 |    4 |     4
 李四 | 物理     |     55 |          5 |    5 |     5
 李四 | 历史     |     47 |          6 |    6 |     6
 李四 | 化学     |     36 |          7 |    7 |     7
 李四 | 生物     |     31 |          8 |    8 |     8
 李四 | 政治     |     17 |          9 |    9 |     9
 王五 | 政治     |     87 |          1 |    1 |     1
 王五 | 英语     |     77 |          2 |    2 |     2
 王五 | 地理     |     76 |          3 |    3 |     3
 王五 | 化学     |     68 |          4 |    4 |     4
 王五 | 历史     |     64 |          5 |    5 |     5
 王五 | 数学     |     35 |          6 |    6 |     6
 王五 | 物理     |     21 |          7 |    7 |     7
 王五 | 生物     |     15 |          8 |    8 |     8
 王五 | 语文     |     11 |          9 |    9 |     9
 赵六 | 地理     |     96 |          1 |    1 |     1
 赵六 | 英语     |     91 |          2 |    2 |     2
 赵六 | 历史     |     88 |          3 |    3 |     3
 赵六 | 物理     |     75 |          4 |    4 |     4
 赵六 | 政治     |     70 |          5 |    5 |     5
 赵六 | 数学     |     61 |          6 |    6 |     6
 赵六 | 生物     |     21 |          7 |    7 |     7
 赵六 | 化学     |      6 |          8 |    8 |     8
 赵六 | 语文     |      5 |          9 |    9 |     9
(36 rows)

wind=#

这些函数可以运用于查询每个同学最好的几门课的成绩等这类问题。但是添加过滤条件的时候不能直接加在where条件中，而是需要将查询结果作为子表，在外面套查询语句再用where过滤。原因是窗口函数是在group by，having之后执行的，在where这一步还没执行到窗口函数

-- 若是直接 where条件中过滤，会报错
select name,subjects,scores,
row_number() over (partition by name order by scores desc) as row_number,
rank() over (partition by name order by scores desc) as rank,
dense_rank() over (partition by name order by scores desc) as drank
from window_test
where row_number<=4;

select * from (
	select name,subjects,scores,
	row_number() over (partition by name order by scores desc) as row_number,
	rank() over (partition by name order by scores desc) as rank,
	dense_rank() over (partition by name order by scores desc) as drank
	from window_test
)a
where row_number<=4;

得到结果看下图。查询得到了每个同学最高的四门课的成绩。注意王五同学的成绩，因为王五同学存在三门课同分的情况，这时用row_number、rank、dense_rank得到的结果不同，这也就是这三个函数不同的地方。

wind=# select * from (
wind(# select name,subjects,scores,
wind(# row_number() over (partition by name order by scores desc) as row_number,
wind(# rank() over (partition by name order by scores desc) as rank,
wind(# dense_rank() over (partition by name order by scores desc) as drank
wind(# from window_test
wind(# )a
wind-# where row_number<=4;
 name | subjects | scores | row_number | rank | drank 
------+----------+--------+------------+------+-------
 张三 | 生物     |     92 |          1 |    1 |     1
 张三 | 数学     |     92 |          2 |    1 |     1
 张三 | 语文     |     75 |          3 |    3 |     2
 张三 | 历史     |     74 |          4 |    4 |     3
 李四 | 英语     |     95 |          1 |    1 |     1
 李四 | 数学     |     85 |          2 |    2 |     2
 李四 | 地理     |     66 |          3 |    3 |     3
 李四 | 语文     |     59 |          4 |    4 |     4
 王五 | 政治     |     87 |          1 |    1 |     1
 王五 | 英语     |     77 |          2 |    2 |     2
 王五 | 地理     |     76 |          3 |    3 |     3
 王五 | 化学     |     68 |          4 |    4 |     4
 赵六 | 地理     |     96 |          1 |    1 |     1
 赵六 | 英语     |     91 |          2 |    2 |     2
 赵六 | 历史     |     88 |          3 |    3 |     3
 赵六 | 物理     |     75 |          4 |    4 |     4
(16 rows)

wind=#

row_number()就是得到行号，不管你的成绩是否一样，都按序依次标号。rank()和dense_rank()都是对同样的成绩都是统一排序，同一名次，但是不同的是，rank()函数对于后面的数据是直接跳过并列数的结果往下编号，而dense_rank()是不跳过序号直接往下编号。
另外，这三个函数对于滑动窗口的使用都是无效的，不会报错，但是没什么改变，以row_number()举个例子看看。

select name,subjects,scores,row_number() over (partition by name order by scores desc),
row_number() over (partition by name order by scores desc rows between 1 preceding and 1 following)
from window_test;

wind=# select name,subjects,scores,row_number() over (partition by name order by scores desc),
wind-# row_number() over (partition by name order by scores desc rows between 1 preceding and 1 following)
wind-# from window_test;
 name | subjects | scores | row_number | row_number 
------+----------+--------+------------+------------
 张三 | 生物     |     92 |          1 |          1
 张三 | 数学     |     92 |          2 |          2
 张三 | 语文     |     75 |          3 |          3
 张三 | 历史     |     74 |          4 |          4
 张三 | 地理     |     71 |          5 |          5
 张三 | 政治     |     54 |          6 |          6
 张三 | 英语     |     46 |          7 |          7
 张三 | 物理     |     45 |          8 |          8
 张三 | 化学     |      6 |          9 |          9
 李四 | 英语     |     95 |          1 |          1
 李四 | 数学     |     85 |          2 |          2
 李四 | 地理     |     66 |          3 |          3
 李四 | 语文     |     59 |          4 |          4
 李四 | 物理     |     55 |          5 |          5
 李四 | 历史     |     47 |          6 |          6
 李四 | 化学     |     36 |          7 |          7
 李四 | 生物     |     31 |          8 |          8
 李四 | 政治     |     17 |          9 |          9
 王五 | 政治     |     87 |          1 |          1
 王五 | 英语     |     77 |          2 |          2
 王五 | 地理     |     76 |          3 |          3
 王五 | 化学     |     68 |          4 |          4
 王五 | 历史     |     64 |          5 |          5
 王五 | 数学     |     35 |          6 |          6
 王五 | 物理     |     21 |          7 |          7
 王五 | 生物     |     15 |          8 |          8
 王五 | 语文     |     11 |          9 |          9
 赵六 | 地理     |     96 |          1 |          1
 赵六 | 英语     |     91 |          2 |          2
 赵六 | 历史     |     88 |          3 |          3
 赵六 | 物理     |     75 |          4 |          4
 赵六 | 政治     |     70 |          5 |          5
 赵六 | 数学     |     61 |          6 |          6
 赵六 | 生物     |     21 |          7 |          7
 赵六 | 化学     |      6 |          8 |          8
 赵六 | 语文     |      5 |          9 |          9
(36 rows)

wind=#

2. 分布函数：percent_rank() | cume_dist()

percent_rank():(rank - 1) / (总行数 - 1)
cume_dist():(前面的行数或与当前行相同的行数)/(总行数)

select name,subjects,scores,
percent_rank() over (partition by name order by scores desc),
cume_dist() over (partition by name order by scores desc)
from window_test;

结果显示如下图。percent_rank()函数是根据rank-1去计算的，所以从0开始计算，两个占比分布函数的第一个数据一个是0/8,一个是1/9计算，然后遇到相同排名的时候，因为rank是并列排名，并列排名后是间隔排名，所以排第四的科目排名直接是(4-1)/8。而cume_dist则是并列排名的比例都一样，然后计算前面的不同成绩的行数(这里是0)+当前相同成绩的行数(这里是3)然后计算排名，就是3/9。

wind=# select name,subjects,scores,
wind-# percent_rank() over (partition by name order by scores desc),
wind-# cume_dist() over (partition by name order by scores desc)
wind-# from window_test;
 name | subjects | scores | percent_rank |     cume_dist      
------+----------+--------+--------------+--------------------
 张三 | 生物     |     92 |            0 | 0.2222222222222222
 张三 | 数学     |     92 |            0 | 0.2222222222222222
 张三 | 语文     |     75 |         0.25 | 0.3333333333333333
 张三 | 历史     |     74 |        0.375 | 0.4444444444444444
 张三 | 地理     |     71 |          0.5 | 0.5555555555555556
 张三 | 政治     |     54 |        0.625 | 0.6666666666666666
 张三 | 英语     |     46 |         0.75 | 0.7777777777777778
 张三 | 物理     |     45 |        0.875 | 0.8888888888888888
 张三 | 化学     |      6 |            1 |                  1
 李四 | 英语     |     95 |            0 | 0.1111111111111111
 李四 | 数学     |     85 |        0.125 | 0.2222222222222222
 李四 | 地理     |     66 |         0.25 | 0.3333333333333333
 李四 | 语文     |     59 |        0.375 | 0.4444444444444444
 李四 | 物理     |     55 |          0.5 | 0.5555555555555556
 李四 | 历史     |     47 |        0.625 | 0.6666666666666666
 李四 | 化学     |     36 |         0.75 | 0.7777777777777778
 李四 | 生物     |     31 |        0.875 | 0.8888888888888888
 李四 | 政治     |     17 |            1 |                  1
 王五 | 政治     |     87 |            0 | 0.1111111111111111
 王五 | 英语     |     77 |        0.125 | 0.2222222222222222
 王五 | 地理     |     76 |         0.25 | 0.3333333333333333
 王五 | 化学     |     68 |        0.375 | 0.4444444444444444
 王五 | 历史     |     64 |          0.5 | 0.5555555555555556
 王五 | 数学     |     35 |        0.625 | 0.6666666666666666
 王五 | 物理     |     21 |         0.75 | 0.7777777777777778
 王五 | 生物     |     15 |        0.875 | 0.8888888888888888
 王五 | 语文     |     11 |            1 |                  1
 赵六 | 地理     |     96 |            0 | 0.1111111111111111
 赵六 | 英语     |     91 |        0.125 | 0.2222222222222222
 赵六 | 历史     |     88 |         0.25 | 0.3333333333333333
 赵六 | 物理     |     75 |        0.375 | 0.4444444444444444
 赵六 | 政治     |     70 |          0.5 | 0.5555555555555556
 赵六 | 数学     |     61 |        0.625 | 0.6666666666666666
 赵六 | 生物     |     21 |         0.75 | 0.7777777777777778
 赵六 | 化学     |      6 |        0.875 | 0.8888888888888888
 赵六 | 语文     |      5 |            1 |                  1
(36 rows)

如果还是不太理解cume_dist()，我们再举个例子。

在这里插入图片描述

这个函数可以应用于求每个科目及格的同学情况及及格率占比。

select * from (
select subjects,name,scores,
cume_dist() over (partition by subjects order by scores desc)
from window_test
)a where scores>=60;


wind=# select * from (
wind(# select subjects,name,scores,
wind(# cume_dist() over (partition by subjects order by scores desc)
wind(# from window_test
wind(# )a where scores>=60;
 subjects | name | scores | cume_dist 
----------+------+--------+-----------
 化学     | 王五 |     68 |      0.25
 历史     | 赵六 |     88 |      0.25
 历史     | 张三 |     74 |       0.5
 历史     | 王五 |     64 |      0.75
 地理     | 赵六 |     96 |      0.25
 地理     | 王五 |     76 |       0.5
 地理     | 张三 |     71 |      0.75
 地理     | 李四 |     66 |         1
 政治     | 王五 |     87 |      0.25
 政治     | 赵六 |     70 |       0.5
 数学     | 张三 |     92 |      0.25
 数学     | 李四 |     85 |       0.5
 数学     | 赵六 |     61 |      0.75
 物理     | 赵六 |     75 |      0.25
 生物     | 张三 |     92 |      0.25
 英语     | 李四 |     95 |      0.25
 英语     | 赵六 |     91 |       0.5
 英语     | 王五 |     77 |      0.75
 语文     | 张三 |     75 |      0.25
(19 rows)

wind=#

同理，这两个函数只存在静态窗口，滑动窗口使用无效，以percent_rank()为例子。

select name,subjects,scores,percent_rank() over (partition by name order by scores desc),
percent_rank() over (partition by name order by scores desc rows between 1 preceding and 1 following)
from window_test;
--执行结果：
wind=# select name,subjects,scores,percent_rank() over (partition by name order by scores desc),
wind-# percent_rank() over (partition by name order by scores desc rows between 1 preceding and 1 following)
wind-# from window_test;
 name | subjects | scores | percent_rank | percent_rank 
------+----------+--------+--------------+--------------
 张三 | 生物     |     92 |            0 |            0
 张三 | 数学     |     92 |            0 |            0
 张三 | 语文     |     75 |         0.25 |         0.25
 张三 | 历史     |     74 |        0.375 |        0.375
 张三 | 地理     |     71 |          0.5 |          0.5
 张三 | 政治     |     54 |        0.625 |        0.625
 张三 | 英语     |     46 |         0.75 |         0.75
 张三 | 物理     |     45 |        0.875 |        0.875
 张三 | 化学     |      6 |            1 |            1
 李四 | 英语     |     95 |            0 |            0
 李四 | 数学     |     85 |        0.125 |        0.125
 李四 | 地理     |     66 |         0.25 |         0.25
 李四 | 语文     |     59 |        0.375 |        0.375
 李四 | 物理     |     55 |          0.5 |          0.5
 李四 | 历史     |     47 |        0.625 |        0.625
 李四 | 化学     |     36 |         0.75 |         0.75
 李四 | 生物     |     31 |        0.875 |        0.875
 李四 | 政治     |     17 |            1 |            1
 王五 | 政治     |     87 |            0 |            0
 王五 | 英语     |     77 |        0.125 |        0.125
 王五 | 地理     |     76 |         0.25 |         0.25
 王五 | 化学     |     68 |        0.375 |        0.375
 王五 | 历史     |     64 |          0.5 |          0.5
 王五 | 数学     |     35 |        0.625 |        0.625
 王五 | 物理     |     21 |         0.75 |         0.75
 王五 | 生物     |     15 |        0.875 |        0.875
 王五 | 语文     |     11 |            1 |            1
 赵六 | 地理     |     96 |            0 |            0
 赵六 | 英语     |     91 |        0.125 |        0.125
 赵六 | 历史     |     88 |         0.25 |         0.25
 赵六 | 物理     |     75 |        0.375 |        0.375
 赵六 | 政治     |     70 |          0.5 |          0.5
 赵六 | 数学     |     61 |        0.625 |        0.625
 赵六 | 生物     |     21 |         0.75 |         0.75
 赵六 | 化学     |      6 |        0.875 |        0.875
 赵六 | 语文     |      5 |            1 |            1
(36 rows)

wind=#

  1. 前后函数：lag() | lead()
    lag()和lead()表示计算当前行的前n行或后n行，n不写默认是1，如果设置default,则表示没有这样的行默认用default代替。
    这两个函数利用计算当前行的前n行或后n行，可以用来处理类似连续登陆n天，或者登陆时间最大间隔天数等的问题。
    以lag()为例，首先插入测试数据：

    create table login_log(
id varchar(255),
log_time date
);
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-14');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-16');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-17');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-18');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-19');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-20');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-21');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-22');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-23');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-24');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-14');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-16');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-18');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-19');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-20');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-21');

    若是想要看看哪些用户连续登陆7天，sql如下：

    select *,lag(log_time,6) over (partition by id order by log_time)
from login_log
--执行结果：

wind=# select *,lag(log_time,6) over (partition by id order by log_time)
wind-# from login_log
wind-# ;
 id  |  log_time  |    lag     
-----+------------+------------
 101 | 2022-02-14 | 
 101 | 2022-02-15 | 
 101 | 2022-02-16 | 
 101 | 2022-02-17 | 
 101 | 2022-02-18 | 
 101 | 2022-02-19 | 
 101 | 2022-02-20 | 2022-02-14
 101 | 2022-02-21 | 2022-02-15
 101 | 2022-02-22 | 2022-02-16
 102 | 2022-02-15 | 
 102 | 2022-02-23 | 
 102 | 2022-02-24 | 
 103 | 2022-02-14 | 
 103 | 2022-02-15 | 
 103 | 2022-02-16 | 
 103 | 2022-02-18 | 
 103 | 2022-02-19 | 
 103 | 2022-02-20 | 
 103 | 2022-02-21 | 2022-02-14
(19 rows)

wind=#

    此时lag()函数将log_time下滑6行进行匹配，如果连续登陆的话，两个时间差之间的间隔应该是<=6的，以此来过滤出用户。

    select id,log_time,lag from (
select *,lag(log_time,6) over (partition by id order by log_time)
from login_log
)a where log_time-lag<=6;
--执行结果：
wind=# select id,log_time,lag from (
wind(# select *,lag(log_time,6) over (partition by id order by log_time)
wind(# from login_log
wind(# )a where log_time-lag<=6;
 id  |  log_time  |    lag     
-----+------------+------------
 101 | 2022-02-20 | 2022-02-14
 101 | 2022-02-21 | 2022-02-15
 101 | 2022-02-22 | 2022-02-16
(3 rows)

wind=#

    如上图，最后得到只有101用户是连续7天登陆的。
    例如想知道用户登录最大不登录时间间隔，sql如下：

    select id,max(log_time-lag) from (
select *,lag(log_time) over (partition by id order by log_time)
from login_log
)tmp group by id;
--执行结果：
wind=# select id,max(log_time-lag) from (
wind(# select *,lag(log_time) over (partition by id order by log_time)
wind(# from login_log
wind(# )tmp group by id;
 id  | max 
-----+-----
 101 |   1
 102 |   8
 103 |   2
(3 rows)

wind=#

    同样，这两个函数也只有静态窗口。

    1. 其他函数：ntile() | nth_value()

    ntile()
    ntile()函数表示将数据分成尽可能相等的分区，n传入多少就分成几组，若是n是1，则所有数据都在一组相当于没分区，若是n>=总的条数，那也不会报错，只是每条记录都是一组数据。
    这个函数可以用来解决随机分组的问题，例如将班级中的学生随机分成2组。

    select name,ntile(2) over (order by random())
from window_test
group by name
--执行结果：
wind=# select name,ntile(2) over (order by random())
wind-# from window_test
wind-# group by name
wind-# ;
 name | ntile 
------+-------
 王五 |     1
 赵六 |     1
 张三 |     2
 李四 |     2
(4 rows)

wind=#

    order by random():表示随机排序，也就是一个打乱的一个过程，这里不是必须的，可以删掉的
    window_function() over ():over后面不加partition by的时候，表示不再分区，而是对整表进行操作。

    1. nth_value()
      返回窗口中排名第n行的计算值。这个函数可以使用于在显示每个科目考得前n名的成绩的同学。以每科考前2名成绩的同学为例：
    select subjects,name,scores,nth_value(scores,2) over (partition by subjects order by scores desc)
from window_test
wind=# select subjects,name,scores,nth_value(scores,2) over (partition by subjects order by scores desc)
wind-# from window_test
wind-# ;
 subjects | name | scores | nth_value 
----------+------+--------+-----------
 化学     | 王五 |     68 |          
 化学     | 李四 |     36 |        36
 化学     | 张三 |      6 |        36
 化学     | 赵六 |      6 |        36
 历史     | 赵六 |     88 |          
 历史     | 张三 |     74 |        74
 历史     | 王五 |     64 |        74
 历史     | 李四 |     47 |        74
 地理     | 赵六 |     96 |          
 地理     | 王五 |     76 |        76
 地理     | 张三 |     71 |        76
 地理     | 李四 |     66 |        76
 政治     | 王五 |     87 |          
 政治     | 赵六 |     70 |        70
 政治     | 张三 |     54 |        70
 政治     | 李四 |     17 |        70
 数学     | 张三 |     92 |          
 数学     | 李四 |     85 |        85
 数学     | 赵六 |     61 |        85
 数学     | 王五 |     35 |        85
 物理     | 赵六 |     75 |          
 物理     | 李四 |     55 |        55
 物理     | 张三 |     45 |        55
 物理     | 王五 |     21 |        55
 生物     | 张三 |     92 |          
 生物     | 李四 |     31 |        31
 生物     | 赵六 |     21 |        31
 生物     | 王五 |     15 |        31
 英语     | 李四 |     95 |          
 英语     | 赵六 |     91 |        91
 英语     | 王五 |     77 |        91
 英语     | 张三 |     46 |        91
 语文     | 张三 |     75 |          
 语文     | 李四 |     59 |        59
 语文     | 王五 |     11 |        59
 语文     | 赵六 |      5 |        59
(36 rows)

wind=#

    此时得到的结果如上图，这里因为是排名第2的值，所以在最高分的nth_value是null。基于这样的数据条件，想得到每个科目成绩前两名成绩的情况是，条件时nth_value是null，或者scores>=nth_value,综上，班级前2名成绩的sql如下：

    select subjects,name,scores from (
select subjects,name,scores,nth_value(scores,2) over (partition by subjects order by scores desc)
from window_test
)a where scores>=nth_value or nth_value is null
;
--执行结果：
wind=# select subjects,name,scores from (
wind(# select subjects,name,scores,nth_value(scores,2) over (partition by subjects order by scores desc)
wind(# from window_test
wind(# )a where scores>=nth_value or nth_value is null
wind-# ;
 subjects | name | scores 
----------+------+--------
 化学     | 王五 |     68
 化学     | 李四 |     36
 历史     | 赵六 |     88
 历史     | 张三 |     74
 地理     | 赵六 |     96
 地理     | 王五 |     76
 政治     | 王五 |     87
 政治     | 赵六 |     70
 数学     | 张三 |     92
 数学     | 李四 |     85
 物理     | 赵六 |     75
 物理     | 李四 |     55
 生物     | 张三 |     92
 生物     | 李四 |     31
 英语     | 李四 |     95
 英语     | 赵六 |     91
 语文     | 张三 |     75
 语文     | 李四 |     59
(18 rows)

wind=#

    这个函数是可以使用滑动窗口的，当使用了滑动窗口，那和静态窗口得到的数据就会有所不同

    select subjects,name,scores,
nth_value(scores,1) over (partition by subjects order by scores desc),
nth_value(scores,1) over (partition by subjects order by scores desc rows between 1 preceding and 1 following)
from window_test
--执行结果：
wind=# select subjects,name,scores,
wind-# nth_value(scores,1) over (partition by subjects order by scores desc),
wind-# nth_value(scores,1) over (partition by subjects order by scores desc rows between 1 preceding and 1 following)
wind-# from window_test
wind-# ;
 subjects | name | scores | nth_value | nth_value 
----------+------+--------+-----------+-----------
 化学     | 王五 |     68 |        68 |        68
 化学     | 李四 |     36 |        68 |        68
 化学     | 张三 |      6 |        68 |        36
 化学     | 赵六 |      6 |        68 |         6
 历史     | 赵六 |     88 |        88 |        88
 历史     | 张三 |     74 |        88 |        88
 历史     | 王五 |     64 |        88 |        74
 历史     | 李四 |     47 |        88 |        64
 地理     | 赵六 |     96 |        96 |        96
 地理     | 王五 |     76 |        96 |        96
 地理     | 张三 |     71 |        96 |        76
 地理     | 李四 |     66 |        96 |        71
 政治     | 王五 |     87 |        87 |        87
 政治     | 赵六 |     70 |        87 |        87
 政治     | 张三 |     54 |        87 |        70
 政治     | 李四 |     17 |        87 |        54
 数学     | 张三 |     92 |        92 |        92
 数学     | 李四 |     85 |        92 |        92
 数学     | 赵六 |     61 |        92 |        85
 数学     | 王五 |     35 |        92 |        61
 物理     | 赵六 |     75 |        75 |        75
 物理     | 李四 |     55 |        75 |        75
 物理     | 张三 |     45 |        75 |        55
 物理     | 王五 |     21 |        75 |        45
 生物     | 张三 |     92 |        92 |        92
 生物     | 李四 |     31 |        92 |        92
 生物     | 赵六 |     21 |        92 |        31
 生物     | 王五 |     15 |        92 |        21
 英语     | 李四 |     95 |        95 |        95
 英语     | 赵六 |     91 |        95 |        95
 英语     | 王五 |     77 |        95 |        91
 英语     | 张三 |     46 |        95 |        77
 语文     | 张三 |     75 |        75 |        75
 语文     | 李四 |     59 |        75 |        75
 语文     | 王五 |     11 |        75 |        59
 语文     | 赵六 |      5 |        75 |        11
(36 rows)

    在这里插入图片描述

此时，所用的滑动窗口是将前一行，当前行，后一行的数据作为一个整体比较，取这三个数据中的第一个数据。

  1. 头尾函数：first_value() | last_value()
    其实first_value()和nth_value()当n=1的时候是一样的，但是对于不知道整体几行的时候，无法知道最后一行的行数的时候，可以使用last_value()。而且这两个函数也是可以使用滑动窗口。
select subjects,name,scores,
first_value(scores) over (partition by subjects ),
first_value(scores) over (partition by subjects rows between 1 preceding and 1 following),
last_value(scores) over (partition by subjects ),
last_value(scores) over (partition by subjects rows between 1 preceding and 1 following)
from window_test
--执行结果：
wind=# select subjects,name,scores,
wind-# first_value(scores) over (partition by subjects ),
wind-# first_value(scores) over (partition by subjects rows between 1 preceding and 1 following),
wind-# last_value(scores) over (partition by subjects ),
wind-# last_value(scores) over (partition by subjects rows between 1 preceding and 1 following)
wind-# from window_test
wind-# ;
 subjects | name | scores | first_value | first_value | last_value | last_value 
----------+------+--------+-------------+-------------+------------+------------
 化学     | 李四 |     36 |          36 |          36 |          6 |         68
 化学     | 王五 |     68 |          36 |          36 |          6 |          6
 化学     | 赵六 |      6 |          36 |          68 |          6 |          6
 化学     | 张三 |      6 |          36 |           6 |          6 |          6
 历史     | 赵六 |     88 |          88 |          88 |         47 |         74
 历史     | 张三 |     74 |          88 |          88 |         47 |         64
 历史     | 王五 |     64 |          88 |          74 |         47 |         47
 历史     | 李四 |     47 |          88 |          64 |         47 |         47
 地理     | 李四 |     66 |          66 |          66 |         96 |         76
 地理     | 王五 |     76 |          66 |          66 |         96 |         71
 地理     | 张三 |     71 |          66 |          76 |         96 |         96
 地理     | 赵六 |     96 |          66 |          71 |         96 |         96
 政治     | 张三 |     54 |          54 |          54 |         70 |         17
 政治     | 李四 |     17 |          54 |          54 |         70 |         87
 政治     | 王五 |     87 |          54 |          17 |         70 |         70
 政治     | 赵六 |     70 |          54 |          87 |         70 |         70
 数学     | 王五 |     35 |          35 |          35 |         61 |         92
 数学     | 张三 |     92 |          35 |          35 |         61 |         85
 数学     | 李四 |     85 |          35 |          92 |         61 |         61
 数学     | 赵六 |     61 |          35 |          85 |         61 |         61
 物理     | 赵六 |     75 |          75 |          75 |         21 |         45
 物理     | 张三 |     45 |          75 |          75 |         21 |         55
 物理     | 李四 |     55 |          75 |          45 |         21 |         21
 物理     | 王五 |     21 |          75 |          55 |         21 |         21
 生物     | 王五 |     15 |          15 |          15 |         21 |         31
 生物     | 李四 |     31 |          15 |          15 |         21 |         92
 生物     | 张三 |     92 |          15 |          31 |         21 |         21
 生物     | 赵六 |     21 |          15 |          92 |         21 |         21
 英语     | 李四 |     95 |          95 |          95 |         91 |         77
 英语     | 王五 |     77 |          95 |          95 |         91 |         46
 英语     | 张三 |     46 |          95 |          77 |         91 |         91
 英语     | 赵六 |     91 |          95 |          46 |         91 |         91
 语文     | 王五 |     11 |          11 |          11 |          5 |         59
 语文     | 李四 |     59 |          11 |          11 |          5 |         75
 语文     | 张三 |     75 |          11 |          59 |          5 |          5
 语文     | 赵六 |      5 |          11 |          75 |          5 |          5
(36 rows)

wind=#

6. 聚合函数
聚合函数也可以配合使用窗口函数，以sum()函数为例，利用窗口函数求每个同学的成绩。

select name,subjects,scores,
sum(scores) over (partition by name),
sum(scores) over (partition by name order by subjects),
sum(scores) over (partition by name order by subjects rows between 1 preceding and 2 following)
from window_test;
--执行借结果：
wind=# select name,subjects,scores,
wind-# sum(scores) over (partition by name),
wind-# sum(scores) over (partition by name order by subjects),
wind-# sum(scores) over (partition by name order by subjects rows between 1 preceding and 2 following)
wind-# from window_test;
 name | subjects | scores | sum | sum | sum 
------+----------+--------+-----+-----+-----
 张三 | 化学     |      6 | 555 |   6 | 151
 张三 | 历史     |     74 | 555 |  80 | 205
 张三 | 地理     |     71 | 555 | 151 | 291
 张三 | 政治     |     54 | 555 | 205 | 262
 张三 | 数学     |     92 | 555 | 297 | 283
 张三 | 物理     |     45 | 555 | 342 | 275
 张三 | 生物     |     92 | 555 | 434 | 258
 张三 | 英语     |     46 | 555 | 480 | 213
 张三 | 语文     |     75 | 555 | 555 | 121
 李四 | 化学     |     36 | 491 |  36 | 149
 李四 | 历史     |     47 | 491 |  83 | 166
 李四 | 地理     |     66 | 491 | 149 | 215
 李四 | 政治     |     17 | 491 | 166 | 223
 李四 | 数学     |     85 | 491 | 251 | 188
 李四 | 物理     |     55 | 491 | 306 | 266
 李四 | 生物     |     31 | 491 | 337 | 240
 李四 | 英语     |     95 | 491 | 432 | 185
 李四 | 语文     |     59 | 491 | 491 | 154
 王五 | 化学     |     68 | 454 |  68 | 208
 王五 | 历史     |     64 | 454 | 132 | 295
 王五 | 地理     |     76 | 454 | 208 | 262
 王五 | 政治     |     87 | 454 | 295 | 219
 王五 | 数学     |     35 | 454 | 330 | 158
 王五 | 物理     |     21 | 454 | 351 | 148
 王五 | 生物     |     15 | 454 | 366 | 124
 王五 | 英语     |     77 | 454 | 443 | 103
 王五 | 语文     |     11 | 454 | 454 |  88
 赵六 | 化学     |      6 | 513 |   6 | 190
 赵六 | 历史     |     88 | 513 |  94 | 260
 赵六 | 地理     |     96 | 513 | 190 | 315
 赵六 | 政治     |     70 | 513 | 260 | 302
 赵六 | 数学     |     61 | 513 | 321 | 227
 赵六 | 物理     |     75 | 513 | 396 | 248
 赵六 | 生物     |     21 | 513 | 417 | 192
 赵六 | 英语     |     91 | 513 | 508 | 117
 赵六 | 语文     |      5 | 513 | 513 |  96
(36 rows)

wind=#

由上图可以看出，使用sum()对每个同学求总分，就能得到一列sum列来显示每个同学的总分，若是添加order by，则是根据科目实现成绩累加的sum1。函数若是配合使用滑动窗口，则是得到窗口内的总分和，此时的order by的累加功能已经失效了，其实和不写效果一样。

注意事项
窗口函数一定配有 over ()语句，若是不需要排序分组，对整表进行操作，可以不写里面的内容，只跟一个()即可。
如果查询包含窗口函数，并且查询使用任何的聚合、group by或者having，那么使用窗口函数的行是聚合得到的组的行，而不是从from/where 得到的原始表行。
窗口调用函数只能在select列，或者查询的order by子句中使用
当查询涉及多个窗口函数时，可以写成每一个都带有单独的OVER子句， 但是，如果期待为多个窗口函数采用相同的窗口行为，这样做就会产生重复，并且容易出错。 作为代替，每个窗口行为可以在WINDOW子句中进行命名，然后再被OVER引用，例如：

  SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

当语法over子句和window子句部分内置部分在Windows子句时，语法注意内容可看：[窗口定义：内置使用 OVER 子句和 WINDOW 子句 ]
window子句必须位于 select 语句的 order by 子句之前
其他参考文档内容：
http://postgres.cn/docs/9.3/tutorial-window.html
http://postgres.cn/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

好啦，本文到这里就结束了。
感谢您的阅读~
————————————————
版权声明：本文为CSDN博主「youzi85」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/youzi85/article/details/128004059

风和雪的爱情
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
posgres help中文
10-26
posgres help中文 帮助文档
node-postgres-example：在Node.js中运行基本的容器化Posgres dev env的示例
02-20
Node.js和PostgreSQL示例 使用容器化的PostgreSQL实例设置Node应用程序的基础知识。 此回购包括： 使用docker-compose.yml的基本容器设置 一项针对PostgreSQL服务构建和运行测试的GitHub操作 PostgreSQL迁移 使用pg的数据库配置 Mocha单元测试示例，查询容器化数据库 一个简化的，可重用的initdb脚本，让您轻松运行 用于单元测试和开发环境的不同数据库配置 有用的NPM脚本 要求 Node.js，我建议使用最新的LTS（当前为v12） 码头工人 设置 $ npm install $ npm run initdb 运行测试 $ npm test
【sql高级】postgresql之窗口函数用法
jayxiaoxionger的博客
03-22 741
postgresql之窗口函数用法
docker-base:使用 jdk 8 + maven 3.3 + posgres 9.4 + node.js 0.12 的 Docker 镜像
06-02
码头工人基地 使用 jdk 8 + maven 3.3 + posgres 9.4 + node.js 0.12 的 Docker 镜像
postgresql-9.0-802.jdbc3.jar
11-25
postgresql-9.0-802.jdbc3.jar postgres数据库的驱动包
postgresql之窗口函数
youzi85的博客
11-25 4544
工作中可能会遇到按照部门业绩排名?找出前N的员工进行业绩奖励？两次消费时间隔了多久？等等这样的问题。对于这样的问题，使用窗口函数能很好的简化sql。并且窗口函数还可以配合连表使用，能简化语法，加快搜索速度。窗口函数是sql中一类特别的函数，通过查询筛选出的行的某些部分，窗口调用函数实现了类似于聚合函数的功能。但是两者又不同，通俗的讲，聚合函数是将结果合并成一行（每组一条数据），但是窗口函数是扫描所有的行，然后你的表有几行，结果就是有几行。
PostGreSQL开窗函数
千屈的博客
09-14 4218
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 wil
PostgreSQL窗口函数
u011447403的专栏
02-18 5031
一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的`OVER`子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理
PostgresSQL的开窗函数
hanwb1的博客
03-12 1854
官方链接 4.2.8. 窗口函数调用   一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚集类的函数。和非窗口聚集函数调用不同，这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出中每一个行仍保持独立。不过，窗口函数能够根据窗口函数调用的分组声明（PARTITION BY列表）访问属于当前行所在分组中的所有行。一个窗口函数调用的语法是下列之一： function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filt
PostgreSQL之SQL开窗函数
奋斗的阿杰的博客
11-12 1494
PostgreSQL之SQL开窗函数
posgres
02-21
posgres
pgsql的窗口函数简述
Lou_Lan的博客
08-10 2212
窗口函数（Window Function） 本身是聚集类函数，具有对特定结果行聚合处理的功能，但是窗口函数并不会使多行被聚集成一个单独的输出行，而是在函数计算的每一行都输出相应的结果，这与通常的非窗口聚集函数不同；此外，窗口函数作用在由关键字 OVER 划定的所谓 窗口 行上，这些行就是窗口函数的作用域。...
postgresql-窗口函数使用
u014033218的专栏
06-06 992
背景 PostgreSQL作为世界上功能最强大的开源数据库，窗口函数作为业务过程中进行数据统计必不可少的功能，对PG自然是不在话下，PG内置了大量的窗口函数。 本文就是和大家一起在实际业务场景下如何使用窗口函数进行一探究竟。。。 一、os环境及postgresql版本 os环境 os：centos 6.4 [postgres@ test ~]$ uname -a Linux db 2....
如何在Postgresql数据库中进行窗口函数的操作
theskylife的博客
08-09 1421
窗口函数如何运用？哪些场景可以用到窗口函数？窗口函数有哪些？SQL中如何进行滚动计算？SQL中如何计算同环比？SQL中如何求最大值、最小值？一篇文章掌握SQL中窗口函数的用法及其运用场景。.........
postgresql系列之窗口函数
热门推荐
uzong
11-11 1万+
本文是《sql基础教程》《postgresql实战》的读书笔记；具体可以参考这两本书相关章节。 一、 窗口函数 1.1 基本概念 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作；聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集的运算。与聚合函数不同的是，窗口函数并不会将结果集进行分组合并输出一行；而是将计算的结果合并到基于结果集运算的列上。 思考：为什么说窗口...
PG中的窗口函数
01-28 1601
PG中的窗口函数 窗口函数提供在与当前查询行相关的行集合上执行计算的能力。 通用窗口函数 函数 返回类型 描述 row_number() bigint 当前行在其分区中的行号，从1计 rank() bigint 带间隙的当前行排名；与该行的第一个同等行的row_number相同 dense_rank() bigint 不带间隙的当前行排名； percent_rank() double precision 当前行的相对排名：（rank-1)/(总行数-1) cume_dist(
PostgreSQL 窗口函数汇总
最新发布
一碗清深的博客
04-17 364
窗口函数是一种在查询结果中执行计算的特殊函数。它可以在不改变查询结果集的情况下，对结果集中的行进行分组、排序和计算。窗口函数可以用于计算排名、累计和、移动平均等各种分析操作。
postgresql-窗口函数
Java_Fly1的博客
09-10 889
包括 AVG、COUNT、MAX、MIN、SUM 以及STRING_AGG。聚合函数的作用是针对一组数据行进行运算，并且返回一条汇总结果分析的窗口函数（Window Function）。不过，窗口函数不是将一组数据汇总为单个结果，而是针对每一行数据，基于和它相关的一组数据计算出一个结果。下图演示了聚合函数和窗口函数的区别区别在于后者包含了 OVER 关键字；空括号表示将所有数据作为整体进行分析，所以得到的数值和聚合函数一样是窗口函数的名称；expression。
postgres 窗口函数
03-31
PostgreSQL窗口函数是一种特殊类型的函数，允许对查询结果集中的一组数据进行聚合或排序，并可以通过指定窗口规范来限制聚合或排序的范围。窗口函数在SELECT语句中使用，允许在查询结果中计算聚合值、排名、分区等操作，而不影响查询结果集的原始数据。 PostgreSQL支持的窗口函数包括： 1. 聚合函数：SUM、AVG、MIN、MAX等。 2. 排名函数：RANK、DENSE_RANK、ROW_NUMBER等。 3. 分区函数：PARTITION BY。 4. 窗口函数：OVER。 下面是一个示例，演示如何使用窗口函数计算每个订单的总金额和平均金额： SELECT order_id, SUM(amount) OVER (PARTITION BY order_id) AS total_amount, AVG(amount) OVER (PARTITION BY order_id) AS avg_amount FROM orders; 这个查询使用了SUM和AVG聚合函数，对每个订单的金额进行计算，并使用PARTITION BY将结果按订单分组。窗口函数OVER指定了计算聚合函数的范围，即每个订单的所有金额。查询结果包括每个订单的ID、总金额和平均金额。 总的来说，PostgreSQL窗口函数提供了一种强大的工具，可以在查询结果集中计算聚合、排序和分区等操作，使得数据分析和报表生成变得更加方便和高效。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值