Транслирует запрос SELECT во внутренний план исполнения («query plan»), который может различаться даже для синтаксически одинаковых запросов и от конкретной СУБД.
Оператор SELECT состоит из нескольких предложений (разделов):
Оператор SELECT имеет следующую структуру:
SELECT [ DISTINCT | DISTINCTROW | ALL ] select_expression ,... FROM table_references [ WHERE where_definition ] [ GROUP BY { unsigned_integer | col_name | formula } ] [ HAVING where_definition ] [ ORDER BY { unsigned_integer | col_name | formula } [ ASC | DESC ], ...]
Используется для определения, какие строки должны быть выбраны или включены в GROUP BY.
GROUP BY - необязательный параметр операторa SELECT , для группировки строк по результатам агрегатных функций (MAX, SUM, AVG, …).
HAVING - необязательный (опциональный) параметр оператора SELECT для указания условия на результат агрегатных функций (MAX, SUM, AVG, …).
HAVING <условия> аналогичен WHERE <условия> за исключением того, что строки отбираются не по значениям столбцов, а строятся из значений столбцов, указанных в GROUP BY , и значений агрегатных функций, вычисленных для каждой группы, образованной GROUP BY.
Необходимо, чтобы в SELECT были заданы только требуемые в выходном потоке столбцы, перечисленные в GROUP BY и/или агрегированные значения. Распространённая ошибка - указание в SELECT столбца, пропущенного в GROUP BY.
Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы, полностью дублируя (допускается не во всех реализациях стандарта SQL).
ORDER BY - необязательный (опциональный) параметр операторов SELECT и UNION , который означает что операторы SELECT , UNION возвращают набор строк, отсортированных по значениям одного или более столбцов. Его можно применять как к числовым столбцам, так и к строковым. В последнем случае, сортировка будет происходить по алфавиту .
Использование предложения ORDER BY является единственным способом отсортировать результирующий набор строк. Без этого предложения СУБД может вернуть строки в любом порядке. Если упорядочение необходимо, ORDER BY должен присутствовать в SELECT , UNION .
Сортировка может производиться как по возрастанию, так и по убыванию значений.
SELECT * FROM T ;
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
SELECT C1 FROM T ;
| C1 |
|---|
| 1 |
| 2 |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 2 | b |
| 1 | a |
Для таблицы T запрос
SELECT * FROM T ;
вернёт все столбцы всех строк данной таблицы. Для той же таблицы запрос
SELECT C1 FROM T ;
вернёт значения столбца C1 всех строк таблицы- в терминах реляционной алгебры проекция . Для той же таблицы запрос
вернёт значения всех столбцов всех строк таблицы, у которых значение поля C1 равно "1"- в терминах реляционной алгебры можно сказать, что была выполнена выборка , так как присутствует ключевое слово WHERE. Последний запрос
SELECT * FROM T ORDER BY C1 DESC ;
вернёт те же строки, что и первый, однако результат будет отсортирован в обратном порядке (Z-A) из-за использования ключевого слова ORDER BY с полем C1 в качестве поля сортировки. Этот запрос не содержит ключевого слова WHERE, поэтому он вернёт всё, что есть в таблице. Несколько элементов ORDER BY могут быть указаны разделённые запятыми [напр. ORDER BY C1 ASC, C2 DESC] для более точной сортировки.
Отбирает все строки, где поле column_name равно одному из перечисленных значений value1,value2,…
Возвращает список идентификаторов отделов, продажи которых превысили 1000 долларов за 1 января 2000 года, вместе с суммами продаж за этот день:
Согласно ISO SQL:2003 возвращаемый набор данных может быть ограничен с помощью:
Существуют различные оконные функции . ROW_NUMBER() OVER может быть использована для простого ограничения числа возвращаемых строк. Например, для возврата не более десяти строк:
ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.
Функция RANK() OVER работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:
Данный код может вернуть более чем 10 строк. Например, если есть два человека с одинаковым возрастом, он вернёт 11 строк.
Не все СУБД поддерживают вышеуказанные оконные функции. При этом многие имеют нестандартный синтаксис для решения тех же задач. Ниже представлены варианты простого ограничения выборки для различных СУБД:
| Производитель/СУБД | Синтаксис ограничения |
|---|---|
| DB2 | (Поддерживает стандарт, начиная с DB2 Version 6) |
SELECT * FROM [ T ] FETCH FIRST 10 ROWS ONLY |
|
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT , MIN , MAX, AVG и SUM ). Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT , не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После чего к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, то есть при группировке по полю, содержащему NULL -значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции, то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT , можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
ВыполнитьSELECT model, COUNT (model) AS Qty_model,
AVG (price) AS Avg_price
FROM PC
GROUP BY model;
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model;
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средняя цена для каждой группы. Результатом выполнения запроса будет следующая таблица
|
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений {модель, дата}.
Существует несколько определенных правил выполнения агрегатных функций.
EXISTS (SELECT MAX (price)
FROM PC
Подзапрос в предикате EXISTS возвращает одну строку с NULL в качестве значения столбца. Поэтому, несмотря на то, что ПК с отрицательными ценами нет в базе данных, запрос в примере вернет 1.
Итак, агрегатные функции, включенные в предложение SELECT запроса, не содержащего предложения GROUP BY , исполняются над всеми результирующими строками этого запроса. Если же запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляют группу, и агрегатные функции выполняются для каждой группы отдельно.
Группировка данных позволяет разделить все данные на логические наборы, благодаря чему становится возможным выполнение статистических вычислений отдельно в каждой группе.
Группы создаются с помощью предложения GROUP BY оператора SELECT . Рассмотрим на примере.
SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product
Данным запросом мы извлекли информацию о количестве реализованной продукции в каждом месяце. Оператор SELECT приказывает вывести два столбца Product - название продукта и Product_num - расчетное поле, которое мы создали для отображения количества реализованной продукции (формула поля SUM (Quantity)). Предложение GROUP BY указывает СУБД сгруппировать данные по столбцу Product . Стоит также отметить, что GROUP BY должен идти после предложения WHERE и перед ORDER BY .
Так же, как мы фильтровали строки в таблице, мы можем осуществлять фильтрацию по сгруппированным данным. Для этого в SQL существует оператор HAVING . Возьмем предыдущий пример и добавим фильтрацию по группам.
SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>4000
Видим, что после того, как была посчитана количество реализованного товара в разрезе каждого продукта, СУБД "отсекла" те продукты, которых было реализовано меньше 4000 шт.
Как видим, оператор HAVING очень похож на оператора WHERE , однако между собой они имеют существенное отличие: WHERE фильтрует данные до того, как они будут сгруппированы, а HAVING - осуществляет фильтрацию после группировки. Таким образом, строки, которые были изъяты предложением WHERE НЕ будут включены в группу. Итак, операторы WHERE и HAVING могут использоваться в одном предложении. Рассмотрим пример:
SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product<>"Skis Long" GROUP BY Product HAVING SUM(Quantity)>4000
Мы к предыдущему примеру добавили оператор WHERE , где указали товар Skis Long , что в свою очередь повлияло на группирование оператором HAVING . Как результат видим, что товар Skis Long не попал в перечень групп с количеством реализованной продукции больше 4000 шт.
Как и при обычной выборке данных, мы можем сортировать группы после группировки оператором HAVING . Для этого мы можем использовать уже знакомый нам оператор ORDER BY . В данной ситуации его применения аналогичное предыдущим примерам. К примеру:
SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY SUM(Quantity)
или просто укажем номер поля по порядку, по которому хотим сортировать:
SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY 2
Видим, что для сортировки сводных результатов нам нужно просто прописать предложения с ORDER BY после оператора HAVING . Однако есть один нюанс. СУБД Access не поддерживает сортировку групп по псевдонимами колонок, то есть в нашем примере, чтобы сортировать значения, мы не сможем в конце запроса прописать ORDER BY Product_num .
В этом учебном материале вы узнаете, как использовать SQL оператор GROUP BY с синтаксисом и примерами.
SQL оператор GROUP BY можно использовать в операторе SELECT для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам.
Синтаксис оператора GROUP BY в SQL:
Давайте посмотрим, как использовать GROUP BY с функцией SUM в SQL.
В этом примере у нас есть таблица employees
со следующими данными:
| dept_id | total_salaries |
|---|---|
| 500 | 119500 |
| 501 | 113000 |
В этом примере мы использовали функцию SUM, чтобы сложить все зарплаты для каждого dept_id , и мы результатам SUM(salary) указали псевдоним «total_salaries». Поскольку dept_id не инкапсулирован в функцию SUM, он должен быть указан в предложении GROUP BY.
Давайте посмотрим, как использовать предложение GROUP BY с функцией COUNT в SQL.
В этом примере у нас есть таблица products со следующими данными:
В этом примере мы использовали функцию COUNT для вычисления количества total_products для каждого category_id , и мы указали псевдоним «total_products» как результаты функции COUNT. Мы исключили все значения category_id , которые имеют значение NULL, отфильтровав их в предложении WHERE. Поскольку category_id не инкапсулирован в функции COUNT, он должен быть указан в предложении GROUP BY.
Давайте теперь посмотрим, как использовать предложение GROUP BY с функцией MIN в SQL.
В этом примере мы снова будем использовать таблицу employees со следующими данными:
Будет выбрано 2 записи. Вот результаты, которые вы получите:
| dept_id | lowest_salary |
|---|---|
| 500 | 57500 |
| 501 | 42000 |
В этом примере мы использовали функцию MIN, чтобы вернуть самое минимальное значение salary для каждого dept_id , и мы присвоили результатам функции MIN псевдоним «lowest_salary». Поскольку dept_id не инкапсулирован в функцию MIN, он должен быть указан в предложении GROUP BY.
Наконец, давайте посмотрим, как использовать предложение GROUP BY с функцией MAX.
Давайте снова воспользуемся таблицей employees , но на этот раз найдем самую максимальную зарплату для каждого dept_id :
| employee_number | first_name | last_name | salary | dept_id |
|---|---|---|---|---|
| 1001 | Justin | Bieber | 62000 | 500 |
| 1002 | Selena | Gomez | 57500 | 500 |
| 1003 | Mila | Kunis | 71000 | 501 |
| 1004 | Tom | Cruise | 42000 | 501 |
Введите следующий SQL оператор.
Если в табличном выражении присутствует раздел GROUP BY SQL , то следующим выполняется GROUP BY .
Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE ), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин “сгруппированная таблица ”.
Если утверждение SELECT содержит предложение GROUP BY (SELECT GROUP BY ), список выбора может содержать только следующие типы выражений :
Пример 1. Вычислить общий объем покупок для каждого товара:
SELECT stock, SUM(quant) FROM ordsale GROUP BY stock;
Фраза GROUP BY не предполагает упорядочивания строк. Для упорядочивания результата этого примера по кодам товаров, следует поместить фразу ORDER BY stock следом за фразой GROUP BY.
Пример 2. Можно использовать группировки данных GROUP BY совместно с условием. Например, выбрать для каждого покупаемого товара его код и общий объем покупок, за исключением покупок покупателя с кодом 23:
SELECT stock, SUM(quant) FROM ordsale WHERE customerno<>23 GROUP BY stock;
Строки, не удовлетворяющие условию WHERE , исключаются перед группированием данных.
Строки таблицы можно группировать по любой комбинации ее полей. Если поле, по значениям которого осуществляется группирование, содержит какие-либо неопределенные значения, то каждое из них порождает отдельную группу.
Допустим, есть задача на вычисление количества какого-либо продукта. Поставщик поставляет нам продукцию по определённой цене. Вычислим общее количество каждого из продуктов. В этом нам поможет фраза GROUP BY. Результатом задачи станет таблица, состоящая из нескольких колонок. Поставки будут группироваться по ПР. Компоновка происходит по группам, которую и инициирует Group By SQL. Необходимо отметить, что данная фраза предполагает применение фразы Select, она же в свою очередь определяет единственное значение для каждого выражения сформированной группы. Бывают три случая для конкретного выражения: оно принимает арифметическое значение, оно становится SQL-функцией, которая будет сводить все значения столбца к сумме или другому заданному значению, также выражение может стать константой. Строки таблицы не обязательно должны быть строго сгруппированы, они могут группироваться по любой комбинации столбцов таблицы. Необходимо учитывать, что упорядочивание запросы по ПР возможно в том случае, если будет сделан соответствующий запрос.
