如何在Azure DevOps中查询特定日期的工作项状态

核心逻辑 #

指定日期的工作项状态本质是历史快照数据，基于工作项的状态变更日志即可回溯：每个工作项的状态变更都会生成带时间戳的记录，对单个工作项来说，指定日期前最后一次变更后的状态，就是该工作项在对应日期的状态。

分场景实现方式 #

• 主流商用项目管理工具（Jira、Azure DevOps、TAPD等）
  这类工具大多原生支持历史状态快照查询，不需要手动处理变更日志：
  1. Jira可以直接用JQL的WAS/AS OF语法，示例语句：status WAS IN ("待处理", "开发中", "已上线") ON "2024/05/15"，直接返回指定日期对应状态的所有工作项
  2. Azure DevOps可以使用WIQL的ASOF关键字，直接指定查询的快照时间点
     按上述逻辑批量查询过去12个月每月15日的数据，导出后即可生成状态分布趋势图。
• 自研工作项系统
  只要系统留存了完整的状态变更流水表（核心字段：工作项ID、变更后状态、变更时间），即可通过SQL批量查询，示例逻辑如下：

  -- 查询2024年5月15日所有工作项的当前状态
  SELECT work_item_id, new_status AS snapshot_status
  FROM (
    SELECT 
      work_item_id,
      new_status,
      -- 按变更时间倒序，取每个工作项在指定日期前的最后一次变更
      ROW_NUMBER() OVER (PARTITION BY work_item_id ORDER BY change_time DESC) AS rank
    FROM work_item_status_change_log
    WHERE change_time <= '2024-05-15 23:59:59'
  ) tmp
  WHERE rank = 1
  

  批量替换日期为过去12个月的15日，即可拿到所有需要的统计数据。

注意事项 #

统计时需要将历史上存在、当前已删除/归档的工作项也纳入范围，否则会出现越早的快照数据量越少的偏差。

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