Databricks SQL免循环实现按日期维度分组统计方法

你不需要使用循环逻辑，这类逐基准日期的统计场景，用构造维度表+关联聚合的集合式写法即可，性能远高于循环遍历，代码维护性也更好。 #

核心逻辑 #

1. 先生成你需要统计的时间范围内所有连续日期的维度表，再和源表的去充分组做笛卡尔积，得到所有「统计日期+分组」的全量基准组合，覆盖你要统计的每一个粒度
2. 用基准集左连源业务表，把你之前硬编码固定日期的聚合条件，替换为和基准日期的比较逻辑即可

可直接复用的代码（以MySQL 8.0/PostgreSQL/Spark SQL等支持递归CTE的引擎为例） #

WITH RECURSIVE date_dim AS (
    -- 按需调整统计的起止日期，这里默认取近4年的日期范围
    SELECT DATE_SUB(CURRENT_DATE, INTERVAL 4 YEAR) AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_dim
    WHERE stat_date < CURRENT_DATE
),
group_dim AS (
    -- 提取源表所有分组，避免遗漏分组维度
    SELECT DISTINCT `Group` FROM dataframe
),
base_stat_dim AS (
    -- 生成所有[统计日期, 分组]的统计基准组合
    SELECT stat_date AS `Date`, `Group`
    FROM date_dim CROSS JOIN group_dim
)
SELECT
    b.`Date`,
    b.`Group`,
    SUM(CASE WHEN d.ATP_Date < b.`Date` AND d.JTH_Date > b.`Date` THEN 1 ELSE 0 END) AS CountNo,
    SUM(CASE WHEN d.ATP_Date < b.`Date` AND d.JTH_Date IS NULL THEN 1 ELSE 0 END) AS CountYes,
    -- 加除零保护，避免CountYes为0时语句报错
    ROUND(
        CASE WHEN SUM(CASE WHEN d.ATP_Date < b.`Date` AND d.JTH_Date IS NULL THEN 1 ELSE 0 END) = 0 THEN NULL
             ELSE SUM(CASE WHEN d.ATP_Date < b.`Date` AND d.JTH_Date > b.`Date` THEN 1 ELSE 0 END) * 1.0
             / SUM(CASE WHEN d.ATP_Date < b.`Date` AND d.JTH_Date IS NULL THEN 1 ELSE 0 END)
        END,
    4) AS `Ratio (No/Yes)`
FROM base_stat_dim b
LEFT JOIN dataframe d
    ON b.`Group` = d.`Group`
GROUP BY b.`Date`, b.`Group`
-- 按需过滤无有效数据的日期，减少无效结果
HAVING CountNo + CountYes > 0
ORDER BY b.`Date`, b.`Group`;

其他引擎适配提示 #

• 如果你用BigQuery/Hive/ClickHouse等内置日期序列函数的引擎，不需要写递归CTE，直接用内置函数生成日期维度即可，以BigQuery为例：

  -- 替换上面代码里的date_dim CTE即可
  WITH date_dim AS (
      SELECT stat_date FROM UNNEST(
          GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 4 YEAR), CURRENT_DATE(), INTERVAL 1 DAY)
      ) AS stat_date
  )
  

你原有代码的问题 #

• 硬编码了固定统计日期2022-05-18，没有生成全量日期维度，所以只能返回单条结果
• 分组维度只加了Group，漏了日期维度，无法按日期粒度聚合
• 写条件时混写了ATP_Date和ATP_date，大小写敏感的引擎会直接报错
• 计算Ratio时没有加除零保护，遇到CountYes为0的日期会抛出除以0的错误

为什么不推荐循环 #

SQL是面向集合的计算引擎，循环属于逐行处理的行式思维，不仅代码冗余、维护成本高，在大数据量下性能会比集合关联写法低1~2个数量级，所有需要遍历维度值统计的场景，都优先用「构造维度表+关联聚合」的方案实现。

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