Проста консолідація

Основна сутність запитів консолідації полягає у поверненні замість всіх вибраних рядків тільки однієї, консолідуючою вихідний набір даних (рис 111) При цьому можуть використовуватися різні типи обчислень – від підсумовування до основних статистичних операцій

Важливо відзначити, що в логічному порядку виконання запиту SQL підсумкові функції слідують відразу за пропозицією FROM і фільтрами WHERE Це означає, що дані можуть бути зібрані і відфільтровані до консолідації, без необхідності використання підзапитів

Рис 111 Підсумкова функція створює один рядок на базі набору даних

Основні підсумкові функції

Мова SQL містить безліч підсумкових функцій, які можна використовувати як виразів в інструкції SELECT для отримання підсумкових даних (табл 111)

Таблиця 111 Основні підсумкові функції

Підсумкова функція

Підтримуваний тип даних

Опис

sum ( )

Числовий

Підсумовує всі непусті значення в стовпці

avg()

Числовий

Усереднює всі непусті значення у стовпці Повертається тип даних, відповідний вихідного, так що часто його доводиться явно перетворювати до більшої точності, наприклад avg (cast col as float)

min ()

Числовий, строковий, дати-часу

Повертає з шпальти найменше число, найбільш ранню дату-час або перший рядок, відповідну поточному порядку символів

max ()

Числовий, строковий, дати-часу

Повертає з шпальти найбільше число, саму останню дату-час або останній рядок, відповідну поточному порядку символів

count

([distinct] *)

Будь-який тип даних

Виконує підрахунок всіх рядків в результуючому наборі даних аж до 2147483647 Чи не виконує підрахунок унікальних ідентифікаторів і порожніх значень

oucnt_big ([distinct*] )

Будь-який тип даних

Аналогічна функції count (), але використовує тип даних bigint в результаті може підрахувати аж до 263-1 Рядків

Для прикладів коду, наведених у цій главі, використана невелика таблиця RowData Програма для створення і заповнення цього набору даних наведена на початку сценарію цієї глави Також ви можете завантажити даний сценарій з сайту www SQLServerBible com

CREATE TABLE RawData (

Region VARCHAR(10),

Category CHAR(l),

Amount INT,

SalesDate DateTime

Наступний простий запит консолідації даних підраховує кількість рядків у таблиці та суму значень стовпця Amount Замість повернення реальних рядків з таблиці RawData, цей запит формує одну підсумкову рядок з кількістю і сумою значень:

SELECT

Count(*) as Count,

Sum(Amount) as [Sum]

FROM RawData

Буде отримано наступний результат:

Count  Sum

20    946

Порожні значення не враховуються ні жодній підсумковій функцією або інший Увага операцією SET

Якщо ви використовуєте конструктор запитів утиліти Management Studio, то будь-який запит можна перетворити в запит консолідації даних за допомогою кнопки Group By панелі інструментів (рис 112) Стовпець Group By використовується для вибору стовпців запиту для підсумкових функцій і угруповання Щоб знайти конструктор запитів, відкрийте таблицю у вікні Object Explorer

Рис 112 Виконання запиту консолідації даних в конструкторі запитів утиліти Management Studio

Використовувати підсумкові функції в інструкції SELECT досить просто, однак при цьому не можна забувати деякі правила

■ Так як SQL при використанні підсумкових функцій в запиті повертає інформацію з безлічі, а не створює набір даних, всі стовпці (що містяться в списку стовпців інструкції або в реченні ORDER BY) повинні брати участь у будь-яких підсумкових функціях Це цілком логічно, оскільки якщо в деякій рядку запит повертає загальна кількість прийнятих замовлень, то він не поверне в ній же номер конкретного замовлення

■ Параметр консолідації DISTINCT служить тієї ж мети, що і вираз SELECT DISTINCT, за винятком того, що в даному випадку запобігається дублювання не рядків, а значень З цієї причини цей параметр не має сенсу використовувати у функції sum () або avg () Функція count (distinct *) не вірна: у ній явно повинен бути вказаний стовпець

■ Функція count (*) підраховує всі рядки, в той час як функція count (стовпець) підраховує тільки непорожні значень шпальти

■ Оскільки підсумкові функції є виразами, результат буде мати порожнє імя стовпця З цієї причини для іменування таких стовпців використовуйте псевдоніми

■ Точність підсумкових функцій визначається точністю типу даних вихідного стовпця Наприклад, якщо стовпець має цілочисельний тип, результатом функції усереднення avg () також буде ціле число Перетворення даних в тип numeric (9,5) може підвищити точність результату:

SELECT Avg(amount) as [Integer Avg],

Avg(Cast((Amount)as Numeric(9,5))) as [Numeric Avg],

Sum(amount) / Count(*) as [Manual Avg]

FROM RawData

Буде отримано наступний результат:

Integer Avg Numeric Avg Manual Avg

47                        47300000                 39

■ Запити консолідації даних ігнорують порожні значення, так що функції sum () і avg () не завершаться помилково, коли їх зустрінуть Водночас з цієї причини результат вираження sum () / count (*) може відрізнятися від результату функції avg ()

Основи статистики

Статистика – дуже обємна і складна предметна область, і SQL Server не претендує замінити повний пакет програм статистичного аналізу У той же час він може підрахувати стандартне відхилення і варіанса, що важливо для розуміння розподілу чисел, що містяться в стовпці

Одного середнього значення зовсім недостатньо для консолідації безлічі значень (у термінах статистики безліч називають популяцією) Значення, що знаходиться в самому центрі популяції, називають медіаною (Це не одне і те ж, що і середнє значення) Середня ширина смуги розкиду даних щодо значення медіани називається варіансой популяції Наприклад, обидві популяції-(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) і (4, 4, 5, 5, 5, 5, 6, 6) – мають середнє значення 5, але значення першої множини сильніше відхиляються від медіани, ніж другий Стандартне відхилення дорівнює квадратному кореню варіанси – воно описує форму колоколообразной кривої, формованої розподілом популяції

Наступний запит використовує функції StDevP () і VarP () для отримання стандартного відхилення і варіанси всієї популяції таблиці RawData:

SELECT

StDevP(Amount) as [StDev],

VarP(Amount) as [Var]

FROM RawData

Буде отримано наступний результат: StDevP VarP

2427158 83287435            58 911

Якщо вам потрібно виконати розширений статистичний аналіз даних, то ре-Назаметку комендуется експортувати набір даних в програму Excel, де вам буде запропоновано широкий вибір статистичних функцій

При обчисленні варіанси і стандартного відхилення використовувані формули злегка відрізняються для випадків, коли в розрахунок береться вся популяція або тільки вибірка з неї[1] Якщо запит консолідації приймає в розрахунок всю популяцію, використовуйте функції StDevP () і VarP () – вони використовують зміщений або п-метод обчислення відхилення

Водночас, якщо запит використовує вибірку з популяції (тобто її підмножина), використовуйте підсумкові функції St Dev () і Var (), які використовують незміщеними метод або метод п-1 Так як угруповання даних в запиті розбиває популяцію на підмножини, в таких запитах рекомендується використовувати функції StDevO і Var ()

Додаткова Про функції розстановки, включаючи обчислюється відсоток, див в розділі 7

інформація

Джерело: Нільсен, Пол 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>

*

*