Анализ лет перерыва в SQL
У меня есть таблица (myt) в SQL, которая выглядит следующим образом:
CREATE TABLE myt (
Name VARCHAR(50),
Date_1 DATE,
Date_2 DATE,
Year_1 INT,
Month_1 INT,
Day_1 INT,
Year_2 INT,
Month_2 INT,
Day_2 INT
);
INSERT INTO myt (ID, Name, Date_1, Date_2, Year_1, Month_1, Day_1, Year_2, Month_2, Day_2) VALUES
( 'p1', '2010-01-01', '2010-05-01', 2010, 1, 1, 2010, 5, 1),
( 'p2', '2010-01-01', '2013-09-09', 2010, 1, 1, 2013, 9, 9);
Name Date_1 Date_2 Year_1 Month_1 Day_1 Year_2 Month_2 Day_2
p1 2010-01-01 2010-05-01 2010 1 1 2010 5 1
p2 2010-01-01 2013-09-09 2010 1 1 2013 9 9
Задача:
- Для каждой строки я хочу узнать, сколько февраля 01 появляется между датой_1 и датой_2.
Например:
P1: (date_1 = 01.01.2010, date_ 2= 01.05.2010) ответ 1 (1 февраля 2010 г.)
P2: (date_1 = 01.01.2010, date_2= 09.09.2013) ответ — 4 (1 февраля 2010 г., 1 февраля 2011 г., 1 февраля 2012 г., 1 февраля 2013 г.)
Я попробовал поискать, есть ли для этого конкретные функции SQL, и не нашел их, поэтому попытался сделать это, используя стандартизированный подход:
SELECT *,
(year_2 - year_1 +
(CASE WHEN month_1 < 2 OR (month_1 = 2 AND day_1 <= 1) THEN 1 ELSE 0 END) -
(CASE WHEN month_2 < 2 OR (month_2 = 2 AND day_2 < 1) THEN 1 ELSE 0 END)) as feb1_count
FROM myt;
Результаты кажутся правильными для этих тестовых случаев:
Name Date_1 Date_2 Year_1 Month_1 Day_1 Year_2 Month_2 Day_2 feb1_count
P1 2010-01-01 2010-05-01 2010 1 1 2010 5 1 1
P2 2010-01-01 2013-09-09 2010 1 1 2013 9 9 4
Это правильный способ анализа этой проблемы?
Ответы 2
Общий подход здесь использует трюк с календарной таблицей. Определите таблицу, содержащую 1 февраля для каждого года, который вам нужно охватить. Затем левой кнопкой мыши присоедините свою текущую таблицу к этой таблице календаря и объедините ее с помощью ID.
-- replace the CTE below with an actual table for better performance
WITH dates AS (
SELECT '2010-02-01' AS dt UNION ALL
SELECT '2011-02-01' UNION ALL
SELECT '2012-02-01' UNION ALL
SELECT '2013-02-01' UNION ALL
SELECT '2014-02-01' UNION ALL
SELECT '2015-02-01'
)
SELECT t1.Name, t1.Date_1, t1.Date_2, t1.Year_1, t1.Month_1, t1.Day_1,
t1.Year_2, t1.Month_2, t1.Day_2, COUNT(t2.dt) AS feb1_count
FROM myt t1
LEFT JOIN dates t2
ON t2.dt BETWEEN t1.Date_1 AND t1.Date_2
GROUP BY t1.Name, t1.Date_1, t1.Date_2, t1.Year_1, t1.Month_1, t1.Day_1,
t1.Year_2, t1.Month_2, t1.Day_2;
Если дата, которую вы считаете, не 29 февраля, ваше решение будет работать.
Однако в расчетах есть небольшая асимметрия с условиями:
(month_1 = 2 AND day_1 <= 1)
(month_2 = 2 AND day_2 < 1)
Я думаю, вы можете сделать код симметричным (или, возможно, антисимметричным), адаптировав его для чтения:
SELECT *,
(year_2 - year_1 - 1 +
(CASE WHEN month_1 < 2 OR (month_1 = 2 AND day_1 <= 1) THEN 1 ELSE 0 END) +
(CASE WHEN month_2 > 2 OR (month_2 = 2 AND day_2 >= 1) THEN 1 ELSE 0 END)) as feb1_count
FROM myt;
Если date_1 <= date_2, это должно работать даже в крайних случаях:
Обратной стороной этого подхода является сложность создания списка дат-кандидатов: вам нужна таблица с одной записью для даты годовщины каждого года между ГОД(МИН(дата1)) и ГОД(МАКС(дата2)). Положительным моментом является то, что он также может обрабатывать 29 февраля, а также легко обрабатывать «фиксированные праздники каждый год: 1 января, 4 июля, 25 декабря», при условии, что вы генерируете эти даты для каждого года в диапазоне.