如何使用 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中文網其他相關文章！