Как посчитать самую длинную серию повторений в столбце SQL?
Есть таблица такого формата
Как посчитать максимальную серию побед (самое длинно количество значений 1 подряд без прерываний) для каждой персоны?
Ответы (3 шт):
попробовал решить для интереса. Как в комментариях указано, необходимо знать порядок сортировки записей представленных на скриншоте. Для примера добавил инкрементальный id.
реализация в ms sql 2012. Исходные:
declare @t table ( id int IDENTITY(1,1), person int, d date, v int);
insert into @t values
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),--3
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),--2
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),--5
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),--6
(2, '2000-01-01', 1);--1
первым шагом A делаем выборку данных и вытаскиваем предыдущее, следующее значения и делаем нумерацию строк внутри групп.
with A as (
SELECT * , lag(v, 1) over (partition by person, d order by id) AS prev
, lead(v,1) over (partition by person, d order by id) AS next
, row_number() over( partition by person, d order by id) AS rn
FROM @t
)
получаем результаты:
id person d v prev next rn
----------- ----------- ---------- ----------- ----------- ----------- --------------------
1 1 2000-01-01 1 NULL 1 1
2 1 2000-01-01 1 1 1 2
3 1 2000-01-01 1 1 0 3
4 1 2000-01-01 0 1 0 4
5 1 2000-01-01 0 0 1 5
6 1 2000-01-01 1 0 1 6
7 1 2000-01-01 1 1 1 7
8 1 2000-01-01 1 1 1 8
9 1 2000-01-01 1 1 1 9
10 1 2000-01-01 1 1 0 10
11 1 2000-01-01 0 1 0 11
12 1 2000-01-01 0 0 0 12
13 1 2000-01-01 0 0 0 13
14 1 2000-01-01 0 0 0 14
15 1 2000-01-01 0 0 0 15
16 1 2000-01-01 0 0 NULL 16
17 2 2000-01-01 1 NULL NULL 1
далее (B) для строк, где next = null увеличиваем значение rn на 1, а также в выборке оставляем только строки, где сменилось значение v <> prev либо next is null
,B AS (
SELECT id, person, d, v,prev, next
, (case when next is null then rn + 1 else rn end) as rn
FROM A
WHERE next is null or v <> prev
--order by id
)
получаем
id person d v prev next rn
----------- ----------- ---------- ----------- ----------- ----------- --------------------
4 1 2000-01-01 0 1 0 4
6 1 2000-01-01 1 0 1 6
11 1 2000-01-01 0 1 0 11
16 1 2000-01-01 0 0 NULL 17
17 2 2000-01-01 1 NULL NULL 2
для полученных строк считаем (C) разность между текущим и предыдущим значением номера строки - rn, то фактическое число последовательных строк с 0 или 1.
, C as (
SELECT *, rn - lag(rn,1, 1) over (partition by person,d order by id) AS cnt
FROM B
)
на выходе
id person d v prev next rn cnt
----------- ----------- ---------- ----------- ----------- ---------- --------- -------
4 1 2000-01-01 0 1 0 4 3
6 1 2000-01-01 1 0 1 6 2
11 1 2000-01-01 0 1 0 11 5
16 1 2000-01-01 0 0 NULL 17 6
17 2 2000-01-01 1 NULL NULL 2 1
заключительным шагом делаем обычную группировку по персоне и дате, выбирая максимальные значения. но с условием, что prev значение = 1, либо если оно отсутствует, то v.
SELECT person, d, max(cnt) AS cnt
FROM C
GROUP BY person, d, v,prev
HAVING ISNULL(prev, v) = 1
что вроде как дает искомые значения
person d cnt
----------- ---------- --------------------
1 2000-01-01 5
2 2000-01-01 1
У меня вот так выходит. Вывожу серии и побед, и поражений, и данные по серии (длина, начало, конец).
Подготовка:
declare @t table ( id smallint identity(1,1), person smallint, EventDatetime date, winn smallint);
insert into @t values
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(2, '2000-01-01', 1);
Вот наши исходные данные:
id person EventDatetime winn
------ ------ ------------- ------
1 1 2000-01-01 1
2 1 2000-01-01 1
3 1 2000-01-01 1
4 1 2000-01-01 0
5 1 2000-01-01 0
6 1 2000-01-01 1
7 1 2000-01-01 1
8 1 2000-01-01 1
9 1 2000-01-01 1
10 1 2000-01-01 1
11 1 2000-01-01 0
12 1 2000-01-01 0
13 1 2000-01-01 0
14 1 2000-01-01 0
15 1 2000-01-01 0
16 1 2000-01-01 0
17 2 2000-01-01 1
Для каждой строки определяем начало текущей серии (ищем последнюю строку из предыдущих, у которой значение winn не равно текущему)
with c_games as(
select t.id, t.person, t.EventDatetime, t.winn,
coalesce((select max(w.id) from @t w
where w.person = t.person and w.EventDatetime = t.EventDatetime
and w.winn <> t.winn and w.id < t.id),0) as max_prev_other_id
from @t as t
)
Получаем:
id person EventDatetime winn max_prev_other_id
------ ------ ------------- ------ -----------------
1 1 2000-01-01 1 0
2 1 2000-01-01 1 0
3 1 2000-01-01 1 0
4 1 2000-01-01 0 3
5 1 2000-01-01 0 3
6 1 2000-01-01 1 5
7 1 2000-01-01 1 5
8 1 2000-01-01 1 5
9 1 2000-01-01 1 5
10 1 2000-01-01 1 5
11 1 2000-01-01 0 10
12 1 2000-01-01 0 10
13 1 2000-01-01 0 10
14 1 2000-01-01 0 10
15 1 2000-01-01 0 10
16 1 2000-01-01 0 10
17 2 2000-01-01 1 0
Теперь группируем по сериям и для каждой считаем начало, конец, длину, и нумеруем их в порядке убывания длины для каждого участника и даты (в итоге первые из них будут нужными нам самыми длинными)
, c_series as(
select g.person, g.EventDatetime, g.winn, count(id) as serial_length,
min(id) as first_id, max(id) as last_id,
row_number() over(partition by g.person, g.EventDatetime, g.winn
order by count(id) desc) as num_series
from c_games g
group by g.person, g.EventDatetime, g.winn, g.max_prev_other_id
)
Результат:
person EventDatetime winn serial_length first_id last_id num_series
------ ------------- ------ ------------- -------- ------- --------------------
1 2000-01-01 1 3 1 3 2
1 2000-01-01 0 2 4 5 2
1 2000-01-01 1 5 6 10 1
1 2000-01-01 0 6 11 16 1
2 2000-01-01 1 1 17 17 1
И берем последовательности с номерами, равными 1.
select s.person, s.EventDatetime, s.winn, s.serial_length, s.first_id, s.last_id
from c_series s
where s.num_series = 1
order by s.person, s.EventDatetime, s.winn desc
Получаем результат:
person EventDatetime winn serial_length first_id last_id
------ ------------- ------ ------------- -------- -------
1 2000-01-01 1 5 6 10
1 2000-01-01 0 6 11 16
2 2000-01-01 1 1 17 17
Это называется проблемой пробелов и островов (Gaps and Islands Problem in English).
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (seq INT identity(1,1) PRIMARY KEY, person INT, EventDatetime DATE, winn SMALLINT);
INSERT INTO @tbl (person, EventDatetime, winn) VALUES
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 1),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(1, '2000-01-01', 0),
(2, '2000-01-01', 1);
-- DDL and sample data population, end
WITH cte AS
(
SELECT *, SUM(IIF(winn <> ns, 1, 0)) OVER (ORDER BY seq) AS series
FROM (
SELECT series.*,
LAG(winn) OVER (ORDER BY seq) AS ns
FROM @tbl AS series
) q
), rs AS
(
SELECT MIN(cte.person) AS person, series, cnt = MAX(cte.seq) - MIN(cte.seq) + 1
FROM cte
WHERE winn = 1
GROUP BY cte.series
)
SELECT rs.person, MAX(cnt) AS cnt
FROM rs
GROUP BY rs.person
ORDER BY rs.person;
Результат
+--------+-----+
| person | cnt |
+--------+-----+
| 1 | 5 |
| 2 | 1 |
+--------+-----+