如何使用子查詢對 PostgreSQL 中的連續數值進行分組和聚合？

在PostgreSQL中使用子查詢分組和聚合連續數值

處理包含數值型資料的表時，通常需要根據數值的順序對這些值進行分組和聚合。在PostgreSQL 9.0以上版本中，可以使用子查詢的組合來實現此目的。

辨識非連續值

第一步是辨識目標數值欄位中的非連續值。這可以透過子查詢來完成，該子查詢為每一行分配一個群組計數，當出現非連續值時，計數器將重設。

<code class="language-sql">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;</code>

定義群組ID

接下來，我們為每個連續的數值序列建立群組ID。這涉及另一個子查詢，它對來自前一個子查詢的群組計數進行求和。

<code class="language-sql">SELECT company, profession, year,
       SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM (
    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
) t1;</code>

最終聚合

最後，我們使用群組ID對每個群組的數值進行聚合。

<code class="language-sql">SELECT company, profession, array_agg(year) AS years
FROM (
    SELECT company, profession, year,
           SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
    FROM (
        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
    ) t1
) t2
GROUP BY company, profession, group_nr
ORDER BY company, profession, group_nr;</code>

此查詢將傳回一個表，其中包含唯一的公司和職業組合，年份值根據其數值順序分組到陣列中。 此方法有效地將連續的數值序列分組，以便於後續分析和處理。

以上是如何使用子查詢對 PostgreSQL 中的連續數值進行分組和聚合？的詳細內容。更多資訊請關注PHP中文網其他相關文章！