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 中国語 Web サイトの他の関連記事を参照してください。