Group BY rollup и group BY grouping sets

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

Пример первый:

-- GROUP BY ROLLUP и функция GROUPING

SELECT

--GROUPING(YEAR(bonus.BonusDate)) g1,

--GROUPING(bonus.EmployeeID) g2,

--GROUPING(emp.Name) g3,

 

CASE

WHEN GROUPING(YEAR(bonus.BonusDate))=1 THEN 'Общий итог'

WHEN GROUPING(bonus.EmployeeID)=1 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год'

END RowTitle,

 

emp.Name,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4,

SUM(bonus.BonusAmount) TotalBonusAmount

FROM EmployeesBonus bonus

JOIN Employees emp ON bonus.EmployeeID=emp.ID

GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)

-- исключаем ненужный итог обрабатывая GROUPING

HAVING NOT(GROUPING(YEAR(bonus.BonusDate))=0 AND GROUPING(bonus.EmployeeID)=0 AND GROUPING(emp.Name)=1)

 

Чтобы понять, как работает функции GROUPING, раскомментируйте поля g1, g2 и g3, чтобы они попали в результирующий набор, а также закомментируйте предложение HAVING.

Пример второй:

-- GROUP BY ROLLUP и функция GROUPING_ID

SELECT

/*

GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)

бинарное 001 = десятичное 1

бинарное 011 = десятичное 3

бинарное 111 = десятичное 7

*/

 

--GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID,

 

CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)

WHEN 7 THEN 'Общий итог'

WHEN 3 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год'

END RowTitle,

 

emp.Name,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4,

SUM(bonus.BonusAmount) TotalBonusAmount

FROM EmployeesBonus bonus

JOIN Employees emp ON bonus.EmployeeID=emp.ID

GROUP BY ROLLUP(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)

-- исключаем ненужный итог обрабатывая GROUPING_ID

HAVING GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)<>1

 

Здесь для понимания, можете так же раскомментировать поле gID и закомментировать предложение HAVING.

Пример третий:

-- GROUP BY GROUPING SETS и функция GROUPING_ID

SELECT

/*

GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)

бинарное 001 = десятичное 1

бинарное 011 = десятичное 3

бинарное 111 = десятичное 7

*/

 

--GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name) gID,

 

CASE GROUPING_ID(YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name)

WHEN 7 THEN 'Общий итог'

WHEN 3 THEN 'Итого за '+CAST(YEAR(bonus.BonusDate) AS varchar(4))+' год'

END RowTitle,

 

emp.Name,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=1 THEN bonus.BonusAmount END) BonusAmountQ1,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=2 THEN bonus.BonusAmount END) BonusAmountQ2,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=3 THEN bonus.BonusAmount END) BonusAmountQ3,

SUM(CASE WHEN DATEPART(QUARTER,bonus.BonusDate)=4 THEN bonus.BonusAmount END) BonusAmountQ4,

SUM(bonus.BonusAmount) TotalBonusAmount

FROM EmployeesBonus bonus

JOIN Employees emp ON bonus.EmployeeID=emp.ID

GROUP BY GROUPING SETS(

             (YEAR(bonus.BonusDate),bonus.EmployeeID,emp.Name), -- Имя сотрудника

             (YEAR(bonus.BonusDate)), -- Сумма по годам

             () -- Общий итог

        )

 

При помощи GROUPING SET можно явно указать какие именно итоги нам нужны, поэтому здесь можно обойтись без предложения HAVING.

Т.е. можно сказать, что GROUP BY ROLLUP частный случай GROUP BY GROUPING SETS, когда делается вывод всех итогов.

Пример использования FULL JOIN

Здесь для примера выведем для каждого сотрудника сводные данные по начислениям бонусов и ЗП, поквартально:

-- пример использования FULL JOIN

WITH cteBonus AS(

SELECT

YEAR(BonusDate) BonusYear,

EmployeeID,

SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=1 THEN BonusAmount END) BonusAmountQ1,

SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=2 THEN BonusAmount END) BonusAmountQ2,

SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=3 THEN BonusAmount END) BonusAmountQ3,

SUM(CASE WHEN DATEPART(QUARTER,BonusDate)=4 THEN BonusAmount END) BonusAmountQ4,

SUM(BonusAmount) TotalBonusAmount

FROM EmployeesBonus

GROUP BY YEAR(BonusDate),EmployeeID

),

cteSalary AS(

SELECT

YEAR(SalaryDate) SalaryYear,

EmployeeID,

SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=1 THEN SalaryAmount END) SalaryAmountQ1,

SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=2 THEN SalaryAmount END) SalaryAmountQ2,

SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=3 THEN SalaryAmount END) SalaryAmountQ3,

SUM(CASE WHEN DATEPART(QUARTER,SalaryDate)=4 THEN SalaryAmount END) SalaryAmountQ4,

SUM(SalaryAmount) TotalSalaryAmount

FROM EmployeesSalary

GROUP BY YEAR(SalaryDate),EmployeeID

)

 

SELECT

ISNULL(s.SalaryYear,b.BonusYear) AccYear,

ISNULL(s.EmployeeID,b.EmployeeID) EmployeeID,

s.SalaryAmountQ1,s.SalaryAmountQ2,s.SalaryAmountQ3,s.SalaryAmountQ4,

s.TotalSalaryAmount,

b.BonusAmountQ1,b.BonusAmountQ2,b.BonusAmountQ3,b.BonusAmountQ4,

b.TotalBonusAmount,

ISNULL(s.TotalSalaryAmount,0)+ISNULL(b.TotalBonusAmount,0) TotalAmount

FROM cteSalary s

FULL JOIN cteBonus b ON s.EmployeeID=b.EmployeeID AND s.SalaryYear=b.BonusYear

 

Попробуйте самостоятельно разобрать, почему я здесь применил именно FULL JOIN. Посмотрите на результаты, которые дают запросы размещенные в блоке WITH.

Приложение 2 – OVER и аналитические функции

Предложение OVER служит для проведения дополнительных вычислений, на окончательном наборе, полученном оператором SELECT (в подзапросах или запросах). Поэтому предложения OVER может быть применено только в блоке SELECT, т.е. его нельзя использовать, например, в блоке WHERE.

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

Для демонстрационных целей, для получения более наглядных результатов, добавим немного новых данных:

-- добавим новые должности

SET IDENTITY_INSERT Positions ON

INSERT Positions(ID,Name)VALUES

(10,N'Маркетолог'),

(11,N'Логист')

SET IDENTITY_INSERT Positions OFF

 

-- новые сотрудники

INSERT Employees(ID,Name,DepartmentID,PositionID,HireDate,Salary,Email)VALUES

(1006,N'Антонов А.А.',4,10,'20150215',1800,'a.antonov@test.tt'),

(1007,N'Максимов М.М.',5,11,'20150405',1200,'m.maksimov@test.tt'),

(1008,N'Данилов Д.Д.',5,11,'20150410',1200,'d.danolov@test.tt'),

(1009,N'Остапов О.О.',5,11,'20150415',1200,'o.ostapov@test.tt')