TDSQL PostgreSQL版 高级 SQL 语句编写

一、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.86 2 | 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.9 4 | 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.54 2 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 4 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1915.86 5 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 6 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 7 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 8 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 9 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 10 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 11 | 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.86 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 2 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 3 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 4 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54 2 | 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.86 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 2 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 3 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 3 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54 2 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15(11 rows)

四、dense_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.86 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 2 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 3 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 3 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 4 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54 2 | 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.86 0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 0.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 1 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 0 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 0.25 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 0.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 0.75 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 1 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 0 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 1 | 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.86 0.33 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 0.67 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 1.00 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 0.20 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 0.40 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 0.60 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 0.80 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 1.00 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 0.50 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 1.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.86 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 1 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 2 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 1 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 1 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 2 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 2 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 2 | 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.86 1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 1 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 2 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 2 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 3 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 2 | 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.31 2022.31 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 8771.11 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 1030.9 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 4182.68 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 5365.04 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 9621.37 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 9886.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.86 0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 0 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 2022.31 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 0 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 0 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 1030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 4182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 5365.04 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 0 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 0 | 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.86 1316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 0 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 0 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 5365.04 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 9621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 8290.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 0 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 0 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 0 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 0 | 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.86 1316.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.27 5365.04 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 9621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 8290.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.11 2022.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.68 1030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 4182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 5365.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.86 2022.31 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 2022.31 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 2022.31 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1030.9 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 1030.9 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 1030.9 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 1030.9 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 1030.9 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 9886.15 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 9886.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.86 2022.31 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 8771.11 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 1316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 1030.9 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 4182.68 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 9621.37 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 9621.37 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 8290.5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54 9886.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.86 1316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 1316.27 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 1316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 9621.37 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 9621.37 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 9621.37 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 9621.37 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 9621.37 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 971.54 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 971.54 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(11 rows)

注意不要加上 order by id，默认情况下，带了 order by 参数会从分组的起始值开始一直叠加，直到当前值（不是当前记录）不同为止，当忽略 order by 参数则是整个分组。
下面通过修改分组的统计范围就可以实现 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.86 1316.27 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 1316.27 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 1316.27 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 8290.5 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 8290.5 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 8290.5 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 8290.5 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 8290.5 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 971.54 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 971.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.31 8771.11 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 8771.11 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 1316.27 | 6 | 5 0铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 1030.9 4182.68 | 7 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 4182.68 4182.68 | 8 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 5365.04 4182.68 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 4182.68 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 971.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.86 12109.69 | 4036.56333333333 | 三明市 | 2022.31 12109.69 | 4036.56333333333 | 三明市 | 8771.11 12109.69 | 4036.56333333333 | 三明市 | 1316.27 28490.49 | 5698.098 | 三门峡市 | 4182.68 28490.49 | 5698.098 | 三门峡市 | 8290.5 28490.49 | 5698.098 | 三门峡市 | 1030.9 28490.49 | 5698.098 | 三门峡市 | 5365.04 28490.49 | 5698.098 | 三门峡市 | 9621.37 10857.69 | 5428.845 | 上海市 | 9886.15 10857.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.86 12109.69 | 4036.56333333333 | 三明市 | 2022.31 12109.69 | 4036.56333333333 | 三明市 | 8771.11 12109.69 | 4036.56333333333 | 三明市 | 1316.27 28490.49 | 5698.098 | 三门峡市 | 4182.68 28490.49 | 5698.098 | 三门峡市 | 8290.5 28490.49 | 5698.098 | 三门峡市 | 1030.9 28490.49 | 5698.098 | 三门峡市 | 5365.04 28490.49 | 5698.098 | 三门峡市 | 9621.37 10857.69 | 5428.845 | 上海市 | 9886.15 10857.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.86 1 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 8771.11 2 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 2022.31 1 | 9 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 9621.37 2 | 10 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 8290.5 1 | 5 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 9886.15 2 | 11 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 971.54(7 rows)