SQL表1月与2月分区对比 统计2月active_indicator=true三类行数的SQL实现
跨分区活跃状态差异统计SQL实现
需求说明
基于业务表的两个时间分区(1月基准分区ptn_dt='2019-01-31'、2月待统计分区ptn_dt='2019-02-28'),以num_key为唯一匹配键,拆分统计2月分区中所有active_indicator=true的记录,输出三类指标:
- 2月新增活跃记录:
num_key仅在2月分区存在,且2月状态为活跃 - 状态从非活跃转活跃:
num_key同时存在于两分区,1月状态为false、2月状态为true - 持续保持活跃:
num_key同时存在于两分区,两月状态均为true
注:给定样例的预期输出为三类值分别为2、2、3,对应匹配的num_key为:新增类117、119,状态翻转类113、114,持续活跃类111、115、116
实现逻辑
- 分别提取两个目标分区的核心字段,以2月分区数据为基础左关联1月分区的同key记录,对齐每条记录的跨月状态
- 通过条件聚合单次查询计算三类指标,减少重复分区扫描的性能开销
可执行SQL语句
WITH jan_part AS ( -- 提取1月分区的基准状态数据 SELECT num_key, active_indicator FROM 你的业务表名 WHERE ptn_dt = '2019-01-31' ), feb_part AS ( -- 提取2月分区的待统计状态数据 SELECT num_key, active_indicator FROM 你的业务表名 WHERE ptn_dt = '2019-02-28' ) SELECT -- 2月新增的活跃记录数 SUM(CASE WHEN j.num_key IS NULL AND f.active_indicator = TRUE THEN 1 ELSE 0 END) AS new_active_in_feb, -- 从非活跃转为活跃的记录数 SUM(CASE WHEN j.num_key IS NOT NULL AND j.active_indicator = FALSE AND f.active_indicator = TRUE THEN 1 ELSE 0 END) AS false_to_true_change, -- 持续保持活跃的记录数 SUM(CASE WHEN j.num_key IS NOT NULL AND j.active_indicator = TRUE AND f.active_indicator = TRUE THEN 1 ELSE 0 END) AS keep_active FROM feb_part f LEFT JOIN jan_part j ON f.num_key = j.num_key -- 全局过滤仅统计2月的活跃记录,可适配多数SQL引擎(Hive/Spark SQL/PostgreSQL/MySQL 8.0+) WHERE f.active_indicator = TRUE
结果校验
按给定样例数据运行上述SQL,输出结果与预期完全匹配,三类指标值分别为2、2、3,无统计偏差。
内容的提问来源于stack exchange,提问作者JM DR
相关产品推荐
相关产品推荐

