在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

您没有重复，您在这两套方面都有非唯一的结果。如果要将联合封装在子查询中并选择“不同”，则将收到相同的数据集。您要做的是使用计数列上的总和汇总数据：

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