条件逐行插入SQL - 如果目标表中存在值，则标记插入的行 - redshift [英] Conditional row by row insert SQL - if value exists in the target table, tag the inserted row - redshift
问题描述
我需要一次向目标表中插入一行。在每次插入之前，我需要扫描目标表并查看该值是否已存在。如果是，则插入行的uniqueID应与目标表中匹配行的uniqueID匹配。如果uniqueID不存在，则将目标表中的MAX uniqueID递增1并将其用作唯一ID。
我被告知我可以这样做有循环和变量等，但亚马逊Redshift目前不支持这些。
我的源表（tbl_source）包含字段anonID，userID和rowNum。
我的目标表（tbl_target）有字段anonID，userID和uniqueID
我的插入本质上非常简单
INSERT 进入 tbl_target
（选择 anonID，userID，XXXX
来自 tbl_source）
XXX是我需要帮助的地方。 XXX是uniqueID。
样本数据
╔════════════════════════════b $ b║rownum║anonID║userID║$ b $b╠══ ═════════════════════════b $ b║1║A║1║
║2║A║2║
║3║A║3║$ b $b║4║B║5║$ b $b║5║B║6║$ b $b║6║C║7║$ b $b║7║D║ 8║$ b $b║8║D║9║$ b $b║9║E║1║$ b $b║10║E║2║$ b $b║11║E║3║
║12║F║9║
╚═════════════════════════════════════════════════（b $ b）
为了向您展示我需要的逻辑，我将采取这个ta逐行显示并显示应该计算的内容;
rowNum 1：
搜索目标表中的和1 - >目标表中都不存在（因为它是空的，这是插入的第一行），因此将目标表中的uniqueID设置为1
目标表
+ ======== + ======== + ========= = +
| anonID | userID | uniqueID |
+ ======== + ======== + ========== +
| A | 1 | 1 |
+ ======== + ======== + ========== +
rowNum 2：
在目标表中搜索A和2 - >存在。因此，将新行的uniqueID设置为目标表中的SAME uniqueID = 1
+ ======== + ===== === + ========== +
| anonID | userID | uniqueID |
+ ======== + ======== + ========== +
| A | 1 | 1 |
+ -------- + -------- + ---------- +
| A | 2 | 1 |
+ ======== + ======== + ========== +
rowNum 3：
在目标表中搜索A和3 - >存在。因此，将新行的uniqueID设置为目标表中的SAME uniqueID = 1.
+ ======== + ==== ==== + ========== +
| anonID | userID | uniqueID |
+ ======== + ======== + ========== +
| A | 1 | 1 |
+ -------- + -------- + ---------- +
| A | 2 | 1 |
+ -------- + -------- + ---------- +
| A | 3 | 1 |
+ ======== + ======== + ========== +
rowNum 4：
在目标表格中搜索B和5 - >既不存在。因此，在目标表（1）中找到MAX uniqueID并递增1.
+ ======== + ======== + ========== +
| anonID | userID | uniqueID |
+ ======== + ======== + ========== +
| A | 1 | 1 |
+ -------- + -------- + ---------- +
| A | 2 | 1 |
+ -------- + -------- + ---------- +
| A | 3 | 1 |
+ -------- + -------- + ---------- +
| B | 5 | 2 |
+ ======== + ======== + ========== +
rowNum 5：
在目标故事中搜索B和6 - > B存在。因此，将新行的uniqueID设置为目标表中的SAME uniqueID = 1
+ ====== == + ======== + ========== +
| anonID | userID | uniqueID |
+ ======== + ======== + ========== +
| A | 1 | 1 |
+ -------- + -------- + ---------- +
| A | 2 | 1 |
+ -------- + -------- + ---------- +
| A | 3 | 1 |
+ -------- + -------- + ---------- +
| B | 5 | 2 |
+ -------- + -------- + ---------- +
| B | 6 | 2 |
+ ======== + ======== + ========== +
rowNum 6：
搜索C和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：
在目标表中搜索E和1。 1已经存在！因此，将uniqueID设置为与已存在的行1相同的唯一ID - 这是唯一ID 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：
在目标表中搜索E和2。 E和2都已经存在。在这种情况下，只返回它找到的第一个的唯一ID（唯一ID对于任何一个都是相同的）。
+ ======== + ======== + ========== +
| 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 |
+ -------- + -------- + ---------- +
....
< br $>
ROWNum 12
在目标表中搜索F和9 - > 9存在。因此，将9的唯一ID设置为存在9的相同唯一ID - > 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 表 tbl_source
（
rownum整数，
anonid varchar （ 8 ），
userid integer
）;
insert into tbl_source
值
（ 1 ，' A'， 1 ），（ 2 ，' A'， 2 ），（ 3 ，' A'， 3 ），
（ 4 ，' B'， 5 ），（ 5 ，' B'， 6 ），（ 6 ，' C'， 7 ），
（ 7 ，' D'， 8 ），（ 8 ，< span class =code-string>' D'， 9 ），（< span class =code-digit> 9 ，' E'， 1 ），
（ 10 ，' E'， 2 ），（ 11 ，' E'， 3 ）， （ 12 ，' F'，< span class =code-digit> 9 ）
;
CREATE TABLE tbl_target
（
anonid < span class =code-keyword> varchar （ 8 ），
userid integer，
uniqueID integer
） ;
我尝试过：
我被告知我可以使用循环和变量等来完成此操作，但Amazon Redshift目前不支持这些。
解决方案
假设您可以使用临时表，然后这似乎工作：
首先获取唯一 anonID s 创建 表 #t1（id integer identity （ 1 ， 1 ），anonid varchar （ 8 ））
insert into ＃t1
SELECT < span class =code-keyword> DISTINCT anonid FROM tbl_source
结果：
1澳元b $ b 2 B
3 C
4 D
5 E
6 F
然后获得一个纯粹的起始位置 anonID
选择 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
然后根据是否调整该位置userID已经出现：
更新 A SET uniqueId = b.uniqueID
FROM #T2 A
inner JOIN ＃T2 B ON A.userid = B.userid 和 a.anonid< > b.anonid
其中 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查询以获取目标
插入 进入 tbl_target
SELECT anonID，userID，uniqueID < span class =code-keyword> FROM #t2
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
WHE RE anonid = @ anonID AND userid = @ userID;
I need to insert one row at a time into my target table. Before each insert, I need to scan the target table and see if the value already exists. If it does, then the uniqueID of the inserted row should match the uniqueID of the matched row in the target table. If the uniqueID does not exist, then increment the MAX uniqueID in the target table by 1 and use this as the uniqueID.
I've been told I can do this with loops and variables etc but Amazon Redshift does not support these at the moment.
My source table (tbl_source) has the fields anonID, userID and rowNum.
My target table (tbl_target) has fields anonID, userID and uniqueID
My insert is very simple in essence
INSERT into tbl_target
(select anonID, userID, XXXX
from tbl_source)
The XXX is where I need help. XXX is uniqueID.
SAMPLE DATA
╔════════╦════════╦════════╗
║ 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 ║
╚════════╩════════╩════════╝
To show you the logic I need, I will take this table row by row and show you what should be computed;
rowNum 1:
Search for "A" and "1" in the target table -> Neither exist in the target table (since it is empty, this is the first row bring inserted), therefore set uniqueID in the target table to 1
TARGET TABLE
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A | 1 | 1 |
+========+========+==========+
rowNum 2:
Search for "A" and "2" in the target table -> A exists. Therefore set the uniqueID of the new row to the SAME uniqueID as in the target table = 1
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A | 1 | 1 |
+--------+--------+----------+
| A | 2 | 1 |
+========+========+==========+
rowNum 3:
Search for "A" and "3" in the target table -> A exists. Therefore set the uniqueID of the new row to the SAME uniqueID as in the target table = 1.
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A | 1 | 1 |
+--------+--------+----------+
| A | 2 | 1 |
+--------+--------+----------+
| A | 3 | 1 |
+========+========+==========+
rowNum 4:
Search for "B" and "5" in the target table -> neither exist. Therefore find the MAX uniqueID in the target table (1) and increment by 1.
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A | 1 | 1 |
+--------+--------+----------+
| A | 2 | 1 |
+--------+--------+----------+
| A | 3 | 1 |
+--------+--------+----------+
| B | 5 | 2 |
+========+========+==========+
rowNum 5:
Search for "B" and "6" in the target tale -> "B" exists. Therefore set the uniqueID of the new row to the SAME uniqueID as in the target table = 1
+========+========+==========+
| anonID | userID | uniqueID |
+========+========+==========+
| A | 1 | 1 |
+--------+--------+----------+
| A | 2 | 1 |
+--------+--------+----------+
| A | 3 | 1 |
+--------+--------+----------+
| B | 5 | 2 |
+--------+--------+----------+
| B | 6 | 2 |
+========+========+==========+
rowNum 6:
Search for "C" and "7". Neither found
+========+========+==========+
| 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:
Search for "E" and "1" in the target table. "1" already exists! Therefore set the uniqueID to the same uniqueID as the row which already exists with "1" - Which is 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:
Search for "E" and "2" in the target table. Both "E" and "2 already exist. In this case just return the uniqueID for the first one it finds (the uniqueID will be the same for either one).
+========+========+==========+
| 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
Search for "F" and "9" in the target table -> 9 exists. Therefore set uniqueID for 9 to the same uniqueID where 9 exists -> 4
THE FINAL TABLE then should look like this;
+========+========+==========+
| 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 |
+========+========+==========+
If you wish to use my data;
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
);
What I have tried:
I've been told I can do this with loops and variables etc but Amazon Redshift does not support these at the moment.
解决方案 Assuming you can use temporary tables, then this appears to work:
Firstly get a list of the unique anonIDscreate table #t1 (id integer identity(1,1), anonid varchar(8))
insert into #t1
SELECT DISTINCT anonid FROM tbl_source
Results:
1 A
2 B
3 C
4 D
5 E
6 F
Then get an intial starting position based purely on anonID
select B.ID as uniqueID, A.*
INTO #T2
FROM #tbl_source A
INNER JOIN #t1 B ON A.anonid = B.anonid
Results:
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
Then adjust that position based on whether or not userID has already appeared:
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
Results:
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
You can then just query from #t2 to get your target
insert into tbl_target
SELECT anonID, userID, uniqueID FROM #t2
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;
这篇关于条件逐行插入SQL - 如果目标表中存在值，则标记插入的行 - redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
