postgres窗口函数

最新推荐文章于 2024-05-31 17:41:09 发布

奋斗_CQS

最新推荐文章于 2024-05-31 17:41:09 发布

阅读量1.9k

点赞数

分类专栏： postgresql

postgresql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

测试数据：

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 '运费';

插入数据，测试数据如下：

mydb=# select * from bills;
id | goodsdesc | beginunit | begincity | pubtime | amount
----+------------------------+-----------+-----------+---------------------+---------
1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29

(11 rows)

1、row_number() --返回行号,对比值重复时行号不重复不间断，即返回 1,2,3,4,5....,不返回 1,2,2,4...

mydb=# select row_number() over(),* from bills;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
4 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
5 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
6 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
7 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
8 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
9 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
10 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
11 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
(11 rows)

mydb=# select row_number() over(),* from bills limit 2 offset 2;
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+----------------------+-----------+-----------+---------------------+---------
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
4 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
(2 rows)

mydb=# select row_number() over(partition by tableoid order by amount desc),* from bills; --按amount字段排序

row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
4 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
5 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
6 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
7 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
8 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
9 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
10 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
11 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
(11 rows)

mydb=# select row_number() over(partition by begincity order by pubtime asc),* from bills;--按begincity分组，pubtime升序排序
row_number | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
1 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
4 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
5 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

2、rank()--返回行号,对比值重复时行号重复并间断，即返回 1,2,2,4...

mydb=# 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 | 1621.47
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
1 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
5 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

3、dance_rank()--返回行号,对比值重复时行号重复但不间断，即返回 1,2,2,3...

dense_rank | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
1 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
2 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
3 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
4 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

4、percent_rank()从当前开始，计算在分组中的比例 (行号-1)*(1/(总记录数-1))

mydb=# 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 | 1621.47
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
0.5 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
1 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
0 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
0.25 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
0.5 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
0.5 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
1 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
0 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
1 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

5、cume_dist() --返回行数除以记录数值

mydb=# 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 | 1621.47
0.33 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
0.67 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
1.00 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
0.20 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
0.40 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
0.60 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
0.80 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
1.00 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
0.50 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
1.00 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

6、ntile(分组数量)--让所有记录尽可以的均匀分布

mydb=# 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 | 1621.47
1 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
2 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
3 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
1 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
1 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
2 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
2 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
3 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
1 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
2 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

7、lag(value any [, offset integer [, default any ]])--返回偏移量值，offset integer 是偏移值，正数时前值，负数时后值，没有取到值时用 default 代替

mydb=# 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 | 1621.47
0 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
165.29 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
114.85 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
0 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
9407.32 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
7806.61 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
6313.45 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
6850.54 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
0 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
5147.07 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

8、lead(value any [, offset integer [, default any ]])--返回偏移量值，offset integer 是偏移值，正数时取后值，负数时取前值，没有取到值时用 default 代替

mydb=# 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 | 1621.47
5409.37 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
| 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
| 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
6313.45 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
6850.54 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3739.03 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
| 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
| 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
| 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
| 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

9、first_value(value any)返回第一值

mydb=# select first_value(amount) over(partition by begincity order by id),* from bills;
first_value | id | goodsdesc | beginunit | begincity | pubtime | amount
-------------+----+------------------------+-----------+-----------+---------------------+---------
1621.47 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
165.29 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
165.29 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
165.29 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
9407.32 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
9407.32 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
9407.32 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
9407.32 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
9407.32 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
5147.07 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
5147.07 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

10、last_value(value any)返回最后值

mydb=# select last_value(amount) over(partition by begincity order by pubtime),* FROM bills;
last_value | id | goodsdesc | beginunit | begincity | pubtime | amount
------------+----+------------------------+-----------+-----------+---------------------+---------
1621.47 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
165.29 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
114.85 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
5409.37 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
9407.32 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
7806.61 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
6850.54 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
6850.54 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
3739.03 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
5147.07 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
3564.18 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

注意不要加上 order by id，默认情况下，带了 order by 参数会从分组的起始值开始一直叠加，直到当前值（不是当前记录）不同为止，当忽略 order by 参数则是整个分组。下面通过修改分组的统计范围就可以实现 order by 参数取最后值。

mydb=# 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
------------+----+------------------------+-----------+-----------+---------------------+---------
1621.47 | 1 | 衣服 | 海南省 | 三亚市 | 2015-10-05 09:32:01 | 1621.47
5409.37 | 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
5409.37 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
5409.37 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
3739.03 | 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
3739.03 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
3739.03 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
3739.03 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
3739.03 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
3564.18 | 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
3564.18 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

11、nth_value(value any, nth integer)：返回窗口框架中的指定值

mydb=# 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 | 1621.47
| 2 | 建筑设备 | 福建省 | 三明市 | 2015-10-05 07:21:22 | 165.29
114.85 | 3 | 设备 | 福建省 | 三明市 | 2015-10-05 11:21:54 | 114.85
114.85 | 4 | 普货 | 福建省 | 三明市 | 2015-10-05 15:19:17 | 5409.37
| 5 | 50铲车，后八轮翻斗车 | 河南省 | 三门峡市 | 2015-10-05 07:53:13 | 9407.32
7806.61 | 6 | 鲜香菇2000斤 | 河南省 | 三门峡市 | 2015-10-05 10:38:29 | 7806.61
7806.61 | 7 | 旋挖附件38吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6313.45
7806.61 | 8 | 旋挖附件35吨 | 河南省 | 三门峡市 | 2015-10-05 10:48:38 | 6850.54
7806.61 | 9 | 旋挖附件39吨 | 河南省 | 三门峡市 | 2015-10-05 11:38:38 | 3739.03
| 10 | 设备 | 上海市 | 上海市 | 2015-10-05 07:59:35 | 5147.07
3564.18 | 11 | 普货40吨需13米半挂一辆 | 上海市 | 上海市 | 2015-10-05 08:13:59 | 3564.18
(11 rows)

12、利用sum()函数和avg()函数统计各个城市的总运费及平均每单的运费

mydb=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills;
sum | avg | begincity | amount
----------+------------------+-----------+---------
1621.47 | 1621.47 | 三亚市 | 1621.47
5689.51 | 1896.50333333333 | 三明市 | 114.85
5689.51 | 1896.50333333333 | 三明市 | 5409.37
5689.51 | 1896.50333333333 | 三明市 | 165.29
34116.95 | 6823.39 | 三门峡市 | 3739.03
34116.95 | 6823.39 | 三门峡市 | 6313.45
34116.95 | 6823.39 | 三门峡市 | 6850.54
34116.95 | 6823.39 | 三门峡市 | 7806.61
34116.95 | 6823.39 | 三门峡市 | 9407.32
8711.25 | 4355.625 | 上海市 | 3564.18
8711.25 | 4355.625 | 上海市 | 5147.07
(11 rows)

13、窗口函数别名使用

mydb=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity);
sum | avg | begincity | amount
----------+------------------+-----------+---------
1621.47 | 1621.47 | 三亚市 | 1621.47
5689.51 | 1896.50333333333 | 三明市 | 114.85
5689.51 | 1896.50333333333 | 三明市 | 5409.37
5689.51 | 1896.50333333333 | 三明市 | 165.29
34116.95 | 6823.39 | 三门峡市 | 3739.03
34116.95 | 6823.39 | 三门峡市 | 6313.45
34116.95 | 6823.39 | 三门峡市 | 6850.54
34116.95 | 6823.39 | 三门峡市 | 7806.61
34116.95 | 6823.39 | 三门峡市 | 9407.32
8711.25 | 4355.625 | 上海市 | 3564.18
8711.25 | 4355.625 | 上海市 | 5147.07
(11 rows)

奋斗_CQS

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
postgres窗口函数

测试数据：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
复制链接

扫一扫