Оконные функции в SQL Oracle: как работают и примеры использования

ORACLE

🟢Оконные функции в Oracle SQL. Введение

Оконные функции в Oracle SQL — это вычисления по «скользящему» набору строк без схлопывания результата.
Они пишутся в виде expr OVER([PARTITION BY ...] [ORDER BY ...] [frame])
и позволяют строить ранги, скользящие средние, накопительные суммы, сравнение с соседями и многое другое.

🚀 Когда использовать оконные функции?

  • Хочешь среднее / сумму / максимум, но не хочешь терять строки — 👍

  • Хочешь нумерацию / рейтинг внутри групп — 👍

  • Хочешь сравнить текущую строку с предыдущей — 👍

Синтаксис

-- Общая форма
func(args) OVER (
  [PARTITION BY expr_list]
  [ORDER BY sort_list]
  [ROWS|RANGE BETWEEN frame_start AND frame_end]
)

-- Примеры фреймов
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW

100 примеров

1. Нумерация строк по отделу

SELECT emp_id, dept_id,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY hire_date) AS rn
FROM employees;

2. Глобальный ранг по зарплате

SELECT emp_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

3. Плотный ранг без пропусков

SELECT emp_id, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

4. Квантование значений на 4 корзины

SELECT emp_id, salary,
       NTILE(4) OVER (ORDER BY salary) AS bucket
FROM employees;

5. Предыдущее значение (LAG)

SELECT emp_id, salary,
       LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_sal
FROM employees;

6. Следующее значение (LEAD)

SELECT emp_id, salary,
       LEAD(salary, 1, 0) OVER (ORDER BY emp_id) AS next_sal
FROM employees;

7. Первое значение в разрезе

SELECT dept_id, emp_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_in_dept
FROM employees;

8. Последнее значение с IGNORE NULLS

SELECT dept_id, ts, val,
       LAST_VALUE(val) IGNORE NULLS OVER (PARTITION BY dept_id ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM metrics;

9. N‑е значение (третье)

SELECT dept_id, emp_id, salary,
       NTH_VALUE(salary, 3) OVER (PARTITION BY dept_id ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_sal
FROM employees;

10. Кумулятивная сумма по дате

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum
FROM orders;

11. Скользящее среднее по 7 дням

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mov_avg_7
FROM series;

12. Нарастающий максимум

SELECT ts, val,
       MAX(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_max
FROM series;

13. Доля от общего (RATIO_TO_REPORT)

SELECT dept_id, salary,
       RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id) AS pct
FROM employees;

14. Процентильный ранг

SELECT emp_id, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS pr
FROM employees;

15. Кумулятивное распределение

SELECT emp_id, salary,
       CUME_DIST() OVER (ORDER BY salary) AS cd
FROM employees;

16. Счётчик строк в разрезе

SELECT dept_id, emp_id,
       COUNT(*) OVER (PARTITION BY dept_id) AS dept_cnt
FROM employees;

17. Сумма по окну текущая и две назад

SELECT ts, val,
       SUM(val) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_3
FROM series;

18. RANGE по интервалу 7 дней

SELECT ts, val,
       SUM(val) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS sum_7d
FROM series;

19. Первое ненулевое с IGNORE NULLS

SELECT ts, val,
       FIRST_VALUE(val) IGNORE NULLS OVER (ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_non_null
FROM series;

20. Последнее ненулевое с IGNORE NULLS

SELECT ts, val,
       LAST_VALUE(val) IGNORE NULLS OVER (ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_non_null
FROM series;

Еще 20 примеров.

21. Дельта относительно предыдущей строки

SELECT ts, val,
       val - LAG(val) OVER (ORDER BY ts) AS delta
FROM series;

22. Отношение к предыдущему значению

SELECT ts, val,
       CASE WHEN LAG(val) OVER (ORDER BY ts) IS NULL THEN NULL
            ELSE val / LAG(val) OVER (ORDER BY ts) END AS ratio_prev
FROM series;

23. Флаг изменения группы

SELECT emp_id, dept_id,
       CASE WHEN dept_id = LAG(dept_id) OVER (ORDER BY emp_id) THEN 0 ELSE 1 END AS is_new_group
FROM employees;

24. Топ‑N по зарплате в отделе

SELECT *
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn <= 3;

25. «Ничего не потерять» в топ‑N

SELECT emp_id, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;

26. Позиция в порядке при равных значениях

SELECT emp_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS pos
FROM employees;

27. Скользящая медиана (приближённо)

SELECT ts, val,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val)
         OVER (ORDER BY ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS med5
FROM series;

28. Список значений в строку по группе

SELECT dept_id,
       LISTAGG(emp_name, ', ') WITHIN GROUP (ORDER BY emp_name)
         OVER (PARTITION BY dept_id) AS list_in_dept
FROM employees;

29. Смена фрейма ROWS → RANGE

SELECT ts, val,
       SUM(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS rows_sum,
       SUM(val) OVER (ORDER BY ts RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum
FROM series;

30. Кумулятивное среднее

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_avg
FROM series;

31. Первые/последние даты по проекту

SELECT project_id, dt,
       FIRST_VALUE(dt) OVER (PARTITION BY project_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_dt,
       MAX(dt)        OVER (PARTITION BY project_id) AS last_dt
FROM timeline;

32. Процент от суммы по отделу

SELECT dept_id, salary,
       100 * salary / SUM(salary) OVER (PARTITION BY dept_id) AS pct_in_dept
FROM employees;

33. Скользящая сумма на 30 дней

SELECT ts, amount,
       SUM(amount) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS sum_30d
FROM payments;

34. Сравнение с средним по группе

SELECT dept_id, salary,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM employees;

35. MAX KEEP / MIN KEEP в сочетании с окнами (агрегат отдельно)

SELECT dept_id,
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) AS newest_high_salary
FROM employees
GROUP BY dept_id;

36. Нарастающий COUNT с рестартом по группе

SELECT dept_id, hire_date,
       COUNT(*) OVER (PARTITION BY dept_id ORDER BY hire_date) AS join_seq
FROM employees;

37. Скользящая коррекция нулями

SELECT ts, NVL(val, 0) AS v,
       SUM(NVL(val,0)) OVER (ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS sum4
FROM series;

38. Окно «текущая ±2»

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS avg5
FROM series;

39. Баланс «вход‑выход» во времени

SELECT ts, SUM(delta) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM movements;

40. Нумерация внутри дня

SELECT ts, val,
       ROW_NUMBER() OVER (PARTITION BY TRUNC(ts) ORDER BY ts) AS rn_in_day
FROM events;

Еще 20 примеров.

41. Ранг по нескольким полям

SELECT emp_id, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rnk
FROM employees;

42. NTILE по отделам раздельно

SELECT emp_id, dept_id, salary,
       NTILE(5) OVER (PARTITION BY dept_id ORDER BY salary) AS quint
FROM employees;

43. Скользящее стандартное отклонение

SELECT ts, val,
       STDDEV_SAMP(val) OVER (ORDER BY ts ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS sd5
FROM series;

44. Кумулятивный минимум

SELECT ts, val,
       MIN(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_min
FROM series;

45. Позиция внутри корзины NTILE

SELECT emp_id, salary,
       NTILE(4) OVER (ORDER BY salary) AS quart,
       ROW_NUMBER() OVER (PARTITION BY NTILE(4) OVER (ORDER BY salary) ORDER BY salary) AS pos_in_quart
FROM employees;

46. Соседние даты — разница в днях

SELECT ts,
       ts - LAG(ts) OVER (ORDER BY ts) AS days_diff
FROM events;

47. Флаг первого появления значения

SELECT key, ts,
       CASE WHEN LAG(key) OVER (ORDER BY ts) != key THEN 1 ELSE 0 END AS is_first
FROM stream;

48. Устранение дублей: взять первую по времени

SELECT *
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY created_at) AS rn
  FROM t
) WHERE rn = 1;

49. Поиск пробелов в последовательности

SELECT id,
       id - LAG(id) OVER (ORDER BY id) AS gap
FROM seq;

50. Скользящее окно с CURRENT ROW … FOLLOWING

SELECT ts, val,
       SUM(val) OVER (ORDER BY ts ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS forward_sum3
FROM series;

51. Взять последнее известное значение (forward fill)

SELECT ts,
       LAST_VALUE(val) IGNORE NULLS OVER (ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled
FROM series;

52. Доля строки в общем числе

SELECT dept_id, emp_id,
       1 / COUNT(*) OVER (PARTITION BY dept_id) AS weight
FROM employees;

53. Позиция среди равных (DENSE_RANK)

SELECT dept_id, salary,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS pos
FROM employees;

54. Top‑1 per group через ROW_NUMBER

SELECT dept_id, emp_id, salary
FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn = 1;

55. Скользящее окно по условным весам

SELECT ts, val,
       SUM(CASE WHEN val > 0 THEN val END) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS pos_sum3
FROM series;

56. Сравнение с максимумом по отделу

SELECT emp_id, salary,
       salary / MAX(salary) OVER (PARTITION BY dept_id) AS pct_of_max
FROM employees;

57. RANGE по денежной шкале (осторожно)

SELECT ts, amount,
       SUM(amount) OVER (ORDER BY amount RANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS sum_100
FROM payments;

58. Первые N% строк WITH TIES‑стилем

SELECT emp_id, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 1 PERCENT ROWS WITH TIES;

59. Скользящее окно на 12 месяцев по дате

SELECT month, sales,
       SUM(sales) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS run_12m
FROM monthly_sales;

60. Место клиента в сегменте

SELECT customer_id, revenue,
       NTILE(10) OVER (ORDER BY revenue DESC) AS decile
FROM customers;

Еще 20 примеров.

61. Процент от квартального итога

SELECT quarter, dept_id, amount,
       amount / SUM(amount) OVER (PARTITION BY quarter) AS pct_of_quarter
FROM sales;

62. Скользящий суммарный вес

SELECT ts, weight,
       SUM(weight) OVER (ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS wsum4
FROM weights;

63. Первые и последние значения в группе

SELECT grp, val,
       FIRST_VALUE(val) OVER (PARTITION BY grp ORDER BY ts) AS first_val,
       LAST_VALUE(val)  OVER (PARTITION BY grp ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM gdata;

64. Кумулятивная уникальная мощность (через DENSE_RANK)

SELECT ts, key,
       DENSE_RANK() OVER (ORDER BY key) AS uniq_so_far
FROM stream;

65. Сравнение с медианой по группе

SELECT dept_id, salary,
       salary - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept_id) AS diff_med
FROM employees;

66. Скользящий перцентиль 90%

SELECT ts, val,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY val) OVER (ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS p90
FROM series;

67. Сравнение двух соседей (две позиции назад)

SELECT ts, val,
       val - LAG(val, 2) OVER (ORDER BY ts) AS diff2
FROM series;

68. Скользящее окно асимметричное

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts ROWS BETWEEN 4 PRECEDING AND 1 FOLLOWING) AS avg6
FROM series;

69. Кумулятивное количество уникальных ключей (наивно)

SELECT ts, key,
       COUNT(DISTINCT key) OVER (ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS uniq_cnt
FROM stream;

70. Позиция в группе и глобальная

SELECT emp_id, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS in_dept,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_pos
FROM employees;

71. Флаг «новый максимум»

SELECT ts, val,
       CASE WHEN val = MAX(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN 1 ELSE 0 END AS is_new_high
FROM series;

72. Скользящая сумма с нулевыми значениями

SELECT ts, NVL(val,0) AS v,
       SUM(NVL(val,0)) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS s3
FROM series;

73. Top‑K per group с TIES

SELECT *
FROM (
  SELECT e.*,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees e
)
WHERE rnk <= 3;

74. Скользящее окно по времени с пропусками

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW) AS avg1h
FROM series_hourly;

75. Кумулятивная доля по убыванию

SELECT emp_id, salary,
       SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       / SUM(salary) OVER () AS cumulative_share
FROM employees;

76. Антидубль по ключу и времени

SELECT *
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) AS rn
  FROM t
) WHERE rn = 1;

77. Ранг с учётом дополнительных полей

SELECT id, score, RANK() OVER (ORDER BY score DESC, id) AS rnk
FROM results;

78. Скользящая корреляция — заготовка

SELECT ts, x, y,
       CORR(x, y) OVER (ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS corr10
FROM pairs;

79. Скользящая разность двух временных рядов

SELECT ts, a - LAG(a) OVER (ORDER BY ts) AS da,
       b - LAG(b) OVER (ORDER BY ts) AS db
FROM series2;

80. Кумулятивная сумма по нескольким ключам

SELECT k1, k2, v,
       SUM(v) OVER (PARTITION BY k1, k2 ORDER BY ts) AS run_sum
FROM facts;

Еще 20 примеров.

81. Скользящее окно по окну событий

SELECT ts, val,
       COUNT(*) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '10' MINUTE PRECEDING AND CURRENT ROW) AS cnt10m
FROM clicks;

82. Граница квартиля — p25/p75

SELECT val,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY val) OVER () AS p25,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY val) OVER () AS p75
FROM t;

83. Сдвиг показателя на N периодов

SELECT ts, metric,
       LAG(metric, :n) OVER (ORDER BY ts) AS shifted
FROM metrics;

84. Проверка монотонности ряда

SELECT ts, val,
       CASE WHEN val >= LAG(val) OVER (ORDER BY ts) THEN 1 ELSE 0 END AS non_decreasing
FROM series;

85. Взвешенное среднее в окне (пример)

SELECT ts, val, w,
       SUM(val*w) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
       / NULLIF(SUM(w) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),0) AS wavg3
FROM weighted;

86. Скользящий максимум по категории

SELECT cat, ts, val,
       MAX(val) OVER (PARTITION BY cat ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS mx
FROM cat_series;

87. Кумулятивная сумма с рестартом по месяцу

SELECT TRUNC(ts,'MM') AS m, ts, val,
       SUM(val) OVER (PARTITION BY TRUNC(ts,'MM') ORDER BY ts) AS m_run_sum
FROM series;

88. Доля в накопительном итоге

SELECT ts, val,
       val / NULLIF(SUM(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS pct_of_cum
FROM series;

89. Нумерация строк по отделу

SELECT emp_id, dept_id,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY hire_date) AS rn
FROM employees;

90. Глобальный ранг по зарплате

SELECT emp_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

91. Плотный ранг без пропусков

SELECT emp_id, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

92. Квантование значений на 4 корзины

SELECT emp_id, salary,
       NTILE(4) OVER (ORDER BY salary) AS bucket
FROM employees;

93. Предыдущее значение (LAG)

SELECT emp_id, salary,
       LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_sal
FROM employees;

94. Следующее значение (LEAD)

SELECT emp_id, salary,
       LEAD(salary, 1, 0) OVER (ORDER BY emp_id) AS next_sal
FROM employees;

95. Первое значение в разрезе

SELECT dept_id, emp_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_in_dept
FROM employees;

96. Последнее значение с IGNORE NULLS

SELECT dept_id, ts, val,
       LAST_VALUE(val) IGNORE NULLS OVER (PARTITION BY dept_id ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM metrics;

97. N‑е значение (третье)

SELECT dept_id, emp_id, salary,
       NTH_VALUE(salary, 3) OVER (PARTITION BY dept_id ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_sal
FROM employees;

98. Кумулятивная сумма по дате

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_sum
FROM orders;

99. Скользящее среднее по 7 дням

SELECT ts, val,
       AVG(val) OVER (ORDER BY ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mov_avg_7
FROM series;

100. Нарастающий максимум

SELECT ts, val,
       MAX(val) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_max
FROM series;

Частые ошибки и подводные камни

  1. ORDER BY в окне. Для функций, зависящих от порядка (ранги, LAG/LEAD, FIRST/LAST), нужен детерминированный порядок.
  2. Фрейм по умолчанию. Для агрегатов без фрейма — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, что отличается от ROWS.
  3. NULL‑поведение. FIRST_VALUE/LAST_VALUE поддерживают RESPECT/IGNORE NULLS.
  4. Производительность. Окно с широкой сортировкой и большим фреймом может быть тяжёлым — оптимизируйте ORDER BY и PARTITION BY.

Альтернативы

  • Самосоединения для «предыдущей/следующей» строки — работают, но хуже читаемость/производительность.
  • Агрегации с GROUP BY — когда не нужен контекст соседних строк.
  • Модель MODEL — для сложных сценариев клеточных вычислений.

Заключение

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

Документация

Oracle SQL — Analytic Functions

Windowing and Window Functions

0
Понравилась статья? Поделиться с друзьями:
Вам также может быть интересно

SELECT в Oracle SQL — 100 простых примеров для старта

SELECT в Oracle SQL. Введение SELECT — основной оператор выборки в Oracle SQL. Он строит результирующие наборы из таблиц и представлений, поддерживает фильтрацию, сортировку, группировку, подзапросы, соединения, аналитические…