Заполнение нулей последним значением not null в SQL Server для почтовых индексов

Question 1

У меня есть два стола PostalCodes (с одним столбцом со значениями от 00-00 до 99-999) и Customers (который имеет, помимо всех данных клиента, почтовый индекс и идентификатор сотрудника, который обслуживает клиента).

Так что к этим двоим я просто присоединюсь с помощью почтового кода:

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

и я получаю это:

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

Так что, как вы можете видеть, не все почтовые индексы используются в Customers таблица, но для моей цели мне нужны все почтовые индексы, присвоенные какому-либо сотруднику, чтобы создать что-то вроде "область обслуживания", поэтому для этого я хочу заполнить пустые значения последним не пустым значением, чтобы получить что-то подобное:

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

Я пытался использовать LAG() функция, но она не работала (или, по крайней мере, я не знаю, как ее правильно использовать)

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

Я уже нашел несколько подобных вопросов, но не смог придумать, как использовать их ответы в моем случае.

Question 2

SQL Server не поддерживает параметр игнорировать значения null в LAG (пока), но вы можете обойти это, создав двоичное значение из столбца, по которому вы хотите упорядочить, и столбца, который вы хотите получить, и вызвав MAX что действительно игнорирует нули. Полное рабочее решение было бы:

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;

Это может помочь объяснить, если это немного разбить, и мы посмотрим на результаты этого:

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

Почтовый	ИДемп	Двоичное значение
00-000	нулевой	нулевой
00-001	12 ЧАСОВ в час	0x30302D30303131325048
00-002	нулевой	нулевой
00-003	нулевой	нулевой
00-004	10 ЧАСОВ в час	0x30302D30303431305048
00-005	нулевой	нулевой

Поскольку объединение нулевого значения приводит к нулевому значению, вы получаете значение только там, где оно не равно нулю. Затем вы можете воспользоваться двоичной сортировкой (слева направо) и получить максимальное значение этого двоичного файла в оконной функции, которая является частью: MAX(...) OVER(ORDER BY Postal).

Это удаляет все нулевые значения (так как MAX игнорирует NULL) кроме первой строки, так как нет предыдущего ненулевого значения и дает следующие данные:

Почтовый	ИДемп	Значение MaxBinaryValue
00-000	нулевой	нулевой
00-001	12 ЧАСОВ в час	0x30302D30303131325048
00-002	нулевой	0x30302D30303131325048
00-003	нулевой	0x30302D30303131325048
00-004	10 ЧАСОВ в час	0x30302D30303431305048
00-005	нулевой	0x30302D30303431305048

Тогда это просто случай извлечения части интересующего вас двоичного файла (символов 7-10) и преобразования обратно в varchar с помощью SUBSTRING и CONVERT

Question 3

Коррелированный подзапрос может сработать:

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 не поддерживает параметр игнорировать значения null в LAG (пока), но вы можете обойти это, создав двоичное значение из столбца, по которому вы хотите упорядочить, и столбца, который вы хотите получить, и вызвав MAX что действительно игнорирует нули. Полное рабочее решение было бы:

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;

Это может помочь объяснить, если это немного разбить, и мы посмотрим на результаты этого:

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

Почтовый	ИДемп	Двоичное значение
00-000	нулевой	нулевой
00-001	12 ЧАСОВ в час	0x30302D30303131325048
00-002	нулевой	нулевой
00-003	нулевой	нулевой
00-004	10 ЧАСОВ в час	0x30302D30303431305048
00-005	нулевой	нулевой

Поскольку объединение нулевого значения приводит к нулевому значению, вы получаете значение только там, где оно не равно нулю. Затем вы можете воспользоваться двоичной сортировкой (слева направо) и получить максимальное значение этого двоичного файла в оконной функции, которая является частью: MAX(...) OVER(ORDER BY Postal).

Это удаляет все нулевые значения (так как MAX игнорирует NULL) кроме первой строки, так как нет предыдущего ненулевого значения и дает следующие данные:

Почтовый	ИДемп	Значение MaxBinaryValue
00-000	нулевой	нулевой
00-001	12 ЧАСОВ в час	0x30302D30303131325048
00-002	нулевой	0x30302D30303131325048
00-003	нулевой	0x30302D30303131325048
00-004	10 ЧАСОВ в час	0x30302D30303431305048
00-005	нулевой	0x30302D30303431305048

Тогда это просто случай извлечения части интересующего вас двоичного файла (символов 7-10) и преобразования обратно в varchar с помощью SUBSTRING и CONVERT

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

1

Коррелированный подзапрос может сработать:

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

Я предполагаю, что это похоже на то, что я предлагаю выше, но перекрестное применение происходит быстрее. Все равно может быть равно нулю, если первое значение равно нулю. Поэтому следует смотреть в обоих направлениях и сортировать по отличию от целевого почтового

vikjon0

Я предполагаю, что это будет зависеть от индексов, но я бы ожидал, что коррелированный подзапрос и CROSS APPLY чтобы создать очень похожие планы.

Richard Deeming

Возможно, у меня был плохой опыт в прошлом, но, конечно, SQL server эволюционировал . Было время, когда переписывание старого вложенного кода для перекрестного применения было несомненным успехом, но, конечно, случаев, которые работали нормально, я бы никогда не увидел.

vikjon0

Добавить AND K.IDemp IS NOT NULL к подзапросу, чтобы пренебречь нулями.

Thorsten Kettner

@Торстенкеттнер, из вопроса неясно, но я бы предположил, что IDemp колонка в Customers таблица представляет собой NOT NULL.

Richard Deeming

Посмотрите на результат запроса OP. Почтовый индекс 00-001 приводит к двум строкам, одна с идентификатором "12PH", другая с идентификатором "NULL". Таким образом, значение NULL не может быть получено из внешнего соединения, но должно существовать в таблице customers.

Thorsten Kettner

@Торстенкеттнер Хороший улов.

Richard Deeming

Заполнение нулей последним значением not null в SQL Server для почтовых индексов

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории