MySQL中统计DURATION_IN_MS列数值区间计数问题求助

问题中的核心错误 #

1. 语法错误：MySQL不支持TOP关键字，需替换为LIMIT
2. 日期格式错误：2022-11.10是非法日期格式，必须改为2022-11-10（用横杠分隔月日）
3. 分组逻辑错误：原语句GROUP BY DURATION_IN_MS是按原始数值列分组，而非CASE生成的区间别名，导致统计结果混乱

修正后的SQL代码 #

WITH tab1 AS(
SELECT * 
FROM MACHINE_PROCESSING_DURATION_EVALUATION
WHERE START_TIMESTAMP BETWEEN '2022-11-10 00:00:00.000' AND '2022-11-10 22:00:00.000'
LIMIT 30)

SELECT
  CASE 
    WHEN DURATION_IN_MS BETWEEN 115000 AND 119999 THEN '115000-120000'
    WHEN DURATION_IN_MS BETWEEN 120000 AND 124999 THEN '120000-125000'
    ELSE 'OTHERS'
  END AS duration_interval, 
  COUNT(*) AS cnt
FROM tab1
GROUP BY duration_interval;

关键优化点 #

• 调整区间边界：将BETWEEN 120000 AND 125000改为BETWEEN 120000 AND 124999，避免数值120000被两个区间重复匹配（BETWEEN包含两端值）
• 区分别名：给CASE结果命名为duration_interval，避免与原始列名DURATION_IN_MS冲突，分组逻辑更清晰
• 若需要显示所有区间（包括计数为0的区间），可以预先定义区间列表，再通过左连接完成统计：

WITH intervals AS (
    SELECT '115000-120000' AS duration_interval UNION ALL
    SELECT '120000-125000' UNION ALL
    SELECT 'OTHERS'
),
tab1 AS(
SELECT * 
FROM MACHINE_PROCESSING_DURATION_EVALUATION
WHERE START_TIMESTAMP BETWEEN '2022-11-10 00:00:00.000' AND '2022-11-10 22:00:00.000'
LIMIT 30)
SELECT
    i.duration_interval,
    COUNT(t.DURATION_IN_MS) AS cnt
FROM intervals i
LEFT JOIN tab1 t ON 
    CASE 
        WHEN t.DURATION_IN_MS BETWEEN 115000 AND 119999 THEN '115000-120000'
        WHEN t.DURATION_IN_MS BETWEEN 120000 AND 124999 THEN '120000-125000'
        ELSE 'OTHERS'
    END = i.duration_interval
GROUP BY i.duration_interval;

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