Итерация между диапазоном дат в SQL
У меня есть данные, которые выглядят следующим образом:
start_date | end_date | value
-----------------------------
2022-01-01 2022-01-05 10
2022-01-01 2022-01-04 5
2022-01-03 2022-01-06 100
Я хочу суммировать значения строк, которые начаты, но еще не закончились. На одну дату я могу написать
SELECT SUM(value) FROM `xxx` WHERE start_date <= '2022-01-04' AND end_date > '2022-01-04');
чтобы получить все «открытые» строки 4 января (110).
Какой самый простой способ получить эти значения для каждого дня между двумя датами в SQL в одном запросе? Это сервер MariaDB.
В идеале результат должен выглядеть так:
date | open_values
------------------------
2022-01-01 15
2022-01-02 15
2022-01-03 115
2022-01-04 110
2022-01-05 100
2022-01-06 0
Спасибо за помощь!
@ПолТ. Да, start_date всегда присутствует. value строки устанавливается при создании и никогда не меняется. Not yet ended означает, что end_date позже запрошенной даты (или, в моем реальном наборе данных, она не установлена, но это должно быть тривиально для добавления позже). Выходная дата должна быть предоставлена каким-то алгоритмом или должна включать по крайней мере все start_date и все end_date для работы. На 4-м значение равно «110», потому что оно включает в себя первую и последнюю строку. Второй ряд уже «закончился» на 4-м. Первая и последняя строки начались, но еще не закончились.
Ответы 1
Используйте календарный стол, чтобы вернуть отдельные даты. ПРИСОЕДИНЯЙТЕСЬ к нему и используйте условную СУММУ для расчета общего количества value за день.
SELECT c.CalendarDate
, SUM( IF(t.End_Date > c.CalendarDate, t.value, 0) ) AS TotalValue
FROM CalendarTable c INNER JOIN YourTable t
ON t.Start_Date <= c.CalendarDate
AND t.End_Date >= c.CalendarDate
GROUP BY c.CalendarDate
ORDER BY c.CalendarDate
;
Полученные результаты:
CalendarDate | TotalValue :----------- | ---------: 2022-01-01 | 15 2022-01-02 | 15 2022-01-03 | 115 2022-01-04 | 110 2022-01-05 | 100 2022-01-06 | 0
Технически вы также можете использовать CTE. Однако календарная таблица будет более эффективной.
-- Demo: Generate 1 year's worth of dates
WITH recursive CalendarCTE AS (
SELECT '2022-01-01' AS CalendarDate
UNION ALL
SELECT DATE_ADD(CalendarDate, INTERVAL 1 DAY)
FROM CalendarCTE
WHERE CalendarDate < '2023-01-01'
)
-- INSERT INTO CalendarTable (CalendarDate)
SELECT c.CalendarDate
, SUM( IF(t.End_Date > c.CalendarDate, t.value, 0) ) AS TotalValue
FROM CalendarCTE c INNER JOIN YourTable t
ON t.Start_Date <= c.CalendarDate
AND t.End_Date >= c.CalendarDate
GROUP BY c.CalendarDate
ORDER BY c.CalendarDate
;
дб <> рабочий пример здесь
Круто, я получил пример CTE, чтобы он работал, спасибо! Вам нужно использовать CalendarCTE в первом предложении FROM. Кроме того, второе предложение WHERE не нужно/ничего не делает - что вы пытались сделать?
Также предложение IF в SUM() не нужно, но второе условие JOIN должно читаться как «t.End_Date > c.Calendar Date».
А, спасибо. Исправлена! Что касается предложения WHERE, исходное требование было немного нечетким, поэтому я пробовал разные вещи и случайно оставил его :)
... but the second JOIN condition should read t.End_Date > c.CalendarDate Это вернет результат, отличный от того, что вы опубликовали. Не могли бы вы уточнить, каких результатов вы ожидаете и почему?
В частности, вы хотите исключить 2022-01-06 или нет?
Вы правы, я как бы перепутал в голове условие СУММ и выбор даты. Еще раз спасибо, это отлично работает!
Что определяет
started, что присутствуетstart_date? Что такоеvalueв начале? Что означаетnot yet ended, является лиend_dateнулевым значением в данном случае? Кроме того, какойdateиспользуется для выходного столбца,start_date? Я немного запутался в выводе всего 110 на 4-м, так как есть только 1 запись с 4-м, у которого 10?