О разработке ПО и эффективности: Основы SQL – DML – SELECT

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

Содержание:

DISTINCT

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

SELECT DISTINCT LEFT(Phone,3) FROM Phones

Про использование “DISTINCT”, в силу его простоты, многого не скажешь. Однако, стоит сказать несколько слов про то, когда его использовать нежелательно. Предположим, нам необходимо получить список всех абонентов, у которых есть хотя бы один телефон. Теоретически, можно это сделать с помощью “DISTINCT”:

SELECT DISTINCT a.PersonID, a.LastName
    FROM Persons a JOIN Phones t ON a.PersonID = t.PersonID

Однако в данном случае правильнее использовать конструкцию “WHERE EXISTS”:

SELECT a.PersonID, a.LastName
    FROM Persons a
    WHERE EXISTS(SELECT * FROM Phones t WHERE a.PersonID = t.PersonID)

В первом случае мы говорим – “соедини две таблицы, а потом исключи дубликаты”, а во втором – “возьми только те строки из первой таблицы, для которых есть соответствие во второй”. Логика подсказывает, что второй вариант экономичнее с точки зрения ресурсов.

Скажу по секрету, что современные версии Microsoft SQL Server обычно интерпретируют подобные запросы одинаково. Однако, для более сложных запросов лучше не искушать судьбу и хотя бы сравнить быстродействие обоих вариантов и план выполнения (о плане выполнения и оптимизаторе я скорее всего расскажу в другой серии статей).

Забегая вперед скажу, что поведение, аналогичное тому, что дает “DISTINCT”, можно получить с помощью “GROUP BY”. По большому счету, “DISTINCT” можно считать синтаксическим сахаром, который удобен в тех случаях, когда список столбцов в результате неудобно перечислять еще раз в “GROUP BY”.

Объединения (UNION)

С помощью “UNION” можно объединить результаты двух или более запросов в один, при условии, что структура результатов этих запросов совпадает (количество столбцов и совместимые типы). При этом “UNION” в чистом виде исключает дубликаты строк на манер “DISTINCT”, а “UNION ALL” не гарантирует уникальность. Нетрудно догадаться, что в случае, когда дубликатов гарантированно нет, лучше использовать “UNION ALL” – иногда это поможет ускорить запрос, с помощью всего лишь трех букв :)

Допустим, что у нас извращенная логика и мы хотим получить фамилии с последующим списком телефонов абонента одним запросом, с минимум дублирующейся информации. В реальной жизни, как правило, лучше сделать это двумя запросами (можно в одном batch’е), но сейчас это неплохая иллюстрация работы “UNION”:

SELECT PersonID, LastName, 0 AS IsPhone FROM Persons
UNION ALL
SELECT PersonID, Phone, 1 FROM Phones
ORDER BY PersonID, IsPhone

Строго говоря, в результате этого запроса все равно присутствует дублирующаяся информация – “PersonID” (без включения этого столбца не получилось бы правильно отсортировать результат запроса). Избавится от него можно с помощью запроса к приведенному “SELECT” как подзапросу.

На примере выше мы видим правила использования “UNION”:

Набор столбцов одинаков (хотя названия исходных полей и их типы могут немного отличаться).
В результате используются названия столбцов из первого запроса объединения.
Сортировать результат можно с помощью “ORDER BY” для последнего запроса (помним о пункте 2).

Группировки (GROUP BY)

В этой главе речь пойдет о группировках. Речь разумеется по-прежнему идет о запросах, а не о вооруженных формированиях :)

Группировать данные можно по любым доступным в запросе столбцам (в том числе и тем, которых нет в “select list”). С другой стороны, если столбец или выражение присутствует в “select list”, то оно (или все составляющие его столбцы) либо должно присутствовать в “GROUP BY”, либо вычисляться с помощью статистической функции (о них речь пойдет чуть позже).

Для начала рассмотрим пример, аналогичный первому примеру с “DISTINCT”:

SELECT LEFT(Phone,3) FROM Phones GROUP BY Phone

Этот запрос полностью аналогичен запросу с “DISCTINCT”… подумают некоторые, и будут неправы :) Если посмотреть внимательно, то этот запрос сначала получит все уникальные телефоны, а потом выведет первые три символа (при этом, одни и те же первые три символа могут встречаться в результате несколько раз). Правильный аналог выглядит так:

SELECT LEFT(Phone,3) FROM Phones GROUP BY LEFT(Phone,3)

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

SELECT PersonID FROM Phones GROUP BY Phone
SELECT LastName, FirstName FROM Persons GROUP BY LastName + FirstName

Обратите внимание – в последнем запросе можно поменять местами выражения, и мы получим корректный запрос:

SELECT LastName + FirstName FROM Persons GROUP BY LastName, FirstName

Почему так происходит? Потому что в результате группировки по выражению, мы можем получить для нескольких наборов исходных столбцов лишь одну результирующую строку (тогда придется выбрать только одно сочетание, что приведет к некорректному запросу). А в обратном примере мы можем, в худшем случае, получить повторяющиеся значения выражения, что, в общем-то неудобно, однако не противоречит логике.

Я специально подобрал не очень очевидный пример, чтобы объяснить принципы. Наиболее наглядно выглядит такой пример некорректного запроса:

SELECT Phone FROM Phones GROUP BY LEFT(Phone,3)

Здесь более очевидно то, что мы можем, скажем, сгруппировать все телефоны с кодом 495 в одну строку, но что в ней вывести?

Статистические функции

Если бы с помощью группировок можно было бы только группировать значения на манер “DISTINCT”, то они, наверное, не пользовались особой популярностью. Тем более, что при большом количестве столбцов запрос с “DISTINCT” получается компактнее.

Для того, чтобы использовать группировки “на всю катушку”, вам потребуется познакомиться со статистическими функциями. Копировать MSDN и описывать каждую из них не вижу смысла, поэтому просто постараюсь показать, как они работают и перечислю наиболее часто используемые:

COUNT – количество записей в группе
MIN/MAX – минимальное или максимальное значение в группе
SUM – сумма числовых значений в группе (строки, битовые или бинарные значения не допускаются)

Как работает группировка со статистической функцией? Давайте представим, что у нас в таблице Persons сравнительно много записей и мы хотим выяснить первые десять самых распространенных фамилий.

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

Однако, к счастью, вам, скорее всего, не придется заниматься подобной работой, а вот писать SQL-запросы для выполнения таких задач, вполне вероятно, доведется. Ниже приведен запрос, выводящий первые десять наиболее распространенных фамилий (для наглядности, увеличил количество одной распространенной фамилии :)

IF NOT EXISTS(SELECT * FROM Persons WHERE FirstName = 'Константин' 
        AND LastName ='Аксенов')
    INSERT Persons (FirstName, LastName) VALUES('Константин', 'Аксенов')

SELECT TOP 10 LastName, COUNT(*) AS LastNameCount FROM Persons
    GROUP BY LastName ORDER BY LastNameCount DESC

По аналогии пишутся более сложные запросы (где больше группирующих полей, функций и таблиц во “FROM”).

Фильтрация групп (HAVING)

Фильтрация групп позволяет отфильтровать результаты группировки, в том числе, по статистическим функциям:

SELECT LastName FROM Persons GROUP BY LastName HAVING COUNT(*) = 1

В общем-то “HAVING” мало отличается от “WHERE”, помимо того, что работает на группах. Однако, если условие можно записать в “WHERE”, то его, как правило, нужно там записать. Допускаю, что есть экзотические ситуации, когда выполнение этого правила может привести к ухудшению производительности (но уж по наглядности кода оно в любом случае в выигрыше).

OVER, пейджинг и ранжирующие функции

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

Начиная с Microsoft SQL Server 2005 появилась возможность использовать “OVER”, одним из самых распространенных применений которого является пейджинг с помощью ранжирующей функции “ROW_NUMBER”. Простейший пример выглядит так:

SELECT * FROM (SELECT ROW_NUMBER() 
        OVER(ORDER BY LastName) AS RowNumber, *
    FROM Persons ) t
WHERE RowNumber BETWEEN 1 AND 2
ORDER BY LastName

Тема пейджинга достойна отдельной статьи, поэтому здесь подробно на нем останавливаться не буду. В принципе, в наши дни ORM (к примеру, Entity Framework) обычно генерируют достаточно неплохой код для пейджинга, который, кстати, можно посмотреть с помощью SQL Profiler. Для тех, кто интересуется разными способами организации пейджинга, в том числе для старых версий Microsoft SQL Server, могу порекомендовать FAQ на SQL.RU. Также от себя добавлю, что пейджинг, какой бы он хороший не был, может быть очень требовательным к ресурсам на больших объемах данных или при большом количестве запросов, поэтому еще несколько советов лично от меня, на случай если пейджинг работает медленно:

Попробуйте другой пейджинг (в разных ситуациях могут быть выигрышными разные алгоритмы).
Можно использовать заранее сохраненные значения (особенно актуально для статистики “лучшие 10” на сайтах с высокой посещаемостью).
Можно использовать тот факт, что редко кто-либо листает более чем 5 страниц с обоих краев, поэтому можно оптимизировать эти два варианта (например, по предыдущему пункту), а самым упорным и не желающим использовать фильтр пользователям придется подождать :) Кстати, IMHO, хорошая практика – мягко посоветовать пользователю (в интерфейсе) уточнить критерии поиска, если количество страниц слишком велико.
Если это возможно, попробуйте сделать пейджинг менее функциональным (например, не показывать количество страниц) или использовать особенности ваших данных.

Прежде чем я перейду к ранжирующим функциям, приведу пример использования статистических функций и фразы “PARTITION BY” (которая разбивает строки на секции):

SELECT PersonID, LastName, FirstName, MiddleName,
    COUNT(*) OVER(PARTITION BY LastName) AS LastNameCount,
    MAX(PersonID) OVER(PARTITION BY LEN(LastName)) AS MaxID
FROM Persons

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

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

SELECT id, parent_obj, name, xtype,
    ROW_NUMBER() OVER (PARTITION BY parent_obj
        ORDER BY xtype) AS RowNumber,
    RANK() OVER (PARTITION BY parent_obj
        ORDER BY xtype) AS [Rank],
    DENSE_RANK() OVER (PARTITION BY parent_obj
        ORDER BY xtype) AS DenseRank,
    NTILE(15) OVER (PARTITION BY parent_obj
        ORDER BY xtype) AS [NTile]
FROM sysobjects  WHERE xtype != 'S'
ORDER BY parent_obj, xtype

В примере я рассчитывал на БД, структура которой подготовлена по скриптам из предыдущих статей – тогда результат будет достаточно нагляден и мы увидим, что:

ROW_NUMBER – делает нумерацию в заданном порядке внутри каждой секции;
RANK – это “значимость” внутри секции относительно параметра сортировки (в нашем случае – “xtype”). Считается он начиная с 1, а каждый следующий – как сумма строк до него, но при этом для одинаковых значений “xtype” ранг одинаков;
DENSE_RANK – это аналог “RANK”, но он считается по количеству разных рангов до него, поэтому, в отличие от своего собрата, всегда последователен (без пропусков);
NTILE(N) – разбивает секцию на заданное количество групп. Если количество строк не делится нацело, то в последних группах количество строк будет на одну меньше чем в первых.

За кадром

За кадром при обсуждении оператора SELECT остались, в том числе, такие нововведения в Microsoft SQL 2005 и 2008 как:

APPLY – если вкратце, позволяет передавать столбцы таблицы в функции
PIVOT/UNPIVOT – сворачивание строк в столбцы и обратно
TABLESAMPLE – еще один способ ограничения размера выборки из таблицы
WITH (CTE) – обобщенные табличные выражения (common table expressions) позволяют, прежде всего, делать рекурсивные запросы (не рекурсивный вариант не слишком отличается от подзапроса во FROM)
GROUPING SETS, ROLLUP и CUBE – дополнительные возможности при группировке

Также я не рассматривал OPENXML – возможность конвертации XML-документа (или его части) в некую виртуальную таблицу.

Резюме

После завершения этой статьи я подумал, что серию статей “Основы SQL” можно считать законченной – полностью эту тему все равно не объять, а обзор базовых понятий вроде бы получился. В будущем, вероятно, я расскажу об SQL и базах данных некоторые трюки и более сложные вещи. Также планирую рассказать об Entity Framework (который тоже достаточно близко связан с этой темой).

Тем кому интересно, могут скачать скрипт с примерами для этой и двух предыдущих статей про SELECT.

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях или на olegaxenow.reformal.ru. Постараюсь учесть.