Teradata查询：如何将客户套餐标记表转换为按日分套餐统计结果

实现思路：先枚举所有目标套餐类型，再和源表去重后的客户、日期组合做笛卡尔积，保证每个客户+日期组合都覆盖全部4种套餐类型，再关联源表按规则统计符合条件的记录数即可。

完整查询代码如下：

SELECT 
    pkg.PACKAGE,
    cd.CUSTOMER,
    cd.DATE,
    COUNT(CASE 
        WHEN pkg.PACKAGE = 'SPEED ONLY' AND t.speed = 1 THEN 1
        WHEN pkg.PACKAGE = 'TV ONLY' AND t.TV = 1 THEN 1
        WHEN pkg.PACKAGE = 'SPEED+TV' AND t.speed = 1 AND t.TV = 1 THEN 1
        WHEN pkg.PACKAGE = 'NO VOUCHER' AND t.NOVOUCHER = 1 THEN 1
        ELSE NULL 
    END) AS COUNT
FROM 
    -- 枚举所有需要展示的套餐类型
    (
        SELECT 'SPEED ONLY' AS PACKAGE UNION ALL
        SELECT 'TV ONLY' AS PACKAGE UNION ALL
        SELECT 'SPEED+TV' AS PACKAGE UNION ALL
        SELECT 'NO VOUCHER' AS PACKAGE
    ) pkg
CROSS JOIN
    -- 提取源表中所有不重复的客户+日期组合
    (
        SELECT DISTINCT CUSTOMER, DATE
        FROM your_source_table -- 此处替换为实际源表名称
    ) cd
LEFT JOIN your_source_table t
    ON cd.CUSTOMER = t.CUSTOMER
    AND cd.DATE = t.DATE
GROUP BY 
    pkg.PACKAGE,
    cd.CUSTOMER,
    cd.DATE
ORDER BY 
    cd.DATE DESC,
    pkg.PACKAGE

注意事项：

• 请将代码中两处your_source_table替换为你实际使用的源表名称
• 若DATE字段为DATE数据类型而非字符串格式，无需额外转换，Teradata可自动匹配
• 后续如果需要增减套餐类型，直接修改pkg子查询的枚举值和CASE中对应的判断条件即可

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