在Teradata SQL中使用UNION删除重复行

Question

我使用Teradata sql使用UNION提取数据。

SEL CAST(a.dttm AS DATE), count(a.cs) FROM  cin.cell a
LEFT JOIN cin.comm c ON a.cs_sk = c.cs_sk
LEFT JOIN CIN.CID d ON a.cn_cd = d.CN_CD
WHERE CAST(a.dttm AS DATE) >= CURRENT_DATE-10
GROUP BY 1
UNION 
SEL CAST(a.dttm AS DATE), count(a.cs) FROM  cin_ps.cell a
LEFT JOIN cin_ps.comm c ON a.cs_sk = c.cs_sk
LEFT JOIN CIN_ps.CID d ON a.cn_cd = d.CN_CD
WHERE CAST(a.dttm AS DATE) >= CURRENT_DATE-10
GROUP BY 1

但是我在第一列中得到了重复的行，如下所示请注意，可能存在这样的情况:对于任何第一组表或第二组表，在任何特定日期都没有行

当前结果:

N.  PROCESSED_DTTM  Count(cs)
1   4/8/2022    40
2   4/8/2022    66
3   4/9/2022    49
4   4/9/2022    71
5   4/10/2022   117
6   4/10/2022   1430
7   4/11/2022   261
8   4/11/2022   841

所需结果:

N.  PROCESSED_DTTM  Count(cs)
1   4/8/2022    106
2   4/9/2022    120
5   4/10/2022   1547
7   4/11/2022   1102

Answer 1

你没有得到重复的，你在两个集合中都有非唯一的结果。如果将联合封装在子查询中并选择distinct，则将收到相同的数据集。您需要做的是在Count列上使用SUM聚合数据:

SELECT  PROCESSED_DTTM,  
SUM([Count(cs)]) [Count(cs)]
FROM
(
SEL       CAST(a.dttm AS DATE) PROCESSED_DTTM, 
count(a.cs) [Count(cs)] 
FROM      cin.cell a
LEFT JOIN cin.comm c ON a.cs_sk = c.cs_sk
LEFT JOIN CIN.CID d ON a.cn_cd = d.CN_CD
WHERE     CAST(a.dttm AS DATE) >= CURRENT_DATE-10
GROUP BY  1
UNION ALL
SEL       CAST(a.dttm AS DATE), count(a.cs) 
FROM      cin_ps.cell a
LEFT JOIN cin_ps.comm c ON a.cs_sk = c.cs_sk
LEFT JOIN CIN_ps.CID d ON a.cn_cd = d.CN_CD
WHERE     CAST(a.dttm AS DATE) >= CURRENT_DATE-10
GROUP BY  1
) AS TBL1
GROUP BY PROCESSED_DTTM