SELECT
min(sub1.id), --находим минимальное значение id в подзапросе sub1
group_id, --выводим значение
count(*) AS count --подсчитаваем общее количество строк
FROM ( -- начинаем первый подхапрос
SELECT
*, --выводм все значения из sub2
count(step OR NULL) -- аналог выражения SUM(CASE WHEN step THEN 1 ELSE 0 END)
OVER ( --начало оконной функции, которая будет работать для count
ORDER BY --портируем по id
id) AS grp
FROM ( -- начинаем второй подзапрос
SELECT
*, (lag(id) -- возвращает значение для строки, положение которой задаётся смещением от текущей строки к концу раздела; если такой строки нет, возвращается значение по_умолчанию (оно должно иметь тот же тип, что и значение). Оба параметра смещение и по_умолчанию вычисляются для текущей строки. Если они не указываются, то смещение считается равным 1, а по_умолчанию — NULL
OVER (PARTITION BY -- пагинация
group_id
ORDER BY
id) + 1)
IS DISTINCT FROM id AS step --Предикаты сравнения. a не равно b
FROM
users) sub2) sub1
GROUP BY
grp, -- группируем по grp
group_id -- подбираем значения, которое есть в grp и группируем по ним
SELECT
*, (lag(id)
OVER (PARTITION BY
group_id
ORDER BY
id) + 1)
IS DISTINCT FROM id AS step,
(lag(id)
OVER (PARTITION BY
group_id
ORDER BY
id) + 1) AS step2
FROM
users
id group_id step step2
1 1 t null
2 1 f 2
3 1 f 3
5 1 t 4
4 2 t null
6 3 t null
SELECT
*,
count(step
OR NULL)
OVER (
ORDER BY
id) AS grp
FROM (
SELECT
*, (lag(id)
OVER (PARTITION BY
group_id
ORDER BY
id) + 1)
IS DISTINCT FROM id AS step
FROM
users) sub2
id group_id step grp
1 1 t 1
2 1 f 1
3 1 f 1
4 2 t 2
5 1 t 3
6 3 t 4
min group_id count
1 1 3
4 2 1
5 1 1
6 3 1