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）。

- Hamma

---

你几个小时前不是已经问过这个问题了吗？没有得到好的答案吗？ - jarlh

我曾认为这是一个相当简单的操作，因为四分位数广泛应用于频率计算中并且是主要的统计变量之一。但这并不是预测任务难易程度的很好依据。与计算平均值相比，计算四分位数（甚至只是中位数）在操作上更加复杂。 - John Bollinger

你说得没错，但我现在正在学习SQL，由于我习惯了其他高级编程语言，缺少统计包让我感到很困难。 - Hamma

SQL不能很好地被定义为编程语言。它是一种基于关系数据模型的数据定义和数据操作语言。尽管SQL通常具有某些针对排序的特性，但更多的实现是基于（无序）集合的模型。这与某些任务不太匹配，例如计算四分位数。这并不意味着您不能在SQL中执行此类计算，但对于某些任务，最好将SQL与另一种语言配对使用。 - John Bollinger

MySQL在这方面特别有限，因为它缺少几个常见的功能，其中任何一个都会使工作更容易（例如窗口函数、公共表达式、各种其他特定函数——即使忽略NTILE()）。 - John Bollinger

好的，但是考虑到我的选择是在查询数据库并获取已计算好的1个单独的值之间，还是获取大约100k个值然后自己计算它们，我认为避免通过互联网传输100k会更有效率。 因此，无论是编程语言还是其他，SQL能否给我四分位数，还是我需要手动计算？ - Hamma

Answer 1

请参见SqlFiddle：http://sqlfiddle.com/#!9/accca6/2/6 注意：对于sqlfiddle，我已生成了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;

基本原理如下： 对于第一四分位数，我们想要从顶部获取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中，因此我们将它们连接起来，联合查询结果，准备查询并执行它。

- Pholochtairze

---

@Hamma：抱歉，我以为没有相同传感器的两行^^这是一个适用于给定传感器的代码（请参见编辑后的代码），我正在编写一个输出所有传感器的代码，完成后会告诉你。 - Pholochtairze

我会尽快尝试。不必为所有传感器的输出烦恼，因为我可以为每个传感器运行不同的查询。对我来说重要的是从查询中获取四分位数，而不是从查询中获取成千上万的原始数据，然后自己计算它们。 - Hamma

如果我们以w3schools的定义为例（谷歌上的第一个），“PRIMARY KEY约束在数据库表中唯一标识每个记录。”。这基本上意味着每行在主键字段中具有不同的值。它允许您唯一地标识一行。这就是为什么拥有冗余的PRIMARY KEY很奇怪。更可能的是外键，这意味着您表中的外键字段链接到另一个表中的主键。请参见：https://dev59.com/E3I-5IYBdhLWcg3wwLW3 - Pholochtairze

也许你不需要主键，但你可能需要一个索引（为了了解索引的用处：https://dev59.com/MnVD5IYBdhLWcg3wXaid#1130）。它可以使查询结果更快。此外，由于你的数据库中有多行具有相同的键，因此你无法使用主索引。当我尝试时，它会返回错误“Duplicate entry '101' for key 'PRIMARY'”。 - Pholochtairze

我刚刚有机会检查了一下，实际上我做得很好，但是我在这里简化了一些东西，所以犯了一个错误。在真正的表格中，我有一个日期列，并且我通过这种方式设置了主键：'PRIMARY KEY(Time,Sensor))'；传感器每30秒就会有一个新的读数，所以我应该没问题。 代码运行得很好，现在我只需要弄清楚如何通过C#来实现它。 - Hamma

Answer 2

使用NTILE非常简单，但它是Postgres函数。你只需要像这样操作：

SELECT value_you_are_NTILING,
    NTILE(4) OVER (ORDER BY value_you_are_NTILING DESC) AS tiles
FROM
(SELECT math_that_gives_you_the_value_you_are_NTILING_here AS value_you_are_NTILING FROM tablename);

这里是我在SQLFiddle上为您制作的一个简单示例：http://sqlfiddle.com/#!15/7f05a/1 在MySQL中，您可以使用RANK... 这是相应的SQLFiddle链接：http://www.sqlfiddle.com/#!2/d5587/1 (这来自下面链接的问题)

这个MySQL RANK()的用法来自于Stackoverflow上的回答：Rank function in MySQL 请查看Salman A.提供的答案。

- user1376214

---

NTILE() 对于这项工作最大的问题是 MySQL 没有它（并且该问题标记为 mysql）。 - John Bollinger

是的，你说得对。我正在使用MySQL，我刚刚注意到NTILE()不是MySQL函数。 很抱歉浪费了你的时间。 - Hamma

这不是浪费时间。我在Mysql的讨论中添加了RANK函数的链接。这会给你想要的东西。 - user1376214

我在我的表格上尝试了这个例子，结果按排名分组。我猜想为了得到四分位数，我需要请求排名数字totalranks0.25和totalranks0.75？ - Hamma

我猜NTILE已经被添加到了MySQL 8中。链接 - y2k-shubham

Answer 3

类似这样的代码应该可以解决问题：

select
    ll.*,
    if (a.position is not null, 1,
        if (b.position is not null, 2, 
        if (c.position is not null, 3, 
        if (d.position is not null, 4, 0)))
    ) as quartile
from
    luxlog ll
    left outer join luxlog a on ll.position = a.position and a.lux > (select count(*)*0.00 from luxlog) and a.lux <= (select count(*)*0.25 from luxlog)
    left outer join luxlog b on ll.position = b.position and b.lux > (select count(*)*0.25 from luxlog) and b.lux <= (select count(*)*0.50 from luxlog)
    left outer join luxlog c on ll.position = c.position and c.lux > (select count(*)*0.50 from luxlog) and c.lux <= (select count(*)*0.75 from luxlog)
    left outer join luxlog d on ll.position = d.position and d.lux > (select count(*)*0.75 from luxlog)
;    

以下是完整的示例：

use example;

drop table if exists luxlog;

CREATE TABLE LuxLog (
  Sensor TINYINT,
  Lux INT,
  position int,
  PRIMARY KEY(Position)
);

insert into luxlog values (0, 1, 10);
insert into luxlog values (0, 2, 20);
insert into luxlog values (0, 3, 30);
insert into luxlog values (0, 4, 40);
insert into luxlog values (0, 5, 50);
insert into luxlog values (0, 6, 60);
insert into luxlog values (0, 7, 70);
insert into luxlog values (0, 8, 80);

select count(*)*.25 from luxlog;
select count(*)*.50 from luxlog;

select
    ll.*,
    a.position,
    b.position,
    if(
        a.position is not null, 1,
        if (b.position is not null, 2, 0)
    ) as quartile
from
    luxlog ll
    left outer join luxlog a on ll.position = a.position and a.lux >= (select count(*)*0.00 from luxlog) and a.lux < (select count(*)*0.25 from luxlog)
    left outer join luxlog b on ll.position = b.position and b.lux >= (select count(*)*0.25 from luxlog) and b.lux < (select count(*)*0.50 from luxlog)
    left outer join luxlog c on ll.position = c.position and c.lux >= (select count(*)*0.50 from luxlog) and c.lux < (select count(*)*0.75 from luxlog)
    left outer join luxlog d on ll.position = d.position and d.lux >= (select count(*)*0.75 from luxlog) and d.lux < (select count(*)*1.00 from luxlog)
;    


select
    ll.*,
    if (a.position is not null, 1,
        if (b.position is not null, 2, 
        if (c.position is not null, 3, 
        if (d.position is not null, 4, 0)))
    ) as quartile
from
    luxlog ll
    left outer join luxlog a on ll.position = a.position and a.lux > (select count(*)*0.00 from luxlog) and a.lux <= (select count(*)*0.25 from luxlog)
    left outer join luxlog b on ll.position = b.position and b.lux > (select count(*)*0.25 from luxlog) and b.lux <= (select count(*)*0.50 from luxlog)
    left outer join luxlog c on ll.position = c.position and c.lux > (select count(*)*0.50 from luxlog) and c.lux <= (select count(*)*0.75 from luxlog)
    left outer join luxlog d on ll.position = d.position and d.lux > (select count(*)*0.75 from luxlog)
;    

- John

---

这个查询花费了80秒来执行，并且它给我返回了所有10k行作为结果。 我尝试做这件事的主要原因是为了避免传输所有那些数据。 我只期望从选择中返回操作的结果。 - Hamma

编辑了我给出的排名答案，以返回每个 ntile 仅一行。 - John

我仍然会得到一个有数千行的表格，使用您的查询，在数据量只有一小部分的情况下，查询需要25秒。我不明白开头的那两个select count(*)应该如何帮助。 - Hamma

Answer 4

我使用MYSQL函数来解决这个问题： x是您想要的百分位数 array_values是您的group_concat值，按顺序用逗号分隔

DROP FUNCTION IF EXISTS centile;

delimiter $$
CREATE FUNCTION `centile`(x Text, array_values TEXT) RETURNS text
BEGIN

Declare DIFF_RANK TEXT;
Declare RANG_FLOOR INT;
Declare COUNT INT;
Declare VALEUR_SUP TEXT;
Declare VALEUR_INF TEXT;

SET COUNT = LENGTH(array_values) - LENGTH(REPLACE(array_values, ',', '')) + 1;
SET RANG_FLOOR = FLOOR(ROUND((x) * (COUNT-1),2));
SET DIFF_RANK = ((x) * (COUNT-1)) - FLOOR(ROUND((x) * (COUNT-1),2));

SET VALEUR_SUP = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(array_values,',', RANG_FLOOR+2),',',-1) AS DECIMAL);
SET VALEUR_INF = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(array_values,',', RANG_FLOOR+1),',',-1) AS DECIMAL);

/****
    https://fr.wikipedia.org/wiki/Quantile
    x_j+1 + g (x_j+2 - x_j+1)       
***/
RETURN  Round((VALEUR_INF + (DIFF_RANK* (VALEUR_SUP-VALEUR_INF) ) ),2);

END$$

例子：

Select centile(3/4,GROUP_CONCAT(lux ORDER BY lux SEPARATOR ',')) as quartile_3
FROM LuxLog
WHERE Sensor=12 AND Lux<>0

- dartaloufe

Answer 5

这是我为计算四分位数想出的一个查询；它在大约5000个表行中运行时间为~0.04秒。我包括了最小/最大值，因为我最终使用这些数据来构建四个四分位数范围：

   SELECT percentile_table.percentile, avg(ColumnName) AS percentile_values
    FROM   
        (SELECT @rownum := @rownum + 1 AS `row_number`, 
                   d.ColumnName 
            FROM   PercentileTestTable d, 
                   (SELECT @rownum := 0) r 
            WHERE  ColumnName IS NOT NULL 
            ORDER  BY d.ColumnName
        ) AS t1, 
        (SELECT count(*) AS total_rows 
            FROM   PercentileTestTable d 
            WHERE  ColumnName IS NOT NULL 
        ) AS t2, 
        (SELECT 0 AS percentile 
            UNION ALL 
            SELECT 0.25
            UNION ALL 
            SELECT 0.5
            UNION ALL 
            SELECT 0.75
            UNION ALL 
            SELECT 1
        ) AS percentile_table  
    WHERE  
        (percentile_table.percentile != 0 
            AND percentile_table.percentile != 1 
            AND t1.row_number IN 
            ( 
                floor(( total_rows + 1 ) * percentile_table.percentile), 
                floor(( total_rows + 2 ) * percentile_table.percentile)
            ) 
        ) OR (
            percentile_table.percentile = 0 
            AND t1.row_number = 1
        ) OR (
            percentile_table.percentile = 1 
            AND t1.row_number = total_rows
        )
    GROUP BY percentile_table.percentile; 

在这里试验：http://sqlfiddle.com/#!9/58c0e2/1

肯定存在性能问题；如果有人能提供改进意见，我会很高兴。

样本数据列表：

 3, 4, 4, 4, 7, 10, 11, 12, 14, 16, 17, 18

示例查询输出：

| percentile | percentile_values |
|------------|-------------------|
|          0 |                 3 |
|       0.25 |                 4 |
|        0.5 |              10.5 |
|       0.75 |                15 |
|          1 |                18 |

- icats

Answer 6

或者你可以像这样使用rank：

select
    ll.*,
    @curRank := @curRank + 1 as rank,
    if (@curRank <= (select count(*)*0.25 from luxlog), 1,
        if (@curRank <= (select count(*)*0.50 from luxlog), 2, 
        if (@curRank <= (select count(*)*0.75 from luxlog), 3, 4))
    ) as quartile
from
    luxlog ll,
    (SELECT @curRank := 0) r
;    

这将为每个四分位数仅提供一条记录：

select
    x.quartile, group_concat(position)
from (
    select
        ll.*,
        @curRank := @curRank + 1 as rank,
        if (@curRank > 0 and @curRank <= (select count(*)*0.25 from luxlog), 1,
            if (@curRank > 0 and @curRank <= (select count(*)*0.50 from luxlog), 2, 
            if (@curRank > 0 and @curRank <= (select count(*)*0.75 from luxlog), 3, 4))
        ) as quartile
    from
        luxlog ll,
        (SELECT @curRank := 0) r
) x
group by quartile

+ ------------- + --------------------------- +
| quartile      | group_concat(position)      |
+ ------------- + --------------------------- +
| 1             | 10,20                       |
| 2             | 30,40                       |
| 3             | 50,60                       |
| 4             | 70,80                       |
+ ------------- + --------------------------- +
4 rows

编辑： 在移除后，sqlFiddle示例（http://sqlfiddle.com/#!9/a14a4/17）看起来像这样

/*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 WHERE Sensor=101 ORDER BY Lux DESC LIMIT 1 OFFSET ', @quartile,')'));
SET @sql_q3 := (CONCAT('( SELECT "Q3" AS quartile_name , Lux, Sensor FROM LuxLog WHERE Sensor=101 ORDER BY Lux ASC LIMIT 1 OFFSET ', @quartile,');'));
SET @sql := (CONCAT(@sql_q1,' UNION ',@sql_q3));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;*/

- John

---

我正在尝试测试它，但它无法运行。您可以在此处查看我的数据小样本表：http://sqlfiddle.com/#!9/a14a4/6 - Hamma

如果我删除你提供的sqlfiddle示例顶部的注释代码（/* */），它对我有效。 - John