如何在SQLite中计算值的中位数? [英] How can I calculate the median of values in SQLite?

问题描述

我想在数字行中计算中位数.如何在SQLite 4中做到这一点?

I'd like to calculate the median value in a numeric row. How can I do that in SQLite 4?

推荐答案

让我们说，中位数是有序列表中间的元素.

Let's say that the median is the element in the middle of an ordered list.

SQLite(4或3)对此没有任何内置函数，但是可以手动完成:

SQLite (4 or 3) does not have any built-in function for that, but it's possible to do this by hand:

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

---

当记录数为偶数时，通常将中位数定义为两个中间记录的平均值. 在这种情况下，可以这样计算平均值:

---

When there is an even number of records, it is common to define the median as the average of the two middle records. In this case, the average can be computed like this:

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

将奇数情况和偶数情况相结合会得出以下结果:

Combining the odd and even cases then results in this:

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))

这篇关于如何在SQLite中计算值的中位数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！