sql - 通过计数获得中位数

Question

我有一个数据集，我们称之为 d1，包含以下信息:

ID count
1   5
2   2 
3   6 
4   6
5   4
6   3

如果我想要中位数，它将用 [1,1,1,1,1,2,2,...,6,6,6] 计算，因为有一个计数重复多次。结果将是 3.5(因为我们得到 3 和 4，并且我们计算它们之间的平均值)。我一直在尝试对子查询使用限制，但我不能，因此我不知道如何获得中间值或中间值的平均值。

我如何在 SQL 中执行此操作？

Answer 1

您可以使用 generate_series 从 1 到 count 为每一行扩展数据集，然后应用 percentile_cont 有序集合聚合函数。这将适用于 postgresql 9.4+

独立示例:

WITH x(id, cnt) as (
values
(1, 5),
(2, 2), 
(3, 6),
(4, 6),
(5, 4),
(6, 3)
)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY id) med
FROM x, generate_series(1,cnt)

# outputs:
med
3.5

另一种选择是使用窗口函数来确定应该平均以获得中位数的元素的位置

WITH x(id,"cnt") as (
values
(1,5),
(2,2), 
(3,6),
(4,6),
(5,4),
(6,3)
)
, windowed AS (
  SELECT id, SUM(cnt) OVER w a, SUM(cnt) OVER u b, SUM(cnt) OVER v / 2.0 c
  FROM x
  WINDOW u AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
         v AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
         w AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
SELECT AVG(id) med
FROM windowed
WHERE c BETWEEN b AND a