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

В лекциях курса "Разработка и защита баз данных в Microsoft SQL Server 2005 " мы научились проектировать и создавать базы данных, защищать их от несанкционированного использования и непредвиденных потерь данных, а также переносить данные в другие места. В данном курсе рассказывается о том, как манипулировать данными, хранящимися в базе данных; в этой лекции мы начинаем с вычисления итогов и другой сводной информации о данных в таблицах.

Прежде, чем приступить к чтению этой лекции, запустите SQL Server Management Studio, установите соединение с сервером SQL Server и выбрав базу данных Adventure Works, откройте окно New Query (Новый запрос).

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

Подсчет строк

Иногда в приложениях нужно сообщить пользователю, сколько элементов имеется в базе данных.

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

Тот же пример, написанный на языке Visual Basic, выглядит так:

Dim Counter As Integer = 0 
While dataReader.Read
  Counter += 1 
End While 
dataReader.Close()

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

Существует другой способ получить количество строк в том же объекте DataReader:

Dim Counter As Integer = dataReader.RecordsAffected()

Однако эта процедура при соединении с базой данных использует столько же сетевых ресурсов, что и предыдущая.

Как и в других задачах, имеющих отношение к базам данных, производительность системы будет более оптимальной, если доступ к данным будет выполнен внутри самой базы данных.

Использование функций T-SQL для вычисления количества записей

Язык Transact SQL (T-SQL) имеет особые функции для агрегации (обобщения информации о данных), эти функции являются скалярными (возвращают только одно значение) и обычно оперируют набором данных.

Используем функцию COUNT

Если нужно подсчитать записи в таблице, можно использовать следующий сценарий:

SELECT COUNT(*) AS [Count] FROM Production.Product

Совет. Да. Здесь (и только здесь) можно использовать *.

Запускаем пример приложения, написанного на Visual Basic и использующего функцию Count

1. Откройте файл Chapter05\Chapter 05.sln с компакт-диска.
2. Откроется окно Microsoft Visual Studio. В меню Build (Построение) выберите Build (Построить) Chapter 05.
3. Из меню Debug (Отладка) выберите команду Start Debugging (Начать отладку). Это действие запускает приложение, вызывающее агрегатные функции. Важно отметить, что в этот момент для выполнения дальнейших действий база данных AdventureWorks должна быть присоединена локально.
4. В меню Database (База данных) выберите команду Connect (Соединиться).
5. В меню Demonstrations (Демонстрация) выберите команду Counting Records. Эти действия запускают приложение Counting Records. Теперь можно нажать три кнопки Run (Запуск) для выполнения трех различных агрегатных функций и сравнить их рабочие циклы.

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

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

Функцию COUNT можно использовать несколькими способами. Первый способ просто считает записи. В этом случае функция COUNT в качестве аргумента получает символ звездочки ( * ).

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

Следующее предложение возвращает два различных результата для одной и той же таблицы:

SELECT COUNT(*) AS [Count],
       COUNT(Class) AS [Classes with values] 
  FROM Production.Product

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

SELECT COUNT(DISTINCT CardType) AS [Different Credit Cards] FROM Sales.CreditCard

Совет. Функция COUNT возвращает тип данных int, а функция COUNT_BIG выполняет те же агрегации, но тип данных возвращаемых ею значений будет bigint. Работая с таблицами, которые содержат миллионы записей, следует использовать функцию COUNT_BIG.

Фильтрация результатов

Иногда приходится выполнять такие запросы к базе данных, которые отвечали бы на вопрос вроде: "Сколько у нас покупателей из Сиэтла?" Чтобы ответить на этот вопрос, нельзя использовать просто COUNT(*) или COUNT(DISTINCT имя_поля). Процесс подсчета требует использования фильтра.

По сути, синтаксис агрегации - это просто модификация инструкции SELECT. Учитывая это, можно фильтровать запрос при помощи предложения WHERE так же, как и в любой другой инструкции SELECT.

SELECT COUNT(*) AS [Customers In Seattle] 
  FROM Person.Address 
  WHERE (City = "Seattle")

Однако было бы лучше, если можно было бы предоставить информацию о пользователях для нескольких городов, а не только для одного города. Конечно, можно повторить запрос с параметром в предложении WHERE, чтобы пользователи могли указать город, который их интересует. Но что, если нужно ответить на вопрос: "Сколько у нас покупателей в каждом городе?"

Чтобы ответить на этот вопрос, придется изменить инструкцию SELECT, добавив в нее предложение GROUP BY. Посмотрите на этот сценарий и возвращенный им результат.

SELECT City, COUNT(*) AS [Count of Customers] 
  FROM Person.Address GROUP BY City

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

Создаем итоговую сводку с сортировкой данных

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Откройте окно New Query (Новый запрос), нажав кнопку New Query (Новый запрос) на панели инструментов. Введите и выполните следующий сценарий для сортировки результатов при помощи предложения ORDER BY. (Этот сценарий и все другие примеры на использование функции COUNT в этом разделе можно найти в файлах примеров в папке \SqlScripts под именем CountExamplesFromText.sql )

   SELECT City, COUNT(*) AS [Count of Customers] 
     FROM Person.Address GROUP BY City ORDER BY City
2. Возможно, вам понадобится еще раз отфильтровать данные для ответа на вопрос: "Сколько покупателей у нас в каждом городе, если брать в расчет только такие города, в которых проживает более 50 покупателей?". Это особый случай, потому что нужно отфильтровать результаты агрегации COUNT после выполнения вычислений. Однако у T-SQL есть решение и для этого случая. Нажмите кнопку New Query (Новый запрос), затем введите и выполните следующий сценарий для фильтрации агрегата COUNT при помощи предложения HAVING.

   SELECT City, COUNT(*) AS [Count of Customers]
     FROM Person.Address
     GROUP BY City
     HAVING (COUNT(*) > 50)
     ORDER BY City

Различие между предложениями WHERE и HAVING заключается в типе информации, к которой применяется каждый фильтр. Предложение WHERE фильтрует оригинальный набор информации из таблицы. Предложение HAVING фильтрует информацию, полученную после выполнения агрегатных функций, при этом фильтры обычно основываются на результатах агрегации.

Если мы рассмотрим предполагаемый план выполнения для сценария, использующего предложение WHERE, и другого сценария, использующего предложение HAVING, разница между этими двумя сценариями станет очевидной: Ознакомьтесь с предполагаемыми планами выполнения для двух предыдущих запросов при помощи следующей процедуры. Эти два плана выполнения показаны на рисунках 1.1 и 1.2.

увеличить изображение
Рис. 1.1. Предполагаемый план выполнения для предложения T-SQL, использующего агрегации и предложение WHERE

увеличить изображение
Рис. 1.2. Предполагаемый план выполнения для предложения T-SQL, использующего агрегации и предложение HAVING

Просматриваем предполагаемый план выполнения

1. Выделите сценарий, план выполнения которого нужно просмотреть.
2. Щелкните на нем правой кнопкой и выберите из контекстного меню команду Display Estimated Execution Plan (Показать предполагаемый план выполнения).

Примечание. Предполагаемый план выполнения в графической форме демонстрирует, как будет обрабатываться запрос и в какой степени каждый этап влияет на стоимость запроса. Ознакомьтесь в электронной документации по SQL Server 2005 с темой "Как вывести на экран предполагаемый план выполнения", чтобы узнать значение всех элементов графического представления.