MySQL根据值范围(直方图)进行任意分组的方法

Question

我有一个简单的数据集，其中脚本需要时间来完成各个步骤。时间是不可预测的，但主要分组在特定的时间范围内，但我想以十分之一秒的分组来绘制图表。 (我知道这很奇怪，这是一些报告可视化内容的要求)。我可以将我的案例陈述延长得非常长，但我认为有更好的方法可以做到这一点。这个方法是什么？

SELECT
  COUNT(thing) AS value,
  CASE 
    WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0 AND 0.1 THEN '0-0.1'
    WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.1 AND 0.2 THEN '0.1-0.2'
    WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.2 AND 0.3 THEN '0.2-0.3'
    WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.3 AND 0.4 THEN '0.3-0.4'
    WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.4 AND 0.5 THEN '0.4-0.5'
    -- ad infinitum
  END AS metric
FROM table
GROUP BY metric

Answer 1

您可以将范围放入临时表中。

CREATE TEMPORARY TABLE metric_ranges (
    low  NUMERIC(4,1) NOT NULL,
    high NUMERIC(4,1) NOT NULL,
    metric VARCHAR(10) NOT NULL,
    PRIMARY KEY (low, high),
    UNIQUE KEY (label)
);
INSERT INTO metric_ranges 
VALUES (0.0, 0.1, '0-0.1'),
       (0.1, 0.2, '0.1-0.2'),
       (0.2, 0.3, '0.2-0.3'),
       (0.3, 0.4, '0.3-0.4'),
       (0.4, 0.5, '0.4-0.5'),
       ...etc...

然后将您的数据加入范围:

SELECT COUNT(t.thing) AS value, m.metric
FROM (
    SELECT thing, 
         COALESCE(step1,0) +
         COALESCE(step2,0) +
         COALESCE(step3,0) +
         COALESCE(step4,0) +
         COALESCE(step5,0) +
         COALESCE(step6,0) +
         COALESCE(step7,0) AS total
    FROM mytable
) AS t
JOIN metric_ranges AS m ON t.total >= m.low AND t.total < m.high
GROUP BY m.metric

由您来确保范围不重叠!