PostgreSQL如何实现两个运行总计相除计算累计终止门店占比

你遇到的累计开店数线性递增的问题，核心原因是没有先做日期维度的聚合，直接在门店明细表上做开窗累加，一行门店明细对应一次计数，最终累加结果就等于行号，和实际每个日期对应的累计开店数逻辑不符。

整体逻辑是先构造全量连续日期序列，再关联门店表统计每日开店、关店数量，最后做窗口累加计算占比：

• 第一步：生成从首个开店日期到当前日期的连续日期序列，避免遗漏无开店/关店动作的日期

  WITH RECURSIVE date_series AS (
      -- 取最早的开店日期作为序列起点
      SELECT MIN(opening_date) AS dt
      FROM your_store_table
      UNION ALL
      -- 递归生成后续日期直到当前日期
      SELECT DATE_ADD(dt, INTERVAL 1 DAY)
      FROM date_series
      WHERE dt < CURDATE()
  )
  

• 第二步：关联门店表，统计每个日期的当日新增开店数、当日终止营业数

  , daily_operation_stats AS (
      SELECT
          ds.dt,
          SUM(CASE WHEN t.opening_date = ds.dt THEN 1 ELSE 0 END) AS daily_open_cnt,
          SUM(CASE WHEN t.termination_date = ds.dt THEN 1 ELSE 0 END) AS daily_close_cnt
      FROM date_series ds
      LEFT JOIN your_store_table t
          ON ds.dt = t.opening_date 
          OR ds.dt = t.termination_date
      GROUP BY ds.dt
  )
  

• 第三步：计算累计值及终止占比，同步处理除以0的异常情况

  SELECT
      dt AS statistic_date,
      SUM(daily_open_cnt) OVER(ORDER BY dt) AS total_open_until_now,
      SUM(daily_close_cnt) OVER(ORDER BY dt) AS total_close_until_now,
      CASE 
          WHEN SUM(daily_open_cnt) OVER(ORDER BY dt) = 0 THEN 0
          ELSE ROUND(
              SUM(daily_close_cnt) OVER(ORDER BY dt) / SUM(daily_open_cnt) OVER(ORDER BY dt),
              4
          )
      END AS terminated_store_ratio
  FROM daily_operation_stats
  ORDER BY dt
  

• 如果你使用的数据库不支持递归CTE（比如低版本MySQL），可以提前维护一张独立的日期维度表替换上述date_series部分，后续逻辑完全不变
• 结果中的terminated_store_ratio保留了4位小数，可根据业务需要调整精度

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