SQL - группировка дат с включением первого значения следующей группы

Рейтинг: 0Ответов: 1Опубликовано: 13.02.2023

не знал как кратко назвать вопрос, тут опишу всё понятнее. Есть такой датасет:

введите сюда описание изображения

Нужно вывести столбцы с ID, минимальной датой в рамках каждого ID, status_1 и status_2, а также максимальную дату в рамках тех же группировок, плюс сами статусы, что-то вроде такого: введите сюда описание изображения

Я сначала думал, что обойдусь простым GROUP BY, но как оказалось этого недостаточно, потому что в рамках каждого ID end_datetime_status должен равняться той дате, с которой начинается уже для последней комбинации статусов в рамках этого ID дата должна быть именно последней, надеюсь понятно, грубо говоря дата начала следующего статуса это и есть последняя дата предыдущего статуса, и ее она мне нужна, вот еще иллюстрация введите сюда описание изображения

вот как выглядел мой SQL-запрос

SELECT 
  id,
  MIN(date_time) AS start_datetime_status,
  MAX(date_time) AS end_datetime_status,
  status_1,
  status_2
FROM `test_id`
GROUP BY
  id,
  status_1,
  status_2
ORDER BY id

Думал использовать оконные функции и LAG, но никакого даже близко рабочего решения не получается

Сори за скриншоты, приложу ссылку на датасет, если нужно - https://disk.yandex.ru/i/wkPEe05hWiIEEQ

sql bigquery
Источник: Stack Overflow на русском

Ответы

▲ 1

Не знаю... может группировать по [id], [status_1], [status_2], сделать нумерацию по MIN([datetime]), а затем сделать выборку "на себя" с шагом +1?

-- пример таблицы
DECLARE @tempTable TABLE (
  [id] [bigint] NOT NULL,
  [datetime] [datetime] NOT NULL,
  [status_1] [varchar](2) NULL,
  [status_2] [varchar](2) NULL
);

-- данные с датасета
INSERT INTO @tempTable
VALUES 
  (33405570, CONVERT([datetime], '2022-09-19 10:49:30.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-09-28 21:22:51.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-09-28 21:24:11.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-09-28 21:28:32.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-09-28 22:25:08.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 14:55:06.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 14:56:58.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 14:57:23.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 14:59:13.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:00:15.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:00:31.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:00:40.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:00:53.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:01:08.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:05:09.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:05:23.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:05:34.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:05:47.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 15:05:54.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:45:07.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:45:16.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:45:25.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:45:43.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:45:54.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:46:04.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:46:19.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:46:27.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 17:46:35.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 21:49:43.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 21:49:52.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-16 21:50:00.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-18 16:58:40.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-18 16:59:29.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-18 17:00:39.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:23:58.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:24:07.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:24:26.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:25:30.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:26:37.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:30:52.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:30:58.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:31:41.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:32:02.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:32:09.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:34:39.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:34:58.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:35:28.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:35:43.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:35:51.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:36:27.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:36:35.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-23 21:36:42.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 19:23:17.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 19:23:28.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 19:23:36.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 19:24:08.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:56:15.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:56:25.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:57:08.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:57:15.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:57:26.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:57:34.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:59:14.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 22:59:26.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-26 23:01:15.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-10-28 07:17:42.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-01 12:44:43.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-02 20:47:29.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-03 17:07:10.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-07 10:19:19.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-08 12:12:40.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-12 09:04:52.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-13 19:54:46.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-14 16:10:07.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-16 20:07:28.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-18 12:22:52.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-18 12:22:52.000', 120), 'up', NULL),
  (33405570, CONVERT([datetime], '2022-11-27 20:26:10.000', 120), 'up', 'se'),
  (33405570, CONVERT([datetime], '2022-12-01 15:19:40.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 15:13:24.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:55:32.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:55:45.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:56:37.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:57:21.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:57:36.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:57:50.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:58:06.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 20:58:43.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 21:00:04.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-07 21:00:19.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-08 22:58:50.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-08 23:00:24.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-08 23:00:35.000', 120), 'se', 'se'),
  (33405570, CONVERT([datetime], '2022-12-15 16:54:43.000', 120), 'se', 'se');

-- cte для облегчения кода
WITH [setOrdering] AS (
SELECT
  [id],
  -- нумерация по минимальной дате, граница группы
  ROW_NUMBER() OVER (ORDER BY [start_datetime_status], [id]) AS [ORDERING],
  [start_datetime_status],
  [end_datetime_status],
  [status_1],
  [status_2]
FROM
  (
    -- такая же группировка как у автора
    SELECT 
      [id],
      [status_1],
      [status_2],
      MIN([datetime]) AS [start_datetime_status],
      MAX([datetime]) AS [end_datetime_status]
    FROM @tempTable
    GROUP BY
      [id],
      [status_1],
      [status_2]
  ) d
)

SELECT
  s1.[id],
  s1.[start_datetime_status],
  -- вот здесь берем следующую дату из группы если имеется, иначе - берем макс дату
  ISNULL(s2.[start_datetime_status], s1.[end_datetime_status]) AS [end_datetime_status],
  s1.[status_1],
  s1.[status_2]
FROM
  [setOrdering] s1
  -- присоединение "на себя" с шагом +1
  LEFT JOIN [setOrdering] s2 ON s2.[ORDERING] = s1.[ORDERING] + 1;