PostgreSQL でユニークな企業や職業の連続した年をグループ化するにはどうすればよいですか?

PostgreSQL 9.0 での連続した年数のグループ

この記事では、PostgreSQL 9.0 で固有の会社と職業の情報を取得しながら、連続した年をグループ化する方法について説明します。

この目標を達成するために、複数のステップからなるアプローチを使用します。まず、その年がシーケンスの最初であるかどうか、または今年と前年の差が 1 より大きいかどうかに基づいて「group_cnt」値を割り当てるサブクエリを使用して、連続していない年を特定します。

次に、企業や職種ごとにソートした年分のgroup_cnt値を合計してグループIDを定義します。これにより、連続しない年に連続したグループが作成されます。

最後に、別の派生テーブルを利用して、グループ 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>

出力:

<code> company | profession |      years
---------+------------+------------------
 Google  | Programmer | {2000}
 Google  | Sales      | {2000,2001,2002}
 Google  | Sales      | {2004}
 Mozilla | Sales      | {2002}
(4 rows)</code>

以上がPostgreSQL でユニークな企業や職業の連続した年をグループ化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。