How to Group Consecutive Years for Unique Companies and Professions in PostgreSQL?

Group consecutive years in PostgreSQL 9.0

This article explores how to group consecutive years in PostgreSQL 9.0 while getting unique company and occupation information.

We will use a multi-step approach to achieve this goal. First, we identify non-consecutive years using a subquery that assigns a "group_cnt" value based on whether the year is the first in the sequence or whether the difference between the current year and the previous year is greater than 1.

Next, we define the group ID by summing the group_cnt values ​​for the sorted years for each company and occupation. This creates consecutive groups for non-consecutive years.

Finally, we leverage another derived table to aggregate years based on group ID. The result is an array of years for each unique company and occupation, with consecutive years grouped together.

Example query:

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

Output:

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

The above is the detailed content of How to Group Consecutive Years for Unique Companies and Professions in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!