• Связанных с текущей
• Без группировки

• Применяются после where/group by/having

• function([distinct] {Столбцы | *}) over (Окно) as Имя
  

select
    StudentId, StudentName, StudentGroup, AvgPoints,
    rank() over 
        (partition by StudentGroup order by AvgPoints desc)
        as Rank
from StudentInfo order by StudentGroup, StudentName;

• partition by Столбцы
  

• По-умолчанию: одна корзина

• rank() over (order by AvgPoints desc) as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	2
  1	Иванов И.И.	M3439	80	5
  4	Михайлов М.М	M3439	90	2
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	2

• order by Столбцы
  

• Столбец [{asc | desc}] [nulls {first | last}]
  

• По-умолчанию: все равны

• rank() over (partition by StudentGroup) as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	1
  4	Михайлов М.М	M3439	90	1
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	1

• Количество (count)
• Сумма/среднее (sum/avg)
• Минимум/максимум (min/max)

• max(AvgPoints) over (partition by StudentGroup) as M
  

• Результат

  Id	Name	Group	Points	Max
  5	Смирнов С.С.	M3438	90	90
  1	Иванов И.И.	M3439	80	360
  4	Михайлов М.М	M3439	90	280
  2	Петров П.П.	M3439	100	100
  3	Сидоров С.С.	M3439	90	280

• sum(AvgPoints) over 
  (partition by StudentGroup order by AvgPoints desc)
  as Sum
  

• Результат

  Id	Name	Group	Points	S
  5	Смирнов С.С.	M3438	90	5
  1	Иванов И.И.	M3439	80	8
  4	Михайлов М.М	M3439	90	7
  2	Петров П.П.	M3439	100
  3	Сидоров С.С.	M3439	90	7

filter (where Условие)

• sum(StudentId) filter (where AvgPoints < 100) over 
  (partition by StudentGroup order by AvgPoints desc)
  as Sum
  

• Результат

  Id	Name	Group	Points	S
  5	Смирнов С.С.	M3438	90	5
  1	Иванов И.И.	M3439	80	8
  4	Михайлов М.М	M3439	90	7
  2	Петров П.П.	M3439	100
  3	Сидоров С.С.	M3439	90	7

• row_number() over 
  (partition by StudentGroup order by AvgPoints desc)
  as Row
  

• Результат

  Id	Name	Group	Points	Row
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	4
  4	Михайлов М.М	M3439	90	3
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	2

• rank() over 
  (partition by StudentGroup order by AvgPoints desc)
  as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	4
  4	Михайлов М.М	M3439	90	2
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	2

• dense_rank() over 
  (partition by StudentGroup order by AvgPoints desc)
  as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	3
  4	Михайлов М.М	M3439	90	2
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	2

• (rank() - 1) / (count() - 1)

• percent_rank() over 
  (partition by StudentGroup order by AvgPoints desc)
  as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	0
  1	Иванов И.И.	M3439	80	1
  4	Михайлов М.М	M3439	90	0.33
  2	Петров П.П.	M3439	100	0
  3	Сидоров С.С.	M3439	90	0.33

• (число-предшествующих-либо-равных) / count()
  

• cume_dist() over 
  (partition by StudentGroup order by AvgPoints desc)
  as Rank
  

• Результат

  Id	Name	Group	Points	Rank
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	1
  4	Михайлов М.М	M3439	90	0.75
  2	Петров П.П.	M3439	100	0.25
  3	Сидоров С.С.	M3439	90	0.75

• ntile(2) over 
  (partition by StudentGroup order by AvgPoints desc)
  as Tile
  

• Результат

  Id	Name	Group	Points	Tile
  5	Смирнов С.С.	M3438	90	1
  1	Иванов И.И.	M3439	80	2
  4	Михайлов М.М	M3439	90	2
  2	Петров П.П.	M3439	100	1
  3	Сидоров С.С.	M3439	90	1

• столбец [, сдвиг [, значение ]]
  

• lag(StudentId, 1, 0) over 
  (partition by StudentGroup order by AvgPoints desc)
  as Lag
  

• Результат

  Id	Name	Group	Points	Lag
  5	Смирнов С.С.	M3438	90	0
  1	Иванов И.И.	M3439	80	4
  4	Михайлов М.М	M3439	90	3
  2	Петров П.П.	M3439	100	0
  3	Сидоров С.С.	M3439	90	2

• Режим Начало Исключения
  

• Режим between Начало and Конец Исключения
  

• rows — строки
• groups — подгруппы равных
• range — отличающиеся на

• Предшествующие

  {unbounded | Сдвиг} preceding
  

• Равные

  current row
  

• Последующие

  {unbounded | Сдвиг} following
  

exclude {current row | group | ties | no others}

• current row – текущая строка
• group – равные строки
• ties – равные строки, текущая остаётся
• no others – без исключений

• first_value(StudentId) over 
      (partition by StudentGroup order by AvgPoints desc 
          groups 1 preceding)
  as Value
  

• Результат

  Id	Name	Group	Points	Value
  5	Смирнов С.С.	M3438	90	5
  1	Иванов И.И.	M3439	80	3
  4	Михайлов М.М	M3439	90	2
  2	Петров П.П.	M3439	100	2
  3	Сидоров С.С.	M3439	90	2

• last_value(StudentId) over 
      (partition by StudentGroup order by AvgPoints desc 
          rows between current row and 1 following)
  as Value
  

• Результат

  Id	Name	Group	Points	Value
  5	Смирнов С.С.	M3438	90	5
  1	Иванов И.И.	M3439	80	1
  4	Михайлов М.М	M3439	90	1
  2	Петров П.П.	M3439	100	3
  3	Сидоров С.С.	M3439	90	4

• nth_value(StudentId, 2)
  over (partition by StudentGroup order by AvgPoints desc)
  as Value
  

• Результат

  Id	Name	Group	Points	Value
  5	Смирнов С.С.	M3438	90
  1	Иванов И.И.	M3439	80	3
  4	Михайлов М.М	M3439	90	3
  2	Петров П.П.	M3439	100
  3	Сидоров С.С.	M3439	90	3

• sum(AvgPoints) over 
  (partition by StudentGroup order by AvgPoints desc
      groups between 1 preceding and 1 following except group)
  as Sum
  

• Результат

  Id	Name	Group	Points	S
  5	Смирнов С.С.	M3438	90	90
  1	Иванов И.И.	M3439	80	260
  4	Михайлов М.М	M3439	90	270
  2	Петров П.П.	M3439	100	280
  3	Сидоров С.С.	M3439	90	270

• Команды
  • Сотрудники

• Проекты

• Годы
  • Кварталы

• Сотрудник × Проект × Квартал

• Отделы: Departments(DId, D)
• Команды: Teams(TId, T, DId)
• Сотрудники: Employees (EId, E, TId)

• Emp = Departments ⋈ Teams ⋈ Employees

  E	T	D	EId	TId	DId
  Анна	BD	Dev	111	11	1
  Вася	BD	Dev	112	11	1
  Пётр	FD	Dev	121	12	1
  Иван	QA	QA	211	21	2
  Яна	QA	QA	212	21	2

• Клиенты: Customers(CId, C)
• Проекты: Projects(PId, P, CId)

• Proj = Customers ⋈ Teams

  P	C	PId	Id
  A1	A	11	1
  A2	A	12	1
  B1	B	21	2

• Pay = Emp ⋈ Proj ⋈ Payments

  E	T	D	P	C	Y	Q	A	EId	TId	DId	PId	CId
  Анна	BD	Dev	A1	A	1	3	1	111	11	1	11	1
  Анна	BD	Dev	A1	A	1	4	2	111	11	1	11	1
  Анна	BD	Dev	A1	A	2	1	3	111	11	1	11	1
  Вася	BD	Dev	A1	A	1	4	10	112	11	1	11	1
  Вася	BD	Dev	A2	A	1	3	20	112	11	1	12	1
  Вася	BD	Dev	B1	B	2	1	30	112	11	1	21	2
  Пётр	FD	Dev	A1	A	1	4	100	121	12	1	11	1
  Пётр	FD	Dev	B1	B	1	3	200	121	12	1	21	2
  Иван	QA	QA	A1	A	2	1	1000	211	21	2	11	1
  Иван	QA	QA	B1	B	1	4	2000	211	21	2	21	2
  Яна	QA	QA	A1	A	2	1	10000	212	21	2	11	1
  Яна	QA	QA	A2	A	1	4	20000	212	21	2	12	1

select E, sum(A) as A from Pay
group by E, EId
order by EId;

select T, sum(A) as A from Pay
group by T, TId
order by TId;

• По каждой в отдельности
• Исключенные столбцы – null

• group by grouping sets (Группы)
  

• Группа

  • (Столбцы)
    

  • Столбец
    

select T, D, sum(A) as A from Pay
group by grouping sets ((T, TId), (D, DId), ())
order by TId, DId;

• group by rollup (С1, С2, С3, ...)
  

• group by grouping sets (
      (), 
      С1, 
      (С1, С2), 
      (С1, С2, С3), 
      ...
  )
  

select T, D, sum(A) as A from Pay
group by rollup ((D, DId), (T, TId))
order by TId, DId;

• group by cube (С1, С2, С3, ...)
  

• group by grouping sets (
      (),
      С1, С2, С3,
      (С1, С2), (С1, С2), (С2, С3),
      (С1, С2, С3),
      ...
  )
  

select D, C, Y, Q, sum(A) as A from Pay
group by cube (
    (D, DId),
    (C, CId),
    (Y, Q)
)
order by DId, CId, Y, Q;

• Table Expressions https://www.postgresql.org/docs/
  current/queries-table-expressions.html
• Window Function Calls https://www.postgresql.org/docs/
  current/sql-expressions.html

• Group by Modifiers https://dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html
• Windows Functions https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html

• SELECT - OVER clause https://learn.microsoft.com/
  en-us/sql/t-sql/queries/select-over-clause-transact-sql
• SELECT - GROUP BY https://learn.microsoft.com/
  en-us/sql/t-sql/queries/select-group-by-transact-sql

• Analytic Functions https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Analytic-Functions.html