我想知道是否有人可以帮助我解决以下问题和数据集的以下问题。
仅使用假设关系数据库结构的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
您将使用窗口函数：
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;