MySQL 分类分钟占比计算方法

本文介绍如何在单条 SQL 查询中直接计算各分类（cat）的分钟数（minutes）占总体分钟数的百分比，避免 PHP 侧二次计算，提升效率与可维护性。核心思路是使用子查询或 CTE 获取总分钟数，并通过 CROSS JOIN 或窗口函数实现比例计算。

本文介绍如何在单条 SQL 查询中直接计算各分类（cat）的分钟数（minutes）占总体分钟数的百分比，避免 PHP 侧二次计算，提升效率与可维护性。核心思路是使用子查询或 CTE 获取总分钟数，并通过 CROSS JOIN 或窗口函数实现比例计算。

在分析时间类业务数据（如工时、服务时长）时，常需按分类（如 cat）统计总分钟数并计算其占全局总量的百分比。虽然可在 PHP 中先查出明细再查总量、最后循环计算，但更优雅高效的方式是在 MySQL 层一次性完成——既减少网络往返，又保证原子性与一致性。

✅ 推荐方案：使用窗口函数（MySQL 8.0+，最简洁可靠）

若您的 MySQL 版本 ≥ 8.0，强烈推荐使用 SUM() OVER() 窗口函数，语义清晰、性能优异：

SELECT 
  COUNT(DISTINCT nid) AS tagged,
  cat,
  SEC_TO_TIME(AVG(TIME_TO_SEC(duration))) AS duration,
  ROUND(SUM(TIME_TO_SEC(duration)) / 60, 0) AS minutes,
  CONCAT(
    ROUND(
      (SUM(TIME_TO_SEC(duration)) / 60) * 100.0 / 
      SUM(SUM(TIME_TO_SEC(duration))) OVER(), 
      2
    ), 
    '%'
  ) AS `Percent`
FROM client_note
JOIN client_note_tag_items ON client_note_tag_items.note_id = client_note.nid
LEFT JOIN client_note_tags ON client_note_tags.tag_id = client_note_tag_items.tag_id
WHERE dte >= ? 
  AND dte <= ? 
  AND name NOT LIKE 'Resolution%' 
  AND duration IS NOT NULL
GROUP BY cat
ORDER BY cat ASC;

? 关键点：SUM(SUM(...)) OVER() 是「聚合函数嵌套窗口函数」——内层 SUM() 按分组计算每类秒数总和，外层 SUM(...) OVER() 对所有分组结果求全局和，无需 JOIN 或子查询。

⚠️ 兼容方案：使用子查询（适配 MySQL 5.7 及以下）

若环境为旧版 MySQL，可采用派生表（Derived Table）方式，但需注意参数绑定重复问题（如原提问中 ? 出现多次，PHP PDO 需传入相同值多次）：

SELECT 
  t1.tagged,
  t1.cat,
  t1.duration,
  t1.minutes,
  CONCAT(ROUND(t1.minutes * 100.0 / t2.total_minutes, 2), '%') AS `Percent`
FROM (
  SELECT 
    COUNT(DISTINCT nid) AS tagged,
    cat,
    SEC_TO_TIME(AVG(TIME_TO_SEC(duration))) AS duration,
    ROUND(SUM(TIME_TO_SEC(duration)) / 60, 0) AS minutes
  FROM client_note
  JOIN client_note_tag_items ON client_note_tag_items.note_id = client_note.nid
  LEFT JOIN client_note_tags ON client_note_tags.tag_id = client_note_tag_items.tag_id
  WHERE dte >= ? 
    AND dte <= ? 
    AND name NOT LIKE 'Resolution%' 
    AND duration IS NOT NULL
  GROUP BY cat
) AS t1
CROSS JOIN (
  SELECT ROUND(SUM(TIME_TO_SEC(duration)) / 60, 0) AS total_minutes
  FROM client_note
  JOIN client_note_tag_items ON client_note_tag_items.note_id = client_note.nid
  LEFT JOIN client_note_tags ON client_note_tags.tag_id = client_note_tag_items.tag_id
  WHERE dte >= ? 
    AND dte <= ? 
    AND name NOT LIKE 'Resolution%' 
    AND duration IS NOT NULL
) AS t2
ORDER BY t1.cat ASC;

✅ 优势：逻辑分离清晰，易于调试；
⚠️ 注意：? 占位符需在 PHP 中按出现顺序传入相同日期参数共 4 次（t1 和 t2 各两次），建议用命名参数（:start_date, :end_date）提升可读性。

? 使用建议与注意事项

• 空值防护：务必保留 AND duration IS NOT NULL，否则 TIME_TO_SEC(NULL) 返回 NULL，导致整行聚合失效；
• 精度控制：ROUND(..., 2) 控制小数位，CONCAT(..., '%') 直接输出带百分号的字符串，前端可直接展示；
• 性能优化：为 dte、name、关联字段（note_id, tag_id）建立复合索引，例如：

  ALTER TABLE client_note ADD INDEX idx_dte_name (dte, name);
  ALTER TABLE client_note_tag_items ADD INDEX idx_note_id (note_id);

• PHP 绑定示例（PDO 命名参数）：

  $stmt = $db->prepare($sql);
  $stmt->execute([
    ':start_date' => $from,
    ':end_date'   => $to,
    ':start_date2'=> $from,
    ':end_date2'  => $to
  ]);

综上，优先采用窗口函数方案（MySQL 8.0+）；若受限于版本，则用派生表 + CROSS JOIN 安全可靠。避免在 PHP 中手动累加计算百分比——既增加代码复杂度，又可能因浮点误差或逻辑遗漏引入 Bug。