Выбрать несколько идущих подряд дат с определенным значением

Question

Имеется следующая таблица c датами и количеством посетителей.

CREATE TABLE visit (
     id int PRIMARY KEY,
     visit_date date,
     people_count int
)

На каждый день приходится только одна запись о посещениях. Как выбрать 3 и более подряд идущих дня, где количество посетителей было больше 100? Частично решил при помощи островов:

DECLARE @SomeDate datetime;
SET @SomeDate = convert(datetime,'01.01.2000',104);

WITH [Islands] AS(
SELECT id, visit_date, people_count,
DATEDIFF(day, @SomeDate, visit_date) - ROW_NUMBER() OVER(ORDER BY visit_date) [island]
FROM visit)
SELECT * FROM Islands
WHERE people_count > 100

Далее их, наверное, можно было бы сгруппировать, но как выбрать именно 3 и более подряд идущих дней все равно не понимаю.

Answer 1

Наверное можно сделать соединениями

WITH [Data] AS(
  SELECT id,
         visit_date,
         people_count,
         ROW_NUMBER() OVER(ORDER BY visit_date) [rowNumber]
  FROM visit),
Data1 as(SELECT Data.id, DataNext.id as id1, DataNextPlus.id as id2 FROM Data
   inner join Data as DataNext 
    on Data.rowNumber = DataNext.rowNumber + 1
   inner join Data as DataNextPlus
    on Data.rowNumber = DataNextPlus.rowNumber + 2
WHERE Data.people_count > 100 and 
      DataNext.people_count > 100 and 
      DataNextPlus.people_count > 100)
Select distinct data.* From data
Inner join data1
 On data.id = data1.id or data.id = data1.id1 or data.id = data1.id2

Answer 2

Есть более универсальный вариант решения данной задачи. Сам принцип поиска непрерывной последовательности заключается в изначальной нумерации дат ( с учетом требования people_count > 100) и затем получения номера групп таких дат путем вычитания дня из даты (DATEPART) и полученного номера (ROW_NUMBER):

SELECT 
id,visit_date,people_count
 FROM
(
 SELECT id,visit_date,people_count,NumGroup,
 COUNT(visit_date)OVER (PARTITION BY NumGroup)CountREcords
 FROM
 (
  SELECT   --получаем номера групп с последовательно идущими датами
   id,visit_date,people_count,DATEADD(DAY,-Number,visit_date) AS NumGroup 
  FROM
   (
    SELECT id, people_count,ROW_NUMBER()OVER(ORDER BY visit_date)Number, visit_date  --нумеруем даты
      
   FROM
    ( 
    --осуществляем выборку нужных данных
     SELECT * FROM visit WHERE people_count>100     
    )A
  )B
 )C
)D
WHERE CountREcords>=3   
        --отображаем ТОЛЬКО ТЕ данные, где количество записей
        --более или равно 3,то есть группа состоит из 3 последовательных дат (или более 3)

БЛОГ НА HUSL

Выбрать несколько идущих подряд дат с определенным значением

Ответы (2 шт):