ОСНОВЫ SQL Групповые операции. Вложенные запросы

ОСНОВЫ SQL

Групповые операции

Вложенные Запросы

Выборка данных, групповые функции SUM и COUNT

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

Пример

Рассмотрим, как осуществляется группировка данных по некоторому столбцу и вычисления над группой на следующем примере:

Запрос:

SELECT author, sum(amount), count(amount) FROM book

GROUP BY author;

Выборка данных, групповые функции SUM и COUNT

1. В таблице  book  определяются строки, в которых в столбце  author  одинаковые значения:

Выборка данных, групповые функции SUM и COUNT

2. Вместо каждой группы в результирующий запрос включается  одна запись. Запись как минимум включает значение столбца, по которому осуществляется группировка (в нашем случае это  author ):

Выборка данных, групповые функции SUM и COUNT

3. Дальше выполняются вычисления над элементами КАЖДОЙ группы в отдельности, например, посчитать общее количество экземпляров книг каждого автора. Для этого используется групповая функция SUM(), а в скобках указывается столбец, по которому нужно выполнить суммирование ( в нашем случае  amount ):

Выборка данных, групповые функции SUM и COUNT

4. Также можно посчитать, сколько записей относится к группе. Для этого используется функция COUNT(), в скобках можно указать ЛЮБОЙ столбец из группы, если группа не содержит пустых значений:

Выборка данных, функция COUNT (группа содержит пустые значения)

Пример

Посчитать, сколько различных книг каждого автора хранится на складе.

Выборка данных, функция COUNT (группа содержит пустые значения)

Пример

Посчитать, сколько различных книг каждого автора хранится на складе.

Запрос:

SELECT author, COUNT(author), COUNT(amount), COUNT(*)

FROM book

GROUP BY author;

!

Если столбец указан в SELECT   БЕЗ  применения групповой функции, то он обязательно должен быть указан и в GROUP BY. Иначе получим ошибку.

Если же в столбцах есть значения  Null , то

COUNT(*)  —  подсчитывает  все записи, относящиеся к группе, в том числе и со значением  NULL ;

COUNT(имя_столбца)  — возвращает количество записей конкретного столбца (только  NOT NULL ), относящихся к группе.

8

Групповые функции MIN, MAX и AVG

MIN(), MAX() и AVG() вычисляют минимальное, максимальное и среднее значение элементов столбца, относящихся к группе.

Пример

Вывести минимальную цену книги каждого автора

Запрос:

SELECT author, MIN(price) AS min_price

FROM book

GROUP BY author;

8

Выборка данных c вычислением, групповые функции

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

Пример

Вывести суммарную стоимость книг каждого автора.

Запрос:

SELECT author, SUM(price * amount) AS Стоимость FROM book

GROUP BY author;

8

Выборка данных c вычислением, групповые функции

  Пример

Найти среднюю цену книг каждого автора, округлить до сотых . Назвать поле Средняя цена.

Запрос:

SELECT author,

ROUND(AVG(price),2) AS Средняя_цена

FROM book

GROUP BY author;

8

Вычисления по таблице целиком

Групповые функции позволяют вычислять итоговые значения по всей таблице.

Пример

Посчитать количество экземпляров книг на складе.

Запрос:

SELECT SUM(amount) AS Количество

FROM book;

!

Результатом таких запросов является единственная строка с вычисленными по таблице значениями.

8

5000; 13 " width="640"

Выборка данных по условию, групповые функции

  В запросах с групповыми функциями вместо WHERE используется ключевое слово HAVING , которое размещается после оператора GROUP BY.

  Пример

Найти минимальную и максимальную цену книг всех авторов, общая стоимость книг которых больше 5000.

Запрос:

SELECT author,

MIN(price) AS Минимальная_цена,

MAX(price) AS Максимальная_цена

FROM book

GROUP BY author

HAVING SUM(price * amount) 5000;

13

10; 13 " width="640"

Выборка данных по условию, групповые функции, WHERE и HAVING

  WHERE и HAVING могут использоваться в одном запросе. 

Пример

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

SELECT author,

MIN(price) AS Минимальная_цена,

MAX(price) AS Максимальная_цена

FROM book

WHERE author 'Есенин С.А.'

GROUP BY author

HAVING SUM(amount) 10;

13

=, , Пример Вывести информацию о самых дешевых книгах, хранящихся на складе.   SELECT title, author, price, amount FROM book WHERE price = ( SELECT MIN(price) FROM book ); 13 " width="640"

Вложенный запрос, возвращающий одно значение

  Вложенный запрос, возвращающий одно значение, может использоваться в условии отбора записей WHERE как обычное значение совместно с операциями =, , =, ,

Пример

Вывести информацию о самых дешевых книгах, хранящихся на складе.  

SELECT title, author, price, amount

FROM book

WHERE price = (

SELECT MIN(price)

FROM book

);

13

3; 13 " width="640"

Использование вложенного запроса в выражении

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

Пример

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

SELECT title, author, amount

FROM book

WHERE ABS (amount - (SELECT AVG(amount)

FROM book)) 3;

13

Вложенный запрос, оператор IN

  Вложенный запрос может возвращать несколько значений одного столбца.  

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

Оператор  NOT IN  выполняет обратное действие – выражение истинно, если значение не содержится во вложенном запросе.

13

= 12 ); 13 " width="640"

Вложенный запрос, оператор IN

Пример

Вывести информацию о книгах тех авторов, общее количество экземпляров книг которых не менее 12.

SELECT title, author, amount, price

FROM book

WHERE author IN ( SELECT author

FROM book

GROUP BY author

HAVING SUM(amount) = 12 );

13

Вложенный запрос, операторы ANY и ALL

Вложенный запрос, возвращающий несколько значений одного столбца, можно использовать для отбора записей с помощью операторов ANY и ALL совместно с операциями отношения (=, , =, ).

Операторы ANY и ALL используются  в SQL для сравнения некоторого значения с результирующим набором вложенного запроса, состоящим из одного столбца.

!

  Операторы  ALL  и  ANY  можно использовать т олько с вложенными запросами .

13

ANY (10, 12) эквивалентно amount 10 amount эквивалентно amount amount = ANY (10, 12) эквивалентно (amount = 10) OR (amount = 12), а также amount IN (10,12) amount ANY (10, 12) вернет все записи с любым значением amount, включая 10 и 12 20 " width="640"

Вложенный запрос, операторы ANY и ALL

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

amount ANY (10, 12) эквивалентно amount 10

amount эквивалентно amount

amount = ANY (10, 12) эквивалентно

(amount = 10) OR (amount = 12),

а также amount IN (10,12)

amount ANY (10, 12) вернет все записи с любым значением amount, включая 10 и 12

20

ALL (10, 12) эквивалентно amount 12 amount эквивалентно amount amount = ALL (10, 12) не вернет ни одной записи, так как эквивалентно (amount = 10) AND (amount = 12) amount ALL (10, 12) вернет все записи кроме тех, в которых amount равно 10 или 12 20 " width="640"

Вложенный запрос, операторы ANY и ALL

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

amount ALL (10, 12) эквивалентно amount 12

amount эквивалентно amount

amount = ALL (10, 12) не вернет ни одной записи, так как эквивалентно

(amount = 10) AND (amount = 12)

amount ALL (10, 12) вернет все записи кроме

тех, в которых amount равно 10 или 12

20

Вложенный запрос, операторы ANY и ALL

Пример

Вывести информацию о тех книгах, количество которых меньше самого маленького среднего количества книг каждого автора.

SELECT title, author, amount, price

FROM book

WHERE amount

FROM book

GROUP BY author

);

20

Вложенный запрос, операторы ANY и ALL

Пример

Вывести информацию о тех книгах, количество которых меньше самого большого среднего количества книг каждого автора.

SELECT title, author, amount, price

FROM book

WHERE amount

FROM book

GROUP BY author

);

20

Вложенный запрос после SELECT

Вложенный запрос может располагаться после ключевого слова SELECT.

В этом случае результат выполнения запроса выводится в отдельном столбце результирующей таблицы.

Результатом запроса может быть только одно значение, оно будет повторяться во всех строках. Также вложенный запрос может использоваться в выражениях.

20

3; 20 " width="640"

Вложенный запрос после SELECT

Пример

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

SELECT title, author, amount,

(SELECT AVG(amount)

FROM book )

AS Среднее_количество

FROM book

WHERE abs(amount - (SELECT AVG(amount)

FROM book)) 3;

20

Вложенный запрос после SELECT

Пример

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

?

Что плохо?

20

?

Какая функция позволяет округлить «вниз»?

3; 27 " width="640"

Вложенный запрос после SELECT

SELECT title, author, amount,

FLOOR((SELECT AVG(amount)

FROM book))

AS Среднее_количество

FROM book

WHERE abs(amount - (SELECT AVG(amount)

FROM book)) 3;

27

27