Как получить строки с самым длинным последовательным одинаковым значением?
Таблица Learning
:
Самое длинное последовательное значение равно 1 (rowID
8-10, так как rowID
1-2 равно 2, а rowID
6-6 равно 1). Как запросить, чтобы получить фактические строки последовательных значений (а не только значения rowStart
и rowEnd
), например:
И для самых длинных последовательных значений 1 и 0?
Это проблема пробелов и островов, и один из подходов заключается в использовании метода разницы в номерах строк:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY rowID) rn1,
ROW_NUMBER() OVER (PARTITION BY values ORDER BY rowID) rn2
FROM yourTable
),
cte2 AS (
SELECT *,
MIN(rowID) OVER (PARTITION BY values, rn1 - rn2) AS minRowID,
MAX(rowID) OVER (PARTITION BY values, rn1 - rn2) AS maxRowID
FROM cte1
),
cte3 AS (
SELECT *, RANK() OVER (PARTITION BY values ORDER BY maxRowID - minRowID DESC) rnk
FROM cte2
)
SELECT rowID, values
FROM cte3
WHERE rnk = 1
ORDER BY values, rowID;
Я думаю, что самый простой подход — использовать количество окон для определения островов. Затем, чтобы получить «самый длинный» остров, нам просто нужно агрегировать, сортировать и ограничивать:
select min(valueid) grp_start, max(valueid) grp_end
from (select t.*, sum(value = 0) over(order by valueid) grp from testing t) t
where value = 1
group by grp
order by count(*) desc limit 1
В скрипте БД, который вы предоставили, запрос возвращает:
Спасибо за ответ. Думаю, я плохо сформулировал вопрос и отредактировал свой исходный вопрос. Мне было интересно, как получить фактические строки с самым длинным последовательным одинаковым значением, а не только начальную строку и конечную строку. Это единственный способ добиться этого путем создания другого запроса, в котором rowID находится между groupStart и groupEnd? Или вы можете сделать это, изменив исходный запрос? Если это возможно сделать, изменив исходный запрос, сможете ли вы предоставить для этого базу данных? Спасибо.