如何在SQLite中计算数值的中位数？

Question

如何在SQLite中计算数值的中位数？

sqlitemedian

35

我想在一个数字行中计算中位数。在SQLite 4中，我应该如何进行操作？

- mafu

6个回答

16

有一个sqlite3的各种数学函数扩展包，其中包括像中位数这样的组函数。

与CL的答案相比，启用该扩展需要更多的工作，但如果您认为将来需要其他任何函数，这可能是值得的。

http://www.sqlite.org/contrib/download/extension-functions.c?get=25

(这里提供了如何编译和加载SQLite扩展的指南。)

从描述中可以知道：

使用可加载扩展机制在SQL查询中提供数学和字符串扩展函数。数学：acos、asin、atan、atn2、atan2、acosh、asinh、atanh、difference、degrees、radians、cos、sin、tan、cot、cosh、sinh、tanh、coth、exp、log、log10、power、sign、sqrt、square、ceil、floor、pi。字符串：replicate、charindex、leftstr、rightstr、ltrim、rtrim、trim、replace、reverse、proper、padl、padr、padc、strfilter。聚合：stdev、variance、mode、median、lower_quartile、upper_quartile。

更新 2015-04-12: 修复“undefined symbol: sinh”

正如评论中提到的那样，即使编译成功，该扩展可能无法正常工作。

例如，在Linux上编译可能会成功，并且您可能会将生成的.so文件复制到/usr/local/lib。然而，在sqlite3 shell中运行.load /usr/local/lib/libsqlitefunctions可能会生成以下错误：

Error: /usr/local/lib/libsqlitefunctions.so: undefined symbol: sinh

以这种方式进行编译似乎是有效的：

gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so -lm

将.so文件复制到/usr/local/lib后，没有显示类似的错误：

sqlite> .load /usr/local/lib/libsqlitefunctions

sqlite> select cos(pi()/4.0);
---> 0.707106781186548

我不确定为什么在这种情况下，gcc选项的顺序很重要，但显然确实如此。

注意到这一点要归功于Ludvick Lidicky在这篇博客文章中的评论。

- Paul

有什么想法如何安装这个？文件本身并没有提供太多帮助。 - jameshfisher

@jameshfisher 尝试在另一个问题中询问，这里是一个开端。出于好奇，我今晚尝试编译扩展程序。按照文件顶部的C注释中包含的说明足够简单（你读了文件并找到了它们，对吧？），但存在一些错误。它使用Ubuntu 14.04 LTS上的gcc进行编译，需要“libsqlite3-dev”，结果是一个共享库“libsqlitefunctions.so”。当给出命令SELECT load_extension('./libsqlitefunctions')时，同样的Ubuntu的sqlite3尝试加载它，但会抛出错误“undefined symbol: sinh”。 - Paul

这个链接（https://github.com/yajirobee/environment/blob/master/common/libsqlitefunctions.so）提供了一个已编译的so文件。它可以正常工作！ - HackNone

这里有人在Windows上运行过这个扩展程序吗？或者有编译好的版本可以分享吗？我在加载时遇到了一些问题... - Kassym Dorsel

1

@Kassym Dorsel：请下载上述的extension-functions.c文件以及从https://www.sqlite.org/2019/sqlite-amalgamation-3290000.zip下载sqlite-amalgamation文件，并将它们全部提取到同一个文件夹中。然后安装MinGw Installer并从这里将“mingw32-base-bin”包安装到c:\MinGW中。现在打开DOS命令并切换到c:\MinGW\bin，运行命令gcc -g -shared "C:\YourPath\extension-functions.c" -o "C:\YourPath\extension-functions.dll"。最后通过.load C:/YourPath/extension-functions.dll在SQLite中加载dll。 - Carsten

1

有一个带有时间戳、标签和延迟的日志表。我们想要查看每个时间戳分组下每个标签的延迟中位数值。将所有延迟值格式化为15个字符长度，前导零，然后连接起来，并切掉中间位置的值，即可得到中位数。

select L, --V, 
       case when C % 2 = 0 then
       ( substr( V, ( C - 1 ) * 15 + 1, 15) * 1 + substr( V, C * 15 + 1, 15) * 1 ) / 2
       else
        substr( V, C * 15 + 1, 15) * 1
       end as MEDST
from (
    select L, group_concat(ST, "") as V, count(ST) / 2 as C
    from (
        select label as L, 
               substr( timeStamp, 1, 8) * 1 as T, 
               printf( '%015d',latency) as ST
        from log
        where label not like '%-%' and responseMessage = 'OK'
        order by L, T, ST ) as XX
    group by L
    ) as YY

- Dixtroy

我喜欢你格式化查询的方式。 - Marc

0

如果您正在使用PDO，则Paul的答案中使用的::loadExtension()可能不可用。

假设您正在使用PHP，则另一种选择是创建聚合函数。

$pdo_handle->sqliteCreateAggregate(
    'median', // the name of the function to declare
    function($context, $row_number, $value){ // a method called for each row
        $context[] = $value; // store the values
        return $context; 
    },
    function($context, $row_count){ // a method called once all row have been iterated over
        // sort the values
        sort($context, SORT_NUMERIC);
        // cound the number of values
        $count = count($context);
        // get the mid point of array (lowest one)
        $middle = floor($count/2);
        // if there is an even amount of values
        if (($count % 2) == 0) {
            // average the two middle values to find the median
            return ($context[$middle--] + $context[$middle])/2;        
        } else {
            // odd amount of elements, so the median value is simply the one in the middle
            return $context[$middle];
        }    
    },
    1
);

然后你就可以自由地进行操作了

SELECT median("column_name") FROM "table_name";

其他编程语言可能也有类似的“创建函数”功能。

- Annoying Technology

0

Dixtroy通过使用group_concat()提供了最佳解决方案。以下是完整的示例：

DROP TABLE [t];
CREATE TABLE [t] (name, value INT);
INSERT INTO t VALUES ('A', 2);
INSERT INTO t VALUES ('A', 3);
INSERT INTO t VALUES ('B', 4);
INSERT INTO t VALUES ('B', 5);
INSERT INTO t VALUES ('B', 6);
INSERT INTO t VALUES ('C', 7);

结果显示在这个表格中：

name|value
A|2
A|3
B|4
B|5
B|6
C|7

现在我们使用 Dextroy 修改过的查询：

SELECT name, --string_list, count, middle,
    CASE WHEN count%2=0 THEN
        0.5 * substr(string_list, middle-10, 10) + 0.5 * substr(string_list, middle, 10)
    ELSE
        1.0 * substr(string_list, middle, 10)
    END AS median
FROM (
    SELECT name, 
        group_concat(value_string,"") AS string_list,
        count() AS count, 
        1 + 10*(count()/2) AS middle
    FROM (
        SELECT name, 
            printf( '%010d',value) AS value_string
        FROM [t]
        ORDER BY name,value_string
    )
    GROUP BY name
);

...然后得到这个结果：

name|median
A|2.5
B|5.0
C|7.0

- Carsten

-1

SELECT AVG(x)仅返回格式为YYYY-MM-DD的日期值的年份，因此我稍微调整了CL的解决方案以适应日期：

SELECT DATE(JULIANDAY(MIN(MyDate)) + (JULIANDAY(MAX(MyDate)) - JULIANDAY(MIN(MyDate)))/2) as Median_Date
FROM (
   SELECT MyDate
      FROM MyTable
      ORDER BY MyDate
      LIMIT 2 - ((SELECT COUNT(*) FROM MyTable) % 2) -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2 FROM MyTable)
);

- DUHdley d'Urite

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- CL. · Accepted Answer

假设中位数是有序列表的中间元素。

SQLite（4或3）没有任何内置函数可以实现这一点，但是可以手动完成：

SELECT x
FROM MyTable
ORDER BY x
LIMIT 1
OFFSET (SELECT COUNT(*)
        FROM MyTable) / 2

当记录数为偶数时，通常将中位数定义为两个中间记录的平均值。在这种情况下，可以按如下方式计算平均值：

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))

将奇数和偶数的情况组合起来，结果如下：

SELECT AVG(x)
FROM (SELECT x
      FROM MyTable
      ORDER BY x
      LIMIT 2 - (SELECT COUNT(*) FROM MyTable) % 2    -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM MyTable))