具有百分比条件的postgreDistinct SQL选择

Question

我有一个PostgreSQL表，如下所示，

portal   partner     location_quality 
38       10060       46
38       10060       46
38       5586        90
4        10060       90
23       11843       10
38       12594       90
38       10438       50
38       10060       90
23       10438       46
36       12660       90
23       12594       50

对于每对入口&合作伙伴位置质量的范围从0到100。我想要所有不同的门户网站&如果每对的90%的location_quality大于50。

我想得到所有不同的门户网站&如果90%的location_quality大于50，则伙伴配对。有人能帮忙吗？

Answer 1

1. 将行分组
2. agregad with max

3. 具有的过滤器

   SELECT
   portal,
   partner,
   max(location_quality) as location_quality
   FROM
   table01
   GROUP BY
   portal,
   partner
   HAVING
   max(location_quality) > 50
   

现在您的问题的解决方案

1. 全部计数
2. 超过50时计数

3. 取门廊，比较是否超过90%

   SELECT
   portal,
   partner,
   count(CASE WHEN location_quality > 50 THEN 1 END) * 100 /  count(location_quality ) as percent_above
   FROM
   table01
   GROUP BY
   portal,
   partner
   HAVING
   count(CASE WHEN location_quality > 50 THEN 1 END) * 100 /  count(location_quality ) > 90;
   

Answer 2

我可能有误解，但这就是你所说的位置质量的90%吗？

SELECT
portal,
partner,
location_quality
FROM
table01
GROUP BY
portal,
partner
WHERE
(location_quality * 0.9) > 50

Answer 3

我提出了一个与Roger类似的解决方案，

SELECT DISTINCT partner, portal from (
SELECT partner, portal, sum(case when location_quality > 50 then 1 else null end) as cnt_greater_50, CAST(count(*) AS FLOAT) as total_cnt
FROM tb
GROUP BY partner, portal
) WHERE cnt_greater_50/total_cnt > 0.9

有更好的主意吗？感谢