Alokacja wydania na dostawy w T-SQL: suma narastająca i ilość do pobrania z każdej dostawy

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Alokacja wydania na dostawy w T-SQL zaczyna się od pytania, z których dostaw wziąć towar, gdy żadna z nich nie pokrywa całego wydania. W naszym przykładzie artykuł leży w pięciu dostawach, jedna z nich jest wyczerpana, a dwie przyszły tego samego dnia. Magazynier przygotowuje wydanie 60 sztuk.

Wydanie większe niż jedna dostawa rozdzielamy jednym zapytaniem, bez kursora. Suma narastająca stanów w jawnej kolejności mówi, ile zużyły dostawy wcześniejsze, więc z każdej kolejnej bierzemy mniejszą z dwóch wartości: resztę do wydania albo stan dostawy. Dla 60 sztuk daje to 30 sztuk z pierwszej dostawy, 20 z drugiej, a trzecia dokłada ostatnie 10.

To wzorzec z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 przy pięciu poziomach zgodności z zakresu od 100 do 160 i za każdym razem dostaliśmy ten sam wynik. Ramki ROWS i RANGE w funkcjach okienkowych pojawiły się w SQL Server 2012, więc wzorzec wymaga silnika w tej wersji lub nowszej (według dokumentacji).

Wydanie większe niż jedna dostawa wymaga rozkładu ilości

Artykuł może leżeć w magazynie w kilku dostawach naraz, a każda ma własny stan i datę przyjęcia. Gdy wydanie przekracza stan najstarszej dostawy, zapytanie musi zdecydować, ile wziąć z której. Ten rozkład ilości nazywamy w artykule alokacją, a jego wynikiem jest lista dostaw z ilością do pobrania z każdej.

W scenariuszu (przykład) pierwsza dostawa oddaje całe 30 sztuk, druga całe 20, a trzecia tylko 10 ze swoich 25. Czwarta dostawa ma stan zero, a piąta zostaje poza wynikiem, bo wydanie kończy się w trzeciej. Gdy ten sam artykuł ma w magazynie jedynie 40 sztuk, zapytanie zatrzymuje się komunikatem o braku stanu.

Zapis rozchodów, czyli wstawienie wyniku z ilością ze znakiem minus i skopiowanie numeru dostawy oraz właściciela z dostawy, jest naturalnym następnym krokiem, ale nie był częścią sprawdzonego przykładu. Model dostawy i właściciela opisuje osobny artykuł o stanie magazynu per dostawa i spółka.

Dane próbne i jawna kolejność dostaw

Tabela dostaw ma pięć pól, wymienionych w bloku poniżej, a stan ma typ decimal(12,3). Dane próbne to pięć dostaw artykułu 1 i jedna dostawa artykułu 2. W artykule 1 jedna dostawa ma stan zero, a dwie przyszły tego samego dnia. Właściciel towaru nazywa się w przykładzie Wlasciciel A albo Wlasciciel B.

CREATE TABLE dbo.Dostawa (
    IdDostawy      int           NOT NULL PRIMARY KEY,
    IdArtykulu     int           NOT NULL,
    Wlasciciel     varchar(20)   NOT NULL,
    DataPrzyjecia  date          NOT NULL,
    Stan           decimal(12,3) NOT NULL
);
INSERT dbo.Dostawa VALUES
 (1, 1, 'Wlasciciel A', '2026-09-01', 30),
 (2, 1, 'Wlasciciel B', '2026-09-03', 20),
 (3, 1, 'Wlasciciel A', '2026-09-03', 25),
 (4, 1, 'Wlasciciel B', '2026-09-10',  0),
 (5, 1, 'Wlasciciel A', '2026-09-15', 40),
 (6, 2, 'Wlasciciel A', '2026-09-02', 10);

Przykład ilustracyjny. Nazwy i wartości są wymyślone.

Kolejność wyboru dostaw jest jawna: najpierw data przyjęcia, potem numer dostawy, czyli najstarsza dostawa jako pierwsza. To decyzja zapytania, a nie cecha tabeli. Zmiana reguły, na przykład na termin ważności, sprowadza się do zmiany klauzuli ORDER BY wewnątrz OVER i nie dotyka niczego poza nią.

O tym, że bez jawnego ORDER BY kolejność wierszy nie jest określona, piszemy w artykule o TOP 1 bez ORDER BY. Brak daty ważności w części wierszy zmienia kolejność w sposób, który opisuje artykuł o NULL w ORDER BY.

Suma narastająca z ramką ROWS mówi, ile zużyły dostawy wcześniejsze

Całe zapytanie opiera się na jednym wyrażeniu: SUM(Stan) OVER (ORDER BY DataPrzyjecia, IdDostawy ROWS UNBOUNDED PRECEDING) - Stan. Wartość nazwana Poprzednio mówi, ile zużyły dostawy stojące wcześniej w kolejności. Dla pierwszej dostawy wynosi zero. Samą funkcję opisuje dokumentacja SUM.

Dostawy ze stanem zero odfiltrowuje już zapytanie wewnętrzne warunkiem Stan > 0, więc nie wchodzą do sumy narastającej. Dlatego dostawy 4 nie ma w tabeli poniżej. Dostawa 5 ma Poprzednio równe 75, czyli więcej niż zadane 60, więc zostaje poza wynikiem.

Przykład. Dostawa 4 odpada przez stan zero. Zadana ilość to 60.

Sumę narastającą liczymy w ramce ROWS z jednoznacznym kluczem w kolejności, bo domyślna ramka RANGE łączy dostawy z tą samą datą w jedną grupę.

Tę samą konstrukcję z ramką ROWS wykorzystuje artykuł o paletodobie w T-SQL, ale tam suma narastająca liczy stan palety na kolejne dni i niczego nie rozkłada. Odczyt według daty najstarszego przyjęcia opisuje artykuł o saldzie lokalizacji.

Z każdej dostawy bierzemy mniejszą z dwóch wartości

Ilość do pobrania z dostawy to reszta do wydania, czyli zadana ilość minus Poprzednio, o ile jest mniejsza od stanu dostawy. W przeciwnym razie bierzemy cały stan. Wiersze, w których reszta jest zerem albo liczbą ujemną, odcina warunek WHERE.

Wynik zapisujemy do tabeli tymczasowej #Alokacja, żeby następne zapytanie mogło go skontrolować. Pierwsze zapytanie po zapisie zwraca trzy dostawy uporządkowane po numerze.

DECLARE @Artykul int = 1, @Ilosc decimal(12,3) = 60;
IF (SELECT ISNULL(SUM(Stan), 0) FROM dbo.Dostawa WHERE IdArtykulu = @Artykul) < @Ilosc
    THROW 50001, N'Brak wystarczającego stanu artykułu.', 1;

WITH n AS (
    SELECT IdDostawy, Wlasciciel, DataPrzyjecia, Stan,
           SUM(Stan) OVER (ORDER BY DataPrzyjecia, IdDostawy
                           ROWS UNBOUNDED PRECEDING) - Stan AS Poprzednio
    FROM dbo.Dostawa
    WHERE IdArtykulu = @Artykul AND Stan > 0)
SELECT IdDostawy, Wlasciciel, Stan, Poprzednio,
       CASE WHEN @Ilosc - Poprzednio < Stan
            THEN @Ilosc - Poprzednio ELSE Stan END AS DoPobrania
INTO #Alokacja
FROM n
WHERE @Ilosc - Poprzednio > 0;

SELECT IdDostawy, Wlasciciel, Stan, DoPobrania FROM #Alokacja ORDER BY IdDostawy;

Przykład ilustracyjny. Alokacja 60 sztuk artykułu 1. Blok i zapytanie kontrolne z następnej sekcji tworzą jedną partię poleceń.

Przykład. Dostawy 4 i 5 nie występują w wyniku.

Tak ten wynik wygląda w siatce zapytania, razem z wierszem kontrolnym z następnej sekcji.

Brak stanu zatrzymuje zapytanie, a zapytanie kontrolne potwierdza wynik

Pierwsza instrukcja po deklaracjach porównuje sumę stanów artykułu z zadaną ilością. Gdy suma jest mniejsza, THROW 50001 przerywa partię komunikatem „Brak wystarczającego stanu artykułu.”. Sprawdziliśmy to dla ilości 500.

Zapytanie kontrolne porównuje sumę alokacji z zadaną ilością i zwraca słowo OK tylko wtedy, gdy obie wartości są równe, a żadna dostawa nie zeszła poniżej zera. Pilnuje tego warunek MIN(Stan - DoPobrania) >= 0. W przykładzie suma wynosi 60, zadana ilość też 60, a kontrola zwraca OK.

SELECT SUM(DoPobrania) AS Suma, @Ilosc AS Zadano,
       CASE WHEN SUM(DoPobrania) = @Ilosc AND MIN(Stan - DoPobrania) >= 0
            THEN 'OK' ELSE 'BLAD' END AS Kontrola
FROM #Alokacja;
DROP TABLE #Alokacja;

Przykład ilustracyjny. Zapytanie kontrolne w tej samej partii co alokacja.

Zatrzymanie wydania przy braku stanu jest decyzją projektową: wolimy błąd niż wydanie części. Reguła, która wydaje to, co jest, wymagałaby innego zapytania. Nie porównywaliśmy czasu zapytania z rozwiązaniem na kursorze, więc nie twierdzimy, że jedno jest szybsze.

Domyślna ramka RANGE i typ zmiennej zaniżają wynik po cichu

SUM OVER z klauzulą ORDER BY, ale bez wpisanej ramki, używa domyślnej ramki RANGE. Dla wierszy o tej samej wartości klucza sortowania liczy ona jedną wspólną sumę. W naszych danych dostawy 2 i 3 mają tę samą datę przyjęcia, więc przy sortowaniu tylko po dacie obie dostały sumę narastającą 75.

SUM(Stan) OVER (ORDER BY DataPrzyjecia) - Stan AS Poprzednio

Przykład ilustracyjny. Jedyna linia, którą zmieniamy: bez numeru dostawy w ORDER BY i bez ROWS.

Poprzednio wyniosło wtedy 55 i 50, do pobrania zostało 5 i 10, a suma alokacji 45 przy zadanych 60. Zapytanie kontrolne zwróciło BLAD. Ten sam wynik 45 powtórzył się przy pięciu poziomach zgodności z zakresu od 100 do 160, więc to zachowanie ramki, a nie poziomu zgodności.

Gdy w ORDER BY stoi także numer dostawy o niepowtarzalnych wartościach, wartości klucza się nie powtarzają i RANGE daje ten sam wynik co ROWS. Mimo to ROWS wpisujemy wprost, żeby zamiar był widoczny w kodzie. To zasada projektowa, a nie wynik pomiaru.

Druga pułapka dotyczy typu zmiennej z ilością. Zmienna int przy wartości 12,5 zapisała 12 bez ostrzeżenia, a decimal(12,3) zachowała 12,500, więc zmienna ma mieć ten sam typ co kolumna stanu. Na SQL Server 2022 nie zaobserwowaliśmy różnicy w wyniku nawet przy poziomie zgodności 100, dlatego wymóg dotyczy wersji silnika, a nie poziomu zgodności.

Pytania przed napisaniem własnej alokacji

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Dostawy: czy jedno wydanie może obejmować kilka dostaw tego samego artykułu i kto ustala kolejność ich wyboru.
• Reguła: czy kolejność, na przykład według daty przyjęcia albo terminu ważności, bywa różna dla różnych artykułów.
• Brak stanu: czy przy stanie mniejszym od zamówienia zatrzymać wydanie, czy wydać część.
• Rozchód: czy wiersz rozchodu ma zachować numer dostawy i właściciela dostawy, z której pochodzi.

Pokrewny wzorzec, w którym zapytanie sprawdza regułę zapisaną w danych, opisuje artykuł o oknie zamówień i DATEFIRST. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.