源文档:http://www.postgres.cn/docs/9.3/functions-window.html(postgre的中文社区)
函数 | 返回类型 | 描述 |
---|---|---|
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。 |
INSERT INTO t_products VALUES ('0006', 'iPhone X', '9600', null, '电器');
INSERT INTO t_products VALUES ('0012', '电视', '3299', '4', '电器');
INSERT INTO t_products VALUES ('0004', '辣条', '5.6', '4', '零食');
INSERT INTO t_products VALUES ('0007', '薯条', '7.5', '1', '零食');
INSERT INTO t_products VALUES ('0009', '方便面', '3.5', '1', '零食');
INSERT INTO t_products VALUES ('0005', '铅笔', '7', '4', '文具');
INSERT INTO t_products VALUES ('0014', '作业本', '1', null, '文具');
INSERT INTO t_products VALUES ('0001', '鞋子', '27', '2', '衣物');
INSERT INTO t_products VALUES ('0002', '外套', '110.9', '3', '衣物');
INSERT INTO t_products VALUES ('0013', '围巾', '93', '5', '衣物');
INSERT INTO t_products VALUES ('0008', '香皂', '17.5', '2', '日用品');
INSERT INTO t_products VALUES ('0010', '水杯', '27', '3', '日用品');
INSERT INTO t_products VALUES ('0015', '洗发露', '36', '1', '日用品');
INSERT INTO t_products VALUES ('0011', '毛巾', '15', '1', '日用品');
INSERT INTO t_products VALUES ('0003', '手表', '1237.55', '5', '电器');
INSERT INTO t_products VALUES ('0016', '绘图笔', '15', null, '文具');
INSERT INTO t_products VALUES ('0017', '汽水', '3.5', null, '零食');
**需求:**给查询出来的数据添加一列序号
select type,name,price,row_number()
over(order by price asc) as idx
from t_products;
结果>
用窗口函数的好处不仅仅可实现序号列,还可以在over()内按指定的列排序,上图是按照price列升序。
对于以上提到的一个问题,根据上面的数据,再做个扩充——如果需要在类别(type)内按照价格(price) 升序排列(就是在类别内做排序),该怎么做呢?
很简单,只需在窗口(over())中声明分隔方式Partition。
select type,name,price,row_number()
over(partition by type order by price asc) as idx
from t_products;
结果>
上面的问题这里需求完美实现,这里其实还可以做个扩充,你可以注意到零食类别内的方便面和汽水价格是一样的,如何将零食和汽水并列第一呢?答案是:用窗口函数rank()
分类排序序号并列,rank()实现:
select type,name,price,rank()
over(partition by type order by price asc)
from t_products;
结果>
需求又完美的实现了,但零食类别中的第三个辣条 排到第三了,如果这里需要在类别里面能保持序号不重不少(将辣条排名至第二),如何实现呢?答案:使用窗口函数 dense_rank()
分类排序序号并列排序,dense_rank()实现:
select type,name,price,dense_rank()
over(partition by type order by price asc)
from t_products;
结果>
以上的几个窗口函数已经能实现大多数业务需求了,如果有兴趣可以看看一些特殊业务可能用到的功能,比如说如何限制序号在0到1之间排序呢?
限制序号在0~1之间(0作为第一个序),窗口函数 percernt_rank() :
select type,name,price,percent_rank()
over(partition by type order by price asc)
from t_products;
结果>
上面的percernt_rank()函数默认是从0开始排序的,如果需要使用相对0~1之间的排名,需要使用cume_dist()
限制序号在0~1之间相对排名,窗口函数 cume_dist() 实现:
select type,name,price,cume_dist()
over(partition by type order by price asc)
from t_products;
结果>
获取分类子项排序中的第一条记录的某个字段的值, first_value(val1) 实现:
select id,type,name,price,first_value(name)
over(partition by type order by price asc)
from t_products;
结果>
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。