Нумерация и ранжирование строк

Для цели нумерации строк используется функция ROW_NUMBER.

Пронумеруем сотрудников по полю Name и по нескольким полям LastName,FirstName,MiddleName:

SELECT

ID,

Name,

-- нумирация в порядке значений Name

ROW_NUMBER() OVER(ORDER BY Name) EmpNoByName,

-- нумирация в порядке значений LastName,FirstName,MiddleName

ROW_NUMBER() OVER(ORDER BY LastName,FirstName,MiddleName) EmpNoByFullName

FROM Employees

ORDER BY Name

 

ID	Name	EmpNoByName	EmpNoByFullName
1005	Александров А.А.	1	6
1003	Андреев А.А.	2	7
1006	Антонов А.А.	3	1
1008	Данилов Д.Д.	4	2
1000	Иванов И.И.	5	8
1007	Максимов М.М.	6	3
1004	Николаев Н.Н.	7	4
1009	Остапов О.О.	8	5
1001	Петров П.П.	9	9
1002	Сидоров С.С.	10	10

Здесь для задания порядка в OVER используется предложение «ORDER BY».

Для разбиения на группы, здесь так же в OVER можно использовать предложение «PARTITION BY»:

SELECT

emp.ID,

emp.Name EmpName,

dep.Name DepName,

-- нумирация сотрудников в разрезе отделов, в порядке значений Name

ROW_NUMBER() OVER(PARTITION BY dep.ID ORDER BY emp.Name) EmpNoInDepByName

FROM Employees emp

LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID

ORDER BY dep.Name,emp.Name

 

ID	EmpName	DepName	EmpNoInDepByName
1005	Александров А.А.	NULL	1
1000	Иванов И.И.	Администрация	1
1002	Сидоров С.С.	Бухгалтерия	1
1003	Андреев А.А.	ИТ	1
1004	Николаев Н.Н.	ИТ	2
1001	Петров П.П.	ИТ	3
1008	Данилов Д.Д.	Логистика	1
1007	Максимов М.М.	Логистика	2
1009	Остапов О.О.	Логистика	3
1006	Антонов А.А.	Маркетинг и реклама	1

 

Ранжирование строк – это можно сказать нумерация, только группами. Есть 2 вида нумерации, с дырками (RANK) и без дырок (DENSE_RANK).

SELECT

emp.ID,

emp.Name EmpName,

emp.PositionID,

-- кол-во сотрудников в разрезе должностей

COUNT(*) OVER(PARTITION BY emp.PositionID) EmpCountInPos,

-- ранжирование с дырками - следующий номер зависит от кол-ва записей в предыдущей группе

RANK() OVER(ORDER BY emp.PositionID) RankValue,

-- ранжирование без дырок – плотная нумерация (последовательная)

DENSE_RANK() OVER(ORDER BY emp.PositionID) DenseRankValue

FROM Employees emp

LEFT JOIN Positions pos ON emp.PositionID=pos.ID

 

ID	EmpName	PositionID	EmpCountInPos	RankValue	DenseRankValue
1005	Александров А.А.	NULL	1	1	1
1002	Сидоров С.С.	1	1	2	2
1000	Иванов И.И.	2	1	3	3
1001	Петров П.П.	3	2	4	4
1004	Николаев Н.Н.	3	2	4	4
1003	Андреев А.А.	4	1	6	5
1006	Антонов А.А.	10	1	7	6
1007	Максимов М.М.	11	3	8	7
1008	Данилов Д.Д.	11	3	8	7
1009	Остапов О.О.	11	3	8	7

 

Аналитические функции: LAG() и LEAD(), FIRST_VALUE() и LAST_VALUE()

Данные функции позволяют получить значения другой строки относительно текущей строки.

Рассмотрим LAG() и LEAD():

SELECT

ID CurrEmpID,

Name CurrEmpName,

-- значения предыдущей строки

LAG(ID) OVER(ORDER BY ID) PrevEmpID,

LAG(Name) OVER(ORDER BY ID) PrevEmpName,

LAG(ID,2) OVER(ORDER BY ID) PrevPrevEmpID,

LAG(Name,2,'not found') OVER(ORDER BY ID) PrevPrevEmpName,

-- значения следующей строки

LEAD(ID) OVER(ORDER BY ID) NextEmpID,

LEAD(Name) OVER(ORDER BY ID) NextEmpName,

LEAD(ID,2) OVER(ORDER BY ID) NextNextEmpID,

LEAD(Name,2,'not found') OVER(ORDER BY ID) NextNextEmpName

FROM Employees

ORDER BY ID

 

CurrEmpID	CurrEmpName	PrevEmpID	PrevEmpName	PrevPrevEmpID	PrevPrevEmpName	NextEmpID	NextEmpName	NextNextEmpID	NextNextEmpName
1000	Иванов И.И.	NULL	NULL	NULL	not found	1001	Петров П.П.	1002	Сидоров С.С.
1001	Петров П.П.	1000	Иванов И.И.	NULL	not found	1002	Сидоров С.С.	1003	Андреев А.А.
1002	Сидоров С.С.	1001	Петров П.П.	1000	Иванов И.И.	1003	Андреев А.А.	1004	Николаев Н.Н.
1003	Андреев А.А.	1002	Сидоров С.С.	1001	Петров П.П.	1004	Николаев Н.Н.	1005	Александров А.А.
1004	Николаев Н.Н.	1003	Андреев А.А.	1002	Сидоров С.С.	1005	Александров А.А.	1006	Антонов А.А.
1005	Александров А.А.	1004	Николаев Н.Н.	1003	Андреев А.А.	1006	Антонов А.А.	1007	Максимов М.М.
1006	Антонов А.А.	1005	Александров А.А.	1004	Николаев Н.Н.	1007	Максимов М.М.	1008	Данилов Д.Д.
1007	Максимов М.М.	1006	Антонов А.А.	1005	Александров А.А.	1008	Данилов Д.Д.	1009	Остапов О.О.
1008	Данилов Д.Д.	1007	Максимов М.М.	1006	Антонов А.А.	1009	Остапов О.О.	NULL	not found
1009	Остапов О.О.	1008	Данилов Д.Д.	1007	Максимов М.М.	NULL	NULL	NULL	not found

В данных функциях вторым параметром можно указать сдвиг относительно текущей строки, а третьим параметром можно указать возвращаемое значение для случая если для указанного смещения строки не существует.

Для разбиения данных по группам, попробуйте самостоятельно добавить предложение «PARTITION BY» в OVER, например, «OVER(PARTITION BY emp.DepartmentID ORDER BY emp.ID)».

Рассмотрим FIRST_VALUE() и LAST_VALUE():

SELECT

ID CurrEmpID,

Name CurrEmpName,

DepartmentID,

-- первое значение в группе

FIRST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpID,

FIRST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID) FirstEmpName,

-- последнее значение в группе

LAST_VALUE(ID) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpID,

LAST_VALUE(Name) OVER(PARTITION BY DepartmentID ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastEmpName

FROM Employees

ORDER BY DepartmentID,ID

 

CurrEmpID	CurrEmpName	DepartmentID	FirstEmpID	FirstEmpName	LastEmpID	LastEmpName
1005	Александров А.А.	NULL	1005	Александров А.А.	1005	Александров А.А.
1000	Иванов И.И.	1	1000	Иванов И.И.	1000	Иванов И.И.
1002	Сидоров С.С.	2	1002	Сидоров С.С.	1002	Сидоров С.С.
1001	Петров П.П.	3	1001	Петров П.П.	1004	Николаев Н.Н.
1003	Андреев А.А.	3	1001	Петров П.П.	1004	Николаев Н.Н.
1004	Николаев Н.Н.	3	1001	Петров П.П.	1004	Николаев Н.Н.
1006	Антонов А.А.	4	1006	Антонов А.А.	1006	Антонов А.А.
1007	Максимов М.М.	5	1007	Максимов М.М.	1009	Остапов О.О.
1008	Данилов Д.Д.	5	1007	Максимов М.М.	1009	Остапов О.О.
1009	Остапов О.О.	5	1007	Максимов М.М.	1009	Остапов О.О.

Думаю, здесь все понятно. Стоит только объяснить, что такое RANGE.

Параметры RANGE и ROWS

При помощи дополнительных параметров «RANGE» и «ROWS», можно изменить область работы функции, которая работает с предложением OVER. У каждой функции по умолчанию используется какая-то своя область действия. Такая область обычно называется окном.

Важное замечание. В разных СУБД для одних и тех же функций область по умолчанию может быть разной, поэтому нужно быть внимательным и смотреть справку конкретной СУБД по каждой отдельной функции.

 

Можно создавать окна по двум критериям:

1.по диапазону (RANGE) значений данных

2.по смещению (ROWS) относительно текущей строки

 

Общий синтаксис этих опций выглядит следующим образом:
Вариант 1:
{ROWS | RANGE} {{UNBOUNDED | выражение} PRECEDING | CURRENT ROW}

Вариант 2:
{ROWS | RANGE}
BETWEEN
{{UNBOUNDED PRECEDING | CURRENT ROW |
{UNBOUNDED | выражение 1}{PRECEDING | FOLLOWING}}
AND
{{UNBOUNDED FOLLOWING | CURRENT ROW |
{UNBOUNDED | выражение 2}{PRECEDING | FOLLOWING}}

 

Здесь проще понять если проанализировать в Excel результат запроса:

SELECT

ID,

Salary,

 

SUM(Salary) OVER() Sum1,

-- сумма всех строк - "все предыдущие" и "все последующие"

SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND unbounded following) Sum2,

-- сумма строк до текущей строки включительно - "все предыдущие" и "текущая строка"

SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND current row) Sum3,

-- сумма всех последующих от текущей строки включительно - "текущая строка" и "все последующие"

SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN current row AND unbounded following) Sum4,

-- сумма следующих трех строк - "1 следующую" и "3 следующие"

SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 following AND 3 following) Sum5,

-- сумма трех строк - "1 предыдущая" и "1 следующую"

SUM(Salary) OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 1 following) Sum6,

-- сумма предыдущих "трех предыдущих" и "текущей"

SUM(Salary) OVER(ORDER BY ID ROWS 3 preceding) Sum7,

-- сумма "всех предыдущих" и "текущей"

SUM(Salary) OVER(ORDER BY ID ROWS unbounded preceding) Sum8

FROM Employees

ORDER BY ID

 

ID	Salary	Sum1	Sum2	Sum3	Sum4	Sum5	Sum6	Sum7	Sum8
1000	5000.00	19900.00	19900.00	5000.00	19900.00	6000.00	6500.00	5000.00	5000.00
1001	1500.00	19900.00	19900.00	6500.00	14900.00	6000.00	9000.00	6500.00	6500.00
1002	2500.00	19900.00	19900.00	9000.00	13400.00	5500.00	6000.00	9000.00	9000.00
1003	2000.00	19900.00	19900.00	11000.00	10900.00	5300.00	6000.00	11000.00	11000.00
1004	1500.00	19900.00	19900.00	12500.00	8900.00	5000.00	5500.00	7500.00	12500.00
1005	2000.00	19900.00	19900.00	14500.00	7400.00	4200.00	5300.00	8000.00	14500.00
1006	1800.00	19900.00	19900.00	16300.00	5400.00	3600.00	5000.00	7300.00	16300.00
1007	1200.00	19900.00	19900.00	17500.00	3600.00	2400.00	4200.00	6500.00	17500.00
1008	1200.00	19900.00	19900.00	18700.00	2400.00	1200.00	3600.00	6200.00	18700.00
1009	1200.00	19900.00	19900.00	19900.00	1200.00	NULL	2400.00	5400.00	19900.00

С RANGE все тоже самое, только здесь смещения идут не относительно строк, а относительно их значений. Поэтому в данном случае в ORDER BY допустимы значения только типа дата или число.

SELECT

PositionID,

Salary,

 

SUM(Salary) OVER(PARTITION BY PositionID) Sum1,

-- сумма ЗП для всех значений PositionID - "все меньшие" и "все большие"

SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND unbounded following) Sum2,

-- сумма ЗП для значений меньших PositionID до текущего значения включительно - "все меньшие" и "текущее значение" (значения<=PositionID)

SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN unbounded preceding AND current row) Sum3,

-- сумма ЗП для всех больших значений от текущего значения включительно - "текущее значение" и "все большие" (значения>=PositionID)

SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN current row AND unbounded following) Sum4,

 

/*

Увы следующие комбинации для RANGE в MS SQL не работают, хотя в Oracle они работают.

 

Вырезки из MSDN:

   

Предложение RANGE не может использоваться со <спецификацией неподписанного значения> PRECEDING или со <спецификацией неподписанного значения> FOLLOWING.

 

<спецификация неподписанного значения> PRECEDING

Указывается с <беззнаковым указанием значения> для обозначения числа строк или значений перед текущей строкой.

Эта спецификация не допускается в предложении RANGE.

 

<спецификация неподписанного значения> FOLLOWING

Указывается с <беззнаковым указанием значения> для обозначения числа строк или значений после текущей строки.

Эта спецификация не допускается в предложении RANGE.

*/

 

-- сумма ЗП для трех значений - "+1" и "+3" (значение BETWEEN PositionID+1 AND PositionID+3)

--SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 following AND 3 following) Sum5,

-- сумма ЗП для трех значений - "-1" и "+1" (значение BETWEEN PositionID-1 AND PositionID+1)

--SUM(Salary) OVER(ORDER BY PositionID RANGE BETWEEN 1 preceding AND 1 following) Sum6,

-- сумма ЗП для предыдущих трех значений - "-3" и "текущее" (значение BETWEEN PositionID-3 AND PositionID)

--SUM(Salary) OVER(ORDER BY PositionID RANGE 3 preceding) Sum7,

 

-- сумма ЗП для "всех предыдущих значений" и "текущего" (значения<=PositionID)

SUM(Salary) OVER(ORDER BY PositionID RANGE unbounded preceding) Sum8

FROM Employees

ORDER BY PositionID

 

PositionID	Salary	Sum1	Sum2	Sum3	Sum4	Sum8
NULL	2000.00	2000.00	19900.00	2000.00	19900.00	2000.00
1	2500.00	2500.00	19900.00	4500.00	17900.00	4500.00
2	5000.00	5000.00	19900.00	9500.00	15400.00	9500.00
3	1500.00	3000.00	19900.00	12500.00	10400.00	12500.00
3	1500.00	3000.00	19900.00	12500.00	10400.00	12500.00
4	2000.00	2000.00	19900.00	14500.00	7400.00	14500.00
10	1800.00	1800.00	19900.00	16300.00	5400.00	16300.00
11	1200.00	3600.00	19900.00	19900.00	3600.00	19900.00
11	1200.00	3600.00	19900.00	19900.00	3600.00	19900.00
11	1200.00	3600.00	19900.00	19900.00	3600.00	19900.00

 

Заключение

Вот и все, уважаемые читатели, на этом я оканчиваю свой учебник по SQL (DDL, DML).

Надеюсь, что вам было интересно провести время за прочтением данного материала, а главное надеюсь, что он принес вам понимание самых важных базовых конструкций языка SQL.

Учитесь, практикуйтесь, добивайтесь получения правильных результатов.