SQL QUERY заменяет значение NULL в строке значением из предыдущего известного значения

Question

SQL QUERY заменяет значение NULL в строке значением из предыдущего известного значения

У меня 2 колонки

date   number       
----   ------
1      3           
2      NULL        
3      5           
4      NULL        
5      NULL        
6      2          
.......

Мне нужно заменить значения NULL новыми значениями, которые принимают значение из последнего известного значения в предыдущей дате в столбце даты, например: date=2 число = 3, дата 4 и 5 число = 5 и 5. Появляются значения NULL случайным образом.

37

sql null

Источник

28 авг '09 в 04:44

12 ответов

Другие вопросы по тегам sql null

user144424 28 авг '09 в 05:02 2009-08-28 05:02 · Answer 1 · 2009-08-28 05:02

Если вы используете Sql Server, это должно работать

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2


SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

user20860 28 авг '09 в 06:01 2009-08-28 06:01 · Answer 2 · 2009-08-28 06:01

Вот решение MySQL:

UPDATE mytable
SET number = (@n := COALESCE(number, @n))
ORDER BY date;

Это сжато, но не обязательно будет работать в других марках СУБД. Для других брендов может существовать конкретное решение, которое является более актуальным. Вот почему важно сообщить нам марку, которую вы используете.

Как прокомментировал @Pax, приятно быть независимым от производителя, но в противном случае также полезно использовать выбранную марку базы данных в полной мере.

Объяснение вышеуказанного запроса:

@n пользовательская переменная MySQL Он начинается с NULL, и ему присваивается значение в каждой строке, поскольку UPDATE проходит через строки. куда number ненулевой, @n присваивается значение number, куда number NULL, COALESCE() по умолчанию предыдущее значение @n, В любом случае это становится новым значением number столбец и ОБНОВЛЕНИЕ переходит к следующей строке. @n переменная сохраняет свое значение от строки к строке, поэтому последующие строки получают значения, полученные из предыдущей строки. Порядок UPDATE предсказуем, потому что MySQL использует ORDER BY с UPDATE (это не стандартный SQL).

user468437 06 окт '10 в 20:27 2010-10-06 20:27 · Answer 3 · 2010-10-06 20:27

Лучшее решение - это предложение Билла Карвина. Недавно мне пришлось решить эту проблему в относительно большом наборе результатов (1000 строк с 12 столбцами, каждый из которых нуждается в этом типе "покажите мне последнее ненулевое значение, если это значение равно нулю в текущей строке") и используя метод обновления с верхним 1 выберите предыдущее известное значение (или подзапрос с топ-1), который работал очень медленно.

Я использую SQL 2005 и синтаксис для замены переменной немного отличается от mysql:

UPDATE mytable 
SET 
    @n = COALESCE(number, @n),
    number = COALESCE(number, @n)
ORDER BY date

Первый оператор set обновляет значение переменной @n до значения текущей строки 'number', если 'number' не равно нулю (COALESCE возвращает первый ненулевой аргумент, который вы передаете ему) Второй оператор set обновляет фактический значение столбца для "число" для себя (если не ноль) или переменной @n (которая всегда содержит последнее значение, отличное от NULL).

Прелесть этого подхода в том, что нет дополнительных затрат ресурсов на сканирование временной таблицы снова и снова... Обновление @n в строке обеспечивает отслеживание последнего ненулевого значения.

У меня недостаточно представителей, чтобы проголосовать за него, но кто-то должен. Это самый элегантный и лучший исполнитель.

user146325 28 авг '09 в 08:00 2009-08-28 08:00 · Answer 4 · 2009-08-28 08:00

Вот решение Oracle (10g или выше). Использует аналитическую функцию last_value() с ignore nulls опция, которая заменяет последнее ненулевое значение для столбца.

SQL> select *
  2  from mytable
  3  order by id
  4  /

        ID    SOMECOL
---------- ----------
         1          3
         2
         3          5
         4
         5
         6          2

6 rows selected.

SQL> select id
  2         , last_value(somecol ignore nulls) over (order by id) somecol
  3  from mytable
  4  /

        ID    SOMECOL
---------- ----------
         1          3
         2          3
         3          5
         4          5
         5          5
         6          2

6 rows selected.

SQL>

user394042 18 май '12 в 11:14 2012-05-18 11:14 · Answer 5 · 2012-05-18 11:14

Следующий скрипт решает эту проблему и использует только обычный ANSI SQL. Я тестировал это решение на SQL2008, SQLite3 и Oracle11g.

CREATE TABLE test(mysequence INT, mynumber INT);

INSERT INTO test VALUES(1, 3);
INSERT INTO test VALUES(2, NULL);
INSERT INTO test VALUES(3, 5);
INSERT INTO test VALUES(4, NULL);
INSERT INTO test VALUES(5, NULL);
INSERT INTO test VALUES(6, 2);

SELECT t1.mysequence, t1.mynumber AS ORIGINAL
, (
    SELECT t2.mynumber
    FROM test t2
    WHERE t2.mysequence = (
        SELECT MAX(t3.mysequence)
        FROM test t3
        WHERE t3.mysequence <= t1.mysequence
        AND mynumber IS NOT NULL
       )
) AS CALCULATED
FROM test t1;

user4379160 28 сен '16 в 20:31 2016-09-28 20:31 · Answer 6 · 2016-09-28 20:31

Если вы ищете решение для Redshift, это будет работать с предложением frame:

SELECT date, 
       last_value(columnName ignore nulls) 
                   over (order by date
                         rows between unbounded preceding and current row) as columnName 
 from tbl

7

Источник

user4379160 28 сен '16 в 20:31

user1299920 29 мар '12 в 05:45 2012-03-29 05:45 · Answer 7 · 2012-03-29 05:45

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

DECLARE @A TABLE(ID INT, Val INT)

INSERT INTO @A(ID,Val) SELECT 1, 3
INSERT INTO @A(ID,Val) SELECT 2, NULL
INSERT INTO @A(ID,Val) SELECT 3, 5
INSERT INTO @A(ID,Val) SELECT 4, NULL
INSERT INTO @A(ID,Val) SELECT 5, NULL
INSERT INTO @A(ID,Val) SELECT 6, 2

UPDATE D
    SET D.VAL = E.VAL
    FROM (SELECT A.ID C_ID, MAX(B.ID) P_ID
          FROM  @A AS A
           JOIN @A AS B ON A.ID > B.ID
          WHERE A.Val IS NULL
            AND B.Val IS NOT NULL
          GROUP BY A.ID) AS C
    JOIN @A AS D ON C.C_ID = D.ID
    JOIN @A AS E ON C.P_ID = E.ID

SELECT * FROM @A

Надеюсь, что это может помочь кому-то:)

user99594 28 авг '09 в 08:31 2009-08-28 08:31 · Answer 8 · 2009-08-28 08:31

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

SELECT  t."date",
        x."number" AS "number"
FROM    @Table t
JOIN    @Table x
    ON  x."date" = (SELECT  TOP 1 z."date"
                    FROM    @Table z
                    WHERE   z."date" <= t."date"
                        AND z."number" IS NOT NULL
                    ORDER BY z."date" DESC)

Если у вас действительно есть ID ("date") столбец, и это первичный ключ (кластеризованный), то этот запрос должен быть довольно быстрым. Но проверьте план запроса: может быть лучше иметь индекс покрытия, включающий Val колонна также.

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

UPDATE  t
SET     t."number" = x."number"
FROM    @Table t
JOIN    @Table x
    ON  x."date" = (SELECT  TOP 1 z."date"
                    FROM    @Table z
                    WHERE   z."date" < t."date" --//@note: < and not <= here, as = not required
                        AND z."number" IS NOT NULL
                    ORDER BY z."date" DESC)
WHERE   t."number" IS NULL

ПРИМЕЧАНИЕ: код должен работать на "SQL Server".

user91218 28 авг '09 в 04:58 2009-08-28 04:58 · Answer 9 · 2009-08-28 04:58

В очень общем смысле:

UPDATE MyTable
SET MyNullValue = MyDate
WHERE MyNullValue IS NULL

2

Источник

user91218 28 авг '09 в 04:58

user1384038 09 май '12 в 08:10 2012-05-09 08:10 · Answer 10 · 2012-05-09 08:10

Это решение для MS Access.

Таблица примеров называется tabс полями id а также val,

SELECT (SELECT last(val)
          FROM tab AS temp
          WHERE tab.id >= temp.id AND temp.val IS NOT NULL) AS val2, *
  FROM tab;

1

Источник

user1384038 09 май '12 в 08:10

user1543217 04 окт '20 в 02:46 2020-10-04 02:46 · Answer 11 · 2020-10-04 02:46

Это будет работать на Snowflake (кредит Даррена Гарднера):

create temp table ss (id int, val int);
insert into ss (id,val) select 1, 3;
insert into ss (id,val) select 2, null;
insert into ss (id,val) select 3, 5;
insert into ss (id,val) select 4, null;
insert into ss (id,val) select 5, null;
insert into ss (id,val) select 6, 2;

select *
      ,last_value(val ignore nulls) over 
       (order by id rows between unbounded preceding and current row) as val2
  from ss;

user4391401 22 янв '20 в 15:48 2020-01-22 15:48 · Answer 12 · 2020-01-22 15:48

Если у вас одна личность (Id) и один общий (Type) столбцы:

UPDATE #Table1 
SET [Type] = (SELECT TOP 1 [Type]
              FROM #Table1 t              
              WHERE t.[Type] IS NOT NULL AND 
              b.[Id] > t.[Id]
              ORDER BY t.[Id] DESC)
FROM #Table1 b
WHERE b.[Type] IS NULL

0

Источник

user4391401 22 янв '20 в 15:48

user135152 28 авг '09 в 05:27 2009-08-28 05:27 · Answer 13 · 2009-08-28 05:27

UPDATE TABLE
   SET number = (SELECT MAX(t.number)
                  FROM TABLE t
                 WHERE t.number IS NOT NULL
                   AND t.date < date)
 WHERE number IS NULL

0

Источник

user135152 28 авг '09 в 05:27

user7358015 30 дек '16 в 13:15 2016-12-30 13:15 · Answer 14 · 2016-12-30 13:15

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

update Projects
set KickOffStatus=2 
where KickOffStatus is null

-3

Источник

user7358015 30 дек '16 в 13:15