Member 10104822 Ответов: 2

Условная строка за строкой insert SQL - если значение существует в целевой таблице, пометьте вставленную строку - redshift

Мне нужно вставлять по одной строке за раз в мою целевую таблицу. Перед каждой вставкой мне нужно сканировать целевую таблицу и посмотреть, существует ли уже это значение. Если это так, то уникальный идентификатор вставленной строки должен соответствовать уникальный идентификатор совпадающей строки в целевой таблице. Если uniqueID не существует, то увеличьте максимальный uniqueID в целевой таблице на 1 и используйте его в качестве uniqueID.

Мне сказали, что я могу сделать это с помощью циклов и переменных и т. д., Но Amazon Redshift на данный момент не поддерживает их.

Моя исходная таблица (tbl_source) содержит поля anonID, userID и rowNum.
Моя целевая таблица (tbl_target) имеет поля anonID, идентификатор пользователя и uniqueid

Моя вставка очень проста в сущности
INSERT into tbl_target
    (select anonID, userID, XXXX
     from tbl_source)

ХХХ - это то место, где мне нужна помощь. XXX-это uniqueID.


ВЫБОРОЧНЫЕ ДАННЫЕ

╔════════╦════════╦════════╗
║ rownum ║ anonID ║ userID ║
╠════════╬════════╬════════╣
║      1 ║ A      ║      1 ║
║      2 ║ A      ║      2 ║
║      3 ║ A      ║      3 ║
║      4 ║ B      ║      5 ║
║      5 ║ B      ║      6 ║
║      6 ║ C      ║      7 ║
║      7 ║ D      ║      8 ║
║      8 ║ D      ║      9 ║
║      9 ║ E      ║      1 ║
║     10 ║ E      ║      2 ║
║     11 ║ E      ║      3 ║
║     12 ║ F      ║      9 ║
╚════════╩════════╩════════╝


Чтобы показать вам логику, которая мне нужна, я возьму эту таблицу ряд за рядом и покажу вам, что должно быть вычислено;

параметр rownum 1:

Найдите "A" и "1" в целевой таблице -> Ни один из них не существует в целевой таблице (так как она пуста, это первая строка, которую вы вставили), поэтому установите uniqueID в целевой таблице на 1

ЦЕЛЕВАЯ ТАБЛИЦА

+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+========+========+==========+

параметр rownum 2:

Найдите "А" и "2" в целевой таблице -> А существует. Поэтому установить уникальный идентификатор новой строки в той же уникальный идентификатор, а в целевой таблице = 1
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+========+========+==========+

rowNum 3:

Найдите "А" и "3" в целевой таблице -> А существует. Поэтому установить уникальный идентификатор новой строки в той же уникальный идентификатор, а в целевой таблице = 1.
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+--------+--------+----------+
| A      | 3      | 1        |
+========+========+==========+


rowNum 4:

Найдите "B" и "5" в целевой таблице-ни того, ни другого не существует. Поэтому найдите максимальный uniqueID в целевой таблице (1) и увеличьте его на 1.

+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+--------+--------+----------+
| A      | 3      | 1        |
+--------+--------+----------+
| B      | 5      | 2        |
+========+========+==========+

rowNum 5:

Ищите "Б" и "6" в целевой сказке -"Б" существует. Поэтому установить уникальный идентификатор новой строки в той же уникальный идентификатор, а в целевой таблице = 1

+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+--------+--------+----------+
| A      | 3      | 1        |
+--------+--------+----------+
| B      | 5      | 2        |
+--------+--------+----------+
| B      | 6      | 2        |
+========+========+==========+


rowNum 6:

Найдите "С" и "7". Ни один из них не найден


    +========+========+==========+
    | anonID | userID | uniqueID |
    +========+========+==========+
    | A      | 1      | 1        |
    +--------+--------+----------+
    | A      | 2      | 1        |
    +--------+--------+----------+
    | A      | 3      | 1        |
    +--------+--------+----------+
    | B      | 5      | 2        |
    +--------+--------+----------+
    | B      | 6      | 2        |
    +--------+--------+----------+
    | C      | 7      | 3        |
    +========+========+==========+
.....
.....
.....

    +========+========+==========+
    | anonID | userID | uniqueID |
    +========+========+==========+
    | A      | 1      | 1        |
    +--------+--------+----------+
    | A      | 2      | 1        |
    +--------+--------+----------+
    | A      | 3      | 1        |
    +--------+--------+----------+
    | B      | 5      | 2        |
    +--------+--------+----------+
    | B      | 6      | 2        |
    +--------+--------+----------+
    | C      | 7      | 3        |
    +--------+--------+----------+
    | D      | 8      | 4        |
    +--------+--------+----------+
    | D      | 9      | 4        |
    +========+========+==========+


rowNum 9:

Найдите "Е" и "1" в целевой таблице. "1" уже существует! Поэтому установите uniqueID на тот же uniqueID, что и строка, которая уже существует с "1" - то есть uniqueID 1.

+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+--------+--------+----------+
| A      | 3      | 1        |
+--------+--------+----------+
| B      | 5      | 2        |
+--------+--------+----------+
| B      | 6      | 2        |
+--------+--------+----------+
| C      | 7      | 3        |
+--------+--------+----------+
| D      | 8      | 4        |
+--------+--------+----------+
| D      | 9      | 4        |
+--------+--------+----------+
| E      | 1      | 1        |
+========+========+==========+


RowNum 10:

Найдите "Е" и "2" в целевой таблице. И "Е", и " 2 " уже существуют. В этом случае просто верните uniqueID для первого найденного объекта (uniqueID будет одинаковым для любого из них). 

    +========+========+==========+
    | anonID | userID | uniqueID |
    +========+========+==========+
    | A      | 1      | 1        |
    +--------+--------+----------+
    | A      | 2      | 1        |
    +--------+--------+----------+
    | A      | 3      | 1        |
    +--------+--------+----------+
    | B      | 5      | 2        |
    +--------+--------+----------+
    | B      | 6      | 2        |
    +--------+--------+----------+
    | C      | 7      | 3        |
    +--------+--------+----------+
    | D      | 8      | 4        |
    +--------+--------+----------+
    | D      | 9      | 4        |
    +--------+--------+----------+
    | E      | 1      | 1        |
    +--------+--------+----------+
    | E      | 2      | 1        |
    +--------+--------+----------+
....


ROwNum 12

Найдите "F" и "9" в целевой таблице -> 9 существует. Поэтому установите uniqueID для 9 на тот же uniqueID, где существует 9 -> 4

Итоговая таблица тогда должна выглядеть так;

+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A      | 1      | 1        |
+--------+--------+----------+
| A      | 2      | 1        |
+--------+--------+----------+
| A      | 3      | 1        |
+--------+--------+----------+
| B      | 5      | 2        |
+--------+--------+----------+
| B      | 6      | 2        |
+--------+--------+----------+
| C      | 7      | 3        |
+--------+--------+----------+
| D      | 8      | 4        |
+--------+--------+----------+
| D      | 9      | 4        |
+--------+--------+----------+
| E      | 1      | 1        |
+--------+--------+----------+
| E      | 2      | 1        |
+--------+--------+----------+
| E      | 3      | 1        |
+--------+--------+----------+
| F      | 9      | 4        |
+========+========+==========+


Если вы хотите использовать мои данные;

CREATE TABLE tbl_source
    (
       rownum  integer,
       anonid  varchar(8),
       userid  integer
    );

    insert into tbl_source
     values
    (1,'A',1), (2,'A',2), (3,'A',3),
    (4,'B',5), (5,'B',6), (6,'C',7),
    (7,'D',8), (8,'D',9), (9,'E',1), 
    (10,'E',2), (11,'E',3), (12,'F',9)   
    ;
 
    CREATE TABLE tbl_target
    (
       anonid  varchar(8),
       userid  integer,
       uniqueID integer
    );


Что я уже пробовал:

Мне сказали, что я могу сделать это с помощью циклов и переменных и т. д., Но Amazon Redshift на данный момент не поддерживает их.

CHill60

Я бы избегал циклов,но у меня есть некоторые серьезные сомнения относительно вашей логики здесь - вы действительно говорите, что если бы была запись B, 1, то просто выберите "первый" 1, чтобы назначить строку E1. Как вы думаете, вы собираетесь определить это "первое" без какого-либо упорядочения? Что вы на самом деле пытаетесь сделать или это просто надуманное упражнение?

Member 10104822

Это не надуманное упражнение. Предоставленные данные-это просто аналогичные фиктивные данные. Все, что я говорю, это то, что для чего мне это нужно, если есть конфликты, то мне не нужно добавлять какую-то дополнительную логику, чтобы определить правильное значение. Его можно заказать первым но для простоты он не нужен

Jörgen Andersson

Как бы вы справились с (a,1),(b,1),(b,2),(c,2),(c,3) ?
Должны ли они все получить один и тот же уникальный идентификатор?

Member 10104822

Правильный

Jörgen Andersson

Итак, как бы вы справились с этим делом:
Сначала вы добавляете (a,1), он получает UniqueID 1
Затем добавьте (b,2), это даст новый UniqueID 2
Как вы справляетесь со следующей строкой, содержащей (a,2)?

Или все данные должны быть добавлены как набор за один раз?

Member 10104822

Этот сценарий не может произойти. Там всегда будет перекрытие.
это будет идти от
A1 -> A2 -> B2 или наоборот.
У вас всегда будет совпадение.

Jörgen Andersson

Как насчет ряда (a,1), (b,2), (b,1) это тоже невозможно?

Member 10104822

Это

Jörgen Andersson

Последний вопрос: вы начинаете с пустой целевой таблицы или нет?

Member 10104822

Он пуст.

2 Ответов

Рейтинг:
2

Santosh kumar Pithani 

INSERT INTO #tbl_target
         SELECT anonid,
                userid,
                COALESCE((SELECT uniqueID FROM #tbl_target WHERE anonid=@anonID),
                        (SELECT MAX(uniqueID)+1 FROM #tbl_target),1)AS uniqueID
           FROM #tbl_source
                       WHERE anonid=@anonID AND userid=@userID;

Member 10104822

к сожалению, это не работает. Я получаю ошибку;

Недопустимая операция: оператор не существует: переменный символ =@ переменный символ;

Вы используете переменные? Красное смещение не поддерживает переменные

Santosh kumar Pithani

Если вы вставляете все записи одновременно в целевую таблицу, то как вы сравниваете их с исходной таблицей?

Member 10104822

В том-то и дело. Мне нужно иметь возможность вставлять по одной строке за раз в целевую таблицу и запрашивать эту таблицу перед каждой вставкой новой строки.

Вот в чем суть проблемы на самом деле

Рейтинг:
19

CHill60

Если предположить, что вы можете использовать временные таблицы, то это, по-видимому, работает:

Во первых получите список уникальных anonIDс

create table #t1 (id integer identity(1,1), anonid varchar(8))
insert into #t1
SELECT DISTINCT anonid FROM tbl_source
Результаты:
1	A
2	B
3	C
4	D
5	E
6	F
Затем получите инциальную стартовую позицию, основанную исключительно на anonID
select B.ID as uniqueID, A.*
INTO #T2
FROM #tbl_source A
INNER JOIN #t1 B ON A.anonid = B.anonid
Результаты:
1	1	A	1
1	2	A	2
1	3	A	3
2	4	B	5
2	5	B	6
3	6	C	7
4	7	D	8
4	8	D	9
5	9	E	1
5	10	E	2
5	11	E	3
6	12	F	9
Затем отрегулируйте эту позицию в зависимости от того, появился ли уже идентификатор пользователя:
UPDATE A SET uniqueId= b.uniqueID
FROM #T2 A
inner JOIN #T2 B ON A.userid = B.userid and a.anonid <> b.anonid
where B.uniqueid < A.Uniqueid
Результаты: 
1	1	A	1
1	2	A	2
1	3	A	3
2	4	B	5
2	5	B	6
3	6	C	7
4	7	D	8
4	8	D	9
1	9	E	1
1	10	E	2
1	11	E	3
4	12	F	9
Затем вы можете просто запросить из #t2, чтобы получить свою цель
insert into tbl_target
	SELECT anonID, userID, uniqueID FROM #t2

Maciej Los

Молодец!

Member 10104822

Вы гений, сэр.

Большое спасибо!