Identifying non-consecutive values is always a bit tricky and involves several nested sub-queries (at least I cannot come up with a better solution).
The first step is to identify non-consecutive values for the year:
Step 1) Identify non-consecutive values
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
This returns the following result:
company | profession | year | group_cnt ---------+------------+------+----------- Google | Programmer | 2000 | 1 Google | Sales | 2000 | 1 Google | Sales | 2001 | 0 Google | Sales | 2002 | 0 Google | Sales | 2004 | 1 Mozilla | Sales | 2002 | 1
Now with the group_cnt value we can create “group IDs” for each group that has consecutive years:
Step 2) Define group IDs
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
This returns the following result:
company | profession | year | group_nr ---------+------------+------+---------- Google | Programmer | 2000 | 1 Google | Sales | 2000 | 2 Google | Sales | 2001 | 2 Google | Sales | 2002 | 2 Google | Sales | 2004 | 3 Mozilla | Sales | 2002 | 4 (6 rows)
As you can see each “group” got its own group_nr and this we can finally use to aggregate over by adding yet another derived table:
Step 3) Final query
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
This returns the following result:
company | profession | years
---------+------------+------------------
Google | Programmer | {2000}
Google | Sales | {2000,2001,2002}
Google | Sales | {2004}
Mozilla | Sales | {2002}
(4 rows)
Which is exactly what you wanted, if I’m not mistaken.