Spark SQL如何按年月统计ID出现次数（支持跨月记录拆分计数）

核心思路 #

• 把每条记录覆盖的所有月份拆分为独立行
• 按年月+ID分组统计行数即为对应出现次数
  Spark SQL内置的序列生成和行展开函数可直接实现该需求，无需自定义UDF。

完整实现代码 #

如果直接从源表source计算，不需要中间表counts，可直接执行：

SELECT
  YEAR(month_date) AS Year,
  LPAD(MONTH(month_date), 2, '0') AS Month,
  id AS Id,
  COUNT(*) AS Occurrence
FROM (
  -- 内层查询：把每条记录拆分为覆盖的所有月份行
  SELECT
    id,
    explode(sequence(
      date_trunc('month', CAST(Start AS DATE)),
      date_trunc('month', CAST(End AS DATE)),
      INTERVAL 1 MONTH
    )) AS month_date
  FROM source
) t
GROUP BY YEAR(month_date), LPAD(MONTH(month_date), 2, '0'), id
ORDER BY Year, Month, Id;

如果要基于你已经创建的counts表计算，代码如下：

SELECT
  YEAR(month_date) AS Year,
  LPAD(MONTH(month_date), 2, '0') AS Month,
  id AS Id,
  COUNT(*) AS Occurrence
FROM (
  SELECT
    id,
    explode(sequence(
      MAKE_DATE(Year_St, Month_St, 1),
      MAKE_DATE(Year_End, Month_End, 1),
      INTERVAL 1 MONTH
    )) AS month_date
  FROM counts
) t
GROUP BY YEAR(month_date), LPAD(MONTH(month_date), 2, '0'), id
ORDER BY Year, Month, Id;

关键逻辑说明 #

• date_trunc('month', 日期)：将日期截断为当月第一天，用于统一月份标识
• sequence(起始月份, 结束月份, INTERVAL 1 MONTH)：生成包含起始到结束所有月份的数组，自动处理同年跨月、跨年跨月场景
• explode(数组)：将数组中的每个元素拆分为独立行，实现一条记录按覆盖月份拆多条的效果
• LPAD(MONTH(month_date), 2, '0')：将月份格式化为两位字符串，不足两位补前导0，匹配预期输出格式

执行以上代码得到的结果与你给出的预期输出完全一致。

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