История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...
Эмиссия газов от очистных сооружений канализации: В последние годы внимание мирового сообщества сосредоточено на экологических проблемах...
Топ:
Выпускная квалификационная работа: Основная часть ВКР, как правило, состоит из двух-трех глав, каждая из которых, в свою очередь...
Характеристика АТП и сварочно-жестяницкого участка: Транспорт в настоящее время является одной из важнейших отраслей народного...
Интересное:
Подходы к решению темы фильма: Существует три основных типа исторического фильма, имеющих между собой много общего...
Искусственное повышение поверхности территории: Варианты искусственного повышения поверхности территории необходимо выбирать на основе анализа следующих характеристик защищаемой территории...
Влияние предпринимательской среды на эффективное функционирование предприятия: Предпринимательская среда – это совокупность внешних и внутренних факторов, оказывающих влияние на функционирование фирмы...
Дисциплины:
2020-05-08 | 176 |
5.00
из
|
Заказать работу |
|
|
Данные конструкции позволяют подбить промежуточные итоги по строкам.
Пример первый:
-- 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,'[email protected]'),
(1007,N'Максимов М.М.',5,11,'20150405',1200,'[email protected]'),
(1008,N'Данилов Д.Д.',5,11,'20150410',1200,'[email protected]'),
(1009,N'Остапов О.О.',5,11,'20150415',1200,'[email protected]')
|
|
Историки об Елизавете Петровне: Елизавета попала между двумя встречными культурными течениями, воспитывалась среди новых европейских веяний и преданий...
Особенности сооружения опор в сложных условиях: Сооружение ВЛ в районах с суровыми климатическими и тяжелыми геологическими условиями...
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
История развития хранилищ для нефти: Первые склады нефти появились в XVII веке. Они представляли собой землянные ямы-амбара глубиной 4…5 м...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!