GROUP BY и агрегировать последовательные числовые значения

Ответ 1

Большое значение для @a_horse_with_no_name answer, как правильное решение, так и, как я уже сказал в комментарии, является хорошим материалом для изучения того, как использовать разные виды оконных функций в PostgreSQL.

И все же я не могу не чувствовать, что подход, принятый в этом ответе, - это слишком много усилий для такой проблемы. В принципе, вам нужен дополнительный критерий для группировки, прежде чем вы начнете агрегировать годы в массивах. У вас уже есть company и profession, теперь вам нужно только что-то отличить годы от разных последовательностей.

Это именно то, что дает вышеупомянутый ответ, и это именно то, что я думаю, может быть сделано более простым способом. Вот как:

WITH MarkedForGrouping AS (
  SELECT
    company,
    profession,
    year,
    year - ROW_NUMBER() OVER (
      PARTITION BY company, profession
      ORDER BY year
    ) AS seqID
  FROM atable
)
SELECT
  company,
  profession,
  array_agg(year) AS years
FROM MarkedForGrouping
GROUP BY
  company,
  profession,
  seqID

Ответ 2

Идентификация неизменяемых значений всегда немного сложна и включает в себя несколько вложенных подзапросов (по крайней мере, я не могу придумать лучшее решение).

Первым шагом является определение непоследовательных значений за год:

Шаг 1) Определите непоследовательные значения

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

Это возвращает следующий результат:

 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

Теперь с помощью значения group_cnt мы можем создать "идентификаторы групп" для каждой группы, которая имеет следующие годы:

Шаг 2) Определение идентификаторов групп

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

Это возвращает следующий результат:

 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)

Как вы можете видеть, каждая "группа" получила свою собственную group_nr, и это мы можем, наконец, использовать для агрегирования, добавив еще одну производную таблицу:

Шаг 3) Окончательный запрос

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

Это возвращает следующий результат:

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

Это именно то, что вы хотели, если я не ошибаюсь.

Ответ 3

Процедурное решение с PL/pgSQL

Проблема довольно громоздка для простого SQL с функциями aggregate/windows. Хотя цикл, как правило, медленнее, чем решения на основе набора с простым SQL, процедурное решение с plpgsql может выполнять однократное последовательное сканирование по таблице (неявный курсор цикла FOR) и должно быть значительно быстрее в этом конкретном случае:

Таблица тестов:

CREATE TEMP TABLE tbl (company text, profession text, year int);
INSERT INTO tbl VALUES
 ('Google',  'Programmer', 2000)
,('Google',  'Sales',      2000)
,('Google',  'Sales',      2001)
,('Google',  'Sales',      2002)
,('Google',  'Sales',      2004)
,('Mozilla', 'Sales',      2002);

Функции:

CREATE OR REPLACE FUNCTION f_periods()
  RETURNS TABLE (company text, profession text, years int[]) AS
$func$
DECLARE
   r  tbl; -- use table type as row variable
   r0 tbl;
BEGIN

FOR r IN
   SELECT * FROM tbl t ORDER BY t.company, t.profession, t.year
LOOP
   IF ( r.company,  r.profession,  r.year)
   <> (r0.company, r0.profession, r0.year + 1) THEN -- not true for first row

      RETURN QUERY
      SELECT r0.company, r0.profession, years; -- output row

      years := ARRAY[r.year];     -- start new array
   ELSE
      years := years || r.year;   -- add to array - year can be NULL, too
   END IF;

   r0 := r;                       -- remember last row
END LOOP;

RETURN QUERY                      -- output last iteration
SELECT r0.company, r0.profession, years;

END
$func$ LANGUAGE plpgsql;

Вызов:

SELECT * FROM f_periods();

Производит запрошенный результат.