Рассчитайте промежуточную сумму чистого дефицита с рекурсивной корректировкой и ограниченным излишком
Я пытаюсь рассчитать чистый дефицит по ряду значений со следующими условиями (в форме гипотетических):
- Существует статическая квота рабочих дней для любого заданного набора (производственного цикла) данных, например 30
- Существует выход продукции на любой данный день
- В зависимости от урожайности может быть дефицит или избыток за день.
- Дефицит накапливается в ходе производственного цикла и компенсируется излишком, но только до нуля.
- Излишек нельзя применить, если нет дефицита, т.е. излишек нельзя «отложить» на случай будущего дефицита.
Представление производственного цикла:
Мне нужно по каждому дню знать, был ли дефицит или излишек и чистый дефицит за весь тираж. Мне не нужно знать ежедневную сеть, но я предоставил ее, чтобы проиллюстрировать, как она накапливается. Согласно набору данных, чистая нехватка для пробега равна -3.
Я пробовал выполнить расчет в запросе, используя SUM() OVER(ORDER BY Date), но всегда зависал, потому что применение излишка зависит от чистого дефицита, а чистый дефицит зависит от излишка. В итоге я запросил приведенные выше данные без NetShortage, а затем перебрал результат на стороне C#, используя условную логику для изменения аккумулятора. Это работает нормально, но мне очень любопытно, есть ли способ сделать это непосредственно в SQL (запрос, а не только эквивалентный цикл в хранимой процедуре)
Я пробовал выполнить расчет в запросе с использованием SUM() OVER(ORDER BY Date), но всегда зависал, потому что применение излишка зависит от чистого дефицита, а чистый дефицит зависит от излишка. В итоге я запросил приведенные выше данные без столбца NetShortage, а затем перебрал результат на стороне C#, используя условную логику для изменения аккумулятора. Это работает нормально, но мне очень интересно, есть ли способ сделать это непосредственно в SQL (запрос, а не просто эквивалентный цикл в хранимой процедуре).
Я пробовал это, я пробовал это, но мы не видим ни одного запроса...
@TheImpaler MS SQL-сервер. Как бы вы сделали это в PostgresQL?
Каковы ваши образцы данных и какие данные вам нужны? Вам нужны последние 3 столбца?
@DaleK Демонстрационные данные будут представлять собой больший набор всех данных производственного цикла. Желательными данными будет подмножество (за январь), как указано выше, а именно чистый дефицит в конце цикла (-3). Я могу получить все, что указано выше, в запросе, кроме последнего столбца; Чтобы вычислить это, мне нужно обработать набор программно.
Как вы составили последние 4 столбца? Какова ваша логика?
@Eric Yield — это реальное значение, например 5 января фабрика выпустила 25 изделий. Недостача – это сумма дохода ниже нормы, профицит – сумма дохода выше нормы. Последний столбец — это то, что мне не удалось сделать непосредственно в запросе...
Извините, последние 4 строки, а не столбцы.
Ответы 3
Вы можете использовать запрос ниже. Я опускаю столбцы Shortage и Surplus.
SELECT [Date], Quota, Yield
, SUM(Yield - Quota) OVER(ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NetShort
FROM #MyTable
Вы это проверяли? Совершенно уверен, что это неправильно рассчитывает чистый дефицит. Это определенно не соответствует результатам, полученным другим ответом.
Для вычисления этих значений вам необходимо использовать рекурсивный CTE. Ты можешь сделать:
with
x as (
select t.*,
least(yield - quota, 0) as shortage,
greatest(yield - quota, 0) as surplus,
row_number() over(order by date) as rn
from t
),
n as (
select x.*, least(shortage + surplus, 0) as netshort from x where rn = 1
union all
select x.*, least(n.netshort + x.shortage + x.surplus, 0)
from n
join x on x.rn = n.rn + 1
)
select * from n;
Результат:
Date Quota Yield shortage surplus rn netshort
----------- ------ ------ --------- -------- --- --------
2000-01-01 30 30 0 0 1 0
2000-01-05 30 25 -5 0 2 -5
2000-01-06 30 30 0 0 3 -5
2000-01-09 30 28 -2 0 4 -7
2000-01-15 30 34 0 4 5 -3
2000-01-17 30 30 0 0 6 -3
2000-01-19 30 29 -1 0 7 -4
2000-01-20 30 37 0 7 8 0
2000-01-21 30 32 0 2 9 0
2000-01-24 30 27 -3 0 10 -3
2000-01-25 30 30 0 0 11 -3
См. пример выполнения в db<>fiddle.
Примечание. Учтите, что по умолчанию SQL Server поставляется с низким пределом рекурсивных итераций (maxrecursion = 100?). В этом случае вам нужно будет добавить дополнительное предложение SQL, чтобы увеличить количество итераций по мере необходимости.
О да, так очень приятно. Я много раз был близок к чему-то подобному; Я знал, что где-то там нужна рекурсия, но просто не мог вообразить это. Затем дошло до того, что мне пришлось отказаться от функциональности и элегантности, просто сделать это и двигаться дальше. Спасибо!
Когда я пытался интегрировать это в свою реальную систему, я заметил, что ее можно немного упростить на основе правил. В рекурсивном CTE, где rn = 1, так как это первый день и излишки можно применять только при недостаче: select x.*, shortage as netshort from x where rn = 1
@ jdcook72 Да, если вы уверены в этом правиле, вы можете его упростить.
CREATE TABLE #t
(
[Date] date NOT NULL PRIMARY KEY
,Quota int NOT NULL
,Yield int NOT NULL
,Shortage int NULL
,Surplus int NULL
,NetShort int NULL
);
INSERT INTO #t ([Date], Quota, Yield)
VALUES ('2000-01-01', 30, 30)
,('2000-01-05', 30, 25)
,('2000-01-06', 30, 30)
,('2000-01-09', 30, 28)
,('2000-01-15', 30, 34)
,('2000-01-17', 30, 30)
,('2000-01-19', 30, 29)
,('2000-01-20', 30, 37)
,('2000-01-21', 30, 32)
,('2000-01-24', 30, 27)
,('2000-01-25', 30, 30);
DECLARE @check int
,@Anchor date
,@NetShort int = 0;
UPDATE T
SET Shortage = CASE WHEN Yield < Quota THEN Yield - Quota ELSE 0 END
,Surplus = CASE WHEN Yield > Quota THEN Yield - Quota ELSE 0 END
,@NetShort = CASE
WHEN @NetShort + Yield - Quota < 0
THEN @NetShort + Yield - Quota
ELSE 0
END
,NetShort = @NetShort
,@check = CASE WHEN [Date] > ISNULL(@Anchor, '1900') THEN 1 ELSE 1/0 END
,@Anchor = [Date]
FROM #t T WITH (TABLOCKX)
OPTION (MAXDOP 1);
SELECT [Date], Quota, Yield, Shortage, Surplus, NetShort
FROM #t
ORDER BY [Date];
Какая база данных? Легко в PostgreSQL.