如何在Snowflake(SQL)中添加指示重复id的列

Question

所以我有一个这样的表，其中每个ID每行都是唯一的：

表1

ID    data
001  Walter
002  Skylar
003    Hank
004   Marie

我有另一个表，ID可以多次出现：

表2

ID  value
001     apple
001    banana
003     grape
004  graphite
003     jones
001      pear

我只想给这两个表，我想在表1中添加一列，以指示一个ID是否在表2中出现多次

最终结果：

ID    data  table2_multiple
001  Walter                1
002  Skylar                0
003    Hank                1
004   Marie                0  

这里我们展示了ID = 1和ID = 3都有table2_multiple = 1，因为它们在表2中都出现了不止一次！

Answer 1

尽管这是一件很奇怪的事情，但以下是如何做到这一点：

update table1
set table2_multiple = case when t.cnt > 1 then 1 else 0 end 
from (select ID , count(*) cnt from table2 group by ID) t 
where t.id = table1.id

或者如果你只是想选择：

select t1.* , case when t2.cnt > 1 then 1 else 0 end as table2_multiple
from table1 t1 
join (select ID , count(*) cnt from table2 group by ID) t2
on t1.id = t2.id

Answer 2

在所有示例中，我们使用case表达式来确定计数是否为>1设置为1，否则为0。

基本聚合函数：

SELECT t1.ID, t1.Data, case when count(*) > 1 then 1 else 0 end as table2_Multiple
FROM Table1 t1 --t1 is an alias of table1
LEFT JOIN table2 t2 --t2 is an alias of table2
ON t1.ID = t2.ID
GROUP BY T1.ID, T1.Data

使用分析函数：(Count((over(partitionxxx(这基本上是说通过唯一的T1ID和数据来计数所有记录，然后表达式说如果该计数>1返回1，否则返回0。然后，distinct将消除所有重复项。

SELECT Distinct t1.ID
, t1.Data
, case when count() over (partition by T1.ID, T1.Data) > 1 then 1 else 0 end as Table_2_multiple
LEFT JOIN Table2 T2
on T1.ID = T2.ID

在这种情况下，使用内联视图(T2(按表2获取计数，子查询每个ID只返回1行，因此不需要处理倍数。

SELECT T1.*, case when coalesce(t2.ValueNo,0) > 1 then 1 else 0 end as table2_Multiple 
FROM Table1
LEFT JOIN (SELECT ID, count(*) as valueNo 
FROM Table2 
GROUP BY ID) T2
on T1.ID = T2.ID