Wypełnienie zer ostatnim wartością not null w SQL Server dla kodów pocztowych

Question 1

Mam dwa biurka PostalCodes (z jedną kolumnę z wartościami od 00-00 do 99-999) i Customers (który ma, oprócz wszystkich danych klienta, kod pocztowy i identyfikator pracownika, który obsługuje klienta).

Więc co do tych dwóch po prostu dołączę za pomocą skrzynki kodu:

SELECT DISTINCT
    KP.postal,
    K.IDemp
FROM
    PostalCodes KP 
LEFT JOIN
    [Customers] K ON K.postal = KP.postal

i dostaję to:

| postal | IDemp |
+--------+-------+
| 00-000 | NULL  |
| 00-001 | NULL  |
| 00-001 | 12PH  |
| 00-002 | NULL  |
| 00-003 | NULL  |
| 00-004 | NULL  |
| 00-004 | 10PH  |
| 00-005 | NULL  |
| ...    | ...   |

Tak że, jak widać, nie wszystkie kody pocztowe są używane w Customers tabela, ale dla mojego celu potrzebne mi są wszystkie kody pocztowe przypisane do żadnego pracownika, aby stworzyć coś takiego jak "zakres usług", więc dla tego chcę wypełnić puste wartości ostatnim nie puste wartości, aby uzyskać coś takiego:

| postal | IDemp |
+--------+-------+
| 00-000 | NULL  |
| 00-001 | 12PH  |
| 00-002 | 12PH  |
| 00-003 | 12PH  |
| 00-004 | 10PH  |
| 00-005 | 10PH  |
| ...    | ...   |

Próbowałem użyć LAG() funkcja, ale ona nie pracowała (lub, przynajmniej, nie wiem, jak go poprawnie używać)

LAG(K.IDemp) OVER (ORDER BY KP.postal)

Już znalazłem kilka podobnych tematów, ale nie mógł wymyślić, jak korzystać z ich odpowiedzi w moim przypadku.

Question 2

SQL Server nie obsługuje opcji ignorują wartości null w LAG (aż), ale można to obejść, tworząc wartość binarna z kolumny, według której chcesz posortować, i kolumny, którą chcesz uzyskać, i wywołując MAX co tak naprawdę ignoruje zera. Pełna robocze rozwiązanie byłoby:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE IF NOT EXISTS #T (Postal VARCHAR(6) NOT NULL, IDemp VARCHAR(4) NULL);
INSERT #T (Postal, IDemp)
VALUES
    ('00-000', NULL),
    ('00-001', '12PH'),
    ('00-002', NULL),
    ('00-003', NULL),
    ('00-004', '10PH'),
    ('00-005', NULL);


SELECT  *,
        LastNonNull = CONVERT(VARCHAR(6), 
                            SUBSTRING(
                                MAX(CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)) 
                                    OVER(ORDER BY Postal), 7,4))
FROM    #T;

To może pomóc wyjaśnić, jeśli jest to trochę rozbić, i spojrzymy na wyniki tego:

SELECT  *,
        BinaryValue = CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)
FROM    #T

Kod	ИДемп	Wartość binarna
00-000	zero	zero
00-001	12 GODZIN na godzinę	0x30302D30303131325048
00-002	zero	zero
00-003	zero	zero
00-004	10 GODZIN na godzinę	0x30302D30303431305048
00-005	zero	zero

Ponieważ łączenie wartości zero, prowadzi do tego samego obwodu wartości, pojawi się wartość tylko tam, gdzie ono nie jest równa zero. Następnie możesz korzystać binarne sortowanie od lewej do prawej) i uzyskać maksymalną wartość tego pliku binarnego w okiennej funkcji, która jest częścią: MAX(...) OVER(ORDER BY Postal).

To usuwa wszystkie wartości zerowe (tak jak MAX ignoruje NULL) oprócz pierwszego wiersza, tak jak nie ma poprzedniego niezerowego wartości i daje następujące dane:

Kod	ИДемп	Wartość MaxBinaryValue
00-000	zero	zero
00-001	12 GODZIN na godzinę	0x30302D30303131325048
00-002	zero	0x30302D30303131325048
00-003	zero	0x30302D30303131325048
00-004	10 GODZIN na godzinę	0x30302D30303431305048
00-005	zero	0x30302D30303431305048

Wtedy to po prostu wypadek wyjęcia części interesującego cię pliku binarnego (znaków 7-10) i konwersji z powrotem w varchar z pomocą SUBSTRING i CONVERT

Question 3

Коррелированный podzapytanie może zadziałać:

SELECT DISTINCT
    KP.postal,
    (SELECT TOP 1 K.IDemp 
     FROM [Customers] K
     WHERE K.postal <= KP.postal
     AND K.IDemp Is Not Null
     ORDER BY K.postal DESC) As IDemp
FROM
    PostalCodes KP

GarethD · Answer 1 · 2021-11-23T13:48:50

SQL Server nie obsługuje opcji ignorują wartości null w LAG (aż), ale można to obejść, tworząc wartość binarna z kolumny, według której chcesz posortować, i kolumny, którą chcesz uzyskać, i wywołując MAX co tak naprawdę ignoruje zera. Pełna robocze rozwiązanie byłoby:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE IF NOT EXISTS #T (Postal VARCHAR(6) NOT NULL, IDemp VARCHAR(4) NULL);
INSERT #T (Postal, IDemp)
VALUES
    ('00-000', NULL),
    ('00-001', '12PH'),
    ('00-002', NULL),
    ('00-003', NULL),
    ('00-004', '10PH'),
    ('00-005', NULL);


SELECT  *,
        LastNonNull = CONVERT(VARCHAR(6), 
                            SUBSTRING(
                                MAX(CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)) 
                                    OVER(ORDER BY Postal), 7,4))
FROM    #T;

To może pomóc wyjaśnić, jeśli jest to trochę rozbić, i spojrzymy na wyniki tego:

SELECT  *,
        BinaryValue = CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)
FROM    #T

Kod	ИДемп	Wartość binarna
00-000	zero	zero
00-001	12 GODZIN na godzinę	0x30302D30303131325048
00-002	zero	zero
00-003	zero	zero
00-004	10 GODZIN na godzinę	0x30302D30303431305048
00-005	zero	zero

Ponieważ łączenie wartości zero, prowadzi do tego samego obwodu wartości, pojawi się wartość tylko tam, gdzie ono nie jest równa zero. Następnie możesz korzystać binarne sortowanie od lewej do prawej) i uzyskać maksymalną wartość tego pliku binarnego w okiennej funkcji, która jest częścią: MAX(...) OVER(ORDER BY Postal).

To usuwa wszystkie wartości zerowe (tak jak MAX ignoruje NULL) oprócz pierwszego wiersza, tak jak nie ma poprzedniego niezerowego wartości i daje następujące dane:

Kod	ИДемп	Wartość MaxBinaryValue
00-000	zero	zero
00-001	12 GODZIN na godzinę	0x30302D30303131325048
00-002	zero	0x30302D30303131325048
00-003	zero	0x30302D30303131325048
00-004	10 GODZIN na godzinę	0x30302D30303431305048
00-005	zero	0x30302D30303431305048

Wtedy to po prostu wypadek wyjęcia części interesującego cię pliku binarnego (znaków 7-10) i konwersji z powrotem w varchar z pomocą SUBSTRING i CONVERT

Richard Deeming · Answer 2 · 2021-11-23T13:38:05

1

Коррелированный podzapytanie może zadziałać:

SELECT DISTINCT
    KP.postal,
    (SELECT TOP 1 K.IDemp 
     FROM [Customers] K
     WHERE K.postal <= KP.postal
     AND K.IDemp Is Not Null
     ORDER BY K.postal DESC) As IDemp
FROM
    PostalCodes KP

Richard Deeming

2021-11-23 13:38:05

Myślę, że to wygląda na to, że proponuję wyżej, ale odwoływanie się do stosowania dzieje się szybciej. I tak może być równa zero, jeśli pierwsza wartość jest równa zero. W związku z tym należy patrzeć w obu kierunkach i sortować na różnice od docelowej skrzynki

vikjon0

Myślę, że to zależy od indeksów, ale spodziewam się, że коррелированный podzapytanie i CROSS APPLY aby stworzyć bardzo podobne plany.

Richard Deeming

Być może miałem złe doświadczenia w przeszłości, ale, oczywiście, SQL server rozwijał . Był czas, kiedy przepisywanie starego zagnieżdżonego kodu dla efektu stosowania było niewątpliwym sukcesem, ale, oczywiście, przypadków, które pracowały normalnie, nigdy bym nie zobaczył.

vikjon0

Dodać AND K.IDemp IS NOT NULL do подзапросу, aby pominąć zerami.

Thorsten Kettner

@Торстенкеттнер, z wydania nie wiadomo, ale przypuszczam, że IDemp kolumna w Customers tabela przedstawia NOT NULL.

Richard Deeming

Spójrz na wynik zapytania OP. Kod pocztowy 00-001 prowadzi do dwóch wierszy, jeden z identyfikatorem "12PH", druga z identyfikatorem "NULL". W ten sposób wartość NULL nie może być uzyskana z połączenia zewnętrznego, ale musi istnieć w tabeli customers.

Thorsten Kettner

@Торстенкеттнер Dobry połów.

Richard Deeming

Wypełnienie zer ostatnim wartością not null w SQL Server dla kodów pocztowych

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii