Номер строки в рамках группы

Давай научимся нумеровать товары в рамках категории. Примерно так:

Мы умеем делать сквозную нумерацию через row_number(). Чтобы сделать отдельную нумерацию в рамках категории, достаточно в OVER добавить разбивку по категориям через PARTITION BY:

SELECT c.category_id,
       c.name AS category_name,
       row_number () OVER (PARTITION BY c.category_id ORDER BY p.name) AS position,
       p.name AS product_name
  FROM category c,
       product p
 WHERE p.category_id = c.category_id
 ORDER BY c.name, position

Как работает PARTITION BY

PARTITION BY работает в оконных функциях так же, как и GROUP BY в агрегатных.

Для вычисления оконной функции все строки запроса разбиваются на группы с одинаковыми значениями полей, перечисленных после PARTITION BY.

У нас PARTITION BY c.category_id, следовательно row_number() вычисляется независимо для следующих наборов строк:

Затем в каждой группе строки сортируются по p.name и получают свой порядковый номер функцией row_number().