Серии дней

Что такое серия

Серия — это кусок подряд идущих календарных дней в табеле. День без записи разрывает серию, даже если это суббота или воскресенье. Один день без соседей — тоже серия, просто длины 1.

Это не интервал назначения с готовыми from_date и to_date. В табеле лежат отдельные даты, и границы серии нужно собрать самим. И не сетка generate_series: там заранее рисуют все дни, здесь склеивают только те, что есть.

Берём учебную базу Команда, таблица worklog. Три человека в начале марта:

SELECT e.name, w.work_date
FROM worklog w
JOIN employees e ON e.id = w.employee_id
WHERE e.name IN ('Павел Чернов', 'Анна Ким', 'Олег Сафин')
ORDER BY e.name, w.work_date;

Анна: пн–пт 4–8 марта, затем пн–вт 11–12. Между ними суббота и воскресенье без строк. Павел: пн–ср 4–6, потом суббота 9-го. Четверга и пятницы нет. Олег: 4, 6 и 8-е — через день.

Что рвёт серию

Календарно подряд — значит, следующая дата ровно на день больше предыдущей. Разница 2 и больше — это уже другая серия.

У Павла 6 марта, затем сразу 9 марта. Между ними 7-е и 8-е. Суббота 9-го не продолжает серию понедельника–среды: двух дней не хватает. У Анны после пятницы 8-го нет 9-го и 10-го — выходные без табеля тоже разрыв.

MAX(дата) − MIN(дата) + 1 по всем строкам человека этого не покажет. У Павла минимум 4-е, максимум 9-е: формула даст 6, хотя подряд идут только три дня, потом дыра, потом один день. Нужны сами куски, не длина отрезка от первой записи до последней.

Дата минус номер строки

Подряд идущие даты растут на 1. ROW_NUMBER по тем же датам тоже растёт на 1. Разность не меняется — это метка одной серии. Пропущенный день: дата прыгает сильнее, номер — нет, метка становится другой.

Номер считают внутри человека: PARTITION BY employee_id, иначе чужие дни сдвинут разность.

SELECT
  e.name,
  w.work_date,
  ROW_NUMBER() OVER (
    PARTITION BY w.employee_id
    ORDER BY w.work_date
  ) AS rn,
  w.work_date - (ROW_NUMBER() OVER (
    PARTITION BY w.employee_id
    ORDER BY w.work_date
  ))::int AS grp
FROM worklog w
JOIN employees e ON e.id = w.employee_id
WHERE e.name = 'Павел Чернов'
ORDER BY w.work_date;

::int нужен, потому что ROW_NUMBER даёт большое целое, а из даты в PostgreSQL вычитают обычный integer. Само значение группы (3 марта или 5 марта) ни на что не похоже: это только общий ключ. Смысл в том, что он одинаковый внутри серии и меняется после разрыва.

У Олега три разных grp: 4 − 1, 6 − 2, 8 − 3 — три серии длины 1. Через день номер вырос на 1, дата — на 2.

Схлопнуть в отрезок

Когда у каждой даты есть grp, серия собирается обычным GROUP BY человека и группы: начало — MIN(work_date), конец — MAX(work_date), длина — COUNT(*).

WITH tagged AS (
  SELECT
    e.name,
    w.work_date,
    w.work_date - (ROW_NUMBER() OVER (
      PARTITION BY w.employee_id
      ORDER BY w.work_date
    ))::int AS grp
  FROM worklog w
  JOIN employees e ON e.id = w.employee_id
  WHERE e.name IN ('Павел Чернов', 'Анна Ким', 'Олег Сафин')
)
SELECT
  name,
  MIN(work_date) AS start_date,
  MAX(work_date) AS end_date,
  COUNT(*) AS days
FROM tagged
GROUP BY name, grp
ORDER BY name, start_date;

У Анны COUNT(*) первой серии — 5, и это совпадает с MAX − MIN + 1 внутри группы: дыр внутри ключа grp уже нет. Снаружи, по всем её датам сразу, такая формула дала бы 9 (с 4-го по 12-е) — лишние выходные.

Серия по будням

Иногда выходной серию рвать не должен: пятница и следующий понедельник — это соседние рабочие дни, хотя календарно между ними суббота и воскресенье.

Тогда из табеля оставляют только пн–пт (EXTRACT(ISODOW FROM work_date) BETWEEN 1 AND 5) и нумеруют уже не календарную дату, а номер рабочего дня. Удобная шкала: сколько будней прошло с понедельника 3 января 2000 года. Из календарной разницы вычитают по два дня на каждую полную неделю:

(work_date - DATE '2000-01-03')
  - 2 * ((work_date - DATE '2000-01-03') / 7)

В PostgreSQL деление целых отбрасывает дробь: 10 / 7 это 1. На этой шкале пятница 8 марта и понедельник 11 марта идут как 6309 и 6310 — соседние числа, хотя календарно между ними три дня.

SELECT
  w.work_date,
  (w.work_date - DATE '2000-01-03')
    - 2 * ((w.work_date - DATE '2000-01-03') / 7) AS biz,
  ROW_NUMBER() OVER (ORDER BY w.work_date) AS rn
FROM worklog w
JOIN employees e ON e.id = w.employee_id
WHERE e.name = 'Анна Ким'
  AND EXTRACT(ISODOW FROM w.work_date) BETWEEN 1 AND 5
ORDER BY w.work_date;

Дальше тот же приём: biz − rn вместо дата − rn. У Анны все семь будней дают одну группу. Календарно это были две серии — выходные их резали.

У Павла суббота 9 марта в будни не входит. Остаются 4, 5 и 6 марта — одна серия длины 3. Пропущенные четверг и пятница по-прежнему рвут, если бы они были нужны: это будни без строки.

Всё вместе

Самая длинная календарная серия каждого из трёх. Если длины равны — та, что началась раньше.

WITH tagged AS (
  SELECT
    e.name,
    w.work_date,
    w.work_date - (ROW_NUMBER() OVER (
      PARTITION BY w.employee_id
      ORDER BY w.work_date
    ))::int AS grp
  FROM worklog w
  JOIN employees e ON e.id = w.employee_id
  WHERE e.name IN ('Павел Чернов', 'Анна Ким', 'Олег Сафин')
),
streaks AS (
  SELECT
    name,
    MIN(work_date) AS start_date,
    MAX(work_date) AS end_date,
    COUNT(*) AS days,
    ROW_NUMBER() OVER (
      PARTITION BY name
      ORDER BY COUNT(*) DESC, MIN(work_date)
    ) AS rn
  FROM tagged
  GROUP BY name, grp
)
SELECT name, days, start_date, end_date
FROM streaks
WHERE rn = 1
ORDER BY days DESC, name;

ROW_NUMBER во втором шаге уже не про серии, а про выбор одной строки на человека среди готовых отрезков. Олег: три серии длины 1, раньше всех 4 марта.

Коротко: подряд идущие даты имеют один и тот же дата − ROW_NUMBER. Разрыв меняет эту разность — начинается новая группа. GROUP BY по группе даёт начало, конец и длину. Для будней без учёта выходных ту же разность считают по номеру рабочего дня, не по календарю.