【数据库】对大数据量数据集，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：人口数量（整数类型）

以下是一些示例数据：
cityagepopulationBeijing3521542000Shanghai4224237800Guangzhou3913081000Shenzhen3612528300Hangzhou349816000Chengdu3716330000Wuhan3811292000Chongqing4130485500Tianjin4313562100Suzhou447725599Nanjing458288345Xi’an468375289Qingdao477779652Dongguan488349652Zhengzhou4913534752Ningbo507896521Harbin5110789652Changsha527896521Xiamen537896521Shenyang547896521Nanning557896521Wuxi567896521Jinan577896521Taiyuan587896521Luoyang597896521Lanzhou607896521Kunming617896521