如何在Hive中实现按天分组并与全量MDN数据集左外连接？

嘿，这个需求在Hive里其实很好实现，我给你拆解清楚，再给你现成的SQL示例！

首先明确核心逻辑：我们需要先拿到全量唯一的MDN集合，再把这个集合和按天分组聚合的数据做左外连接——这里一定要注意左连接的顺序，全量MDN表作为左表，这样即使某个MDN在某天没有业务数据，也能被保留在结果里，对应字段显示null，完全符合你要的示例效果。

方案一：用CTE（通用表表达式，Hive 0.13及以上支持） #

CTE写法更清晰易读，适合复杂查询：

-- 第一步：获取全量唯一MDN
WITH full_mdn_list AS (
    SELECT DISTINCT mdn
    FROM your_business_table  -- 这里填你的业务表名，或者专门的MDN维度表
),
-- 第二步：处理单日分组聚合数据
daily_agg_data AS (
    SELECT 
        mdn,
        day,
        COUNT(*) AS record_count,  -- 这里替换成你需要的聚合指标，比如sum、max等
        MAX(some_other_col) AS max_col
    FROM your_business_table
    WHERE day = '20180302'  -- 指定单日，多日查询可去掉这个条件
    GROUP BY mdn, day
)
-- 第三步：左外连接得到最终结果
SELECT 
    f.mdn,
    d.record_count,
    d.day,
    d.max_col
FROM full_mdn_list f
LEFT JOIN daily_agg_data d 
    ON f.mdn = d.mdn;

方案二：用子查询（兼容低版本Hive） #

如果你的Hive版本不支持CTE，用子查询也能实现：

SELECT 
    f.mdn,
    d.record_count,
    d.day,
    d.max_col
FROM (
    -- 子查询获取全量唯一MDN
    SELECT DISTINCT mdn FROM your_business_table
) f
LEFT JOIN (
    -- 子查询处理单日分组聚合
    SELECT 
        mdn,
        day,
        COUNT(*) AS record_count,
        MAX(some_other_col) AS max_col
    FROM your_business_table
    WHERE day = '20180302'
    GROUP BY mdn, day
) d 
    ON f.mdn = d.mdn;

几个关键注意点： #

• 全量MDN的来源：如果有专门的MDN维度表（比如dim_mdn），优先用维度表替换full_mdn_list里的查询，这样能保证MDN的完整性，避免业务表中遗漏某些从未产生过数据的MDN。
• 多日场景适配：如果需要查询多天的数据，只需要去掉daily_agg_data里的WHERE day = '20180302'，结果会自动包含每个MDN在所有日期的记录，无数据的日期对应字段显示null。
• 字段类型匹配：确保关联的mdn字段在两张表中的数据类型一致，避免隐式转换导致的匹配失败。

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