GROUP BYを使用してPostgreSQLで連続する数値をグループ化するにはどうすればよいですか?

PostgreSQL の GROUP BY を使用して連続する数値を集計する

PostgreSQL はデータ集約のための強力な機能を提供します。 このガイドでは、GROUP BY 句を使用して連続する数値をグループ化する方法を説明し、特に連続した年が関係するシナリオに焦点を当てます。

シナリオ例: 会社および職業ごとに年をグループ化する

列 qualification、company、および profession を持つ year という名前のテーブルを想像してください。目標は、企業と専門職の一意の組み合わせを特定し、その連続した年数を配列にグループ化することです。

段階的な解決策:

1. 連続しない年の特定: 最初のステップでは、連続する年のシーケンスが途切れる場所を正確に特定します。 これはウィンドウ関数を使用して行われます:

<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>

このクエリは、ROW_NUMBER() を使用して各企業職種グループ内で一意のランクを割り当て、LAG() を使用して今年と前年を比較します。 group_cnt は、連続しない年に 1 のフラグを付けます。

2. グループ ID の割り当て: 次に、連続する各年のシーケンスに一意のグループ ID を割り当てます:

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

SUM() OVER() ウィンドウ関数は、group_cnt 値を累積的に合計し、グループ ID (group_nr) として機能する累計を作成します。

3. 最終集計: 最後に、array_agg() を使用して各グループ内の年を集計します:

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

このクエリは、結果を 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 中国語 Web サイトの他の関連記事を参照してください。