【数据库】对大数据量数据集，PostgreSQL分组统计数量，限定每组最多数量
一、背景介绍
在处理大数据量数据集时，我们经常需要进行分组统计。例如，我们需要统计每个城市的人口数量、每个年龄段的人数等。在 PostgreSQL 中，我们可以使用 row_number() 函数结合 over (partition by) 子句来实现这个功能。同时，为了限定每组最多数量，我们可以使用 row_num <= 100 条件进行筛选。
二、实现方法
1. 使用 row_number() 函数
row_number() 函数是一个窗口函数，它可以为每一行分配一个唯一的序号。在 over (partition by) 子句中，我们可以指定按照哪个字段进行分组。例如，如果我们要按照城市进行分组，可以这样写：
SELECT city, age, population, row_number() over (partition by city order by population desc) as row_num
FROM population_data;
这里，我们首先按照城市进行分组，然后按照人口数量降序排列。接着，我们使用 row_number() 函数为每一行分配一个序号。最后，我们将结果命名为 row_num。
2. 使用 row_num <= 100 条件进行筛选
为了限定每组最多数量，我们可以使用 row_num <= 100 条件进行筛选。例如，如果我们只想显示每个城市前100名的人口数量，可以这样写：
SELECT city, age, population, row_num
FROM (
SELECT city, age, population, row_number() over (partition by city order by population desc) as row_num
FROM population_data
) as subquery
WHERE row_num <= 100;
这里，我们首先将原始查询语句放入一个子查询中，然后在外部查询中添加 WHERE row_num <= 100 条件进行筛选。这样，我们就可以得到每个城市前100名的人口数量了。
三、示例数据
为了演示如何使用 row_number() 函数和 row_num <= 100 条件进行分组统计，我们创建了一个名为 population_data 的表，包含以下字段：
city：城市名称（字符串类型）age：年龄（整数类型）population：人口数量（整数类型）
以下是一些示例数据：
| city | age | population |
|---|---|---|
| Beijing | 35 | 21542000 |
| Shanghai | 42 | 24237800 |
| Guangzhou | 39 | 13081000 |
| Shenzhen | 36 | 12528300 |
| Hangzhou | 34 | 9816000 |
| Chengdu | 37 | 16330000 |
| Wuhan | 38 | 11292000 |
| Chongqing | 41 | 30485500 |
| Tianjin | 43 | 13562100 |
| Suzhou | 44 | 7725599 |
| Nanjing | 45 | 8288345 |
| Xi'an | 46 | 8375289 |
| Qingdao | 47 | 7779652 |
| Dongguan | 48 | 8349652 |
| Zhengzhou | 49 | 13534752 |
| Ningbo | 50 | 7896521 |
| Harbin | 51 | 10789652 |
| Changsha | 52 | 7896521 |
| Xiamen | 53 | 7896521 |
| Shenyang | 54 | 7896521 |
| Nanning | 55 | 7896521 |
| Wuxi | 56 | 7896521 |
| Jinan | 57 | 7896521 |
| Taiyuan | 58 | 7896521 |
| Luoyang | 59 | 7896521 |
| Lanzhou | 60 | 7896521 |
| Kunming | 61 | 7896521 |
