#sql #postgresql #amazon-aurora #gaps-and-islands
#sql #postgresql #амазонка-аврора #пробелы и острова
Вопрос:
Я изо всех сил пытаюсь найти какую-либо информацию об этом в Интернете после пары часов поиска, проб, ошибок и неудач. У нас есть следующая структура таблицы:
| Имя | Дата события | Марк |
|---|---|---|
| Дейв | 2021-03-24 09:00:00 | Подарок |
| Дейв | 2021-03-24 14:00:00 | Отсутствует |
| Дейв | 2021-03-25 09:00:00 | Отсутствует |
| Дейв | 2021-03-26 09:00:00 | Отсутствует |
| Дейв | 2021-03-27 09:00:00 | Подарок |
| Дейв | 2021-03-27 14:00:00 | Отсутствует |
| Дейв | 2021-03-28 09:00:00 | Отсутствует |
| Дейв | 2021-03-29 10:00:00 | Отсутствует |
| Дейв | 2021-03-30 13:00:00 | Отсутствует |
| Джейн | 2021-03-30 13:00:00 | Отсутствует |
В основном регистрируется для людей на мероприятия. Нам нужно составить отчет, чтобы узнать, с кем мы не контактировали более x дней подряд. Последовательное значение для дней, в которые в данных есть события, а не последовательные календарные дни. Кроме того, если есть подарок в один из дней, когда они также отсутствовали, подсчет должен начинаться снова со следующего дня, когда они отсутствовали.
Первая проблема, с которой я столкнулся, — это получение четких дат, когда есть только отсутствующие, затем 2-я-получение количества последовательных дней отсутствия-я сделал 2-е в MySQL с переменными, но изо всех сил пытался перенести это в PostgreSQL, откуда ведется отчетность.
Примером вывода, который я хотел бы получить, является:
| Имя | Дата события | Марк | Количество последовательностей |
|---|---|---|---|
| Дейв | 2021-03-24 09:00:00 | Подарок | 0 |
| Дейв | 2021-03-24 14:00:00 | Отсутствует | 0 |
| Дейв | 2021-03-25 09:00:00 | Отсутствует | 1 |
| Дейв | 2021-03-26 09:00:00 | Отсутствует | 2 |
| Дейв | 2021-03-27 09:00:00 | Подарок | 0 |
| Дейв | 2021-03-27 14:00:00 | Отсутствует | 0 |
| Дейв | 2021-03-28 09:00:00 | Отсутствует | 1 |
| Дейв | 2021-03-29 10:00:00 | Отсутствует | 2 |
| Дейв | 2021-03-30 13:00:00 | Отсутствует | 3 |
| Джейн | 2021-03-30 13:00:00 | Отсутствует | 0 |
В настоящее время в этой таблице содержится 639931 запись, и они были созданы с 1 октября и будут продолжать расти такими темпами.
Любая помощь или совет о том, с чего начать, было бы здорово.
Комментарии:
1. Я бы с удовольствием помог, но сейчас у меня нет времени. Не могли бы вы поискать по
gaps-and-islandsтегу, который я добавил к вашему вопросу?
Ответ №1:
Это может быть достигнуто с помощью оконных функций следующим образом:
WITH with_row_numbers AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EventDateTime) AS this_row_number, (CASE WHEN Mark = 'Present' THEN ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EventDateTime) ELSE 0 END) AS row_number_if_present FROM events ) SELECT Name, EventDateTime, Mark, GREATEST(0, this_row_number - MAX(row_number_if_present) OVER (PARTITION BY Name ORDER BY EventDateTime) - 1) FROM with_row_numbers Оригинальный ответ с LATERAL присоединением
WITH with_row_numbers AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EventDateTime) FROM events e ) SELECT t1.Name, t1.EventDateTime, t1.Mark, GREATEST(0, t1.ROW_NUMBER - COALESCE(sub.prev_present_row_number, 0) - 1) AS ConsecCount FROM with_row_numbers AS t1 CROSS JOIN LATERAL ( SELECT MAX(row_number) AS prev_present_row_number FROM with_row_numbers t2 WHERE t2.Name = t1.Name AND t2.EventDateTime lt;= t1.EventDateTime AND t2.Mark = 'Present' ) sub