基于Timestamp字段计算每日耗时平均值的SQL实现咨询

嘿，我来帮你搞定这个问题！你的日历表正好是关键工具——它能帮我们把每个工单的跨天耗时拆分成对应每一天的片段，之后再计算每日平均就很简单了。

核心思路 #

我们需要先把每个工单的耗时按日期拆分：

• 创建当天：从工单创建时间到当天23:59:59的耗时
• 中间日期：全天24小时（1440分钟）
• 解决当天：从当天00:00:00到工单解决时间的耗时

之后再按日期聚合，计算每日的平均耗时。

具体SQL实现 #

假设你的业务表叫tickets，字段是created（Timestamp）、resolved（Timestamp）；日历表叫calendar，字段是date（Date类型）。

第一步：拆分工单到对应日期并计算单日耗时

SELECT
  t.id,
  c.date,
  -- 计算单条工单在当前日期的耗时分钟数
  CASE
    -- 当天是工单创建日
    WHEN c.date = DATE(t.created) THEN
      TIMESTAMPDIFF(MINUTE, t.created, LEAST(t.resolved, c.date + INTERVAL 1 DAY))
    -- 当天是工单解决日
    WHEN c.date = DATE(t.resolved) THEN
      TIMESTAMPDIFF(MINUTE, c.date, t.resolved)
    -- 中间日期，全天1440分钟
    ELSE 1440
  END AS mins
FROM tickets t
JOIN calendar c
  -- 关联工单覆盖的所有日期
  ON c.date BETWEEN DATE(t.created) AND DATE(t.resolved)

第二步：计算每日平均耗时

基于上面的结果，按日期分组求和后除以当天的工单数量：

SELECT
  date,
  -- 保留两位小数，不需要的话可以去掉ROUND
  ROUND(SUM(mins) / COUNT(DISTINCT id), 2) AS avg_daily_mins
FROM (
  -- 嵌套第一步的查询
  SELECT
    t.id,
    c.date,
    CASE
      WHEN c.date = DATE(t.created) THEN
        TIMESTAMPDIFF(MINUTE, t.created, LEAST(t.resolved, c.date + INTERVAL 1 DAY))
      WHEN c.date = DATE(t.resolved) THEN
        TIMESTAMPDIFF(MINUTE, c.date, t.resolved)
      ELSE 1440
    END AS mins
  FROM tickets t
  JOIN calendar c
    ON c.date BETWEEN DATE(t.created) AND DATE(t.resolved)
) AS daily_mins
GROUP BY date
ORDER BY date;

匹配你的示例数据 #

如果只有你给出的那一条工单，执行后会得到和你期望完全一致的结果：

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