mysql - SQL 查询中的四分位数

Question

我有一个非常简单的表格:

CREATE TABLE IF NOT EXISTS LuxLog (
  Sensor TINYINT,
  Lux INT,
  PRIMARY KEY(Sensor)
)

它包含来自不同传感器的数千条日志。

我想为所有传感器设置 Q1 和 Q3。

我可以对每个数据进行一个查询，但对我来说最好对所有传感器进行一个查询(从一个查询中返回 Q1 和 Q3)

我认为这将是一个相当简单的操作，因为四分位数被广泛使用并且是频率计算中的主要统计变量之一。事实上，我发现了大量过于复杂的解决方案，而我希望找到一些简洁明了的解决方案。

谁能给我一个提示？

编辑:这是我在网上找到的一段代码，但它对我不起作用:

SELECT  SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(                 -- 1) make a sorted list of values
                Lux
                ORDER BY Lux
                SEPARATOR ','
            )
        ,   ','                           -- 2) cut at the comma
        ,   75/100 * COUNT(*)        --    at the position beyond the 90% portion
        )
    ,   ','                               -- 3) cut at the comma
    ,   -1                                --    right after the desired list entry
    )                 AS `75th Percentile`
    FROM    LuxLog
    WHERE   Sensor=12
    AND     Lux<>0

我得到 1 作为返回值，而它应该是一个可以被 10 整除的数字 (10,20,30.....1000)

Answer 1

参见 SqlFiddle:http://sqlfiddle.com/#!9/accca6/2/6注意:对于 sqlfiddle，我生成了 100 行，1 到 100 之间的每个整数都有一行，但它是随机顺序(在 excel 中完成)。

代码如下:

SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);
SET @quartile := (ROUND(@number_of_rows*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

编辑:

SET @current_sensor := 101;
SET @quartile := (ROUND((SELECT COUNT(*) FROM LuxLog WHERE Sensor = @current_sensor)*0.25));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=', @current_sensor,' ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

基本推理如下:对于四分位数 1，我们希望从顶部获得 25%，因此我们想知道有多少行，即:

SET @number_of_rows := (SELECT COUNT(*) FROM LuxLog);

现在我们知道了行数，我们想知道其中的 25% 是多少，就是这一行:

SET @quartile := (ROUND(@number_of_rows*0.25));

然后为了找到一个四分位数，我们想按 Lux 对 LuxLog 表进行排序，然后获取行号“@quartile”，为了做到这一点，我们将 OFFSET 设置为 @quartile 表示我们要开始选择从行号 @quartile 开始，我们说 limit 1 表示我们只想检索一行。那是:

SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile_name , Lux, Sensor FROM LuxLog ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));

我们(几乎)对另一个四分位数做同样的事情，但我们不是从顶部开始(从高值到低值)，而是从底部开始(它解释了 ASC)。

但现在我们只将字符串存储在变量@sql_q1 和@sql_q3 中，因此将它们连接起来，我们合并查询的结果，准备查询并执行它。