SQL按日期分组统计交易表回溯区间行数问题排查

原SQL有3个核心错误，导致结果不符合预期：

• 关联逻辑错误：where date(t.arrival_date) = dates.date直接将交易表数据限制为仅和统计日期同一天的记录，每个分组内没有历史日期的交易数据，根本无法统计回溯周期内的总数。
• 边界逻辑错误：用>做时间判断容易漏算边界日期的数据，且回溯周期的日期间隔计算和闭区间规则不匹配，比如近3天含当日的场景下，间隔值应该是2天而不是3天。
• 关联方式错误：用隐式笛卡尔积加等值过滤的写法，会自动过滤掉没有交易的日期，如果需要基于日历表补全所有日期（含0交易日期）的统计值，这种写法无法满足需求。

以下写法默认近N天包含统计当日，比如统计日期为2022-06-20，近3天为2022-06-18、2022-06-19、2022-06-20共3天，近1天为统计当日。如果需要不含当日的回溯逻辑，只需要调整日期边界即可。

方案1：基于dates日历表实现（支持补全无交易日期） #

这个方案兼容性最好，能返回日历表覆盖范围内所有日期的统计值，哪怕当天没有交易也会显示0：

SELECT
  d.date AS Arrival_date,
  -- 统计近3天（含当日）数据
  COUNT(t.Arrival_date) FILTER (
    WHERE DATE(t.Arrival_date) >= d.date - INTERVAL '2 day'
  ) AS nb3days,
  -- 统计近1天（当日）数据
  COUNT(t.Arrival_date) FILTER (
    WHERE DATE(t.Arrival_date) = d.date
  ) AS nb1day
FROM dates d
LEFT JOIN t
  ON DATE(t.Arrival_date) BETWEEN d.date - INTERVAL '2 day' AND d.date
-- 如果不需要返回无交易的日期，取消下面这行的注释即可
-- WHERE EXISTS (SELECT 1 FROM t t2 WHERE DATE(t2.Arrival_date) = d.date)
GROUP BY d.date
ORDER BY d.date;

注意点：

• 用LEFT JOIN明确关联逻辑，关联条件直接放开到回溯周期的整个时间范围，确保每个统计日期能拿到所有需要的历史交易记录
• 计数用COUNT(t.Arrival_date)而不是COUNT(*)，避免左连产生的空值被误计数
• 近1天的判断直接用等值匹配即可，比时间区间计算更直观不容易出错

方案2：窗口函数实现（无需日历表，仅返回有交易的日期） #

如果不需要补全无交易的日期，可以直接用窗口函数实现，写法更简洁，性能也更优：

WITH daily_stat AS (
  SELECT
    DATE(Arrival_date) AS dt,
    COUNT(*) AS daily_cnt
  FROM t
  GROUP BY DATE(Arrival_date)
)
SELECT
  dt AS Arrival_date,
  SUM(daily_cnt) OVER (
    ORDER BY dt RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
  ) AS nb3days,
  daily_cnt AS nb1day
FROM daily_stat
ORDER BY dt;

如果业务定义里「近N天」不包含统计当日，比如2022-06-20的近3天是2022-06-17、2022-06-18、2022-06-19，只需要做两处调整：

1. 方案1的关联条件改为DATE(t.Arrival_date) BETWEEN d.date - INTERVAL '3 day' AND d.date - INTERVAL '1 day'，nb3days的filter条件同步改为>= d.date - INTERVAL '3 day'，nb1day的filter条件改为DATE(t.Arrival_date) = d.date - INTERVAL '1 day'
2. 方案2的窗口范围改为RANGE BETWEEN INTERVAL '3 day' PRECEDING AND INTERVAL '1 day' PRECEDING，nb1day改为LAG(daily_cnt, 1) OVER (ORDER BY dt)即可。

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