Блог

Главная - Вопросы по программированию - Как обновлять каждый столбец по одному для каждой строки в snowflake

Как обновлять каждый столбец по одному для каждой строки в snowflake

#sql #snowflake-cloud-data-platform

Вопрос:

Предположим, у меня в таблице 10 столбцов, и я хочу обновлять каждый столбец, но по одному для каждой строки до 10 строк.

если таблица похожа

 1,2,3
4,5,6
7,8,9

Я хочу обновить его, как

 x,2,3
4,y,6
7,8,z

Столбцы могут иметь любое количество, поэтому необходим динамический подход. Также иногда необходимо исключить некоторые столбцы.

Я попытался посмотреть, могу ли я обновить строку на основе идентификатора строки, но такой опции, как идентификатор строки, не существует. Я не хочу менять дизайн таблицы, чтобы включить столбец счетчика.

Комментарии:

1. Что вы подразумеваете под «по одному»? Должен ли «X» в вашем примере всегда быть в первой строке?

2. Как и для первого ряда, обновите первый столбец. Для второго ряда обновите второй столбец и так далее

3. Если вы не упорядочиваете таблицу по какому — либо ключу, то данные в первой строке могут каждый раз меняться-это не детерминировано. Это нормально?

4. Да, это не проблема

Ответ №1:

вы можете использовать функцию окна для присвоения идентификатора строки и на основе этого :

 with cte as (
select * from ( 
   select * , row_number() over (order by id) rn
   from tablename
) t ) ;

update t 
set col1 = case when rn = 1 then <updatevalue> else col1 end
  , col2 = case when rn = 2 then <updatevalue> else col2 end
  , col3 = case when rn = 3 then <updatevalue> else col3 end
  , ...
from tablename t
join cte on cte.id = t.id

Комментарии:

1. Есть ли что-то неправильное в синтаксисе ? проблема синтаксиса в этом

2. Я не думаю, что вы можете обновить CTE таким образом в Snowflake

3. @EXODIA похоже , что snowflake не поддерживает несъедобные cte, посмотрите, работает ли обновленный ответ

4. Что это за удостоверение личности ? Исходная таблица не имеет такого идентификатора или первичного ключа

Ответ №2:

Требование «Столбцы могут иметь любое количество, поэтому необходим динамический подход» выглядит как попытка реализовать матрицу в виде таблицы.

Альтернативным подходом может быть использование типа МАССИВА и сохранение всей структуры в виде одной «ячейки» в таблице.

 CREATE OR REPLACE TABLE t 
AS
SELECT ARRAY_CONSTRUCT(ARRAY_CONSTRUCT(1,2,3),
                       ARRAY_CONSTRUCT(4,5,6),
                       ARRAY_CONSTRUCT(7,8,9)) c
UNION ALL
SELECT ARRAY_CONSTRUCT(ARRAY_CONSTRUCT(10,20,30), 
                       ARRAY_CONSTRUCT(40,50,60), 
                       ARRAY_CONSTRUCT(70,80,90)) c;


SELECT *
FROM t;
/*
C
[[1,2,3],[4,5,6],[7,8,9]]
[[10,20,30],[40,50,60],[70,80,90]]
*/

Доступ к элементам:

 SELECT c[0][0], c[0][1], c[0][2],
       c[1][0], c[1][1], c[1][2],
       c[2][0], c[2][1], c[2][2]
FROM t;
/*
C[0][0] C[0][1] C[0][2] C[1][0] C[1][1] C[1][2] C[2][0] C[2][1] C[2][2]
1   2   3   4   5   6   7   8   9
10  20  30  40  50  60  70  80  90
*/

Обновить:

 UPDATE t
SET c = ARRAY_CONSTRUCT(ARRAY_CONSTRUCT('x'    , c[0][1], c[0][2])
                       ,ARRAY_CONSTRUCT(c[1][0], 'y'     ,c[1][2])
                       ,ARRAY_CONSTRUCT(c[2][0], c[2][1] , 'z'   )
                     );
                   
SELECT * FROM t;
/*
C
[["x",2,3],[4,"y",6],[7,8,"z"]]
[["x",20,30],[40,"y",60],[70,80,"z"]]
*/

Более надежные преобразования могут быть выполнены с помощью пользовательских функций.

Метки: