Rozbicie ilości na palety w T-SQL bez pętli i bez błędu zaokrągleń

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

Rozbicie ilości na palety w T-SQL da się zapisać jednym zapytaniem, bez pętli: liczbę palet daje dzielenie w górę na liczbach całkowitych, a kolejne wiersze palet daje tabela liczbowa. W opisywanym wdrożeniu formularz przyjęcia zewnętrznego rozbija ilość na palety, gdy pole numeru palety jest puste, a regułę tego formularza opisuje strona o numerze palety w przyjęciu zewnętrznym. Ten artykuł pokazuje wzorzec, który da się przenieść do dowolnej bazy, a nie kod formularza.

Dla ilości q i zawartości palety z liczba palet to n = (q + z - 1) / z. Na liczbach całkowitych to dzielenie w górę bez CEILING i bez rzutowania, a wiersze palet daje tabela liczbowa złączona warunkiem k <= n. Ostatnia paleta dostaje resztę q - z * (n - 1), a kontrola porównuje sumę z ilością.

Wzór nie wnosi błędu zaokrągleń, gdy ilość i zawartość są liczbami całkowitymi: dzielenie w górę na kolumnach int daje wynik bez liczb dziesiętnych. Przykład dał identyczny wynik na poziomach zgodności 130 i 160. Dla ilości ułamkowych wzór działa inaczej: na kolumnach decimal liczy w dół, a kontrola sumy zwraca „BLAD”. Granice wzorca opisujemy w ostatniej sekcji.

Liczbę palet daje dzielenie ilości przez zawartość w górę

Problem jest prosty. Z dokumentu dostawy znamy ilość, a na jednej palecie mieści się określona zawartość, więc trzeba policzyć, ile palet powstanie i ile sztuk trafi na każdą z nich. Liczba palet to najmniejsza liczba całkowita nie mniejsza od ilorazu ilości przez zawartość.

Dla ilości 250 i zawartości 100 powstają trzy palety. Dla 1000 przy 48 jest ich dwadzieścia jeden, a dla 26 przy 24 dwie. Pełne palety mają ilość równą zawartości, a ostatnia dostaje resztę.

Tabela w dalszej części pokazuje sześć takich przykładów razem z wynikiem kontroli sumy. Każdy z nich dał identyczny wynik na obu poziomach zgodności.

Dzielenie w górę na liczbach całkowitych nie wymaga zaokrąglania

Wzór (ilosc + na_palecie - 1) / na_palecie na kolumnach int daje dzielenie w górę bez CEILING i bez rzutowania na liczby dziesiętne. Dzielenie dwóch liczb całkowitych obcina ułamek, więc dodanie na_palecie - 1 do ilości przesuwa wynik do następnej liczby całkowitej, gdy reszta jest większa od zera. Dla ilości 26 i zawartości 24 wzór daje (26 + 24 - 1) / 24, czyli 49 / 24, a po obcięciu ułamka dwie palety.

W teście 30000 par, z ilością od 1 do 500 i zawartością od 1 do 60 na kolumnach int, wzór dał tę samą liczbę palet co CEILING na ilorazie dziesiętnym.

• Kolumny int: wzór daje dzielenie w górę bez CEILING i bez rzutowania; przykład użyty w artykule działa na kolumnach int.
• Kolumny decimal: wzór liczy w dół: 10,5 przy 5 daje dwie palety i sumę 10, a kontrola sumy zwraca „BLAD”.

Tabela liczbowa zastępuje pętlę po paletach

Pierwszy blok kodu tworzy tabelę przyjecie_przyklad, w której każdy artykuł ma ilość i zawartość palety. Kolumny ilosc i na_palecie mają typ int i ograniczenie CHECK, które wymaga wartości większych od zera. Sześć wierszy danych odpowiada sześciu artykułom z tabeli wyników.

SET NOCOUNT ON;
CREATE TABLE dbo.przyjecie_przyklad (
    artykul    varchar(10) PRIMARY KEY,
    ilosc      int NOT NULL CHECK (ilosc > 0),
    na_palecie int NOT NULL CHECK (na_palecie > 0));
INSERT dbo.przyjecie_przyklad VALUES
    ('ART-01', 250, 100), ('ART-02', 1000, 48), ('ART-03', 26, 24),
    ('ART-04', 7, 3), ('ART-05', 5, 100), ('ART-06', 48, 48);
WITH cyfry AS (SELECT x FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(x)),
liczby AS (SELECT 100 * a.x + 10 * b.x + c.x + 1 AS k FROM cyfry a, cyfry b, cyfry c),
palety AS (
    SELECT p.artykul, p.ilosc, p.na_palecie, l.k AS paleta,
           (p.ilosc + p.na_palecie - 1) / p.na_palecie AS ile_palet
    FROM dbo.przyjecie_przyklad AS p
    JOIN liczby AS l ON l.k <= (p.ilosc + p.na_palecie - 1) / p.na_palecie)
SELECT artykul, ilosc, paleta, ile_palet,
       CASE WHEN paleta < ile_palet THEN na_palecie
            ELSE ilosc - na_palecie * (ile_palet - 1) END AS ilosc_na_palecie
INTO #rozbicie FROM palety;

Przykład ilustracyjny, nazwy wymyślone. Tabela z sześcioma artykułami i zapytanie, które zapisuje rozbicie do tabeli tymczasowej.

Zapytanie składa się z czterech kroków, które opisuje poniższa lista.

1. Wyrażenie tabelowe cyfry zawiera cyfry od 0 do 9.
2. Wyrażenie liczby składa z nich trzy cyfry, czyli liczby od 1 do 1000.
3. Złączenie z warunkiem k <= liczba palet daje po jednym wierszu na każdą paletę każdego artykułu.
4. Wynik trafia do tabeli tymczasowej #rozbicie, z której korzysta kolejny blok.

Nie ma więc pętli ani kursora: o liczbie wierszy decyduje warunek złączenia.

Ostatnia paleta dostaje resztę

Wyrażenie warunkowe w ostatnim zapytaniu pierwszego bloku decyduje, ile sztuk dostaje dana paleta. Paleta o numerze mniejszym niż liczba palet dostaje pełną zawartość. Ostatnia dostaje ilość pomniejszoną o pełne palety, czyli ilosc - na_palecie * (ile_palet - 1). Dla 250 przy 100 są to dwie palety po 100 i ostatnia z resztą 50. Dla 1000 przy 48 jest dwadzieścia pełnych palet po 48 i ostatnia z ilością 40.

Gdy ilość jest mniejsza od zawartości, powstaje jedna paleta z całą ilością: 5 przy 100 daje paletę 5. Gdy ilość równa się zawartości, też powstaje jedna paleta: 48 przy 48 daje paletę 48. W obu przypadkach kolumna z ilością pełnej palety ma wartość NULL, bo pełnych palet nie ma. Dla 7 przy 3 powstają trzy palety: dwie po 3 i ostatnia z ilością 1.

Wynik drugiego zapytania dla sześciu artykułów z przykładu.

Kontrola sumy porównuje wynik z ilością wejściową

Drugi blok liczy z tabeli #rozbicie podsumowanie dla każdego artykułu, w siedmiu kolumnach, od ilości po kontrolę. Kontrola ma wartość „OK”, gdy suma równa się ilości, a w przeciwnym razie „BLAD”. W przykładzie wszystkie sześć wierszy ma „OK”.

SELECT artykul, MAX(ilosc) AS ilosc, MAX(ile_palet) AS palet,
       MAX(CASE WHEN paleta < ile_palet THEN ilosc_na_palecie END) AS pelna,
       MAX(CASE WHEN paleta = ile_palet THEN ilosc_na_palecie END) AS ostatnia,
       SUM(ilosc_na_palecie) AS suma,
       CASE WHEN SUM(ilosc_na_palecie) = MAX(ilosc) THEN 'OK' ELSE 'BLAD' END AS kontrola
FROM #rozbicie GROUP BY artykul ORDER BY artykul;
SELECT CAST(26 AS decimal(19,10)) / 24 * 24 AS iloczyn_dla_literalu,
       CASE WHEN CAST(26 AS decimal(19,10)) / 24 * 24 < 26 THEN 'test q > n*z doliczy palete' ELSE 'brak' END AS wynik_dla_literalu;
SELECT CAST(ilosc AS decimal(19,10)) / na_palecie * na_palecie AS iloczyn_dla_kolumn_int,
       CASE WHEN CAST(ilosc AS decimal(19,10)) / na_palecie * na_palecie < ilosc THEN 'test q > n*z doliczy palete' ELSE 'brak' END AS wynik_dla_kolumn_int
FROM dbo.przyjecie_przyklad WHERE artykul = 'ART-03';
DECLARE @iloraz decimal(19,10) = CAST(26 AS decimal(19,10)) / 24;
SELECT @iloraz AS iloraz_zapisany, @iloraz * 24 AS iloczyn_z_zapisanego_ilorazu,
       CASE WHEN @iloraz * 24 < 26 THEN 'test q > n*z doliczy palete' ELSE 'brak' END AS wynik_dla_zapisanego_ilorazu;

Przykład ilustracyjny. Podsumowanie z kontrolą sumy i trzy zapytania z kontrprzykładem dziesiętnym.

Kontrola działa na wierszach wyniku. Artykuł, który nie ma w wyniku żadnego wiersza, nie pojawi się też w kontroli. Kontrola sumy sprawdza więc to, co rozbicie zapisało, a nie to, czy każdy artykuł z dokumentu został rozbity.

Pozostałe zapytania drugiego bloku pokazują neutralny kontrprzykład dziesiętny. Poniższa tabela zestawia ich wyniki. Wynik ilorazu dziesiętnego zależy więc od jego zaokrąglenia.

Wyniki trzech zapytań z kontrprzykładem dziesiętnym.

Zawartość zero i bardzo duża ilość wymagają osobnych zabezpieczeń

Wzór ma cztery granice, które opisuje poniższa lista. Ograniczenie CHECK odrzuca zawartość równą zero komunikatem Msg 547. Bez niego dzielenie przez zero zatrzymałoby zapytanie.

The INSERT statement conflicted with the CHECK constraint.

• Zawartość zero: ograniczenie CHECK odrzuca wiersz komunikatem Msg 547, na obu poziomach zgodności.
• Sufit tabeli liczbowej: tabela z trzech cyfr daje liczby od 1 do 1000, więc wystarcza dla co najwyżej 1000 palet na artykuł; przy ilości 1500 i zawartości 1 wynik miał 1000 wierszy i sumę 1000, a kontrola zwróciła „BLAD”, czyli kontrola sumy wykrywa przekroczenie sufitu, ale go nie usuwa.
• Ilości ułamkowe: na kolumnach decimal wzór liczy w dół: 10,5 przy 5 daje dwie palety i sumę 10, a kontrola sumy zwraca „BLAD”.
• Wartość bliska granicy int: ilość 2147483647 przy zawartości 2 daje komunikat Msg 8115 o przepełnieniu przy konwersji na typ int.

Przykład uruchomiono w jednorazowej bazie na poziomach zgodności 130 i 160 i dał identyczny wynik; starszej wersji serwera nie testowano. Przedstawiony wzorzec dotyczy ilości całkowitych, a ułamki wymagałyby innego wzoru. Wzorzec, który sprawdza wsad z pliku przed zapisem, opisuje artykuł o walidacji wsadu w tabeli roboczej. Dzienny stan palet w T-SQL opisuje strona o dziennym stanie palet w T-SQL, a podział dostaw sumą narastającą strona o alokacji wydania na dostawy.

Przykład (dane wymyślone). Artykuł A przychodzi w ilości 26 sztuk, a na palecie mieści się 24. Liczba palet to (26 + 24 - 1) / 24, czyli 2 na liczbach całkowitych. Tabela liczbowa daje numery 1 i 2; paleta 1 dostaje 24, a ostatnia 26 - 24 * 1, czyli 2. Kontrola sumy daje 26 = 26 i status „OK”.

Test typu „iloczyn mniejszy od ilości” zawodzi na zaokrąglonym ilorazie dziesiętnym, bo literał 24 dał 25,9999999999992. To samo wyrażenie na kolumnach int daje dokładnie 26.

Pytania do analizy

Przed zastosowaniem wzorca odpowiedz na cztery pytania.

1. Czy w bazie ilości i zawartości palet są zapisane jako liczby całkowite?
2. Jaki największy rozmiar rozbicia na jeden artykuł dopuszcza tabela liczbowa?
3. Gdzie w schemacie ma zostać odrzucona zawartość równa zero: w definicji tabeli czy w zapytaniu?
4. Kto sprawdza wynik kontroli sumy po rozbiciu i co robi z wierszem oznaczonym „BLAD”?