如何计算postgres的中位数？

hvvq6cgz 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(506)

我已经创建了一个基本数据库（附图片）数据库，我正在尝试查找以下内容：
“每个日历月每个用户花费的总金额中位数”
我尝试了以下操作，但出现了错误：

SELECT 
user_id,
AVG(total_per_user)
FROM (SELECT user_id,
        ROW_NUMBER() over (ORDER BY total_per_user DESC) AS desc_total,
        ROW_NUMBER() over (ORDER BY total_per_user ASC) AS asc_total
      FROM (SELECT EXTRACT(MONTH FROM created_at) AS calendar_month,
            user_id,    
            SUM(amount) AS total_per_user
            FROM transactions
            GROUP BY calendar_month, user_id) AS total_amount   
      ORDER BY user_id) AS a
WHERE asc_total IN (desc_total, desc_total+1, desc_total-1)
GROUP BY user_id
;

sql postgresql group-by Date Median

来源：https://stackoverflow.com/questions/62285793/how-to-calculate-the-median-in-postgres

2条答案

按热度按时间

ukxgm1gy1#

在postgres中，您可以只使用聚合函数 percentile_cont() :

select 
    user_id,
    percentile_cont(0.5) within group(order by total_per_user) median_total_per_user
from (
    select user_id, sum(amount) total_per_user
    from transactions
    group by date_trunc('month', created_at), user_id
) t
group by user_id

请注意 date_trunc() 可能更接近你想要的 extract(month from ...) -除非你想把同一个月不同年份的金额加起来，否则我不理解你的要求。

赞(0）回复(0）举报 2021-07-29

fxnxkyjh2#

只是使用 percentile_cont() . 我不完全理解这个问题。如果你想要每月支出的中位数，那么：

SELECT user_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_per_user
        ROW_NUMBER() over (ORDER BY total_per_user DESC) AS desc_total,
        ROW_NUMBER() over (ORDER BY total_per_user ASC) AS asc_total
FROM (SELECT DATE_TRUNC('month', created_at) AS calendar_month,
             user_id, SUM(amount) AS total_per_user
      FROM transactions t
      GROUP BY calendar_month, user_id
     ) um   
GROUP BY user_id;

中位数有一个内置函数。不需要更华丽的处理。

赞(0）回复(0）举报 2021-07-29

我来回答

如何计算postgres的中位数？

2条答案

相关问题

热门标签

最新问答