PostgreSQL查询性能及变化

Question

我有一个事件表，包含以下列user_id、points、created。我需要的是得到给定月份的所有积分，给定user_id的总分和总排名。我需要在一个查询中这样做，这导致了以下尝试：

select
    e.user_id,
    sum(e.points) as total_points,
    (select sum(points) from event where user_id = 1 and extract(month from created) = 1) as month_points,
    (select rank from (select user_id as id, rank() over (order by sum(points) desc) from event group by user_id) as rank where id = 1)
from
    event pte
where
    e.user_id = 1
group by
    e.user_id
;

我想问的是：

1. 这个查询会慢吗？
2. 还能用另一种方式做得更好吗？
3. rank函数是否足够可靠，还是被滥用了？

Answer 1

假设postgresql大于或等于9.4，则可以使用聚合filter子句来避免为month_points和rank进行子选择

select * from
    (select
        e.user_id,
        sum(e.points) as total_points,
        sum(points) filter (where extract(month from created) = 1) as month_points,
        rank() over (order by sum(points) desc)
    from
        event e
    group by
        e.user_id
    ) as inner_query
where user_id = 1;