窗口函数的使用
环境准备
drop table if exists bills ;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() 返回行号,不分组
postgres=# select row_number() over(),* from bills limit 2;row_number | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+-----------+-----------+-----------+---------------------+---------1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.862 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31(2 rows)
postgres=# select row_number() over(),* from bills limit 2 offset 2;row_number | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+-----------------------+-----------+-----------+---------------------+---------3 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.94 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04(2 rows)5.3.1.3、row_number() --返回行号,按amount排序postgres=# select row_number() over(order by amount),* from bills;row_number | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+------------------------+-----------+-----------+---------------------+---------1 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.542 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.93 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.274 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.865 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.316 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.687 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.048 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.59 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.1110 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.3711 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)
row_number() 返回行号,按 begincity 分组,pubtime 排序
postgres=# 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 | 1915.861 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.113 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.92 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.683 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.044 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.375 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.542 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)
rank() 返回行号,对比值重复时行号重复并间断,即返回1,2,2,4...
postgres=# 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 | 1915.861 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.113 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.92 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.683 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.043 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.375 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.542 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)
dance_rank() 返回行号,对比值重复时行号重复但不间断,即返回1,2,2,3...
postgres=# 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 | 1915.861 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.113 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.92 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.683 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.043 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.374 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.542 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)
percent_rank() 从当前开始,计算在分组中的比例 (行号 - 1) * (1 / (总记录数 - 1))
postgres=# 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 | 1915.860 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.310.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.111 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.270 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.90.25 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.680.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.040.75 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.371 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.50 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.151 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
cume_dist() 返回行数除以记录数值
postgres=# 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 | 1915.860.33 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.310.67 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.111.00 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.270.20 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.90.40 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.680.60 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.040.80 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.371.00 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.50.50 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.151.00 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
ntile(分组数量) 让所有记录尽可以的均匀分布
postgres=# 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 | 1915.861 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.311 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.112 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.91 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.681 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.042 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.372 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.152 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)postgres=# 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 | 1915.861 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.113 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.91 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.682 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.042 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.373 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.152 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
lag(value any [, offset integer [, default any]] ) 返回偏移量值
offset integer 是偏移值,正数时取前值,负数时取后值,没有取到值时用 default 代替。
postgres=# 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 | 1915.86| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312022.31 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.118771.11 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27| 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.91030.9 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.684182.68 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.045365.04 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.379621.37 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5| 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.159886.15 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)postgres=# 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 | 1915.860 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.310 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.112022.31 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.270 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.90 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.681030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.044182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.375365.04 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.50 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.150 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)postgres=# 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 | 1915.861316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.310 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.110 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.275365.04 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.99621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.688290.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.040 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.370 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.50 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.150 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
lead(value any [,offset integer [, default any]] ) 返回偏移量值
offset integer 是偏移值,正数时取后值,负数时取前值,没有取到值时用 default 代替。
postgres=# 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 | 1915.861316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31| 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11| 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.275365.04 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.99621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.688290.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04| 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37| 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5| 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15| 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)postgres=# 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 | 1915.86| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31| 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.112022.31 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27| 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9| 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.681030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.044182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.375365.04 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5| 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15| 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
first_value(value any) 返回第一值
postgres=# select first_value(amount) over(partition by begincity order by id),* from bills;first_value | id | goodsdesc | beginunit | begincity | pubtime | amount-------------+----+------------------------+-----------+-----------+---------------------+---------1915.86 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.862022.31 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.312022.31 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.112022.31 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271030.9 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.91030.9 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.681030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.041030.9 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.371030.9 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.59886.15 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.159886.15 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
last_value(value any) 返回最后值
postgres=# select last_value(amount) over(partition by begincity order by pubtime),* FROM bills;last_value | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+------------------------+-----------+-----------+---------------------+---------1915.86 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.862022.31 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.318771.11 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.111316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.271030.9 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.94182.68 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.689621.37 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.049621.37 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.378290.5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.549886.15 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)postgres=# select last_value(amount) over(partition by begincity),* FROM bills;last_value | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+------------------------+-----------+-----------+---------------------+---------1915.86 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.861316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.311316.27 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.111316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.279621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.689621.37 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.59621.37 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.99621.37 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.049621.37 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37971.54 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
注意不要加上 order by id,默认情况下,带了 order by 参数会从分组的起始值开始一直叠加,直到当前值(不是当前记录)不同为止,当忽略 order by 参数则是整个分组。n下面通过修改分组的统计范围就可以实现 order by 参数取最后值。
postgres=# 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------------+----+------------------------+-----------+-----------+---------------------+---------1915.86 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.861316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.311316.27 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.111316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.278290.5 | 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.98290.5 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.688290.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.048290.5 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.378290.5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5971.54 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
nth_value(value any, nth integer):返回窗口框架中的指定值
postgres=# 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 | 1915.86| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.318771.11 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.118771.11 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27| 6 | 5 0铲车,后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.94182.68 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.684182.68 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.044182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.374182.68 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5| 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)
统计各个城市的总运费及平均每单的运费
postgres=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills;sum | avg | begincity | amount----------+------------------+-----------+---------1915.86 | 1915.86 | 三亚市 | 1915.8612109.69 | 4036.56333333333 | 三明市 | 2022.3112109.69 | 4036.56333333333 | 三明市 | 8771.1112109.69 | 4036.56333333333 | 三明市 | 1316.2728490.49 | 5698.098 | 三门峡市 | 4182.6828490.49 | 5698.098 | 三门峡市 | 8290.528490.49 | 5698.098 | 三门峡市 | 1030.928490.49 | 5698.098 | 三门峡市 | 5365.0428490.49 | 5698.098 | 三门峡市 | 9621.3710857.69 | 5428.845 | 上海市 | 9886.1510857.69 | 5428.845 | 上海市 | 971.54(11 rows)
窗口函数别名使用
postgres=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity);sum | avg | begincity | amount----------+------------------+-----------+---------1915.86 | 1915.86 | 三亚市 | 1915.8612109.69 | 4036.56333333333 | 三明市 | 2022.3112109.69 | 4036.56333333333 | 三明市 | 8771.1112109.69 | 4036.56333333333 | 三明市 | 1316.2728490.49 | 5698.098 | 三门峡市 | 4182.6828490.49 | 5698.098 | 三门峡市 | 8290.528490.49 | 5698.098 | 三门峡市 | 1030.928490.49 | 5698.098 | 三门峡市 | 5365.0428490.49 | 5698.098 | 三门峡市 | 9621.3710857.69 | 5428.845 | 上海市 | 9886.1510857.69 | 5428.845 | 上海市 | 971.54(11 rows)
获取每个城市运费前两名订单
postgres=# select * from (select row_number() over(partition by begincity order by amount desc),* from bills) where row_number<3;row_number | id | goodsdesc | beginunit | begincity | pubtime | amount------------+----+------------------------+-----------+-----------+---------------------+---------1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.861 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.112 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.311 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.372 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.51 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.152 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(7 rows)