MySQL如何统计30秒内被服务聊天的占比？

要计算符合筛选条件的聊天中，30秒内被服务的占比，核心是先拿到符合所有筛选规则的总聊天数，再拿到等待时长≤30秒的聊天数，最后用后者除以前者再乘以100得到百分比。

高效实现方案（单表扫描） #

直接用条件聚合函数在一次查询中完成统计，避免多次扫描表，SQL代码如下：

SELECT
    COUNT(c.id) AS '总聊天数',
    SUM(CASE WHEN c.wait_time <= 30 THEN 1 ELSE 0 END) AS '30秒内服务数',
    ROUND((SUM(CASE WHEN c.wait_time <= 30 THEN 1 ELSE 0 END) / COUNT(c.id)) * 100, 2) AS '30秒内服务占比(%)'
FROM lh_chat as c
    LEFT JOIN lh_departament as d ON c.dep_id = d.id
    INNER JOIN lh_users u ON c.user_id = u.id
WHERE FROM_UNIXTIME(c.time-10800, '%Y-%m-%d') BETWEEN '2023-01-01' AND '2023-01-31'
    AND u.id != 178
    AND c.`status` <> 0
    AND c.dep_id IN (7,19,23)

代码说明 #

• COUNT(c.id)：统计满足所有WHERE条件的总聊天记录数
• SUM(CASE WHEN c.wait_time <=30 THEN 1 ELSE 0 END)：遍历每条记录，符合等待时长≤30秒的记1，否则记0，求和后得到符合要求的数量
• ROUND(..., 2)：把百分比结果保留2位小数，可根据需求调整位数

另一种实现方式（子查询） #

如果更习惯分步统计，也可以用子查询分别获取总数和目标数，再计算占比：

SELECT
    target_count AS '30秒内服务数',
    total_count AS '总聊天数',
    ROUND((target_count / total_count) * 100, 2) AS '30秒内服务占比(%)'
FROM (
    SELECT
        COUNT(c.id) AS total_count,
        SUM(CASE WHEN c.wait_time <=30 THEN 1 ELSE 0 END) AS target_count
    FROM lh_chat as c
        LEFT JOIN lh_departament as d ON c.dep_id = d.id
        INNER JOIN lh_users u ON c.user_id = u.id
    WHERE FROM_UNIXTIME(c.time-10800, '%Y-%m-%d') BETWEEN '2023-01-01' AND '2023-01-31'
        AND u.id != 178
        AND c.`status` <> 0
        AND c.dep_id IN (7,19,23)
) AS stats

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