Найти максимальное количество дней подряд без продаж в SQL
СУБД, которую я использую, — Teradata.
У меня есть таблица «Продажи» со столбцами: Магазин, предмет, DT и объем
Это таблица ежедневных продаж, где Store и Item — числа, а DT — дата.
Теперь может случиться так, что если в комбинации «магазин/товар» не было продаж на определенную дату, о ней может не быть записи в таблице, или ее запись имеет 0 в качестве значения объема. Крайне важно рассмотреть оба случая.
Я хотел бы рассчитать максимальное количество последовательных дней без продаж для комбинации магазин/товар, где диапазон дат находится между «31 декабря 2023 г.» и «30 марта 2024 г.».
Вот что я придумал, но, к сожалению, он не учитывает многие случаи:
SELECT
sq.STORE,
sq.ITEM,
MAX(sq.Consec_No_Sales_Days) AS Max_Consec_No_Sales_Days
FROM (
SELECT
STORE,
ITEM,
DT - LAG(DT)
OVER(ORDER BY STORE,ITEM,DT) AS Consec_No_Sales_Days
FROM
SALES
WHERE
SALES.VOLUME > 0
AND DT BETWEEN '2023-12-31' AND '2024-03-30'
GROUP BY
STORE,
ITEM,
DT
) sq
WHERE
sq.Consec_No_Sales_Days > 0
GROUP BY
sq.STORE,
sq.ITEM
Ответы 1
Если вы получаете данные за определенный период ('2023-12-31' - '2024-03-30') и у вас есть данные до начала периода, то какой будет предыдущая дата продажи для МАГАЗИНА/ТОВАРА, имеющего первый продажа в период '10.01.2024' ???
ПРИМЕЧАНИЕ. В таком случае приведенный ниже код принимает начало периода как дату предыдущей продажи (с использованием функции объединения). То же самое было сделано с окончанием периода относительно следующей даты продажи. Если это не так, вам следует внести соответствующие изменения в соответствии с вашими потребностями.
/* SALES table sample data
STORE ITEM DT VOLUME
---------- ---------- ---------- ----------
10 101 2023-11-30 12
10 101 2023-12-31 10
10 101 2024-01-25 21
10 103 2024-03-25 32
10 103 2024-03-30 10
20 107 2024-01-30 54
20 107 2024-02-12 23
20 109 2024-03-01 43
30 110 2024-01-10 17 */
Создайте внутренний запрос, который исключает нулевые или нулевые строки VOLUME и извлекает только строки в пределах периода (предложение Where). Без строк нулевого/нулевого объема аналитические функции LAG()/LEAD() Over() будут работать нормально, если разделение по частям и порядок по частям в порядке. Существует также математический столбец расчета даты DAY_DIFF, описанный в ПРИМЕЧАНИЕ выше. Внешний запрос просто объединяет и группирует набор результатов внутреннего запроса.
-- S Q L :
SELECT STORE, ITEM, Max(DAY_DIFF) AS MAX_DAY_DIFF
FROM ( Select STORE, ITEM, DT, VOLUME,
GREATEST( DT - Coalesce( LAG(DT) Over(Partition By STORE, ITEM Order By DT), To_Date('2023-12-31', 'yyyy-mm-dd') ),
Coalesce( LEAD(DT) Over(Partition By STORE, ITEM Order By DT), To_Date('2024-03-30', 'yyyy-mm-dd') ) - DT ) AS DAY_DIFF
From sales
Where Coalesce(VOLUME, 0) > 0 And
DT Between DATE '2023-12-31' And DATE '2024-03-30'
)
GROUP BY STORE, ITEM
HAVING Max(DAY_DIFF) > 0
ORDER BY STORE, ITEM
/* R e s u l t :
STORE ITEM MAX_DAY_DIFF
---------- ---------- ------------
10 101 65
10 103 85
20 107 47
20 109 61
30 110 80 */
По большей части это работает. Исключение: предположим, что у товара была распродажа только один день, 10 января. Кажется, что код вернет 10 дней, однако я хочу, чтобы он возвращал 80 дней; (Конец диапазона дат) – 10 января = 80.
@feonyte Только что обновил ответ. Та же логика с окончанием периода. Получите НАИБОЛЬШУЮ ценность из двух.
Можете ли вы уточнить не охваченные требования/случаи и, возможно, предоставить примеры данных и желаемые результаты? Например, если что-то было продано 1 декабря 2023 г., а затем снова 01 января 2024 г., что бы вы хотели указать в качестве Consec_No_Sales_Days? Тот же вопрос для чего-то, что было продано 1 марта 2024 г., а затем не было продано 30 марта 2024 г.? А как насчет вещей, которые вообще не продавались в рассматриваемый период – их нужно включать?