Ile kompletów da się złożyć: alokacja zachłanna składowych w T-SQL

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

Ile kompletów da się złożyć w T-SQL, zależy od składowej, której jest najmniej w stosunku do receptury. Magazynier zebrał dla zlecenia po 3 sztuki każdej z trzech składowych, a zamówiono po 3 komplety dwóch rodzajów, które zużywają wspólną składową.

Liczbę kompletów, które da się złożyć, wyznacza najmniejszy iloraz stanu składowej przez jej ilość w recepturze, wzięty po wszystkich składowych kompletu i nie większy od zamówionej liczby. Gdy komplety dzielą składowe, o wyniku decyduje jawna kolejność alokacji zapisana w danych, a pętla po kompletach zwraca status każdego z nich.

To wzorzec z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 przy poziomach zgodności 130 i 160 i za każdym razem dostaliśmy ten sam wynik. Zmienne tablicowe i pętla WHILE oraz pozostałe użyte konstrukcje są dostępne od SQL Server 2016 (według dokumentacji).

Liczbę kompletów wyznacza kolejność, w jakiej komplety biorą składowe

Przykład: zlecenie 26-1004 zamawia po 3 sztuki Kompletu X i Kompletu Y, a magazynier zebrał po 3 sztuki każdej z trzech składowych. Komplet X składa się z dwóch sztuk S1 i jednej S2, a Komplet Y z jednej S1 i jednej S3. Składowa S1 jest wspólna, więc oba komplety konkurują o to samo.

Gdy pierwszy w kolejności jest Komplet X, powstaje 1 komplet X i 1 komplet Y, a po 2 sztuki składowych S2 i S3 zostają luzem. Po odwróceniu kolejności Komplet Y zabiera wszystkie 3 sztuki S1 i S3, Komplet X nie powstaje, a luzem zostają 3 sztuki S2. Łącznie jest 2 komplety w pierwszym wariancie i 3 w drugim.

Kolejność alokacji to więc decyzja zapisana w danych, a nie w kodzie. Status każdego kompletu mówi, czy po alokacji coś zostało luzem. Funkcję systemu magazynowego, w której komplet nie ma własnego stanu i powstaje z receptury, opisuje osobny artykuł o komplecie z receptury bez stanu, a ogólne omówienie kompletów strona ozmianie indeksu i kompletach w WMS. Ten artykuł pokazuje wyłącznie wzorzec zapytania.

Dane próbne i receptura kompletów

Receptura ma cztery wiersze: komplet K-X składa się z 2 sztuk S1 i 1 sztuki S2, a komplet K-Y z 1 sztuki S1 i 1 sztuki S3. Tabela receptury ma klucz po parze komplet i składowa oraz ograniczenie CHECK, że ilość jest większa od zera. Tabela zebranych składowych trzyma stan dla zlecenia, a tabela zamówień ma kolumnę Priorytet, która wyznacza kolejność alokacji.

Przykład. Cztery wiersze receptury; składowa S1 należy do obu kompletów.

Sześć zleceń przykładowych, od 26-1001 do 26-1006, odtwarza po jednej sytuacji.

CREATE TABLE dbo.Receptura (Komplet varchar(10), Skladowa varchar(10), Ilosc int NOT NULL CHECK (Ilosc > 0), PRIMARY KEY (Komplet, Skladowa));
CREATE TABLE dbo.Zebrano (Zlecenie varchar(20), Skladowa varchar(10), Ilosc int, PRIMARY KEY (Zlecenie, Skladowa));
CREATE TABLE dbo.Zamowienie (Zlecenie varchar(20), Priorytet int, Komplet varchar(10), Zam int, PRIMARY KEY (Zlecenie, Priorytet));
INSERT dbo.Receptura VALUES ('K-X','S1',2),('K-X','S2',1),('K-Y','S1',1),('K-Y','S3',1);
INSERT dbo.Zebrano VALUES ('26-1001','S1',6),('26-1001','S2',2),('26-1001','S3',2),
                          ('26-1002','S1',7),('26-1002','S2',2),('26-1002','S3',2),
                          ('26-1003','S1',1),('26-1003','S3',1),
                          ('26-1004','S1',3),('26-1004','S2',3),('26-1004','S3',3),
                          ('26-1005','S1',3),('26-1005','S2',3),('26-1005','S3',3),
                          ('26-1006','S1',2);
INSERT dbo.Zamowienie VALUES ('26-1001',1,'K-X',2),('26-1001',2,'K-Y',3),
                             ('26-1002',1,'K-X',2),('26-1002',2,'K-Y',3),
                             ('26-1003',1,'K-X',1),('26-1003',2,'K-Y',1),
                             ('26-1004',1,'K-X',3),('26-1004',2,'K-Y',3),
                             ('26-1005',1,'K-Y',3),('26-1005',2,'K-X',3),
                             ('26-1006',1,'K-X',1);

Przykład ilustracyjny. Receptura, zebrane składowe i zamówienia dla sześciu zleceń; nazwy i wartości są wymyślone.

Najmniejszy iloraz po składowych i brakująca składowa

Liczba kompletów, które da się złożyć, to najmniejszy iloraz zasób przez ilość w recepturze po składowych kompletu, nie większy od zamówionej liczby. Składowa, której nie zebrano wcale, liczy się jako 0 dzięki złączeniu LEFT JOIN i funkcji ISNULL. Złączenie wewnętrzne pominęłoby taką składową i zawyżyło wynik.

Dla zlecenia 26-1006 zebrano tylko 2 sztuki S1, a komplet K-X wymaga też S2. Złączenie wewnętrzne dało 1 komplet, a LEFT JOIN poprawnie 0.

SELECT 'JOIN_6' AS Sekcja,
 (SELECT MIN(z.Ilosc / r.Ilosc) FROM dbo.Receptura r JOIN dbo.Zebrano z ON z.Skladowa = r.Skladowa AND z.Zlecenie = '26-1006' WHERE r.Komplet = 'K-X') AS Inner_join,
 (SELECT MIN(ISNULL(z.Ilosc, 0) / r.Ilosc) FROM dbo.Receptura r LEFT JOIN dbo.Zebrano z ON z.Skladowa = r.Skladowa AND z.Zlecenie = '26-1006' WHERE r.Komplet = 'K-X') AS Left_join;

Drugie zagadnienie to dzielenie. Dzielenie int przez int obcina wynik (7 / 2 = 3), a dzielenie dziesiętne zwraca ułamek (7.0 / 2 = 3,500000, także dla decimal(15,3) przez 2). Dla ilości dziesiętnych potrzebna jest więc funkcja FLOOR, której wynik to 3. Dzielenie opisuje dokumentacja operatora dzielenia.

SELECT 'DZIELENIE' AS Sekcja, 7 / 2 AS int_int, 7.0 / 2 AS dziesietne, FLOOR(7.0 / 2) AS floor_dziesietne,
       CAST(7 AS decimal(15,3)) / 2 AS decimal_int, FLOOR(CAST(7 AS decimal(15,3)) / 2) AS floor_decimal;

Przykład. Jeden wiersz wyniku zapytania z dzieleniem.

Alokacja zachłanna w jawnej kolejności zabiera składowe po kolei

Alokacja to pętla WHILE po tabeli zmiennej, bez kursora, w kolejności kolumny Priorytet. Dla każdego kompletu liczba złożona to mniejsza z liczby zamówionej i liczby możliwej, a od zasobu odejmuje się liczbę złożoną razy ilość z receptury. Następny komplet widzi już tylko to, co zostało.

Procedura ma dwie tabele zmienne, które trzymają zasób (kolumna Reszta) i wynik, oraz pętlę po kompletach. W pętli zapytanie liczy najmniejszy iloraz po składowych kompletu, z LEFT JOIN do zasobu i ISNULL. Dwa polecenia UPDATE zapisują potem liczbę złożoną i zmniejszają zasób. Po pętli zapytanie końcowe nadaje status każdemu kompletowi.

CREATE PROCEDURE dbo.UlozKomplety @Zlecenie varchar(20) AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @zasob TABLE (Skladowa varchar(10) PRIMARY KEY, Reszta int);
  DECLARE @wynik TABLE (Priorytet int PRIMARY KEY, Komplet varchar(10), Zam int, Zlozone int);
  INSERT @zasob SELECT Skladowa, SUM(Ilosc) FROM dbo.Zebrano WHERE Zlecenie = @Zlecenie GROUP BY Skladowa;
  INSERT @wynik SELECT Priorytet, Komplet, Zam, 0 FROM dbo.Zamowienie WHERE Zlecenie = @Zlecenie;
  DECLARE @k int = (SELECT MIN(Priorytet) FROM @wynik), @ile int;
  WHILE @k IS NOT NULL
  BEGIN
    SELECT @ile = MIN(ISNULL(z.Reszta, 0) / r.Ilosc)
    FROM @wynik w JOIN dbo.Receptura r ON r.Komplet = w.Komplet
    LEFT JOIN @zasob z ON z.Skladowa = r.Skladowa
    WHERE w.Priorytet = @k;
    UPDATE @wynik SET Zlozone = CASE WHEN ISNULL(@ile, 0) < Zam THEN ISNULL(@ile, 0) ELSE Zam END WHERE Priorytet = @k;
    UPDATE z SET Reszta = z.Reszta - w.Zlozone * r.Ilosc
    FROM @zasob z JOIN dbo.Receptura r ON r.Skladowa = z.Skladowa
    JOIN @wynik w ON w.Komplet = r.Komplet AND w.Priorytet = @k;
    SELECT @k = MIN(Priorytet) FROM @wynik WHERE Priorytet > @k;
  END;
  SELECT w.Priorytet, w.Komplet, w.Zam, w.Zlozone,
         CASE WHEN EXISTS (SELECT 1 FROM dbo.Receptura r JOIN @zasob z ON z.Skladowa = r.Skladowa
                           WHERE r.Komplet = w.Komplet AND z.Reszta > 0) THEN 'CZESCIOWY'
              WHEN w.Zlozone > 0 THEN 'PELNY' ELSE 'BEZ_SKLADNIKOW' END AS Status
  FROM @wynik w ORDER BY w.Priorytet;
END;

Alokacja zachłanna daje wynik zgodny z kolejnością zapisaną w danych, ale nie maksymalizuje łącznej liczby kompletów.

Zlecenia 26-1004 i 26-1005 mają te same zasoby (po 3 sztuki każdej składowej) i to samo zamówienie (po 3 sztuki K-X i K-Y). Kolejność K-X, K-Y daje 1 + 1 = 2 komplety i po 2 sztuki S2 i S3 luzem. Kolejność K-Y, K-X daje 3 + 0 = 3 komplety i 3 sztuki S2 luzem.

Status kompletu mówi, co zostało luzem po alokacji

Status mówi o tym, co zostało po alokacji, a nie o tym, czy zamówienie jest wykonane w całości. Reguły są trzy i rozstrzygamy je w tej kolejności. CZESCIOWY dostaje komplet, gdy luzem została choć jedna składowa jego receptury. PELNY dostaje komplet, gdy złożono więcej niż 0 i nic jego składowych nie zostało luzem. BEZ_SKLADNIKOW dostaje komplet, gdy nic nie złożono i nic nie zostało luzem.

Złożenie mniejszej liczby, niż zamówiono, nie jest błędem, gdy nic nie zostało luzem. W zleceniu 26-1001 (S1 6, S2 2, S3 2; zamówiono K-X 2 i K-Y 3) K-X złożono 2, a K-Y 2 z 3, nic nie zostało luzem, więc oba komplety mają status PELNY. W zleceniu 26-1002 (S1 7) liczby złożone są te same, ale 1 sztuka S1 zostaje luzem, więc oba komplety mają status CZESCIOWY, bo S1 należy do obu receptur.

W zleceniu 26-1003 zebrano S1 1 i S3 1 i zamówiono po 1 sztuce K-X i K-Y. K-X złożono 0 (status BEZ_SKLADNIKOW), a K-Y złożono 1 (status PELNY).

Przykład. W zleceniu 26-1005 kolumna Status podaje wartości w kolejności K-X, K-Y, a alokacja szła w kolejności K-Y, K-X.

Zapytanie kontrolne liczy dla każdej składowej, ile zebrano, ile zużyto (złożono razy ilość z receptury) i ile zostało luzem, niezależnie od pętli. Dla zlecenia 26-1004 luzem zostają 2 sztuki S2 i 2 sztuki S3, a S1 jest zużyte w całości. Procedura zwraca wynik do tabeli tymczasowej, dlatego kolumna Status ma długość 16 znaków: wartość BEZ_SKLADNIKOW ma 14 znaków i nie zmieściłaby się w węższej kolumnie.

CREATE TABLE #w (Priorytet int, Komplet varchar(10), Zam int, Zlozone int, Status varchar(16));
GO
TRUNCATE TABLE #w;
INSERT #w EXEC dbo.UlozKomplety '26-1004';
SELECT 'ALOKACJA 26-1004' AS Sekcja, * FROM #w ORDER BY Priorytet;
SELECT z.Skladowa, z.Ilosc AS Zebrano, ISNULL(SUM(w.Zlozone * r.Ilosc), 0) AS Zuzyto,
       z.Ilosc - ISNULL(SUM(w.Zlozone * r.Ilosc), 0) AS Luzem
FROM dbo.Zebrano z
LEFT JOIN dbo.Receptura r ON r.Skladowa = z.Skladowa
LEFT JOIN #w w ON w.Komplet = r.Komplet
WHERE z.Zlecenie = '26-1004'
GROUP BY z.Skladowa, z.Ilosc ORDER BY z.Skladowa;

Przykład ilustracyjny. Wywołanie dla zlecenia 26-1004 i zapytanie kontrolne; GO oddziela partię z CREATE TABLE.

Przykład. Wynik zapytania kontrolnego dla zlecenia 26-1004.

Tak wygląda wynik dla zlecenia 26-1002 w siatce zapytania, razem z listą składowych pozostałych luzem.

Przypisanie zmiennej z GROUP BY zostawia wartość ostatniego wiersza

Przypisanie wartości do zmiennej w poleceniu SELECT z GROUP BY zostawia w zmiennej wartość z ostatniego zwróconego wiersza. Dla zlecenia z dwoma kompletami wyrażenie SELECT @n = COUNT(DISTINCT Komplet) z GROUP BY Komplet zostawia w @n wartość 1, bo każda grupa zwraca 1. Bez GROUP BY wynik to 2, a z podzapytaniem SELECT COUNT(*) z grupowaniem w środku także 2.

Dla SUM(Zam) z GROUP BY Komplet zmienna zależy od kolejności wierszy: wynosi 3 przy ORDER BY rosnąco i 2 przy ORDER BY malejąco. Pętla z procedury unika jej, bo przypisuje wartość z zapytania bez grupowania, zawsze jednowierszowego.

DECLARE @n int;
SELECT @n = COUNT(DISTINCT Komplet) FROM dbo.Zamowienie WHERE Zlecenie = '26-1001' GROUP BY Komplet;
SELECT 'GROUPBY_COUNT_DISTINCT' AS Sekcja, @n AS Zmienna;
SELECT @n = COUNT(DISTINCT Komplet) FROM dbo.Zamowienie WHERE Zlecenie = '26-1001';
SELECT 'BEZ_GROUPBY' AS Sekcja, @n AS Zmienna;
SELECT @n = (SELECT COUNT(*) FROM (SELECT Komplet FROM dbo.Zamowienie WHERE Zlecenie = '26-1001' GROUP BY Komplet) t);
SELECT 'PODZAPYTANIE' AS Sekcja, @n AS Zmienna;
SELECT @n = SUM(Zam) FROM dbo.Zamowienie WHERE Zlecenie = '26-1001' GROUP BY Komplet ORDER BY Komplet ASC;
SELECT 'SUM_GROUPBY_ORDER_ASC' AS Sekcja, @n AS Zmienna;
SELECT @n = SUM(Zam) FROM dbo.Zamowienie WHERE Zlecenie = '26-1001' GROUP BY Komplet ORDER BY Komplet DESC;
SELECT 'SUM_GROUPBY_ORDER_DESC' AS Sekcja, @n AS Zmienna;

Przykład. Wynik kolejnych zapytań; poprawna liczba kompletów w zleceniu 26-1001 to 2.

Pytania przed napisaniem własnej alokacji kompletów

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

• Konkurencja: czy kilka kompletów dzieli te same składowe i kto ustala, który ma pierwszeństwo.
• Mniej niż zamówiono: czy dopuszczalne jest złożenie mniejszej liczby kompletów, niż zamówiono, jeśli nic nie zostaje luzem.
• Reszta: co ma się dziać ze składowymi, które zostały luzem po alokacji.
• Ułamki: czy ilości składowych bywają ułamkowe, na przykład wagowe, i jak je wtedy dzielić.

Pokrewny wzorzec, w którym jeden artykuł jest rozdzielany na dostawy sumą narastającą, opisuje artykuł o alokacji wydania na dostawy. Wpływ jawnej kolejności w ORDER BY pokazuje artykuł o NULL w ORDER BY przy wyborze partii, a inną pułapkę zapytania o jeden wiersz artykuł o TOP 1 bez ORDER BY. Warunek zamknięcia zlecenia opisuje artykuł o zamykaniu zlecenia w T-SQL, a pobranie dokumentów do wysyłki bez wyścigu artykuł o UPDATE z OUTPUT. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.