Riempimento di valori null con ultimo non valore null in SQL Server su Codici di avviamento Postale

Question 1

Ho due tabelle PostalCodes (con una colonna con i valori 00-00 per 99-999) e Customers (che ha, oltre a tutti i dati del cliente, il codice postale e l'ID del dipendente che è al servizio del cliente).

Quindi questi due sono semplicemente entrare in via codice postale:

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

e sto ottenendo questo:

| 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  |
| ...    | ...   |

Quindi, come potete vedere non tutti i codici di avviamento postale sono utilizzati in Customers tabella, ma per il mio scopo ho bisogno di tutti i codici di avviamento postale assegnati ad alcuni dipendenti per creare qualcosa come "area di servizio", in modo da fare che voglio riempire di valori null con ultimo valore null non per ottenere qualcosa di simile a questo:

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

Stavo cercando di utilizzare LAG() la funzione, ma non funzionava (o almeno io non so come usarlo correttamente)

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

Ho trovato alcune domande simili già, ma non poteva venire su come utilizzare le loro risposte al mio caso.

Question 2

SQL Server non supporta la ignora null opzione LAG (ancora), ma si può ottenere tutto questo con la creazione di un valore binario dalla colonna che si desidera ordinare, e la colonna che si desidera recuperare e chiamata MAX che non ignora i valori null. Una soluzione potrebbe essere:

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;

Potrebbe aiutare a spiegare se questo è rotto giù un po ' e guardiamo i risultati di questo:

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

Postale	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

Dal momento che la concatenazione di valore null restituisce un valore nullo, si ottiene solo un valore per cui non è null. Quindi, si può prendere vantaggio di binari di smistamento (da sinistra a destra) e di ottenere il massimo valore di questo binario all'interno di una finestra di funzione, che è parte: MAX(...) OVER(ORDER BY Postal).

Questo rimuove tutti i valori NULL (dal MAX ignora NULL) parte la prima riga, poiché non vi è alcun precedente di non valore null e fornisce dati come segue:

Postale	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

Quindi è solo un caso di estrarre la porzione di binario che ti interessano (caratteri 7-10) e di conversione di tipo varchar utilizzando SUBSTRING e CONVERT

Question 3

Un correlato sub-query potrebbe funzionare:

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 non supporta la ignora null opzione LAG (ancora), ma si può ottenere tutto questo con la creazione di un valore binario dalla colonna che si desidera ordinare, e la colonna che si desidera recuperare e chiamata MAX che non ignora i valori null. Una soluzione potrebbe essere:

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;

Potrebbe aiutare a spiegare se questo è rotto giù un po ' e guardiamo i risultati di questo:

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

Postale	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

Dal momento che la concatenazione di valore null restituisce un valore nullo, si ottiene solo un valore per cui non è null. Quindi, si può prendere vantaggio di binari di smistamento (da sinistra a destra) e di ottenere il massimo valore di questo binario all'interno di una finestra di funzione, che è parte: MAX(...) OVER(ORDER BY Postal).

Questo rimuove tutti i valori NULL (dal MAX ignora NULL) parte la prima riga, poiché non vi è alcun precedente di non valore null e fornisce dati come segue:

Postale	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

Quindi è solo un caso di estrarre la porzione di binario che ti interessano (caratteri 7-10) e di conversione di tipo varchar utilizzando SUBSTRING e CONVERT

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

1

Un correlato sub-query potrebbe funzionare:

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

Credo che questo è simile a quella che vi propongo qui sopra, ma il cross da applicare è quindi più veloce. Potrebbe ancora essere null se il primo è null. Così si dovrebbe guardare in entrambe le direzioni e l'ordinamento del diff a destinazione postale

vikjon0

Credo che sarebbe dipenderà da indici, ma mi sarei aspettato correlati sub-query e un CROSS APPLY per produrre molto simili progetti.

Richard Deeming

Forse, ho avuto brutte esperienze in passato, ma, naturalmente, SQL server si è evoluto . C'erano un tempo, quando ri-scrivere il vecchio nidificati codice di croce, è stato un sicuro successo, ma, naturalmente, i casi che ha funzionato bene che non avrei mai visto.

vikjon0

Aggiungere AND K.IDemp IS NOT NULL per la subquery in ordine a trascurare i valori null.

Thorsten Kettner

@ThorstenKettner non È chiara la domanda, ma presumo che il IDemp colonna in Customers tabella NOT NULL.

Richard Deeming

Guardate il risultato di OP query. Il codice di avviamento postale 00-001 risultati in due file, uno con il IDemp '12PH' uno con IDemp NULL. Così il NULL non può derivare da outer join, ma deve esistere nella tabella clienti.

Thorsten Kettner

@ThorstenKettner Buona pesca.

Richard Deeming

Riempimento di valori null con ultimo non valore null in SQL Server su Codici di avviamento Postale

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria