postgresql 如何使用ANSI SQL计算百分比[已关闭]

jum4pzuy 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(129)

已关闭。此问题需要details or clarity。当前不接受答案。
**想要改进此问题？**添加详细信息并通过editing this post阐明问题。

1年前关闭。
Improve this question
我想知道是否有人可以帮助我解决以下问题和数据集的问题。
仅使用ANSI SQL（假设关系数据库结构），报告每个站点的最低3个收入事件和每个站点的第75百分位收入。结果应包含三列（站点、收入、第75百分位收入）。每个站点应包含三行以报告最低3个收入事件（这三行将包含站点和第75百分位收入的相同值）。

"revenue","top","browser","platform","site"
0.00776943843268547,30.9602731291705,"safari","mobile",3
0.014156805607701,9.40058400290278,"chrome","desktop",3
0.0122753797773888,14.1199488319863,"safari","desktop",3
0.0106614882554566,29.9070545370937,"chrome","mobile",3
0.00947284187142993,19.3632264586303,"chrome","mobile",3
0.00617645820494863,20.6914934615515,"safari","mobile",4
0.00826814556437385,9.60096864605439,"chrome","mobile",4
0.00632831928919495,9.76504233515594,"chrome","mobile",2
0.00876230897437953,10.7436149034003,"chrome","mobile",2
0.0122542371574979,9.10155570695745,"safari","desktop",3
0.00734122719327969,11.9796512017936,"safari","mobile",2
0.00700850269424599,19.4384601758623,"safari","mobile",2
0.00636258609706847,23.3436268959235,"safari","mobile",2
0.0106938493186955,8.44233917110576,"safari","desktop",1
0.0104336231416902,18.2609732065633,"chrome","mobile",1
0.00609590043025647,19.8308911166086,"safari","mobile",3
0.0132940835699967,12.790110661125,"chrome","desktop",3
0.0143056180039869,14.50619738211,"chrome","desktop",4
0.00410721818995674,17.1302843168817,"safari","mobile",1
0.0139846720621539,2.28439602214985,"chrome","desktop",4
0.0139832667223559,8.64296576855354,"chrome","desktop",2
0.011386144188017,5.87860889164693,"safari","desktop",4
0.00967748879893385,4.76435336494166,"safari","desktop",3

postgresql

来源：https://stackoverflow.com/questions/66960142/how-to-calcualte-percentile-using-ansi-sql

1条答案

按热度按时间

t3psigkw1#

你可以使用窗口函数：

select t.*
from (select t.*,
             row_number() over (partition by site order by revenue desc) as seqnum,
             percentile_cont(0.75) within group (order by revenue) over (partition by site) as revenue_75
      from t
     ) t
where seqnum <= 3;

赞(0）回复(0）举报 2023-04-05

我来回答

postgresql 如何使用ANSI SQL计算百分比[已关闭]

1条答案

相关问题

热门标签

最新问答