Запрос T-SQL обновляет нулевые значения

У меня очень специфическая проблема в T-SQL.

Если я смогу решить этот примерный случай, который я даю вам, я думаю, что смогу решить свой первоначальный случай.

Имея эти данные в таблице:

DECLARE @Test TABLE
(
    Value INT
    ,Date DATETIME2(7)
);

INSERT INTO @Test
VALUES
(NULL, '2011-01-01 10:00'),
(NULL, '2011-01-01 11:00'),
(2, '2011-01-01 12:00'),
(NULL, '2011-01-01 13:00'),
(3, '2011-01-01 14:00'),
(NULL, '2011-01-01 15:00'),
(NULL, '2011-01-01 16:00'),
(4, '2011-01-01 17:00'),
(NULL, '2011-01-01 18:00'),
(5, '2011-01-01 19:00'),
(6, '2011-01-01 20:00')

Мне нужно выбрать этот вывод:

Value   Date
2       2011-01-01 10:00
2       2011-01-01 11:00
2       2011-01-01 12:00
2       2011-01-01 13:00
3       2011-01-01 14:00
3       2011-01-01 15:00
3       2011-01-01 16:00
4       2011-01-01 17:00
4       2011-01-01 18:00
5       2011-01-01 19:00
6       2011-01-01 20:00

Чтобы дать некоторое объяснение. Если где-то значение равно NULL, мне нужно обновить значение предыдущего часа. Если в строке есть несколько нулевых значений, ближайший предыдущий час с ненулевым значением распространяется и заполняет все эти нулевые значения. Кроме того, если первый час дня равен нулю, то самый ранний час дня с ненулевым значением распространяется вниз, например 2 в этом случае. В вашем случае вы можете предположить, что хотя бы одно значение не равно нулю.

Моя цель - решить эту проблему с помощью общих табличных выражений или чего-то подобного. Я думаю, что с помощью курсора у меня будет решение в короткие сроки, если я попытаюсь, но мои попытки с CTE и рекурсивными CTE пока не увенчались успехом.


sql-server tsql common-table-expression
person John    schedule 18.05.2012    source источник
comment
что ты пробовал? Полезно опубликовать то, что вы сделали до сих пор - вы можете быть близки и вам нужно всего несколько предложений, чтобы исправить ваш существующий запрос.   -  person Taryn    schedule 18.05.2012
comment
Поскольку я не знаю, сколько нулевых значений находится в строке, одного соединения в предыдущий час недостаточно. Там я подумал, может быть, я мог бы решить это каким-то образом с помощью рекурсии. Я использовал только рекурсию CTE с классической иерархией, но моя идея состоит в том, что если бы я мог обновлять одно нулевое значение каждый раз в рекурсии, возможно, я мог бы заполнить их все. На самом деле, когда я пробовал эту идею, я потерял ее довольно рано. Так что я не верю, что это сильно поможет.   -  person John    schedule 18.05.2012
comment
В вашем примере Values не уменьшаются по мере увеличения Date. Это совпадение или так будет всегда?   -  person AakashM    schedule 18.05.2012
comment
+1 за фактическое включение начальной точки рабочей таблицы и кода вставки данных   -  person KM.    schedule 18.05.2012

Ответы (2)


arrow_upward
3
arrow_downward

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

SELECT  B.Value,
        A.[Date]
FROM @Test A
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE [Date] <= A.[Date] AND Value IS NOT NULL
             ORDER BY [Date] DESC) B

Но в вашем случае, я думаю, вам нужно это:

SELECT  ISNULL(B.Value,C.Value) Value,
        A.[Date]
FROM @Test A
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE [Date] <= A.[Date] AND Value IS NOT NULL
             ORDER BY [Date] DESC) B
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE Value IS NOT NULL
             ORDER BY [Date]) C
person Lamak    schedule 18.05.2012
comment
Я был немного быстр, когда принял другой ответ на самом деле. Он работал только с моими образцами данных, которые увеличивались каждый час. Это правильный ответ, и он дает мне правильные результаты. Но у меня ужасные проблемы с производительностью с моими реальными данными. Любые предложения, что вы могли бы сделать, чтобы немного увеличить производительность? - person John; 25.05.2012
comment
@John - Да, у него определенно будет плохая производительность, так как вы просматриваете таблицу test как минимум дважды. Вы знаете, второй OUTER APPLY существует только тогда, когда первые значения равны NULL, может быть, вы могли бы избежать этого лучше. И я предполагаю, что у вас есть хотя бы индекс столбца Date. - person Lamak; 25.05.2012

arrow_upward
2
arrow_downward

попробуй это:

select 
    t.value, t.date
      ,COALESCE(t.value
               ,(select MAX(tt.value) from @Test tt WHERE t.Date>tt.Date)
               ,(SELECT MIN(ttt.Value) FROM @Test ttt Where ttt.Date IS NOT NULL)
               ) AS UseValue
    from @Test   t

ВЫХОД:

value       date                    UseValue
----------- ----------------------- -----------
NULL        2011-01-01 10:00:00.000 2
NULL        2011-01-01 11:00:00.000 2
2           2011-01-01 12:00:00.000 2
NULL        2011-01-01 13:00:00.000 2
3           2011-01-01 14:00:00.000 3
NULL        2011-01-01 15:00:00.000 3
NULL        2011-01-01 16:00:00.000 3
4           2011-01-01 17:00:00.000 4
NULL        2011-01-01 18:00:00.000 4
5           2011-01-01 19:00:00.000 5
6           2011-01-01 20:00:00.000 6
person KM.    schedule 18.05.2012
comment
Сначала я принял этот ответ, но на самом деле я выбрал другое решение, приведенное выше, так как думал, что оба работают. Но теперь, когда у меня возникли проблемы с производительностью с другим решением, я начал пробовать это и обнаружил, что оно не работает. Это работает только в этом примере, потому что значение увеличивается каждый час. Попробуйте немного перетасовать значения и посмотреть, что произойдет :) - person John; 25.05.2012
comment
@Джон, если бы у тебя был правильный ПК! - person KM.; 29.05.2012