我有一个如下结构的表:
| id | bool | amt |
-------------------
| 1 | 0 | 4 |
| 1 | 1 | 3 |
| 1 | 1 | 5 |
| 2 | 0 | 8 |
| 2 | 1 | 4 |
| 2 | 0 | 4 |
我想得到amt
的和,但只有当bool = 1
/bool = 0
/id
的比值大于0.6时。
我已经成功地做到了这一点:
SELECT SUM(amt) as total_amt,
FROM table
WHERE id IN (
SELECT id
FROM table
GROUP BY id
HAVING CAST(SUM(bool) AS DOUBLE) / CAST(COUNT(bool) AS DOUBLE) > 0.6
)
然而,我的问题是,这是对我实际的表格和数据的玩具模拟,实际上这是一个非常大的数据量。当我对所有数据运行这个查询时,我会收到错误,要么说已经达到集群的内存限制,要么说执行时间已经达到限制。如果我删除WHERE
语句,该语句发现id
s满足该比率,则它运行时不会出错。
在增加这些限制之前,有没有什么方法可以更有效地实现这一点,无论是在内存、执行时间还是两者方面?
您可以使用两个级别的聚合:
select sum(id_amount)
from (select id, sum(amount) as id_amount,
avg(case when bool then 1.0 else 0 end) as ratio
from t
group by id
) t
where ratio > 0.6;
注:我对普雷斯托没有太多经验。我认为你可以使用:
avg(bool)
或:
avg(bool::int)
而不是上述表达式。