目录一、ADS层概述与应用场景1.1 ADS层定位1.2 设计特点二、建表语句深度解析2.1 完整建表语句2.2 字段设计解析三、数据装载策略详解3.1 整体装载逻辑设计原因分析3.2 UNION vs UNION ALL选择四、核心SQL代码逐行解析4.1 最近1天数据处理关键函数解析4.2 最近7天和30天数据处理高级函数详解1. LATERAL VIEW EXPLODE()2. CASE WHEN表达式3. COUNT(DISTINCT IF())4.3 GROUP BY分组原理五、性能优化与最佳实践5.1 存储优化5.2 查询优化技巧六、扩展思考与改进建议6.1 可能的问题与解决方案6.2 监控与维护七、总结一、ADS层概述与应用场景1.1 ADS层定位ADSApplication Data Store层是数据仓库的最后层直接面向业务应用。它存储经过深度聚合和分析的数据为报表、数据可视化、API服务等提供直接可用的数据。1.2 设计特点-- 核心设计原则 -- 1. 行式存储TSV格式便于数据导出到MySQL等关系型数据库 -- 2. GZIP压缩在数据量不大时保证压缩效率 -- 3. 非分区设计分析结果数据量通常较小 -- 4. 按需建表客户需要什么字段就创建什么字段二、建表语句深度解析2.1 完整建表语句DROP TABLE IF EXISTS ads_order_stats_by_tm; -- 使用EXTERNAL TABLE确保数据安全删除表时不删除HDFS数据 CREATE EXTERNAL TABLE ads_order_stats_by_tm ( dt STRING COMMENT 统计日期, recent_days BIGINT COMMENT 最近天数,1:最近1天,7:最近7天,30:最近30天, tm_id STRING COMMENT 品牌ID, tm_name STRING COMMENT 品牌名称, order_count BIGINT COMMENT 下单数, order_user_count BIGINT COMMENT 下单人数 ) COMMENT 各品牌商品下单统计 ROW FORMAT DELIMITED FIELDS TERMINATED BY \t -- TSV格式制表符分隔 LOCATION /warehouse/gmall/ads/ads_order_stats_by_tm/;2.2 字段设计解析字段名数据类型说明设计考虑dtSTRING统计日期固定日期格式便于时间维度分析recent_daysBIGINT最近天数支持多时间窗口分析1/7/30天tm_idSTRING品牌ID业务主键用于关联tm_nameSTRING品牌名称便于直接展示减少关联查询order_countBIGINT下单数核心指标统计订单数量order_user_countBIGINT下单人数统计独立用户数反映用户覆盖三、数据装载策略详解3.1 整体装载逻辑-- 为什么采用查询历史数据 UNION 新增数据的模式 insert overwrite table ads_order_stats_by_tm select * from ads_order_stats_by_tm -- 步骤1先查询历史所有数据 union -- 步骤2UNION去重合并 -- 新增数据计算部分...设计原因分析容错性考虑脚本可能因资源不足失败重新执行时需要保留历史数据数据完整性确保不会因部分失败导致数据丢失幂等性设计支持重复执行结果一致3.2 UNION vs UNION ALL选择-- 使用UNION而非UNION ALL的原因 -- 1. UNION会自动去重防止重复插入 -- 2. 当脚本重复执行时避免生成重复记录 -- 3. 虽然性能稍差但数据量小可接受 -- UNION ALL示例不去重 SELECT id FROM table1 UNION ALL -- 合并所有行包括重复 SELECT id FROM table2; -- UNION示例去重 SELECT id FROM table1 UNION -- 合并并去重 SELECT id FROM table2;四、核心SQL代码逐行解析4.1 最近1天数据处理select 1 recent_days, -- 固定值表示统计最近1天 tm_id, tm_name, sum(order_count_1d) order_count, -- 聚合函数对单日订单数求和 count(distinct(user_id)) order_user_count -- 去重统计独立用户数 from dws_trade_user_sku_order_1d where dt2022-06-08 -- 筛选特定日期 group by tm_id, tm_name -- 按品牌分组关键函数解析SUM()聚合函数计算总和COUNT(DISTINCT)统计不重复值的数量GROUP BY分组子句配合聚合函数使用4.2 最近7天和30天数据处理select recent_days, tm_id, tm_name, sum(order_count), -- 对炸裂后的订单数求和 count(distinct(if(order_count0,user_id,null))) -- 条件去重统计 from ( select recent_days, user_id, tm_id, tm_name, case recent_days -- CASE条件表达式 when 7 then order_count_7d when 30 then order_count_30d end order_count from dws_trade_user_sku_order_nd lateral view explode(array(7,30)) tmp as recent_days -- 炸裂函数 where dt2022-06-08 )t1 group by recent_days, tm_id, tm_name高级函数详解1. LATERAL VIEW EXPLODE()-- 原始数据一行包含7日和30日数据 -- 炸裂后每个recent_days值生成一行 lateral view explode(array(7,30)) tmp as recent_days -- 示例 -- 原始order_count_7d100, order_count_30d500 -- 炸裂后 -- 行1: recent_days7, order_count100 -- 行2: recent_days30, order_count5002. CASE WHEN表达式-- 根据条件返回不同值 case recent_days when 7 then order_count_7d when 30 then order_count_30d else 0 -- 可选的默认值 end -- 等同于 if(recent_days7, order_count_7d, if(recent_days30, order_count_30d, 0))3. COUNT(DISTINCT IF())-- 条件去重统计 count(distinct(if(order_count0, user_id, null))) -- IF函数条件成立返回user_id否则返回null -- COUNT不统计null值实现只统计有订单的用户4.3 GROUP BY分组原理-- 分组规则示例 group by recent_days, tm_id, tm_name -- 情况1上下级关系如省份-城市 -- 以最细粒度城市为基准分组 group by city_id, province_name -- province_name只作为展示 -- 情况2关联关系如用户ID-用户名 -- 以唯一标识用户ID为基准 group by user_id, user_name -- user_name不会影响分组 -- 情况3无关联字段如用户ID-商品ID -- 将两个字段组合作为分组键 group by user_id, product_id -- 每个组合唯一五、性能优化与最佳实践5.1 存储优化-- ADS层存储特点 -- 1. 行式存储适合频繁全表扫描的小数据量场景 -- 2. TSV格式简单高效兼容性好 -- 3. GZIP压缩文本压缩率高查询时自动解压 -- 4. 小文件合并定期合并减少文件数量5.2 查询优化技巧-- 1. 使用分区过滤虽然ADS层通常不分区 where dt2022-06-08 -- 2. 避免全表扫描 create index on ads_order_stats_by_tm(tm_id) -- 如果支持索引 -- 3. 合理使用统计信息 analyze table ads_order_stats_by_tm compute statistics;六、扩展思考与改进建议6.1 可能的问题与解决方案问题解决方案代码示例数据量增长增加dt分区PARTITIONED BY (dt STRING)查询性能下降增加索引或物化视图CREATE INDEX idx_tm ON ...数据重复风险使用MERGE语句MERGE INTO ads_table USING ...6.2 监控与维护-- 1. 数据质量检查 select count(*) as total_rows, count(distinct dt) as date_count, min(dt) as earliest_date, max(dt) as latest_date from ads_order_stats_by_tm; -- 2. 存储空间监控 hdfs dfs -du -h /warehouse/gmall/ads/ads_order_stats_by_tm/ -- 3. 数据新鲜度检查 select max(dt) as latest_load_time from ads_order_stats_by_tm;七、总结ADS层作为数据仓库的最后一公里其设计需要平衡多个因素业务友好性字段命名直观符合业务术语性能考量针对小数据量优化快速响应查询数据一致性通过合理的装载策略确保数据准确可维护性代码清晰便于理解和修改通过本例的品牌商品下单统计表我们可以看到如何将DWS层的明细数据转化为业务可用的聚合指标这种模式在ADS层设计中具有典型性和代表性。