Náplň nuly s poslední není hodnota null v SQL Serveru na Poštovní Kódy

Question 1

Mám dvě tabulky PostalCodes (s jedním sloupcem s hodnotami z 00-00 na 99-999) a Customers (který má, vedle všechny údaje zákazníka, poštovní směrovací číslo a ID zaměstnance, který je slouží zákazníkovi).

Takže tyto dva jsem jednoduše připojil přes poštovní směrovací číslo:

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

a dostanu toto:

| 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 můžete vidět, ne všechny poštovní kódy jsou používány v Customers tabulka, ale pro můj cíl potřebuju všechny poštovní kódy přiřazeny k některé zaměstnance, aby vytvořil něco jako "oblasti služeb", tak k tomu, že chci naplnit hodnoty null s poslední nenulové hodnoty, aby si něco takového:

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

Snažil jsem se použít LAG() funkce, ale to nefunguje (nebo alespoň nevím, jak jej správně používat)

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

Našla jsem pár podobných dotazů, ale nemohl přijít, jak používat své odpovědi na můj případ.

Question 2

SQL Server nepodporuje ignorovat hodnoty null možnost na LAG (zatím), ale můžete obejít toto tím, že vytvoří binární hodnota ze sloupce, které chcete objednat, a sloupce, které chcete načíst a volání MAX které se ignorují hodnoty null. Kompletní pracovní řešení by bylo:

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 by mohlo pomoci vysvětlit, pokud je to rozbité trochu dolů a podíváme se na výsledky:

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

Poštovní	IDemp	BinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	NULL
00-003	NULL	NULL
00-004	10PH	0x30302D30303431305048
00-005	NULL	NULL

Od zřetězení hodnotu null výnosy null hodnotu, můžete jen získat hodnotu tam, kde to není null. Pak můžete využít binární řazení (zleva doprava) a získat maximální hodnotu z této binární v okně funkce, která je součástí: MAX(...) OVER(ORDER BY Postal).

Tím se odstraní všechny hodnoty NULL (od MAX ignoruje NULL) na rozdíl od první řady, protože tam je žádné předchozí non hodnotu null a poskytuje údaje takto:

Poštovní	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

To je pak jen případ extrakce část binární máte zájem (znaky 7-10) a převod zpět do varchar pomocí SUBSTRING a CONVERT

Question 3

Korelační sub-query by mohlo fungovat:

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 nepodporuje ignorovat hodnoty null možnost na LAG (zatím), ale můžete obejít toto tím, že vytvoří binární hodnota ze sloupce, které chcete objednat, a sloupce, které chcete načíst a volání MAX které se ignorují hodnoty null. Kompletní pracovní řešení by bylo:

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 by mohlo pomoci vysvětlit, pokud je to rozbité trochu dolů a podíváme se na výsledky:

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

Poštovní	IDemp	BinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	NULL
00-003	NULL	NULL
00-004	10PH	0x30302D30303431305048
00-005	NULL	NULL

Od zřetězení hodnotu null výnosy null hodnotu, můžete jen získat hodnotu tam, kde to není null. Pak můžete využít binární řazení (zleva doprava) a získat maximální hodnotu z této binární v okně funkce, která je součástí: MAX(...) OVER(ORDER BY Postal).

Tím se odstraní všechny hodnoty NULL (od MAX ignoruje NULL) na rozdíl od první řady, protože tam je žádné předchozí non hodnotu null a poskytuje údaje takto:

Poštovní	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

To je pak jen případ extrakce část binární máte zájem (znaky 7-10) a převod zpět do varchar pomocí SUBSTRING a CONVERT

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

1

Korelační sub-query by mohlo fungovat:

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

Myslím, že je to podobné tomu, co navrhuji výše, ale kříž aplikovat je pak rychlejší. Stále může být null, pokud první z nich je null. Tak by měl vypadat v obou směrech a seřadit podle diff na cílový poštovní

vikjon0

Myslím, že to bude záviset na indexy, ale předpokládám, že korelovaných sub-query a CROSS APPLY vyrábět velmi podobné plány.

Richard Deeming

Možná jsem měl špatné zkušenosti v minulosti, ale samozřejmě SQL server se vyvinul . Tam byl čas, když re-psaní staré vnořené kód cross apply byl určitě úspěch, ale samozřejmě případy, které fungovalo v pohodě, já bych nikdy neviděl.

vikjon0

Přidat AND K.IDemp IS NOT NULL na poddotaz aby zanedbávat hodnoty null.

Thorsten Kettner

@ThorstenKettner To není jasné z otázky, ale já bych předpokládat, že IDemp sloupec v Customers stůl je NOT NULL.

Richard Deeming

Podívejte se na výsledek OPERACE dotazu. Psč 00-001 výsledky ve dvou řadách, jeden s IDemp '12PH' s IDemp NULL. Takže NULL nemůže pramenit z vnější spojení, ale musí existovat v tabulce zákazníci.

Thorsten Kettner

@ThorstenKettner Dobrý úlovek.

Richard Deeming

Náplň nuly s poslední není hodnota null v SQL Serveru na Poštovní Kódy

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii