Le remplissage de zéros avec le dernier pas de valeur null en SQL Server sur les Codes Postaux

Question 1

J'ai deux tables PostalCodes (avec une colonne avec des valeurs de 00-00 à 99-999) et Customers (qui a, à côté de toutes les données du client, le code postal et l'ID de l'employé qui est de servir le client).

Donc, ces deux je suis tout simplement joindre via votre code postal:

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

et j'obtiens ceci:

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

Donc, comme vous pouvez le voir pas tous les codes postaux sont utilisés dans le Customers le tableau, mais pour mon but, j'ai besoin de tous les codes postaux attribué à certains employés pour créé quelque chose comme "zone de service", de sorte à faire que je veux remplir les valeurs null avec le dernier pas de valeur null pour obtenir quelque chose comme ceci:

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

J'ai essayé d'utiliser LAG() la fonction, mais ça ne fonctionnait pas (ou du moins je ne sais pas comment l'utiliser correctement)

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

J'ai trouvé quelques questions similaires déjà, mais ne pouvait pas venir jusqu'comment utiliser leurs réponses à mon cas.

Question 2

SQL Server ne prend pas en charge les ignorer les valeurs null option sur LAG (encore), mais vous pouvez contourner ce problème en créant une valeur binaire à partir de la colonne que vous souhaitez commander par, et la colonne que vous souhaitez récupérer et d'appel MAX qui ignore les valeurs null. Un travail plein de solution serait:

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;

Il pourrait aider à expliquer si c'est cassé vers le bas un peu et nous regardons les résultats de cette:

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

Postal	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

Depuis la concaténation de la valeur null donne une valeur nulle, vous obtenez seulement une valeur où il n'est pas null. Vous pouvez ensuite profiter de tri binaire (de gauche à droite) et d'obtenir le maximum de la valeur de ce binaire à l'intérieur d'une fenêtre de la fonction, qui est de la partie: MAX(...) OVER(ORDER BY Postal).

Cela supprime toutes les valeurs NULL (depuis MAX ignore NULL) en dehors de la première ligne, car il n'y a pas de précédent valeur non nulle et donne des données comme suit:

Postal	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

C'est alors un cas de l'extraction de la partie de la binaire qui vous intéressent (caractères de 7 à 10) et de le convertir en varchar à l'aide de SUBSTRING et CONVERT

Question 3

Une sous-requête corrélée peut fonctionner:

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 ne prend pas en charge les ignorer les valeurs null option sur LAG (encore), mais vous pouvez contourner ce problème en créant une valeur binaire à partir de la colonne que vous souhaitez commander par, et la colonne que vous souhaitez récupérer et d'appel MAX qui ignore les valeurs null. Un travail plein de solution serait:

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;

Il pourrait aider à expliquer si c'est cassé vers le bas un peu et nous regardons les résultats de cette:

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

Postal	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

Depuis la concaténation de la valeur null donne une valeur nulle, vous obtenez seulement une valeur où il n'est pas null. Vous pouvez ensuite profiter de tri binaire (de gauche à droite) et d'obtenir le maximum de la valeur de ce binaire à l'intérieur d'une fenêtre de la fonction, qui est de la partie: MAX(...) OVER(ORDER BY Postal).

Cela supprime toutes les valeurs NULL (depuis MAX ignore NULL) en dehors de la première ligne, car il n'y a pas de précédent valeur non nulle et donne des données comme suit:

Postal	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

C'est alors un cas de l'extraction de la partie de la binaire qui vous intéressent (caractères de 7 à 10) et de le convertir en varchar à l'aide de SUBSTRING et CONVERT

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

1

Une sous-requête corrélée peut fonctionner:

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

J'imagine que c'est similaire à ce que j'ai proposé ci-dessus, mais les appliquer est alors plus rapide. Pourrait encore être null si la première est nulle. Donc faut regarder à la fois la direction et les trier par l'écart à la cible postal

vikjon0

Je suppose que cela dépend de l'index, mais je m'attends à de la sous-requête corrélée et un CROSS APPLY de produire de très des projets similaires.

Richard Deeming

Éventuellement, j'ai eu de mauvaises expériences dans le passé mais bien sûr SQL server a évolué . Il fut un temps où la ré-écriture de vieux code imbriqué à la croix-appliquer un certain succès, mais bien sûr le cas qui a bien fonctionné, je n'aurais jamais vu.

vikjon0

Ajouter AND K.IDemp IS NOT NULL pour la sous-requête dans le but de négliger les valeurs null.

Thorsten Kettner

@ThorstenKettner Il n'est pas clair à partir de la question, mais je suppose que le IDemp colonne de la Customers le tableau est NOT NULL.

Richard Deeming

Regardez le résultat de l'OP, de la requête. Le code postal 00-001 résultats en deux rangées, l'une avec le IDemp '12PH' un avec IDemp NULL. De sorte que la valeur NULL ne peut pas provenir de la jointure externe, mais doit exister dans la table clients.

Thorsten Kettner

@ThorstenKettner Bonne prise.

Richard Deeming

Le remplissage de zéros avec le dernier pas de valeur null en SQL Server sur les Codes Postaux

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie