GROUP BY を使用して PostgreSQL で連続する数値をグループ化および集計する方法

PostgreSQL で GROUP BY を使用して連続値を集計する方法

PostgreSQL 9.0 以降では、GROUP BY 句を使用して特定のフィールドの連続した数値を集計できます。次のクエリは、会社、職業、年のフィールドを含むテーブルでこれを実現する方法を示しています:

ステップ 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>

このクエリは、年が連続しているかどうかに基づいて、各行にグループ数 (group_cnt) を割り当てます。 group_cnt 1 の行は、新しいグループの開始を示します。

ステップ 2: グループ ID を定義する

<code class="language-sql">-- 步骤二：定义分组 ID
SELECT
  company,
  profession,
  year,
  SUM(group_cnt) OVER (ORDER BY company, profession, year) AS group_nr
FROM qualification
WHERE group_cnt = 1;</code>

このクエリは、各グループにグループ番号 (group_nr) を割り当てます。連続する各年は同じグループに属します。

ステップ 3: 連続した年を集計する

<code class="language-sql">-- 步骤三：聚合连续年份
SELECT
  company,
  profession,
  ARRAY_AGG(year) AS years
FROM qualification
WHERE group_cnt <> 0
GROUP BY
  company,
  profession,
  group_nr
ORDER BY
  company,
  profession,
  group_nr;</code>

この最後のクエリは、連続した年を配列に集計し、連続していない行を除外します。結果は企業および職種ごとにグループ化されています。

上記の 3 つの手順に従うことで、PostgreSQL で連続値を効果的に集計できます。 qualification テーブルは実際のテーブル名に置き換える必要があることに注意してください。

以上がGROUP BY を使用して PostgreSQL で連続する数値をグループ化および集計する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。