hypc's blog
0%

Postgres分组统计后取前几行

Posted on Disqus:

使用ROW_NUMBER() OVER()来实现此功能，语法如下：

1
ROW_NUMBER() OVER([PARTITION BY col1] ORDER BY col2 [DESC])
  • ROW_NUMBER: 为返回的记录定义行编号
  • PARTITION BY col1: 根据字段col1进行分组
  • ORDER BY col2: 按字段col2进行排序

示例

某市停车场停车统计数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
parking_id,park_date,park_counts
00892a40c1ce0d7a02101d6f716a95a8,2020-01-01,479
011fca99dfc8fd22060c3d90c2c998e6,2020-01-01,205
01e29027cfc9833f076e4b2193c38c51,2020-01-01,24
03d4550171bc01e918fe6556e337e0b4,2020-01-01,29
04327a4180ca98105233098ac2ab730f,2020-01-01,180
0a577156a1b4e845ae9d1bff28fffef9,2020-01-01,9
0a8ee281cfc62618deff5fe2dcc56057,2020-01-01,16
0e7f4e3ac5f511e692ba0242ac120002,2020-01-01,51
10c72c08a28e825f48186ca8464fce6b,2020-01-01,604
1137233254950fc2c5a9997ec82e2f0e,2020-01-01,58
11d44b246fde92964e8d54a6ebebfa98,2020-01-01,467
12e59b7fe4b8c234d7726805a87acb5d,2020-01-01,361
136988fc7e0bf1f2b7cadce315260bc2,2020-01-01,90
14fc069d62cb11c574c356b2c22f4077,2020-01-01,25
16f3ed2ec60311e692ba0242ac120002,2020-01-01,299
17f52e57dbe684d427d58758b8f65963,2020-01-01,1081
18489e1d95fc35f88004473f1de3fbf3,2020-01-01,222
1e0e1e4540c766c9d16567536b1e6df3,2020-01-01,88
1e54816aa98ad53738c6d8b575d5ac68,2020-01-01,84

按停车场分组，查询每个停车场次数最多的三天的数据

1
2
3
4
5
6
7
8
9
10
SELECT parking_id,
       park_date,
       park_counts
FROM
  (SELECT *,
          row_number() over(PARTITION BY parking_id ORDER BY park_counts DESC) AS rn
   FROM park_stats) AS a
WHERE a.rn <= 3
ORDER BY parking_id,
         park_counts DESC;

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
parking_id,park_date,park_counts
00892a40c1ce0d7a02101d6f716a95a8,2020-01-11,661
00892a40c1ce0d7a02101d6f716a95a8,2020-01-12,644
00892a40c1ce0d7a02101d6f716a95a8,2020-01-18,632
011fca99dfc8fd22060c3d90c2c998e6,2020-01-16,477
011fca99dfc8fd22060c3d90c2c998e6,2020-01-17,469
011fca99dfc8fd22060c3d90c2c998e6,2020-01-15,468
01e29027cfc9833f076e4b2193c38c51,2020-01-14,168
01e29027cfc9833f076e4b2193c38c51,2020-01-15,165
01e29027cfc9833f076e4b2193c38c51,2020-01-10,162
031f697b85ee016ab98c44831e415568,2020-01-10,4
03d4550171bc01e918fe6556e337e0b4,2020-01-15,116
03d4550171bc01e918fe6556e337e0b4,2020-01-16,94
03d4550171bc01e918fe6556e337e0b4,2020-01-14,91
04327a4180ca98105233098ac2ab730f,2020-01-17,304
04327a4180ca98105233098ac2ab730f,2020-01-03,256
04327a4180ca98105233098ac2ab730f,2020-01-13,253

按停车日期分组，查询每天停车次数最多的三个停车场

1
2
3
4
5
6
7
8
9
10
SELECT park_date,
       parking_id,
       park_counts
FROM
  (SELECT *,
          row_number() over(PARTITION BY park_date ORDER BY park_counts DESC) AS rn
   FROM park_stats) AS a
WHERE a.rn <= 3
ORDER BY park_date,
         park_counts DESC;

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
park_date,parking_id,park_counts
2020-01-01,3198bed4067abf223c9766f0f6c0d7a1,4280
2020-01-01,a81ebf846194a367d2049f468d2a0cf4,2672
2020-01-01,fb718e3ff4aa5a5686f7ca8722073481,2398
2020-01-02,3198bed4067abf223c9766f0f6c0d7a1,3505
2020-01-02,b61eb790962cf024e717cf96239466c4,3396
2020-01-02,a81ebf846194a367d2049f468d2a0cf4,3063
2020-01-03,a81ebf846194a367d2049f468d2a0cf4,3585
2020-01-03,3198bed4067abf223c9766f0f6c0d7a1,3526
2020-01-03,b61eb790962cf024e717cf96239466c4,3517
2020-01-04,3198bed4067abf223c9766f0f6c0d7a1,3653
2020-01-04,a81ebf846194a367d2049f468d2a0cf4,3035
2020-01-04,fb718e3ff4aa5a5686f7ca8722073481,2847
2020-01-05,3198bed4067abf223c9766f0f6c0d7a1,3875
2020-01-05,a81ebf846194a367d2049f468d2a0cf4,3667
2020-01-05,fb718e3ff4aa5a5686f7ca8722073481,2597