Тема: оконные функции, группировка, ловушка в агрегатах
📌 Задача:
Есть таблица
SALES со следующей структурой:
CREATE TABLE SALES (
ID NUMBER,
REGION VARCHAR2(20),
SALE_DATE DATE,
AMOUNT NUMBER
);
Пример данных:
| ID | REGION | SALE_DATE | AMOUNT |
|----|--------|-----------|--------|
| 1 | North | 01-JAN-24 | 100 |
| 2 | North | 02-JAN-24 | 120 |
| 3 | South | 01-JAN-24 | 90 |
| 4 | South | 03-JAN-24 | 200 |
| 5 | East | 01-JAN-24 | 150 |
| 6 | East | 02-JAN-24 | 100 |🧩 Найти:
Для каждого региона — ту дату, в которую была вторая по величине сумма продаж.
Если в регионе меньше двух дат — не выводить его вовсе.
🎯 Подвох:
- нельзя использовать
LIMIT, FETCH FIRST, QUALIFY и подзапросы с ROWNUM напрямую (нужно решение через оконные функции Oracle)- многие пытаются взять
MAX(AMOUNT) с OFFSET 1, но в Oracle это не так просто✅ Ожидаемый результат:
| REGION | SALE_DATE | AMOUNT |
|--------|-----------|--------|
| North | 01-JAN-24 | 100 |
| South | 01-JAN-24 | 90 |
| East | 02-JAN-24 | 100 |
🔍 Решение:
```sql
SELECT REGION, SALE_DATE, AMOUNT
FROM (
SELECT
REGION,
SALE_DATE,
AMOUNT,
DENSE_RANK() OVER (PARTITION BY REGION ORDER BY AMOUNT DESC) AS rnk,
COUNT(DISTINCT SALE_DATE) OVER (PARTITION BY REGION) AS cnt
FROM SALES
)
WHERE rnk = 2 AND cnt >= 2;
```
📌 **Объяснение подвоха:**
- `DENSE_RANK` гарантирует, что если есть одинаковые суммы, они получат один и тот же ранг
- `COUNT(DISTINCT SALE_DATE)` проверяет, что у региона хотя бы две разные даты (иначе регион исключается)
- Работает чисто на оконных функциях, без подзапросов с ROWNUM — идеально для Oracle
🧪 Проверь результат и попробуй адаптировать под похожие задачи с TOP-N логикой.
@sqlhub