postgresql-窗口函数使用 原作者:阿弟 创作时间:2016-12-19 22:49:02+08 |
doudou586 发布于2016-12-19 22:49:02 评论: 1 浏览: 15341 顶: 935 踩: 939 |
PostgreSQL作为世界上功能最强大的开源数据库,窗口函数作为业务过程中进行数据统计必不可少的功能,对PG自然是不在话下,PG内置了大量的窗口函数。
本文就是和大家一起在实际业务场景下如何使用窗口函数进行一探究竟。。。
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));
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=#
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=#
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=#
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)
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=#
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=#
--返回偏移量值,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)
--返回偏移量值,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)
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=#
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=#
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=#