如何在Metabase中按月份获取各月最优100条Incident记录？

我在Metabase中使用以下SQL查询：

SELECT
  date_trunc('month', "resolvedAt") AS "resolvedAt",
  (CAST(avg("solution_time") AS float) / CASE WHEN 3600.0 = 0 THEN NULL ELSE 3600.0 END) AS "Average Resolution Time",
  12 AS "Minium",
  8 AS "Expected"
FROM "custom_Incident"
RIGHT JOIN "ims_Incident" ON "incident" = "ims_Incident"."id"
WHERE {{resolveAt}}
  AND {{assigned_group_name}}
  AND {{service}}
  AND {{incident_type}}
  AND "statusDetail" = 'Closed'
  AND "ims_Incident"."uid" NOT IN
    (SELECT "uid"
     FROM "ignore_Incident")
  AND "ims_Incident"."uid" IN
    (SELECT "incident_id"
     FROM "custom_Incident"
     WHERE "custom_Incident"."incident_id" in
         (SELECT "uid"
          FROM "ims_Incident"
          WHERE {{resolveAt}}
            AND {{assigned_group_name}}
            AND {{service}}
            AND {{incident_type}}
            AND "statusDetail" = 'Closed'
            AND "ims_Incident"."uid" NOT IN
              (SELECT "uid"
               FROM "ignore_Incident"))
     ORDER BY "solution_time"
     LIMIT {{take_into_account}})
GROUP BY date_trunc('month', "ims_Incident"."resolvedAt")

当前查询会在{{resolveAt}}整个时间范围内选取{{take_into_account}}（如100条）按solution_time排序的最优Incident记录。我需要修改查询，使其按{{resolveAt}}涵盖的每个月份，分别获取各月前100条按solution_time排序的最优Incident记录（若时间范围包含3个月，则总计300条），请问该如何调整SQL语句实现此需求？

调整后的SQL查询 #

核心是用窗口函数按月份分组排序，筛选出每月的前N条最优记录，再聚合计算平均值：

WITH filtered_incidents AS (
    SELECT
        i."uid",
        i."resolvedAt",
        c."solution_time",
        -- 按月份分组，每条记录按solution_time升序（耗时越短越优）分配序号
        ROW_NUMBER() OVER (PARTITION BY date_trunc('month', i."resolvedAt") ORDER BY c."solution_time") AS rn
    FROM "ims_Incident" i
    JOIN "custom_Incident" c ON i."id" = c."incident"
    WHERE {{resolveAt}}
      AND {{assigned_group_name}}
      AND {{service}}
      AND {{incident_type}}
      AND i."statusDetail" = 'Closed'
      AND i."uid" NOT IN (SELECT "uid" FROM "ignore_Incident")
),
top_monthly_incidents AS (
    SELECT *
    FROM filtered_incidents
    WHERE rn <= {{take_into_account}} -- 保留每个月的前N条最优记录
)
SELECT
    date_trunc('month', "resolvedAt") AS "resolvedAt",
    (CAST(avg("solution_time") AS float) / 3600.0) AS "Average Resolution Time",
    12 AS "Minium",
    8 AS "Expected"
FROM top_monthly_incidents
GROUP BY date_trunc('month', "resolvedAt")
ORDER BY "resolvedAt";

关键改动说明 #

• 用CTE拆分逻辑：将过滤、排序筛选、聚合分成三个步骤，可读性更强
• 窗口函数实现按月取前N：ROW_NUMBER() OVER (PARTITION BY 月份 ORDER BY solution_time)给每个月内的记录按耗时排序编号，再筛选编号≤{{take_into_account}}的记录
• 优化关联方式：把原RIGHT JOIN改为INNER JOIN，避免引入无solution_time的无效记录（原查询中RIGHT JOIN的逻辑实际不需要）
• 简化计算：移除了无意义的CASE判断，直接除以3600.0转换为小时单位
• 增加排序：最终结果按月份升序排列，展示更规整

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