MySQL 5.5跨日期范围统计netID对应call_count的SQL修正问题

在MySQL 5.5环境下，使用datetime类型的logdate列查询最近5天数据时，出现call_count统计值不准确的问题：例如netID 9476实际应统计为4，原SQL返回3；netID 9477实际应统计为7，原SQL返回2。问题根源在于原SQL的日期范围判断逻辑、分组方式及子查询关联逻辑存在缺陷。

原SQL代码 #

SELECT
    netID,
    logdate,
    netcall,
    (
        SELECT COUNT(*)
        FROM NetLog sub
        WHERE sub.netID = NetLog.netID
        AND sub.logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
        AND sub.logdate <= CURDATE()
    ) AS call_count,
    (
        SELECT COUNT(DISTINCT netID)
        FROM NetLog
        WHERE logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
        AND logdate <= CURDATE()
    ) AS netID_count,
    logclosedtime
FROM NetLog
WHERE
    logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
    AND logdate <= CURDATE()
GROUP BY netID, netcall
ORDER BY netID DESC;

错误原因分析 #

1. 日期范围判断缺陷：CURDATE()返回日期类型（如2023-07-11），与datetime类型的logdate比较时，会自动转换为2023-07-11 00:00:00，导致当天非0点的记录被排除，统计范围不全。
2. 分组逻辑不符合需求：按netID, netcall分组会将同一netID下不同netcall的记录拆分为多个组，且非聚合字段（如logdate、logclosedtime）会随机取值；同时子查询的call_count虽统计该netID总记录数，但主查询分组结果会导致同一netID重复出现，不符合“每个netID统计总call_count”的核心需求。

修正后的SQL #

场景1：每个netID一行，展示总call_count及最新相关信息 #

适合需要按netID汇总统计的场景，同时展示该netID的最新日志时间等信息：

SELECT
    main.netID,
    MAX(main.logdate) AS latest_logdate,
    main.netcall,
    stats.call_count,
    total.total_netID_count,
    MAX(main.logclosedtime) AS latest_logclosedtime
FROM NetLog main
JOIN (
    SELECT
        netID,
        COUNT(*) AS call_count
    FROM NetLog
    WHERE logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
      AND logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY) -- 包含当天所有时间的记录
    GROUP BY netID
) stats ON main.netID = stats.netID
CROSS JOIN (
    SELECT COUNT(DISTINCT netID) AS total_netID_count
    FROM NetLog
    WHERE logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
      AND logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
) total
WHERE main.logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
  AND main.logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
GROUP BY main.netID, main.netcall, stats.call_count, total.total_netID_count
ORDER BY main.netID DESC;

场景2：保留每条符合条件的记录，附带对应netID的总call_count #

适合需要查看原始日志记录，同时知晓该netID在5天内总调用次数的场景：

SELECT
    nl.netID,
    nl.logdate,
    nl.netcall,
    stats.call_count,
    total.total_netID_count,
    nl.logclosedtime
FROM NetLog nl
JOIN (
    SELECT
        netID,
        COUNT(*) AS call_count
    FROM NetLog
    WHERE logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
      AND logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    GROUP BY netID
) stats ON nl.netID = stats.netID
CROSS JOIN (
    SELECT COUNT(DISTINCT netID) AS total_netID_count
    FROM NetLog
    WHERE logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
      AND logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
) total
WHERE nl.logdate >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
  AND nl.logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ORDER BY nl.netID DESC;

关键修正说明 #

• 日期范围修正：使用logdate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)替代logdate <= CURDATE()，确保包含当天所有时间的datetime记录。
• 预统计优化：通过子查询预先计算每个netID的call_count和全局的total_netID_count，避免重复子查询的性能损耗，同时保证统计准确性。
• 分组逻辑调整：根据业务需求选择合适的分组方式，要么按netID聚合得到汇总结果，要么保留原始记录并附带统计值，避免非聚合字段随机取值的问题。

内容的提问来源于stack exchange，提问作者Keith D Kaiser