Preenchimento de nulos com última não valor null no SQL Server Códigos Postais

Question 1

Eu tenho duas tabelas PostalCodes (com uma coluna com valores a partir de 00-00 para 99-999) e Customers (que, além de todos os dados do cliente, um código postal e a IDENTIFICAÇÃO do empregado que estiver servindo o cliente).

Assim, estes dois eu sou simplesmente adesão através do código postal:

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

e eu estou ficando este:

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

Então, como você pode ver, nem todos os códigos postais são utilizados no Customers tabela, mas para o meu objetivo, preciso de todos os códigos postais atribuído a algum funcionário criado algo como "área de serviço", de modo a fazer que eu precisava para preencher valores nulos com o último valor não nulo para obter algo como isto:

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

Eu estava tentando usar LAG() função, mas ele não estava trabalhando (ou pelo menos eu não sei como usá-lo corretamente)

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

Achei algumas perguntas semelhantes já, mas não poderia vir até como usar as suas respostas para o meu caso.

Question 2

O SQL Server não oferece suporte a ignorar nulos opção LAG (ainda), mas você pode contornar isso criando um valor binário da coluna que você deseja a ordem, e a coluna que você deseja recuperar e chamando MAX que não ignoram valores nulos. Uma completa solução de trabalho seria:

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;

Isso pode ajudar a explicar se isso é quebrado para baixo um pouco e olharmos para os resultados do presente:

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

Desde a concatenação de valor nulo produz um valor nulo, você só obter um valor onde ele não é nulo. Em seguida, você pode tirar vantagem de binário de classificação (da esquerda para a direita) e obter o máximo valor do binário dentro de uma janela de função, que é a parte: MAX(...) OVER(ORDER BY Postal).

Isso remove todos os valores NULOS (desde MAX ignora NULLpara além da primeira linha, uma vez que não há anteriores não nulo valor e confere os dados da seguinte forma:

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

Ele é, então, apenas um caso de extrair a parte binária que você está interessado (caracteres 7-10), para converter de volta para varchar usando SUBSTRING e CONVERT

Question 3

Um correlacionada sub-consulta pode 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

O SQL Server não oferece suporte a ignorar nulos opção LAG (ainda), mas você pode contornar isso criando um valor binário da coluna que você deseja a ordem, e a coluna que você deseja recuperar e chamando MAX que não ignoram valores nulos. Uma completa solução de trabalho seria:

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;

Isso pode ajudar a explicar se isso é quebrado para baixo um pouco e olharmos para os resultados do presente:

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

Desde a concatenação de valor nulo produz um valor nulo, você só obter um valor onde ele não é nulo. Em seguida, você pode tirar vantagem de binário de classificação (da esquerda para a direita) e obter o máximo valor do binário dentro de uma janela de função, que é a parte: MAX(...) OVER(ORDER BY Postal).

Isso remove todos os valores NULOS (desde MAX ignora NULLpara além da primeira linha, uma vez que não há anteriores não nulo valor e confere os dados da seguinte forma:

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

Ele é, então, apenas um caso de extrair a parte binária que você está interessado (caracteres 7-10), para converter de volta para varchar usando SUBSTRING e CONVERT

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

1

Um correlacionada sub-consulta pode 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

Eu acho que isso é semelhante ao que proponho acima, mas entre aplicar, em seguida, é mais rápido. Ainda pode ser null se o primeiro é nulo. Então deve olhar em ambas as direções e classificar pelo 'diff' para o alvo postal

vikjon0

Eu acho que dependeria de os índices, mas eu esperaria a relação de sub-consulta e uma CROSS APPLY para produzir muito semelhante planos.

Richard Deeming

Possivelmente, eu tive experiências ruins no passado, mas é claro que o SQL server tem evoluído . Houve um momento de re-escrita de idade aninhadas código para cross apply foi um certo sucesso, mas é claro que os casos que trabalhou muito bem que eu nunca teria visto.

vikjon0

Adicionar AND K.IDemp IS NOT NULL para a subconsulta, a fim de negligência nulos.

Thorsten Kettner

@ThorstenKettner não está claro a partir da pergunta, mas eu diria que a IDemp coluna a Customers a tabela é NOT NULL.

Richard Deeming

Veja o resultado da OP consulta. O código postal 00-001 resultados em duas linhas, uma com o IDemp '12PH' um com IDemp NULO. Assim, o NULO não pode provir de associação externa, mas deve existir na tabela de clientes.

Thorsten Kettner

@ThorstenKettner Boa pescaria.

Richard Deeming

Preenchimento de nulos com última não valor null no SQL Server Códigos Postais

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria