Relleno de nulos con el último, no el valor null de SQL Server en los Códigos Postales

Question 1

Tengo dos tablas PostalCodes (con una columna con los valores de 00-00 a 99-999) y Customers (que tiene, además de todos los datos del cliente, el código postal y el ID de empleado, la cual es servir al cliente).

Así que estos dos simplemente estoy uniendo a través de código postal:

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

y me estoy poniendo esto:

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

Así que como puedes ver, no todos los códigos postales son utilizados en la Customers de la tabla, pero para mi objetivo es necesario que todos los códigos postales asignados a algunos de los empleados para crear algo así como "área de servicio", así que para que quiero rellenar los valores null con el último no nulo valor para obtener algo como esto:

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

Yo estaba tratando de usar LAG() la función, pero no estaba funcionando (o al menos yo no sé cómo usarlo correctamente)

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

He encontrado algunas preguntas similares ya, pero no podían llegar hasta cómo utilizar sus respuestas para mi caso.

Question 2

SQL Server no admite el ignorar nulos opción en LAG (aún), pero usted puede evitar esto mediante la creación de un valor binario de la columna que desea ordenar por, y la columna que desea recuperar y llamadas MAX que no ignorar nulos. Una solución completa de trabajo sería:

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;

Esto podría ayudar a explicar si este se rompe un poco y nos fijamos en los resultados de este:

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	10 PH	0x30302D30303431305048
00-005	NULL	NULL

Desde la concatenación de valor nulo se obtiene un valor nulo, usted consigue solamente un valor, donde el valor no es nulo. Usted puede tomar ventaja de la ordenación binaria (de izquierda a derecha) y obtener el máximo valor de este binario dentro de una ventana de función, que es la parte: MAX(...) OVER(ORDER BY Postal).

Esto elimina todos los valores NULL (desde MAX ignora NULL) de distancia de la primera fila, ya que no hay anterior no nulo valor y le da los datos de la siguiente manera:

Postal	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10 PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

Es un caso de la extracción de la porción de los binarios usted está interesado en (caracteres 7-10) y realizar la conversión a varchar utilizando SUBSTRING y CONVERT

Question 3

Una correlación sub-consulta podría funcionar:

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 no admite el ignorar nulos opción en LAG (aún), pero usted puede evitar esto mediante la creación de un valor binario de la columna que desea ordenar por, y la columna que desea recuperar y llamadas MAX que no ignorar nulos. Una solución completa de trabajo sería:

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;

Esto podría ayudar a explicar si este se rompe un poco y nos fijamos en los resultados de este:

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	10 PH	0x30302D30303431305048
00-005	NULL	NULL

Desde la concatenación de valor nulo se obtiene un valor nulo, usted consigue solamente un valor, donde el valor no es nulo. Usted puede tomar ventaja de la ordenación binaria (de izquierda a derecha) y obtener el máximo valor de este binario dentro de una ventana de función, que es la parte: MAX(...) OVER(ORDER BY Postal).

Esto elimina todos los valores NULL (desde MAX ignora NULL) de distancia de la primera fila, ya que no hay anterior no nulo valor y le da los datos de la siguiente manera:

Postal	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10 PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

Es un caso de la extracción de la porción de los binarios usted está interesado en (caracteres 7-10) y realizar la conversión a varchar utilizando SUBSTRING y CONVERT

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

1

Una correlación sub-consulta podría funcionar:

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

Supongo que esto es similar a lo que propongo arriba, pero la cruz de aplicar, es luego más rápido. Todavía podría ser null si el primero es null. Así que hay que buscar en ambas direcciones y ordenar por el diff para el destino postal

vikjon0

Supongo que dependerá de los índices, pero yo esperaría que la correlación sub-consulta y un CROSS APPLY para producir muy similar planes.

Richard Deeming

Posiblemente, he tenido malas experiencias en el pasado, pero de curso de SQL server ha evolucionado . Hubo un tiempo, cuando la re-escritura de edad código anidado a la cruz se aplican fue un éxito seguro pero, por supuesto, los casos que funcionado bien nunca me hubiera visto.

vikjon0

Agregar AND K.IDemp IS NOT NULL a la subconsulta en orden a la negligencia de valores nulos.

Thorsten Kettner

@ThorstenKettner no está claro a partir de la pregunta, pero supongo que la IDemp columna en el Customers tabla NOT NULL.

Richard Deeming

Mira el resultado de OP de la consulta. El código postal 00-001 resultados en dos filas, una con el IDemp '12PH' uno con IDemp NULL. Así que el NULL no se derivan de la combinación externa, sino que debe existir en la tabla de clientes.

Thorsten Kettner

@ThorstenKettner Buena captura.

Richard Deeming

Relleno de nulos con el último, no el valor null de SQL Server en los Códigos Postales

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría