如何修改PostgreSQL查询以获取过去12个月的月度统计数据？

字段说明 #

• jiraaction.created：评论创建时间
• jiraissue.created：工单创建时间

修改思路 #

原查询仅限定统计当月数据，且未按月份分组聚合。要实现过去12个月的月度统计，需要：

1. 调整时间过滤条件，覆盖过去12个月的工单
2. 按工单创建的月份进行分组，确保每个月的统计独立计算
3. 最终输出每个月份对应的平均首次响应时间

修改后的查询代码 #

WITH monthly_first_responses AS (
    SELECT
        DATE_TRUNC('month', jiraissue.created)::DATE AS month_start,
        jiraissue.pkey,
        MIN(AGE(jiraaction.created, jiraissue.created)) AS first_response_time
    FROM jiraaction
    INNER JOIN jiraissue ON jiraaction.issueid = jiraissue.id
    WHERE
        jiraissue.pkey LIKE '%TPAGENT-%'
        -- 筛选过去12个月的工单，确保覆盖完整月度周期
        AND jiraissue.created >= DATE_TRUNC('month', NOW() - INTERVAL '11 months')::DATE
        AND jiraissue.created < DATE_TRUNC('month', NOW() + INTERVAL '1 month')::DATE
    GROUP BY month_start, jiraissue.pkey
)
SELECT
    month_start AS month,
    AVG(first_response_time) AS avg_first_response_time
FROM monthly_first_responses
GROUP BY month_start
ORDER BY month_start DESC;

关键修改点说明 #

• 时间范围调整：用NOW() - INTERVAL '11 months'获取12个月前的月初，搭配< DATE_TRUNC('month', NOW() + INTERVAL '1 month')确保只包含过去12个完整自然月的工单数据
• 分组维度新增：在CTE中加入month_start（工单创建月份）作为分组字段，保证每个工单的首次响应时间归属到对应的月份
• 最终统计逻辑：按月份分组计算平均响应时间，按月份倒序输出（最新月份在前）

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