如何使用 GROUP BY 对 PostgreSQL 中的连续数值进行分组？

使用 PostgreSQL 的 GROUP BY 聚合连续数字

PostgreSQL 提供了强大的数据聚合功能。 本指南演示如何使用 GROUP BY 子句对连续数值进行分组，特别关注涉及连续年份的场景。

示例场景：按公司和职业对年份进行分组

想象一个名为 qualification 的表，其中包含 company、profession 和 year 列。目标是确定独特的公司-专业组合，并将其连续年份分组到数组中。

分步解决方案：

1. 识别非连续年份：第一步涉及查明连续年份序列中断的位置。 这是使用窗口函数完成的：

SELECT
    company,
    profession,
    year,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY company, profession ORDER BY year) = 1 OR
             year - LAG(year, 1, year) OVER (PARTITION BY company, profession ORDER BY year) > 1
        THEN 1
        ELSE 0
    END AS group_cnt
FROM qualification;

此查询使用 ROW_NUMBER() 在每个公司专业组内分配唯一的排名，并使用 LAG() 将当前年份与前一年进行比较。 group_cnt 用 1 标记非连续年份。

2. 分配组 ID： 接下来，我们为每个连续的年份序列分配一个唯一的组 ID：

SELECT
    company,
    profession,
    year,
    SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM (
    -- The query from Step 1 goes here
) t1;

SUM() OVER() 窗口函数对 group_cnt 值进行累积求和，创建一个用作组 ID (group_nr) 的运行总计。

3. 最终聚合： 最后，我们使用 array_agg() 聚合每组内的年份：

SELECT
    company,
    profession,
    ARRAY_AGG(year) AS years
FROM (
    -- The query from Step 2 goes here
) t2
GROUP BY company, profession, group_nr
ORDER BY company, profession, group_nr;

此查询按 company、profession 和 group_nr 对结果进行分组，使用 ARRAY_AGG() 将年份组合到每个组的数组中。

预期输出：最终输出将连续年份分组为数组：

<code> company | profession |      years
---------+------------+------------------
 Google  | Programmer | {2000}
 Google  | Sales      | {2000,2001,2002}
 Google  | Sales      | {2004}
 Mozilla | Sales      | {2002}
(4 rows)</code>

该方法有效地处理了连续数值的分组，为 PostgreSQL 中的数据分析提供了清晰简洁的解决方案。

以上是如何使用 GROUP BY 对 PostgreSQL 中的连续数值进行分组？的详细内容。更多信息请关注PHP中文网其他相关文章！