база данных

Построение отчетов в Transact SQL

Построение отчетов в T-SQL имеет 2 характерные черты:

 

  • Отчет является окончательным, т.е. использоваться больше он не будет. Это не промежуточный результат для дальнейших фильтраций и т.п.
  • Отчет оптимизирован под чтение обычному человеку — удобство чтения.

 

Для этих целей используют методы: группировки (GROUP BY), топ (TOP).

Подведение подитогов

В отчетах обычно принято подводить некоторые подитоги — конструкция WITH ROLLUP (при этом сам сервер делает всю работу):

SELECT ShipCountry, Year(OrderDate), Count(*)
FROM Orders
GROUP BY ShipCountry, Year(OrderDate)
WITH ROLLUP

Есть констукция WITH CUBE — она заменяет собой 2 ROLLUP.

Совместно с вышеперечисленными конструкциями используется GROUPING. Обычно его вызывают, когда идет группировка по нескольким полям, чтобы различить по какому признаку (полю) построен подитог.

Перекрестная таблица PIVOT (кросстабличное выражение, сводная таблица)

Используется оператор PIVOT — идет группировка по двум полям. Разница в том, что при обычной группировке оба признака идут в одну вертикальную ось, а при PIVOT эта группировка разводится по двум перпендикулярным осям.

Операция PIVOT делает 2 действия: группировка + группировка разодится по двум осям.

SELECT ShipCountry, [2010],[2011],[2012] --перечислим все годы вручную, это горизонтальная ось
FROM (
   SELECT Year(OrderDate) AS OrderYear, ShipCountry, OrderID
   FROM Orders
)MyTable --даем название новой таблице
PIVOT (
   COUNT(OrderID) FOR OrderYear IN  [2010],[2011],[2012]
)MyReport  --даем название новой таблице

Операция PIVOT необратима, т.е. обратно к данным уже не вернуться. Есть операция UNPIVOT, но она отменяет только разведение на 2 оси, а группировку — нет.

Функции ранжирования

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

Выделяют функции ранжирования:

 

  • Row_Number () — нумерует строки по заданному полю.

 

SELECT ProductName, UnitPrice
   Row_number() OVER (ORDER BY ProductName ASC),
   Row_number() OVER (ORDER BY UnitPrice DESC)
FROM Products

 

  • Rank () — нумерует строки по заданному полю, но смотрит на содержимое ячеек. Например, при группировке ячеек (двух книг с одинаковой ценой 400р.) функция даст им одинаковые номера. Например, есть два места №5, тогда следующее — №7.
  • Dense_Rank () — как предыдущая, только нумерация по порядку. Например, есть два места №5, тогда следующее — №6.
  • nTile (x) — делит содержимое одной колонки на x-колонок с учетом критерия ранжирования. Пример, на выходе получим 4 новые колонки с учетом критерия цены:

 

SELECT ProductName, UnitPrice
   nTile(4) OVER (ORDER BY UnitPrice DESC)
FROM Products



Добавить комментарий

Ваш e-mail не будет опубликован.