如何连续比较一个值以查看它是否高于同一列中所有值的75％？

Question

我有一个看起来像这样的表：

groups  created_utc            score    count_comments  d_posts ups  downs   ratio
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572   .42(8530/20286)
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303   .58(11756/20286) 

从此查询中生成：

SELECT *, ups  / SUM(ups) OVER () AS ratio
FROM table
order by ratio desc;

如何在比率逐行比较每个值，以查看该比率是否大于所有比率的75％以创建新的比率标志列，greate_75p？

新表格应该看起来像这样（无法获得新的col格式化，但应为y/n作为选项）：

groups  created_utc            score    count_comments  d_posts ups  downs   ratio                greater_75p
                                                                                                   y 
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572   .42(8530/20286)
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303   .58(11756/20286)

我尝试了此查询，但是获取错误标量子查询产生了多个元素< /code>：

SELECT *,ups * 100 / SUM(ups) OVER () AS ratio, 
PERCENT_RANK() OVER(order by (SELECT ups * 100 / SUM(ups) OVER () AS ratio from table )) AS greater_75p
FROM table

不确定我在做什么错以及如何得出SQL内的百分比比较？

先感谢您。

Answer 1

要获取persecon_rank（）的结果，您可以使用以下通用表表达式：

with cte as
(SELECT *, ups  / SUM(ups) OVER () AS ratio
FROM table) 
select *,(case when percent_rank()over(order by ration) >0.75 then 'yes' else 'no' end) greater_75p from cte

请阐明计算greate_75p列的逻辑。