计算 MySQL 查询结果中各分类分钟数占总量的百分比-php教程-PHP中文网

计算 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 <blockquote><p>? 关键点：SUM(SUM(...)) OVER() 是「聚合函数嵌套窗口函数」——内层 SUM() 按分组计算每类秒数总和，外层 SUM(...) OVER() 对所有分组结果求全局和，无需 JOIN 或子查询。</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill10629" title="kashen-mysql DDLtoExcel"><img
														src="https://img.php.cn/upload/skill/001/246/273/6adaf20e352aa140.jpg" alt="kashen-mysql DDLtoExcel" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/xiazai/skill10629" title="kashen-mysql DDLtoExcel" class="overflowclass">kashen-mysql DDLtoExcel</a>
													<p class="overflowclass">解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。</p>
												</div>
												<a rel="nofollow" href="/xiazai/skill10629" title="kashen-mysql DDLtoExcel" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div></blockquote><h3>⚠️ 兼容方案：使用子查询（适配 MySQL 5.7 及以下）</h3><p>若环境为旧版 MySQL，可采用派生表（Derived Table）方式，但需注意参数绑定重复问题（如原提问中 ? 出现多次，PHP PDO 需传入相同值多次）：</p><pre class="brush:php;toolbar:false;">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 dte <p>✅ <strong>优势</strong>：逻辑分离清晰，易于调试；<br>
⚠️ <strong>注意</strong>：? 占位符需在 PHP 中按出现顺序传入<strong>相同日期参数共 4 次</strong>（t1 和 t2 各两次），建议用命名参数（:start_date, :end_date）提升可读性。</p><h3>? 使用建议与注意事项</h3>

空值防护：务必保留 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。