Узнать соседние позиции в рейтинге

Question

Имеется таблица accounts, примерно с такими данными:

name | c1| c2    
test1| 5 | 0    
test2| 1 | 6    
test3| 5 | 11    
test4| 0 | 0    
...

Мне нужно было получить позицию аккаунта в рейтинге по сумме двух колонок c1 и c2.

Сделал это так:

SELECT 
   name, (c1 + c2) as total,
   (SELECT count(*)+1 FROM accounts WHERE (c1 + c2) > total) as position 
FROM accounts WHERE name = 'test2'

Получится аккаунт test2 будет на втором месте. Как теперь можно получить аккаунты, которые стоят на 1 и 3 месте (то есть рядом с этим аккаунтом)?

Answer 1

Используйте оконные функции.

Из таблицы, которую вы получаете после подсчетов, это будет выглядеть как-то так:

SELECT  
    name, 
    LAG(name) OVER(ORDER BY position) prev_name, 
    LEAD(name) OVER(ORDER BY position) next_name
FROM ...

Этим вы получите название аккаунта, который стоит на позиции перед текущим:

LAG(name) OVER(ORDER BY position)

А через LEAD - следующим. У граничных аккаунтов будет NULL в prev_name/next_name соответственно.

Answer 2

Так будет на большинстве SQL диалектов работать:

with ranked as (
    select name, (c1+c2) as total, dense_rank () over (order by (c1+c2) desc) rnk
    from accounts a 
) select r.name, r.total, r.rnk, p.name "lower ranked", n.name "higher ranked"  
from ranked r
left join ranked p on p.rnk = r.rnk+1
left join ranked n on n.rnk = r.rnk-1
order by r.rnk

Результат:

NAME          TOTAL        RNK lower    higher  
-------- ---------- ---------- -------- --------
test3            16          1 test2            
test2             7          2 test1    test3   
test1             5          3 test4    test2   
test4             0          4          test1

Рабочий пример на db<>fiddle, там же можно попробовать с другим брэндом БД.

БЛОГ НА HUSL

Узнать соседние позиции в рейтинге

Ответы (2 шт):