Главная » Microsoft SQL Server, Базы данных » Простая консолидация

0

Основная сущность запросов консолидации заключается в возвращении вместо всех выбранных строк только одной, консолидирующей исходный набор данных (рис. 11.1). При этом могут использоваться различные типы вычислений — от суммирования до основных статистических операций.

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

Рис. 11.1. Итоговая функция создает одну строку на базе набора данных

Основные итоговые функции

Язык SQL содержит множество итоговых функций, которые можно использовать в качестве выражений в инструкции SELECT для получения итоговых данных (табл. 11.1).

Таблица 11.1. Основные итоговые функции

Итоговая функция

Поддерживаемый тип данных

Описание

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 панели инструментов (рис. 11.2). Столбец Group By используется для выбора столбцов запроса для итоговых функций и группировки. Чтобы найти конструктор запросов, откройте таблицу в окне Object Explorer.

Рис. 11.2. Выполнение запроса консолидации данных в конструкторе запросов утилиты 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                        47.300000                 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

24.27158 83287435            58 9.11

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

При вычислении вариансы и стандартного отклонения используемые формулы слегка отличаются для случаев, когда в расчет берется вся популяция или только выборка из нее[1]. Если запрос консолидации принимает в расчет всю популяцию, используйте функции StDevP () и VarP () — они используют смещенный или п-метод вычисления отклонения.

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

Дополнительная О функциях расстановки, включая вычисляющие процентили, см. в главе 7.

информация

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

По теме:

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