Прозоречни функции

Накратко: Прозоречните функции с OVER и PARTITION BY смятат върху група редове, без да ги свиват в един: място в класация, текуща сума или предишният ред с LAG.

Проблем

Средната, но и редовете

Средната цена за всяка категория вече я можеш с GROUP BY:

SQLбаза: магазин

SELECT category_id, ROUND(AVG(price), 2) AS category_avg
FROM products
GROUP BY category_id;

SELECT category_id, ROUND(AVG(price), 2) AS category_avg FROM products GROUP BY category_id;

▶ ИзпълниВърни заявкатаНова база

Само че управителят иска таблица с всички продукти и до всеки средната на неговата категория, за да види кой колко се отклонява. GROUP BY е свил продуктите: от 25 реда са останали 7. В урока за свързаните подзаявки го направи с подзаявка за всеки ред. Има и по-кратък начин.

Идеята

OVER и PARTITION BY

SQLбаза: магазин

SELECT name, category_id, price,
  ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg
FROM products;

SELECT name, category_id, price, ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg FROM products;

▶ ИзпълниВърни заявкатаНова база

AVG(price) OVER (PARTITION BY category_id) смята средната върху групата на реда, но не свива редовете: всички 25 продукта са тук, всеки със средната на своята категория.

OVER (…) казва върху кои редове да се смята. Тази група редове се казва прозорец, а функцията с OVER се казва прозоречна функция (window function). PARTITION BY разделя редовете на групи, като GROUP BY, но без да ги слива.

Едно отличие от свързаната подзаявка: при подаръчната карта средната е 20 €, а не NULL. PARTITION BY слага всички NULL в една група, както GROUP BY, а p2.category_id = p.category_id не беше вярно за NULL.

Идеята

Място в класацията

ROW_NUMBER() дава поредния номер на реда в прозореца. С ORDER BY в OVER казваш по какво се подреждат:

SQLбаза: магазин

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

▶ ИзпълниВърни заявкатаНова база

Във всяка категория номерата започват отначало, от най-скъпия продукт: най-скъпото в „Кухня“ (категория 8) е комплектът ножове с номер 1.

Естественото продължение е WHERE place = 1, за да останат само първите. Но:

SQLбаза: магазин

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products
WHERE place = 1;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products WHERE place = 1;

▶ ИзпълниВърни заявкатаНова база

Прозоречните функции се смятат след WHERE: първо се избират редовете, после се подреждат в прозорци. Затова класацията се прави в стъпка, а филтърът е отвън.

Пробвай

Най-скъпото във всяка категория

Покажи за всяка категория името ѝ (колона category), най-скъпия ѝ продукт и цената му (колони name и price). Полето вече номерира продуктите. Сложи го в WITH и вземи само първите.

SQLбаза: магазин

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

Провери▶ Изпълни

Виж го

Равни места

Сега класация по поръчани бройки в „Хляб и тестени“ (категория 3), с трите функции една до друга:

SQLбаза: магазин

WITH sold AS (
  SELECT p.name, p.category_id, SUM(oi.quantity) AS sold
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category_id
)
SELECT name, sold,
  ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number,
  RANK() OVER (ORDER BY sold DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank
FROM sold
WHERE category_id = 3;

WITH sold AS ( SELECT p.name, p.category_id, SUM(oi.quantity) AS sold FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.id, p.name, p.category_id ) SELECT name, sold, ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number, RANK() OVER (ORDER BY sold DESC) AS rank, DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank FROM sold WHERE category_id = 3;

▶ ИзпълниВърни заявкатаНова база

1. Баницата и белият хляб имат по 16 бройки. Какво място им дава RANK, а какво DENSE_RANK на кифлата след тях?

RANK: 1 и 2; DENSE_RANK: 1 и 1RANK: 1 и 1, кифлата е 3-та; DENSE_RANK: кифлата е 2-раИ двете дават на кифлата 3-то място

Провери

• ROW_NUMBER дава различни номера дори при равенство. Кой от двамата е първи, решава второто подреждане (тук името). Без него не е обещано.
• RANK дава еднакво място на равните и после прескача: 1, 1, 3.
• DENSE_RANK дава еднакво място и не прескача: 1, 1, 2.

За „топ 3 продукта“ избирай съзнателно: с ROW_NUMBER излизат точно трима, с RANK излизат всички, които делят място.

Идеята

Текуща сума

С ORDER BY в OVER прозорецът на всеки ред е „от началото до тук“. Колко сме продали от 1 септември до всеки ден (без отказаните поръчки):

SQLбаза: магазин

WITH daily AS (
  SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled'
  GROUP BY o.ordered_at
)
SELECT day, ROUND(revenue, 2) AS revenue,
  ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total
FROM daily
ORDER BY day;

WITH daily AS ( SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled' GROUP BY o.ordered_at ) SELECT day, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total FROM daily ORDER BY day;

▶ ИзпълниВърни заявкатаНова база

running_total расте ред по ред: на 3 октомври става 415,30 €, а на 19 октомври е 480,30 €. Това се казва текуща сума (running total).

По-точно, за любопитните

Ако два реда имат една и съща стойност в ORDER BY, по подразбиране и двата получават сумата заедно с другия. Тук всеки ден е един ред, затова няма как да се случи. Ако подреждаш поръчки по дата, а в един ден има две поръчки, добави второ подреждане, например ORDER BY ordered_at, id, или изрично ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Пробвай

Колко клиенти сме имали

За всеки клиент покажи името, датата на регистрация и колко клиенти е имал магазинът към тази дата, заедно с него (колона customers_so_far).

SQLбаза: магазин

SELECT name, joined_at FROM customers
ORDER BY joined_at;

SELECT name, joined_at FROM customers ORDER BY joined_at;

Провери▶ Изпълни

Идеята

LAG: предишният ред

LAG(колона) дава стойността от предишния ред в прозореца. Колко дни минават между поръчките на Мария?

SQLбаза: магазин

SELECT id, ordered_at,
  LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous,
  julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days
FROM orders
WHERE customer_id = 1;

SELECT id, ordered_at, LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous, julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days FROM orders WHERE customer_id = 1;

▶ ИзпълниВърни заявкатаНова база

previous е датата на предишната ѝ поръчка. julianday превръща датата в номер на ден, затова разликата е броят дни между двете поръчки. Първата поръчка няма предишна и получава NULL. Обратната функция е LEAD: следващият ред.

Упражнение

Две задачи

1. Витрината на сайта показва по два продукта от всяка категория, най-скъпите.

Покажи за всяка категория името ѝ (колона category) и двата ѝ най-скъпи продукта с цените им (колони name и price).

SQLбаза: магазин

Провери▶ Изпълни

2. Маркетингът иска да знае колко най-дълго е чакал всеки клиент между две свои поръчки.

За всеки клиент с поне две поръчки покажи името и най-дългата пауза в дни между две поредни негови поръчки (колона longest_pause).

SQLбаза: магазин

Провери▶ Изпълни

Тест

Провери се

1. Каква е разликата между GROUP BY и OVER (PARTITION BY …)?

OVER работи само с ORDER BYНяма разликаGROUP BY свива всяка група в един ред, а OVER пази всички редове и добавя резултата към всеки

2. Защо WHERE place = 1 дава грешка, ако place е ROW_NUMBER() OVER (…)?

Защото place е запазена думаЗащото прозоречните функции се смятат след WHEREЗащото ROW_NUMBER започва от 0

3. Какво дава SUM(revenue) OVER (ORDER BY day)?

Сумата от първия ден до текущия: текуща сумаСумата само за текущия денОбщата сума на всеки ред

4. Какво връща LAG(ordered_at) на първия ред?

Датата на същия редГрешкаNULL, защото няма предишен ред

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: JOIN: две таблици в една заявка · Подзаявки · Корелирани подзаявки · Отчет на стъпки: WITH
• Продължава в: Потоци от събития · Колонни и времеви бази · Пекарната, етап 6: отчетът, копието и DROP
• Задачи по темата: Подзаявки и прозорци