【数据库】对大数据量数据集，PostgreSQL分组统计数量，限定每组最多数量

傷城~ 2024-02-05 19:23 56阅读 0赞

### 一、背景介绍 ###

在处理大数据量数据集时，我们经常需要进行分组统计。例如，我们需要统计每个城市的人口数量、每个年龄段的人数等。在 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`：人口数量（整数类型）

以下是一些示例数据：

<table> 
 <thead> 
  <tr> 
   <th>city</th> 
   <th>age</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Beijing</td> 
   <td>35</td> 
   <td>21542000</td> 
  </tr> 
  <tr> 
   <td>Shanghai</td> 
   <td>42</td> 
   <td>24237800</td> 
  </tr> 
  <tr> 
   <td>Guangzhou</td> 
   <td>39</td> 
   <td>13081000</td> 
  </tr> 
  <tr> 
   <td>Shenzhen</td> 
   <td>36</td> 
   <td>12528300</td> 
  </tr> 
  <tr> 
   <td>Hangzhou</td> 
   <td>34</td> 
   <td>9816000</td> 
  </tr> 
  <tr> 
   <td>Chengdu</td> 
   <td>37</td> 
   <td>16330000</td> 
  </tr> 
  <tr> 
   <td>Wuhan</td> 
   <td>38</td> 
   <td>11292000</td> 
  </tr> 
  <tr> 
   <td>Chongqing</td> 
   <td>41</td> 
   <td>30485500</td> 
  </tr> 
  <tr> 
   <td>Tianjin</td> 
   <td>43</td> 
   <td>13562100</td> 
  </tr> 
  <tr> 
   <td>Suzhou</td> 
   <td>44</td> 
   <td>7725599</td> 
  </tr> 
  <tr> 
   <td>Nanjing</td> 
   <td>45</td> 
   <td>8288345</td> 
  </tr> 
  <tr> 
   <td>Xi’an</td> 
   <td>46</td> 
   <td>8375289</td> 
  </tr> 
  <tr> 
   <td>Qingdao</td> 
   <td>47</td> 
   <td>7779652</td> 
  </tr> 
  <tr> 
   <td>Dongguan</td> 
   <td>48</td> 
   <td>8349652</td> 
  </tr> 
  <tr> 
   <td>Zhengzhou</td> 
   <td>49</td> 
   <td>13534752</td> 
  </tr> 
  <tr> 
   <td>Ningbo</td> 
   <td>50</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Harbin</td> 
   <td>51</td> 
   <td>10789652</td> 
  </tr> 
  <tr> 
   <td>Changsha</td> 
   <td>52</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Xiamen</td> 
   <td>53</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Shenyang</td> 
   <td>54</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Nanning</td> 
   <td>55</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Wuxi</td> 
   <td>56</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Jinan</td> 
   <td>57</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Taiyuan</td> 
   <td>58</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Luoyang</td> 
   <td>59</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Lanzhou</td> 
   <td>60</td> 
   <td>7896521</td> 
  </tr> 
  <tr> 
   <td>Kunming</td> 
   <td>61</td> 
   <td>7896521</td> 
  </tr> 
 </tbody> 
</table>