Ranking window functions

ROW_NUMBER() не требует аргумента передаваемого в функцию

SELECT *,
             ROW_NUMBER() OVER ()
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616);
-- ROW_NUMBER нумерует записи в таблице в том порядке в котором они в ней представлены

ORDER BY выполняется после ROW_NUMBER()

SELECT *,
             ROW_NUMBER() OVER ()
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY user_id;

ORDER BY можно передать в функцию OVER

SELECT *, 
       ROW_NUMBER() OVER (ORDER BY user_id DESC)
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY user_id;

Можно сортировать по нескольким полям

SELECT *, 
       ROW_NUMBER() OVER (ORDER BY user_id, event_dt DESC)
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY user_id;

Пример выбора записи с определенным рангом

WITH sessions AS
  (SELECT *,
          ROW_NUMBER() OVER (ORDER BY session_start) AS rn
   FROM online_store.sessions)
SELECT *
FROM sessions
WHERE rn = 100;

RANK() и DENSE_RANK()

отличаются от RAW_NUMBER() тем как происходит ранжирование одинаковых значений:

• RAW_NUMBER() нумерует последовательно
• RANK() нумерует одинаковыми значениями одинаковый ранг, но чтобы вычислить каждый следующий ранг, к текущему номеру ранга прибавляется количество записей этого ранга. Поэтому нумерация растет не последовательно
• DENSE_RANK() нумерует одинаковыми значениями одинаковый ранг, нумерация последовательная

SELECT *, 
       RANK() OVER (ORDER BY user_id)
FROM online_store.orders
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY user_id;

SELECT *, 
       DENSE_RANK() OVER (ORDER BY user_id)
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY user_id;

NTILE()

Делит весь объем записей на N бакетов так, чтобы количество записей в группах было максимально близким. Если записи не получается разбить на группы поровну, предпочтение отдаётся первым группам — в них войдёт больше записей. NTILE() принимает на вход количество рангов.

SELECT *,
         NTILE(3) OVER (ORDER BY revenue)
FROM online_store.orders 
WHERE user_id IN (300768196,
                  840452722,
                  59432616)
ORDER BY revenue;

Операторы PARTITION BY и ORDER BY

влияют на результат работы оконной функции. В выражении OVER их можно использовать вместе. Выберем данные о втором по счёту заказе для каждого пользователя. Оператор PARTITION BY позволяет разделить данные на окна по user_id. Оператор ORDER BY отсортирует значения внутри окон по дате заказа, а функция ROW_NUMBER() их проранжирует.

-- данные о втором по счёту заказе для каждого пользователя:
WITH orders AS
  (SELECT *,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_dt) AS rn
   FROM online_store.orders)
SELECT *
FROM orders
WHERE rn = 2;

-- третья с конца сессия для каждого пользователя
WITH sessions AS
  (SELECT *,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_start DESC) AS rn
   FROM online_store.sessions)
SELECT *
FROM sessions
WHERE rn = 3;

Функцию NTILE() тоже можно использовать в сочетании с оператором PARTITION BY. В этом случае записи разделятся на группы не по всей таблице, а внутри каждой из групп, сформированных PARTITION BY

SELECT *,
         NTILE(3) OVER (PARTITION BY user_id ORDER BY session_start)
FROM online_store.sessions 
WHERE user_id IN (300768196,
                  840452722,
                  59432616);