Проверка последовательных дней в SQL
У меня есть набор данных, структурированный следующим образом, который содержит тысячи материалов и миллионы строк:
Поскольку столбец дат пропускает и повторяет некоторые даты, мне сложно делать то, что я хочу.
Мне нужно рассчитать, будет ли прогнозируемый запас составлять 200% или более для каждых 28 дней подряд. Таким образом, в приведенном выше примере прогнозируемый запас на 200 % превышает целевой запас (24 * 200 % = 48), поэтому должен быть столбец, который проверяет текущую строку и выполняет поиск в течение следующих 28 дней (то есть не 28 строк). ), и если каждый день из этих 28 дней прогнозируемый запас превышает 48, то соответствующий материал помечается флажком. Для справки: мой запрос в настоящее время использует CTE для преобразования необработанных данных в набор данных ниже.
Projected_stock as (
select
Material,
Date,
Stock,
Target_Stock,
Demand,
Supply,
sum(Stock - Demand + Supply) over (partition by material order by date, rows between unbounded preceding and current row) as Projected_Stock
from datasource
group by all
order by material,date)
Спасибо.
Редактировать: Я использую блоки данных и ожидаю увидеть, что для этого материала 123456 рядом со столбцом Target_Stock есть еще один столбец Overstock, который имеет флаг во всех строках, поскольку существует более 28 последовательных дней, когда прогнозируемый запас превышает 200% целевого запаса (48)
Какие строки должны иметь флаг Overstock? Можете ли вы включить столбец id или row_number, который однозначно идентифицирует каждую строку?
Я добавил колонку и еще материал. Уникальным идентификатором является «Материал», и, как правило, все строки должны иметь флаг «Избыток», если в любом периоде, присутствующем в наборе данных, имеется 28 дней избытка запасов подряд.
Почему у вас есть Overstock=NO для материала 123457 и даты 30.07.2024? Только 1 из 4 записей с этой датой ниже 48.
Вы правы, должно быть? потому что мы не знаем, останется ли она в ближайшие 28 дней ниже или выше
«Представьте проблему, прежде чем публиковать какой-либо код» (см.: Как спросить). В настоящее время у вас есть бесполезное утверждение («содержит тысячи..... и миллионы строк») и слишком много данных, прежде чем наконец попытаться задать вопрос.
Если вы действительно хотите опубликовать так много данных, вам лучше опубликовать DBFIDDLE с этими данными....
Ответы 2
Версии решения для Postgres и MySQL. Функция DateAdd может отличаться для вас.
PostGres рабочий пример
WITH Projected_stock as
(
select
Material,
Date,
Stock,
Target_Stock,
Demand,
Supply,
sum(Stock - Demand + Supply) over (partition by material order by date, rows between unbounded preceding and current row) as Projected_Stock
from datasource
group by all
order by material,date),
CTE as
(
SELECT material, c.date,
CASE
WHEN MIN(CASE WHEN projected_stock>48 THEN '1' ELSE '0' END)!=MAX(CASE WHEN projected_stock>48 THEN '1' ELSE '0' END) THEN '?'
ELSE MIN(CASE WHEN projected_stock>48 THEN '1' ELSE '0' END)
END as Overstock
FROM Projected_stock c
WHERE c.date between c.date AND c.date + INTERVAL '28 day' --dateadd(DAY, 28, c.date)
GROUP BY material, date
),
SELECT p.*, c.overstock
FROM CTE c
LEFT JOIN Projected_stock p ON p.date=c.date and c.material=p.material
MySQL рабочий пример
WITH Projected_stock as
(
select
Material,
Date,
Stock,
Target_Stock,
Demand,
Supply,
sum(Stock - Demand + Supply) over (partition by material order by date, rows between unbounded preceding and current row) as Projected_Stock
from datasource
group by all
order by material,date), CTE as
(
SELECT material, c.Date,
CASE
WHEN MIN(CASE WHEN Projected>48 THEN '1' ELSE '0' END)!=MAX(CASE WHEN Projected>48 THEN '1' ELSE '0' END) THEN '?'
ELSE MIN(CASE WHEN Projected>48 THEN '1' ELSE '0' END)
END as Overstock
FROM Projected_stock c
WHERE c.Date between c.Date AND DATE_ADD(c.Date, INTERVAL 28 DAY)
GROUP BY c.material, c.Date
),
SELECT p.*, c.overstock
FROM CTE c
LEFT JOIN Projected_stock p ON p.Date=c.Date and c.material=p.material
«Ссылки на внешние ресурсы приветствуются» (см.: Как написать хороший ответ?). В настоящее время показаны только некоторые операторы SQL, и читатель должен выяснить, почему это должно работать (или нет). (Кстати: я не сторонник отрицательных оценок (на этот раз 😉))
Запросы обновлены и теперь включают Projected_Stock из источника данных.
Спасибо, Барт, для меня это работает и это именно то, что мне нужно. Исходная версия справилась со своей задачей, не знаете, зачем ее улучшать?
Какую дату вы использовали?
Сначала я использовал + INTERVAL «28 дней», и это сработало. Теперь я попробовал DATE_ADD(c.Date, INTERVAL 28 DAY), но это выдает ошибку DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE
Я думаю, что вам, вероятно, нужно что-то вроде этого. Арифметика дат - Oracle.
WITH refdate as
(
SELECT material, date, max(target_stock) target
FROM Projected_stock c
GROUP BY material, date
)
select material,date
,case when times_over > 0 and times_under = 0 then 'Overstocked'
when times_over = 0 then 'Not overstocked'
when times_over > 0 and times_under > 0 then 'Sometimes overstocked'
from
(SELECT r.material,r.date
,count(case when p.projected_stock > 2 * target then 1 end) times_over
,count(case when p.projected_stock < 2 * target then 1 end) times_under
FROM refdate r
JOIN Projected_stock p ON p.material=c.material and p.date between r.date+1 and r.date + 28
group by r.material,r.date
)
К сожалению, из-за отсутствия дат и, возможно, более одной записи на дату, я не думаю, что вы можете использовать аналитическую функцию, поэтому вы вынуждены соединить свою прогнозируемую таблицу запасов с самой собой.
Пожалуйста, отметьте свою СУБД и ожидаемый результат.