如何按ID和月份获取最新事件列表，统计月度末7天无事件的ID数

核心逻辑拆解 #

要实现2021、2022年每个月末的统计需求，需要完成两个关键步骤：

• 生成目标年份的所有月末日期数据集
• 针对每个月末日期，筛选出该月末前7天内无任何事件记录且此前有过事件记录的唯一Id，统计其数量

方案一：基于事件日期范围判断（通用SQL语法，以BigQuery为例） #

-- 生成2021、2022年所有月末日期
WITH month_end_dates AS (
  SELECT 
    DATE_TRUNC(date, MONTH) + INTERVAL 1 MONTH - INTERVAL 1 DAY AS month_end
  FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2022-12-31', INTERVAL 1 MONTH)) AS date
),

-- 标记每个Id在对应月末前7天内是否有事件
id_activity_check AS (
  SELECT
    med.month_end,
    t.Id,
    -- 若月末前7天内有事件则标记为1，否则为0
    MAX(CASE WHEN t.date BETWEEN DATE_SUB(med.month_end, INTERVAL 7 DAY) AND med.month_end THEN 1 ELSE 0 END) AS has_event_in_last7d
  FROM month_end_dates med
  -- 仅关联月末前有过事件的Id，排除从未产生事件的无效Id
  LEFT JOIN `table` t ON t.date <= med.month_end
  GROUP BY med.month_end, t.Id
)

-- 统计每个月末符合条件的Id数量
SELECT
  month_end,
  COUNT(DISTINCT Id) AS inactive_id_count
FROM id_activity_check
WHERE has_event_in_last7d = 0
  AND Id IS NOT NULL
GROUP BY month_end
ORDER BY month_end;

方案二：基于最后事件日期优化（性能更优） #

如果需求等价于Id的最后一次事件发生在月末前7天更早的时间，可以先聚合每个Id的最后事件日期，再关联判断：

-- 生成2021、2022年所有月末日期
WITH month_end_dates AS (
  SELECT 
    DATE_TRUNC(date, MONTH) + INTERVAL 1 MONTH - INTERVAL 1 DAY AS month_end
  FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2022-12-31', INTERVAL 1 MONTH)) AS date
),

-- 获取每个Id的最后事件日期
latest_event_per_id AS (
  SELECT
    Id,
    MAX(date) AS last_event_date
  FROM `table`
  GROUP BY Id
)

-- 统计每个月末最后事件早于月末前7天的Id数量
SELECT
  med.month_end,
  COUNT(DISTINCT lepi.Id) AS inactive_id_count
FROM month_end_dates med
LEFT JOIN latest_event_per_id lepi 
  ON lepi.last_event_date <= DATE_SUB(med.month_end, INTERVAL 7 DAY)
  AND lepi.last_event_date <= med.month_end
GROUP BY med.month_end
ORDER BY med.month_end;

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