sql - 如何对用户进行排名并从该排名中获取我的用户以及按排名位置高于和低于用户的子集

Question

我现在正在处理查询以获得我的用户排名。我有两个表，一个用于用户，另一个用于利润，我在其中保存金额和相关的用户 ID。通过获取用户产生的总利润，我需要建立一个包含三个用户的排名，排名比我的用户高的用户，我的用户和排名比我的用户低的用户。例如:

  id   |            name             | total_profit | rank
-------+-----------------------------+--------------+------
 10312 | John Doe                    |       7000.0 |    1
 10329 | Michael Jordan              |       5000.0 |    2
 10333 | Kobe Bryant                 |       4000.0 |    3
 10327 | Mike Bibby                  |       4000.0 |    3
 10331 | Phil Jackson                |       1000.0 |    4

如果我的用户是 Kobe Bryant，我需要获得 Michael Jordan、Kobe Bryant 和 Phil Jackson 的排名。

如果我的用户是 Mike Bibby，我需要获得与 Michale Jordan、Mike Bybby 和 Phil Jackson 的排名。

直到现在，我有一个查询返回所有用户的完整排名，但我现在不知道什么是获得我想要的三个用户的好方法。我曾尝试使用 ruby​​ 执行此操作，但我认为如果我在数据库中执行所有这些处理会更好。

SELECT users.id, users.name, total_profit, rank() OVER(ORDER BY total_profit DESC)
FROM users
INNER JOIN (SELECT sum(profits.amount) AS total_profit, investor_id
          FROM profits GROUP BY profits.investor_id) profits ON profits.investor_id = users.id
ORDER BY total_profit DESC;

我正在使用 PostgresSQL 9.1.4

Answer 1

with s as (
    select
        users.id, users.name, total_profit,
        rank() over(order by total_profit desc) as r
    from
        users
        inner join
            (
                select sum(profits.amount) as total_profit,
                investor_id
                from profits
                group by profits.investor_id
            ) profits on profits.investor_id = users.id
), u as (
    select r from s where name = 'Kobe Bryant'
)
select distinct on (r) id, name, total_profit, r
from s
where
    name = 'Kobe Bryant'
    or r in (
        (select r from u) - 1, (select r from u) + 1
    )
order by r;