【数据库】对大数据量数据集，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