postgresql

2024-01-29 09:50| 来源: 网络整理| 查看: 265

背景

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

【本文地址】

postgresql

postgresql

今日新闻

推荐新闻