如何使用Postgres窗口函数按属性查找中位数？ [英] How to find median by attribute with Postgres window functions?

问题描述

我使用PostgreSQL并在一群人上有这样的记录：

I use PostgreSQL and have records like this on groups of people:

name    | people | indicator
--------+--------+-----------
group 1 | 1000   | 1 
group 2 | 100    | 2
group 3 | 2000   | 3

我需要找到指标 中位数人。结果应为

I need to find the indicator for the median person. The result should be

group 3 | 2000   | 3

如果我愿意

select median(name) over (order by indicator) from table1

它将会是 group 2 。

不确定是否可以通过窗口功能选择它。

Not sure if I can select this with a window function.

为每条记录生成1000/2000行似乎不切实际，因为记录中有数百万人。

Generating 1000/2000 rows per record seems impractical, because I have millions of people in the records.

推荐答案

找到第一个累积的总和，该总和大于总和的中位数：

Find the first cumulative sum of people greater than the median of total sum:

with the_data(name, people, indicator) as (
values
    ('group 1', 1000, 1),
    ('group 2', 100, 2),
    ('group 3', 2000, 3)
)
select name, people, indicator
from (
    select *, sum(people) over (order by name)
    from the_data
    cross join (select sum(people)/2 median from the_data) s
    ) s
where sum > median
order by name
limit 1;

  name   | people | indicator 
---------+--------+-----------
 group 3 |   2000 |         3
(1 row)

这篇关于如何使用Postgres窗口函数按属性查找中位数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！