Teradata中基于条件的多分组过滤逻辑实现求助

原表结构 #

实现逻辑 #

• 以COLA和COLB为分组键：
  1. 若分组内distinct COLC的数量>1，则进一步按COLA、COLB、COLC分组，排除其中存在COLD='R004'的子分组的所有记录；
  2. 若分组内distinct COLC的数量≤1，则保留该分组的所有记录。

例如12345,asdf,1子分组存在COLD='R004'，需排除该子分组的所有记录。

预期输出 #

问题背景 #

尝试过count(distinct) over (partition by COLA, COLB)语法，但Teradata不支持该写法，询问是否可通过单查询实现上述逻辑。

解决方案 #

可以通过嵌套窗口函数或子查询统计分组信息后过滤，以下两种单查询方式均可实现：

方式1：嵌套窗口函数实现 #

WITH group_stats AS (
    SELECT 
        t.*,
        -- 替代count(distinct) over，统计COLA+COLB分组内不同COLC的数量
        MAX(DENSE_RANK() OVER (PARTITION BY COLA, COLB ORDER BY COLC)) 
            OVER (PARTITION BY COLA, COLB) AS colc_distinct_count,
        -- 标记当前COLA+COLB+COLC子分组是否存在R004
        MAX(CASE WHEN COLD = 'R004' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY COLA, COLB, COLC) AS has_r004
    FROM your_table t
)
SELECT 
    COLA, COLB, COLC, COLD, CO, COLF, COLG, COLH
FROM group_stats
WHERE 
    colc_distinct_count <= 1
    OR (colc_distinct_count > 1 AND has_r004 = 0);

说明

• 用DENSE_RANK()按COLA,COLB分组后对COLC排序，取分组内最大排名即可得到distinct COLC的数量，这是Teradata替代count(distinct) over的常用方法；
• 用MAX(CASE...)窗口函数标记子分组是否包含R004；
• 最后通过WHERE条件过滤符合需求的记录。

方式2：子查询关联实现 #

SELECT 
    t.*
FROM your_table t
JOIN (
    SELECT 
        COLA, COLB,
        COUNT(DISTINCT COLC) AS colc_distinct_count
    FROM your_table
    GROUP BY COLA, COLB
) g ON t.COLA = g.COLA AND t.COLB = g.COLB
LEFT JOIN (
    SELECT 
        COLA, COLB, COLC
    FROM your_table
    WHERE COLD = 'R004'
    GROUP BY COLA, COLB, COLC
) r ON t.COLA = r.COLA AND t.COLB = r.COLB AND t.COLC = r.COLC
WHERE 
    g.colc_distinct_count <= 1
    OR (g.colc_distinct_count > 1 AND r.COLC IS NULL);

说明

• 第一个子查询统计COLA+COLB分组内的distinct COLC数量；
• 第二个子查询找出所有存在R004的COLA+COLB+COLC子分组；
• 关联后过滤：要么分组内COLC数量≤1，要么数量>1且当前子分组不在存在R004的列表中。

内容的提问来源于stack exchange，提问作者GIN