понедельник, 18 апреля 2011 г.

Основы SQL – DML – SELECT – Часть III

В прошлый раз в этой серии статей я рассказал об операторе “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”:

  1. Набор столбцов одинаков (хотя названия исходных полей и их типы могут немного отличаться).
  2. В результате используются названия столбцов из первого запроса объединения.
  3. Сортировать результат можно с помощью “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. Постараюсь учесть.

5 комментариев:

  1. Обещал написать в третьей части про более сложные варианты использования GROUP BY и ORDER BY, но слишком о многом хотел написать. Так что обещанное будет в виде отдельной статьи в течение пары дней.

    ОтветитьУдалить
  2. UPD: обещанная статья про CASE и группировки с сортировками: http://www.olegaxenow.com/2011/04/tryuki-v-sql-ispolzuem-case.html

    ОтветитьУдалить
  3. Этот комментарий был удален автором.

    ОтветитьУдалить
  4. спасибо за OVER и ранжирующие функции, вполне доступно

    ОтветитьУдалить
  5. спасибо, все четко

    ОтветитьУдалить