如何按月份统计存续开放项数量并生成对应图表？

一、Excel 实操步骤 #

如果用Excel处理表格，按以下步骤来：

• 先列全需要统计的月份：在空白列（比如E列）逐个输入要统计的年月，格式统一为YYYY-MM（比如2023-01），覆盖所有项目的open date到当前的所有月份。
• 标记每个项目在对应月份是否开放：假设open date在A列、closed date在B列、status在C列，目标年月在E2，在F2输入公式：

  =IF(AND(E2>=TEXT(A2,"YYYY-MM"), (B2="")+E2<=TEXT(B2,"YYYY-MM"), C2="开放"), 1, 0)
  

  下拉填充所有行和月份对应的单元格。公式里考虑了项目未关闭（closed date为空）的情况，只要当前月份在open date之后且状态是开放，就算作有效。
• 汇总月度数量：用数据透视表，把E列的年月拖到「行」，F列的标记值拖到「值」并选择「求和」，直接得到每个月的开放项目数，之后就能生成图表。

二、SQL 实现方法 #

如果数据存在数据库中，用SQL直接生成统计结果：

• 先生成所有需要统计的月份区间：

  WITH months AS (
    SELECT DATE_TRUNC('month', MIN(open_date)) AS stat_month
    FROM your_table
    UNION ALL
    SELECT DATE_TRUNC('month', stat_month + INTERVAL '1 month')
    FROM months
    WHERE stat_month + INTERVAL '1 month' <= DATE_TRUNC('month', CURRENT_DATE)
  )
  

• 关联原表统计开放项目数：

  SELECT
    m.stat_month,
    COUNT(DISTINCT t.id) AS open_project_count
  FROM months m
  LEFT JOIN your_table t
    ON m.stat_month >= DATE_TRUNC('month', t.open_date)
    AND (t.closed_date IS NULL OR m.stat_month <= DATE_TRUNC('month', t.closed_date))
    AND t.status = '开放'
  GROUP BY m.stat_month
  ORDER BY m.stat_month;
  

  这段SQL会把每个项目的开放区间和统计月份匹配，统计每个月内处于开放状态的项目数量（用DISTINCT避免重复计数同一个项目）。

三、关于你的矩阵标记思路 #

你的思路完全可行，本质就是把每个项目的开放周期拆分成对应的月份，给每个符合的月份打1，再汇总求和。上面的两种方法都是这个思路的落地实现：Excel是逐单元格判断标记，SQL是通过关联自动生成每个项目对应的统计月份并计数，不用手动建矩阵，效率更高。

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