postgresql-窗口函数使用
作者: 阿弟
联系: QQ:4893310 / Email:4893310@qq.com
背景
PostgreSQL作为世界上功能最强大的开源数据库，窗口函数作为业务过程中进行数据统计必不可少的功能，对PG自然是不在话下，PG内置了大量的窗口函数。
本文就是和大家一起在实际业务场景下如何使用窗口函数进行一探究竟。。。
一、os环境及postgresql版本
os环境
os：centos 6.4 [postgres@ test ~]$ uname -a Linux db 2.6.32-358.el6.x86_64 [postgres@ test ~]$ gcc -v gcc 版本 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC)
测试数据
create table bills ( id serial not null, goodsdesc text not null, beginunit text not null, begincity text not null, pubtime timestamp not null, amount float8 not null default 0, primary key (id) ); COMMENT ON TABLE bills is '运单记录'; COMMENT ON COLUMN bills.id IS 'id号'; COMMENT ON COLUMN bills.goodsdesc IS '货物名称'; COMMENT ON COLUMN bills.beginunit IS '启运省份'; COMMENT ON COLUMN bills.begincity IS '启运城市'; COMMENT ON COLUMN bills.pubtime IS '发布时间'; COMMENT ON COLUMN bills.amount IS '运费'; INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'衣服','海南省','三亚市','2015-10-05 09:32:01',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'建筑设备','福建省','三明市','2015-10-05 07:21:22',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'设备','福建省','三明市','2015-10-05 11:21:54',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'普货','福建省','三明市','2015-10-05 15:19:17',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'5 0铲车，后八轮翻斗车','河南省','三门峡市','2015-10-05 07:53:13',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'鲜香菇2000斤','河南省','三门峡市','2015-10-05 10:38:29',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'旋挖附件38吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'旋挖附件35吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'旋挖附件39吨','河南省','三门峡市','2015-10-05 11:38:38',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'设备','上海市','上海市','2015-10-05 07:59:35',ROUND((random()*10000)::NUMERIC,2)); INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) VALUES(default,'普货40吨需13米半挂一辆','上海市','上海市','2015-10-05 08:13:59',ROUND((random()*10000)::NUMERIC,2));
二、操作实践
row_number() --返回行号,对比值重复时行号不重复不间断，即返回1,2,3,4,5....,不返回1,2,2,4...
test=# select row_number() over(),* from bills limit 2;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+-----------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
2 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
(2 rows)
test=# select row_number() over(),* from bills limit 2 offset 2;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+-----------+-----------+-----------+---------------------+---------
3 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
4 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
(2 rows)
--接amount排序
test=# select row_number() over(partition by tableoid order by amount),* from bills;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
2 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
3 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
4 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
5 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
6 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
7 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
8 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
9 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
10 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
11 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
(11 rows)
test=#
--按begincity分组，pubtime排序，注意红色记录行号不间断
test=# select row_number() over(partition by begincity order by pubtime),* from bills;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
4 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
5 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
1 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
2 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
rank()--返回行号,对比值重复时行号重复并间断，即返回1,2,2,4...
test=# select rank() over(partition by begincity order by pubtime),* from bills;
rank | id | goodsdesc | beginunit | begincity | pubtime | amount
------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
5 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
1 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
2 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
dance_rank()--返回行号,对比值重复时行号重复但不间断，即返回1,2,2,3...
test=# select dense_rank() over(partition by begincity order by pubtime),* from bills;
dense_rank | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
1 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
2 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
percent_rank()从当前开始，计算在分组中的比例 (行号-1)*(1/(总记录数-1))
test=# select percent_rank() over(partition by begincity order by id),* from bills;
percent_rank | id | goodsdesc | beginunit | begincity | pubtime | amount
--------------+----+------------------------+-----------+-----------+---------------------+---------
0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
0.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
1 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
0 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
0.25 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
0.5 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
0.75 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
1 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
1 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select percent_rank() over(partition by begincity order by pubtime),* from bills;
percent_rank | id | goodsdesc | beginunit | begincity | pubtime | amount
--------------+----+------------------------+-----------+-----------+---------------------+---------
0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
0.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
1 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
0 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
0.25 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
0.5 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
0.5 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
1 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
1 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
cume_dist() --返回行数除以记录数值
test=# select ROUND((cume_dist() over(partition by begincity order by id))::NUMERIC,2) AS cume_dist,* from bills;
cume_dist | id | goodsdesc | beginunit | begincity | pubtime | amount
-----------+----+------------------------+-----------+-----------+---------------------+---------
1.00 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
0.33 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
0.67 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
1.00 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
0.20 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
0.40 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
0.60 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
0.80 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
1.00 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0.50 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
1.00 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
ntile(分组数量)--让所有记录尽可以的均匀分布
test=# select ntile(3) over(partition by begincity order by id),* from bills;
ntile | id | goodsdesc | beginunit | begincity | pubtime | amount
-------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
2 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
2 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
3 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
1 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
2 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select ntile(2) over(partition by begincity order by id),* from bills;
ntile | id | goodsdesc | beginunit | begincity | pubtime | amount
-------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
1 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
2 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
1 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
2 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
2 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
1 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
2 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
lag(value any [, offset integer [, default any ]])
--返回偏移量值，offset integer是偏移值，正数时前值，负数时后值，没有取到值时用default代替
test=# select lag(amount,1,null) over(partition by begincity order by id),* from bills;
lag | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
| 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
9370.12 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
6573.33 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
| 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2350.68 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
549 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
4089.25 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4766.76 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
| 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select lag(amount,1,0::float8) over(partition by begincity order by id),* from bills;
lag | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
9370.12 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
6573.33 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
0 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2350.68 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
549 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
4089.25 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4766.76 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select lag(amount,2,0::float8) over(partition by begincity order by id),* from bills;
lag | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
0 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
9370.12 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
0 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
0 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
2350.68 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
549 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4089.25 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
0 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select lag(amount,-2,0::float8) over(partition by begincity order by id),* from bills;
lag | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1352.16 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
0 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
0 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
4089.25 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
4766.76 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
7614.53 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
0 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
0 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
0 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
0 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
lead(value any [, offset integer [, default any ]])
--返回偏移量值，offset integer是偏移值，正数时取后值，负数时取前值，没有取到值时用default代替
test=# select lead(amount,2,null) over(partition by begincity order by id),* from bills;
lead | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
| 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1352.16 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
| 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
| 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
4089.25 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
4766.76 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
7614.53 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
| 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
| 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
| 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
| 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=# select lead(amount,-2,null) over(partition by begincity order by id),* from bills;
lead | id | goodsdesc | beginunit | begincity | pubtime | amount
---------+----+------------------------+-----------+-----------+---------------------+---------
| 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
| 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
9370.12 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
| 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
| 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
2350.68 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
549 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4089.25 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
| 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
| 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
first_value(value any)返回第一值
test=# select first_value(amount) over(partition by begincity order by id),* from bills;
first_value | id | goodsdesc | beginunit | begincity | pubtime | amount
-------------+----+------------------------+-----------+-----------+---------------------+---------
1569.6 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
9370.12 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
9370.12 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
9370.12 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
2350.68 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
2350.68 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
2350.68 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
2350.68 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
2350.68 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
5094.08 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
last_value(value any)返回最后值
test=# select last_value(amount) over(partition by begincity order by pubtime),* FROM bills;
last_value | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1569.6 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
9370.12 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
6573.33 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
1352.16 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
2350.68 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
549 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
4766.76 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
4766.76 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
7614.53 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
5094.08 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
test=# select last_value(amount) over(partition by begincity),* FROM bills;
last_value | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1569.6 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
9370.12 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
9370.12 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
9370.12 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
4089.25 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
4089.25 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
4089.25 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
4089.25 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
4089.25 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
5094.08 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
(11 rows)
注意不要加上order by id，默认情况下，带了order by 参数会从分组的起始值开始一直叠加，直到当前值（不是当前记录）不同为止，当忽略order by 参数则是整个分组。下面通过修改分组的统计范围就可以实现order by参数取最后值
test=# select last_value(amount) over(partition by begincity order by id range between unbounded preceding and
unbounded following),* FROM bills;
last_value | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1569.6 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
1352.16 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
1352.16 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
1352.16 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
7614.53 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
7614.53 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
7614.53 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
7614.53 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
7614.53 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
5333.02 | 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
nth_value(value any, nth integer)：返回窗口框架中的指定值
test=# select nth_value(amount,2) over(partition by begincity order by id),* from bills;
nth_value | id | goodsdesc | beginunit | begincity | pubtime | amount
-----------+----+------------------------+-----------+-----------+---------------------+---------
| 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1569.6
| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 9370.12
6573.33 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6573.33
6573.33 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1352.16
| 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2350.68
549 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 549
549 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4089.25
549 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 4766.76
549 | 11 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 7614.53
| 9 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5094.08
5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 5333.02
(11 rows)
test=#
统计各个城市的总运费及平均每单的运费
test=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills; sum | avg | begincity | amount ----------+------------------+-----------+--------- 1569.6 | 1569.6 | 三亚市 | 1569.6 17295.61 | 5765.20333333333 | 三明市 | 6573.33 17295.61 | 5765.20333333333 | 三明市 | 1352.16 17295.61 | 5765.20333333333 | 三明市 | 9370.12 19370.22 | 3874.044 | 三门峡市 | 4766.76 19370.22 | 3874.044 | 三门峡市 | 7614.53 19370.22 | 3874.044 | 三门峡市 | 2350.68 19370.22 | 3874.044 | 三门峡市 | 549 19370.22 | 3874.044 | 三门峡市 | 4089.25 10427.1 | 5213.55 | 上海市 | 5333.02 10427.1 | 5213.55 | 上海市 | 5094.08 (11 rows) test=#
窗口函数别名使用
test=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity); sum | avg | begincity | amount ----------+------------------+-----------+--------- 1569.6 | 1569.6 | 三亚市 | 1569.6 17295.61 | 5765.20333333333 | 三明市 | 6573.33 17295.61 | 5765.20333333333 | 三明市 | 1352.16 17295.61 | 5765.20333333333 | 三明市 | 9370.12 19370.22 | 3874.044 | 三门峡市 | 4766.76 19370.22 | 3874.044 | 三门峡市 | 7614.53 19370.22 | 3874.044 | 三门峡市 | 2350.68 19370.22 | 3874.044 | 三门峡市 | 549 19370.22 | 3874.044 | 三门峡市 | 4089.25 10427.1 | 5213.55 | 上海市 | 5333.02 10427.1 | 5213.55 | 上海市 | 5094.08 (11 rows) test=#
三、相关文档
- http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/
- http://my.oschina.net/Kenyon/blog/79543
党章学习心得:https://www.nanss.com/xuexi/2612.html 心灵鸡汤:https://www.nanss.com/yulu/2912.html 红岩读后感:https://www.nanss.com/xuexi/2609.html 让生活更美好:https://www.nanss.com/xuexi/2605.html 疫情作文800字最美逆行者:https://www.nanss.com/xuexi/2601.html 解除劳动合同证明:https://www.nanss.com/gongzuo/2652.html 辞职书模板:https://www.nanss.com/gongzuo/2658.html 入团申请书:https://www.nanss.com/xuexi/2664.html 家风作文:https://www.nanss.com/xuexi/2868.html 青春励志演讲稿:https://www.nanss.com/xuexi/2835.html 我的家乡作文:https://www.nanss.com/xuexi/2887.html 傲慢与偏见读后感:https://www.nanss.com/shenghuo/2886.html 长津湖观后感:https://www.nanss.com/xuexi/2796.html 形形色色的人作文:https://www.nanss.com/xuexi/2625.html 培训计划:https://www.nanss.com/gongzuo/2775.html 爱国故事:https://www.nanss.com/yuedu/2909.html 生活中的数学:https://www.nanss.com/shenghuo/2888.html 自我鉴定毕业生登记表:https://www.nanss.com/xuexi/2665.html 写人作文:https://www.nanss.com/xuexi/2876.html 关于成长的作文:https://www.nanss.com/xuexi/2743.html 学习心得体会:https://www.nanss.com/xuexi/2614.html 公务员年度考核总结:https://www.nanss.com/gongzuo/2573.html 党课学习心得:https://www.nanss.com/xuexi/2623.html 仿写繁星七一:https://www.nanss.com/xuexi/2098.html 爱情密语:https://www.nanss.com/yulu/2528.html 工作心得体会:https://www.nanss.com/gongzuo/2615.html 沙棘的功效与作用:https://www.nanss.com/yinshi/2929.html 比喻句:https://www.nanss.com/xuexi/2778.html 书籍是人类进步的阶梯:https://www.nanss.com/xuexi/2930.html 少先队员入队申请书:https://www.nanss.com/xuexi/2922.html