Генерування підсумків

Служба звітності дозволяє без праці додати підсумкові та проміжні суми, не вимагаючи написання запитів, але може виявитися корисним обчислювати підсумки для додатків 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&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2005                  4

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2006                11

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2005                  4

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2006                11

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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:00000 11

X  2006-01-01 00:00:00000 24

Y&nbsp&nbsp&nbsp 2006-08-01 00:00:00000 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:00000 11

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

avg       sum

45        225

Category SalesDate                                Amount

Y&nbsp&nbsp&nbsp 2006-07-01 00:00:00000 54

Y&nbsp&nbsp&nbsp 2006-07-01 00:00:00000 63

Y&nbsp&nbsp&nbsp 2006-03-01 00:00:00000 62 avg sum

61        427

Category SalesDate                                Amount

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

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

avg       sum

53&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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:00000 11

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

sum

225

Category SalesDate                               Amount

Y&nbsp 2006-07-01 00:00:00000 54

Y&nbsp 2006-03-01 00:00:00000 62 sum

427

Category SalesDate                               Amount

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

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

sum

215

avg      sum

54&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 867

Зверніть увагу на те, що в той час як інші функції агрегування автоматично не підставляти імя підсумкового шпальти – його потрібно ідентифікувати в результуючому наборі даних як псевдонім, – функція COMPUTE автоматично підставляє імя стовпця

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*