窗口函数的使用
-
环境准备
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) ) distribute by shard(id) to group default_group; 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()返回行号，不分组
teledb=# select row_number() over(),* from bills limit 2; row_number | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+-----------+-----------+-----------+---------------------+--------- 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 2 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 (2 rows) teledb=# select row_number() over(),* from bills limit 2 offset 3; row_number | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+-----------------------+-----------+-----------+---------------------+--------- 3 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 4 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 (2 rows) -
row_number()--返回行号，按amount排序
teledb=# select row_number() over(order by amount),* from bills; row_number | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 35.93 2 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1715.46 3 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 1926.81 4 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1988.18 5 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 2787.06 6 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 2807.08 7 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 3346.33 8 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 4714.07 9 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5238.68 10 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 6888.82 11 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 8370.24 (11 rows) -
row_number()返回行号，按begincity分组，pubtime 排序
teledb=# select row_number() over(partition by begincity order by pubtime),* from bills; row_number | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 4 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 5 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)按begincity分组，按照pubtime进行排序，计算行号，每组的行号不中断
-
rank()返回行号，对比值重复时行号重复并间断，即返回1,2,3,3,5...
teledb=# select rank() over(partition by begincity order by pubtime),* from bills; rank | id | goodsdesc | beginunit | begincity | pubtime | amount ------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 5 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)使用rank(), order by比较的pubtime相同的情况下，行号重复并且间断
-
dense_rank()返回行号，对比值重复时行号重复但不间断，即返回1,2,3,3,4...
teledb=# select dense_rank() over(partition by begincity order by pubtime),* from bills; dense_rank | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 4 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)使用dense_rank()，order by比较的pubtime相同的情况下，行号重复，但是下一个不中断
-
percent_rank()从当前开始，计算在分组中的比例(行号 - 1) * (1 / (总记录数 - 1))
teledb=# select percent_rank() over(partition by begincity order by id),* from bills; percent_rank | id | goodsdesc | beginunit | begincity | pubtime | amount --------------+----+------------------------+-----------+-----------+---------------------+--------- 0 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 0.25 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 0.5 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 0.75 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 1 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 0.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 1 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 0 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) -
cume_dist()返回行数除以记录数值
teledb=# 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 -----------+----+---------------+----------+-----------+------------+--------- 0.20 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 0.40 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 0.60 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 0.80 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 1.00 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 0.33 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 0.67 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 1.00 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1.00 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 0.50 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1.00 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) -
ntile(分组数量)让所有记录尽可以的均匀分布
teledb=# select ntile(2) over(partition by begincity order by id),* from bills; ntile | id | goodsdesc | beginunit | begincity | pubtime | amount -------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 1 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 2 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 2 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 1 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 2 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) teledb=# select ntile(3) over(partition by begincity order by id),* from bills; ntile | id | goodsdesc | beginunit | begincity | pubtime | amount -------+----+------------------------+-----------+-----------+---------------------+--------- 1 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 2 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 2 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 3 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)调用ntile(k)，会将按照begincity分类后的记录，每一个类别平均分成k组，尽可能平均
-
lag(value any [, offset integer [, default any]] )返回偏移量值
offset integer是偏移值，正数时取前值，负数时取后值，没有取到值时用default代替
teledb=# select lag(amount,1,null) over(partition by begincity order by id),* from bills; lag | id | goodsdesc | beginunit | begincity | pubtime | amount ---------+----+------------------------+-----------+-----------+---------------------+--------- | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 6252.91 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 1828.83 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 9885.95 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 8195.98 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 6351.44 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1784.63 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)lag(amount, 1, null)，1为正数，取前一条记录的amount值。
teledb=# select lag(amount,-2,0::float8) over(partition by begincity order by id),* from bills; lag | id | goodsdesc | beginunit | begincity | pubtime | amount ---------+----+------------------------+-----------+-----------+---------------------+--------- 9376.8 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 9885.95 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 4971.79 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 0 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 0 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 7626.41 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 0 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 0 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 0 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 0 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 0 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)取后面第二条记录amount的值。
-
lead(value any [,offset integer [, default any]] )返回偏移量值
与lag类似，offset integer是偏移值，但是正数时取后值，负数时取前值，没有取到值时用default代替。
teledb=# select lead(amount,2,null) over(partition by begincity order by id),* from bills; lead | id | goodsdesc | beginunit | begincity | pubtime | amount ---------+----+------------------------+-----------+-----------+---------------------+--------- 9376.8 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 9885.95 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 4971.79 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 7626.41 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) -
first_value(value any) 返回第一值
teledb=# select first_value(amount) over(partition by begincity order by id),* from bills; first_value | id | goodsdesc | beginunit | begincity | pubtime | amount -------------+----+------------------------+-----------+-----------+---------------------+--------- 6252.91 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 6252.91 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 6252.91 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 6252.91 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 6252.91 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 8195.98 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 8195.98 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 8195.98 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1784.63 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1784.63 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)返回每组的第一个值。
-
last_value(value any) 返回最后值
teledb=# select last_value(amount) over(partition by begincity order by pubtime),* from bills; last_value | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+----+-----------+-----------+---------------------+--------- 6252.91 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 1828.83 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 9376.8 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 4971.79 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 8195.98 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 6351.44 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 7626.41 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1784.63 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) teledb=# select last_value(amount) over(partition by begincity),* FROM bills; last_value | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------------------------+-----------+-----------+---------------------+--------- 9376.8 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 9376.8 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 9376.8 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 9376.8 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 8195.98 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 8195.98 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 8195.98 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1425.64 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) teledb=# select last_value(amount) over(partition by begincity order by pubtime),* from bills; last_value | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------------------------+-----------+-----------+---------------------+--------- 6252.91 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 1828.83 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 9376.8 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 4971.79 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 8195.98 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 6351.44 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 7626.41 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1784.63 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) teledb=# select last_value(amount) over(partition by begincity),* FROM bills; last_value | id | goodsdesc | beginunit | begincity | pubtime | amount ------------+----+------+-----------+-----------+--------------+--------- 9376.8 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 9376.8 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 9376.8 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 9376.8 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 9376.8 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 8195.98 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 8195.98 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 8195.98 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1425.64 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)使用last_value时，注意不要加上order by，默认情况下，带了order by参数会从分组的起始值开始一直叠加，直到当前值（不是当前记录）不同为止，当忽略order by 参数则是整个分组。下面通过修改分组的统计范围就可以实现order by参数取最后值。
teledb=# 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 ------------+----+------------------------+-----------+-----------+---------------------+--------- 4971.79 | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 4971.79 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 4971.79 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 4971.79 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 4971.79 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 7626.41 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 7626.41 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 7626.41 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 3714.15 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1425.64 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows) -
nth_value(value any, nth integer)：返回窗口框架中的指定值。
teledb=# select nth_value(amount,2) over(partition by begincity order by id),* from bills; nth_value | id | goodsdesc | beginunit | begincity | pubtime | amount -----------+----+------------------------+-----------+-----------+---------------------+--------- | 5 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 6252.91 1828.83 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 1828.83 1828.83 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 1828.83 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 1828.83 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 4971.79 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 6351.44 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 6351.44 6351.44 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (11 rows)返回每组的第2个amount值
-
统计各个城市的总运费及平均每单的运费
teledb=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills; sum | avg | begincity | amount ----------+------------------+-----------+--------- 32316.28 | 6463.256 | 三门峡市 | 6252.91 32316.28 | 6463.256 | 三门峡市 | 1828.83 32316.28 | 6463.256 | 三门峡市 | 9885.95 32316.28 | 6463.256 | 三门峡市 | 4971.79 32316.28 | 6463.256 | 三门峡市 | 9376.8 22173.83 | 7391.27666666667 | 三明市 | 6351.44 22173.83 | 7391.27666666667 | 三明市 | 7626.41 22173.83 | 7391.27666666667 | 三明市 | 8195.98 3714.15 | 3714.15 | 三亚市 | 3714.15 3210.27 | 1605.135 | 上海市 | 1784.63 3210.27 | 1605.135 | 上海市 | 1425.64 (11 rows) -
窗口函数别名使用
teledb=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity); sum | avg | begincity | amount ----------+------------------+-----------+--------- 32316.28 | 6463.256 | 三门峡市 | 6252.91 32316.28 | 6463.256 | 三门峡市 | 1828.83 32316.28 | 6463.256 | 三门峡市 | 9885.95 32316.28 | 6463.256 | 三门峡市 | 4971.79 32316.28 | 6463.256 | 三门峡市 | 9376.8 22173.83 | 7391.27666666667 | 三明市 | 6351.44 22173.83 | 7391.27666666667 | 三明市 | 7626.41 22173.83 | 7391.27666666667 | 三明市 | 8195.98 3714.15 | 3714.15 | 三亚市 | 3714.15 3210.27 | 1605.135 | 上海市 | 1784.63 3210.27 | 1605.135 | 上海市 | 1425.64 (11 rows) -
获取每个城市运费前两名订单
teledb=# 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 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9885.95 2 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9376.8 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 8195.98 2 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 7626.41 1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 3714.15 1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 1784.63 2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 1425.64 (7 rows)
