Главная » Microsoft SQL Server, Базы данных » Генерирование итогов

0

Служба отчетности позволяет без труда добавить итоговые и промежуточные суммы, не требуя написания запросов, но может оказаться полезным вычислять итоги для приложений .NET, чтобы отображать их в окне формы или Web-страницы. Следующие три итоговые команды являются идеальным решением этой задачи.

Запросы сведения

Итоговые функции rollup и cube генерируют общие и промежуточные итоги как отдельные строки и подставляют пустое значение в столбец group by, чтобы обозначить общий итог. Функция rollup последовательно генерирует строки общих и промежуточных итогов для столбцов group by. Функция cube расширяет эти возможности, генерируя промежуточные итоги для всех столбцов group by. Специальная функция grouping () имеет истинное значение, когда строка является общим или промежуточным итогом. В этом разделе я продемонстрирую функцию rollup.

Параметр ROLLUP, помещенный в предложение GROUP BY, дает указание SQL Server генерировать дополнительную строку итогов. В приведенном ниже примере функция rollup О используется в выражении CASE для преобразования стандартной итоговой строки в нечто осмысленное.

SELECT

CASE Grouping(Category)

WHEN 0 THEN Category

WHEN 1 THEN ‘All Categories’

END AS Category,

Count(*) as Count

FROM RawData

GROUP BY Category WITH ROLLUP

Результат выполнения запроса будет следующим:

Category      Count

X                         5

Y                             15

Z                         4

All Categories   24

Добавляя второй столбец, Year (SalesDate), с подведением промежуточных итогов по годам, мы указываем SQL Server вычислять по нему промежуточные итоги:

SELECT

CASE Grouping(Category)

WHEN 0 THEN Category

WHEN 1 THEN ‘All Categories’

END AS Category,

CASE Grouping(Year(SalesDate))

WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))

WHEN 1 THEN ‘All Years’

END AS Year,

Count(*) as Count FROM RawData

GROUP BY Category, Year(SalesDate)

WITH ROLLUP

Результат этого запроса следующий:

Category   Year           Count

X              2006                   5

X              All Years            5

Y                       2005                  4

Y                       2006                11

Y                      All Years           15 Z        2006        4 Z          All Years                4 All Categories All Years 24

Кубические запросы

Кубические запросы являются логическим развитием запросов сведения. Они добавляют промежуточные итоги для всех измерений. В рассмотренном ранее примере запрос сведения создавал промежуточные итоги для каждой категории; кубический запрос создаст итоги и для каждого года.

SELECT

CASE Grouping(Category)

WHEN 0 THEN Category

WHEN 1 THEN ’All Categories’

END AS Category,

CASE Grouping(Year(SalesDate))

WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))

WHEN 1 THEN ‘All Years’

END AS Year, Count(*) as Count FROM RawData

GROUP BY Category, Year(SalesDate)

WITH CUBE ORDER BY IsNull(Category, ‘zzz ‘ )

Результат запроса будет следующим:

Category   Year           Count

X              2006                   5

X              All Years           5

Y                       2005                  4

Y                       2006                11

Y                      All Years        15 Z           2006        4 Z          All Years                4 All Categories      All Years                24 All Categories    2005        4 All Categories      2006        20

Вычисления итогов

Предложение COMPUTE полностью отличается от всех запросов консолидации данных. Добавленное в конец, оно позволяет превратить любой обычный запрос в запрос консолидации данных. Такой запрос вернет обычный набор данных со строками деталей, а затем добавит в его конец сводную информацию о том же наборе данных. В следующем примере возвращается вся таблица RowData, за которой следует нечто подобное второму набору данных со средними значениями и суммами:

SELECT Category, SalesDate, Amount FROM RawData

WHERE Year(SalesDate) = ‘2006’

COMPUTE Avg(Amount), sum(Amount)

Результат выполнения запроса будет следующим:

Category SalesDate                                Amount

X  2006-01-01 00:00:00.000 11

X  2006-01-01 00:00:00.000 24

Y    2006-08-01 00:00:00.000 NULL avg sum

54        867

Предложение COMPUTE может даже содержать собственное миниатюрное предложение GROUP BY. В таком случае все строки деталей будут разделены на группы со своими промежуточными итогами, почти как в полноценном отчете:

SELECT Category, SalesDate, Amount FROM RawData

WHERE Year(SalesDate) = ‘2006’

ORDER BY Category

COMPUTE Avg(Amount), sum(Amount)

BY Category

Результат запроса будет следующим:

Category SalesDate                                Amount

X  2006-01-01 00:00:00.000 11

X  2006-06-01 00:00:00.000 86

avg       sum

45        225

Category SalesDate                                Amount

Y    2006-07-01 00:00:00.000 54

Y    2006-07-01 00:00:00.000 63

Y    2006-03-01 00:00:00.000 62 avg sum

61        427

Category SalesDate                                Amount

Z   2006-04-01 00:00:00.000 33

Z   2006-05-01 00:00:00.000 55

avg       sum

53             215

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

SELECT Category, SalesDate, Amount FROM RawData

WHERE Year(SalesDate) = ‘2006’

ORDER BY Category

COMPUTE avg(Amount), sum(amount)

COMPUTE sum(Amount)

BY Category

Результат выполнения запроса следующий:

Category SalesDate                               Amount

X 2006-01-01 00:00:00.000 11

X 2006-06-01 00:00:00.000 86

sum

225

Category SalesDate                               Amount

Y  2006-07-01 00:00:00.000 54

Y  2006-03-01 00:00:00.000 62 sum

427

Category SalesDate                               Amount

Z 2006-04-01 00:00:00.000 33

Z 2006-05-01 00:00:00.000 55

sum

215

avg      sum

54           867

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии