Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Ожидаемый результат 2.2.7.e.
error_exists
0
Решение 2.2.7.a{114}.
MySQL Решение 2.2.7.a
1SELECT `a_id`,
2`a_name`,
3COUNT(`sb_book`) AS `books`
4 FROM `authors`
5JOIN `m2m_books_authors` USING ( `a_id` )
6LEFT OUTER JOIN `subscriptions`
|
7 |
|
|
|
|
ON `m2m_books_authors`.`b_id` = `sb_book` |
|
|
|
|
|
|
|
|
8 |
|
GROUP |
BY |
`a_id` |
|
|
9 |
|
ORDER |
BY |
`books` DESC |
|
|
|
|
||||
|
MS SQL |
Решение 2.2.7.a |
||||
1SELECT [authors].[a_id],
2[authors].[a_name],
3COUNT([sb_book]) AS [books]
4 FROM [authors]
5JOIN [m2m_books_authors]
6ON [authors].[a_id] = [m2m_books_authors].[a_id]
7LEFT OUTER JOIN [subscriptions]
8 |
|
ON [m2m_books_authors].[b_id] = [sb_book] |
9GROUP BY [authors].[a_id],
10[authors].[a_name]
11 ORDER BY COUNT([sb_book]) DESC
Oracle |
Решение 2.2.7.a |
1SELECT "a_id",
2"a_name",
3COUNT("sb_book") AS "books"
4 FROM "authors"
5JOIN "m2m_books_authors" USING ( "a_id" )
6LEFT OUTER JOIN "subscriptions"
7 |
|
|
|
|
ON "m2m_books_authors"."b_id" = "sb_book" |
8 |
|
GROUP |
BY |
"a_id", |
|
9 |
|
|
|
"a_name" |
|
10 |
|
ORDER |
BY |
"books" |
DESC |
Решение для всех трёх СУБД отличается только нюансами синтаксиса, а по сути тривиально: нужно собрать воедино информацию об авторах, книгах и фактах выдачи книг (это достигается за счёт двух JOIN), после чего подсчитать количество фактов выдачи книг, сгруппировав результаты подсчёта по идентификаторам авторов.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 115/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Решение 2.2.7.b{114}.
MySQL Решение 2.2.7.b
1-- Вариант 1: на основе функции MAX
2SELECT `a_id`,
3`a_name`,
4COUNT(`sb_book`) AS `books`
5 FROM `authors`
6JOIN `m2m_books_authors` USING (`a_id`)
7LEFT OUTER JOIN `subscriptions`
8 |
|
ON `m2m_books_authors`.`b_id` = `sb_book` |
9GROUP BY `a_id`
10HAVING `books` = (SELECT MAX(`books`)
11 |
|
FROM |
|
12 |
|
(SELECT |
COUNT(`sb_book`) AS `books` |
13 |
|
FROM |
`authors` |
14 |
|
|
JOIN `m2m_books_authors` USING ( `a_id` ) |
15 |
|
|
LEFT OUTER JOIN `subscriptions` |
16 |
|
|
ON `m2m_books_authors`.`b_id` = `sb_book` |
17 |
|
|
GROUP BY `a_id` |
18 |
|
) AS `books_per_author`) |
|
|
|
|
|
Поскольку MySQL не поддерживает ни ранжирующие (оконные) функции, ни специфичный для MS SQL Server синтаксис TOP ... WITH TIES, здесь остаётся единственный вариант: полностью аналогично решению задачи 2.2.7.a{115} подсчитать, сколько раз читатели брали книги каждого из авторов (строки 2-9 запроса), а затем оставить в выборке только тех авторов, для которых это количество совпадает с максимальным значением по всем авторам (этот максимум вычисляется в строках 10-18 запроса).
Если бы MySQL поддерживал общие табличные выражения, можно было бы обойтись без повторного определения количества выдач книг по каждому автору (подзапрос в строках 12-17 отличается от основной части запроса в строках 2-9 только исключением из выборки полей a_id и a_name — в остальном это полное дублирование кода).
Модификация этого варианта решения с использованием общих табличных выражений представлена ниже для MS SQL Server и Oracle.
MS SQL Решение 2.2.7.b
1-- Вариант 1: на основе функции MAX
2WITH [prepared_data]
3AS (SELECT [authors].[a_id],
4 |
|
|
[authors].[a_name], |
5 |
|
|
COUNT([sb_book]) AS [books] |
6 |
|
FROM |
[authors] |
7 |
|
|
JOIN [m2m_books_authors] |
8 |
|
|
ON [authors].[a_id] = [m2m_books_authors].[a_id] |
9 |
|
|
LEFT OUTER JOIN [subscriptions] |
10 |
|
|
ON [m2m_books_authors].[b_id] = [sb_book] |
11 |
|
GROUP |
BY [authors].[a_id], |
12 |
|
|
[authors].[a_name]) |
|
|
|
|
13SELECT [a_id],
14[a_name],
15[books]
16 |
|
FROM |
[prepared_data] |
|
17 |
|
WHERE |
[books] = (SELECT |
MAX([books]) |
18 |
|
|
FROM |
[prepared_data]) |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 116/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
MS SQL Решение 2.2.7.b
1-- Вариант 2: на основе ранжирования
2WITH [prepared_data]
3AS (SELECT [authors].[a_id],
4 |
|
|
[authors].[a_name], |
|
5 |
|
|
COUNT([sb_book]) |
AS [books], |
6 |
|
|
RANK() |
|
7 |
|
|
OVER ( |
|
8 |
|
|
ORDER BY COUNT([sb_book]) DESC) AS [rank] |
|
9 |
|
FROM |
[authors] |
|
10 |
|
|
JOIN [m2m_books_authors] |
|
11 |
|
|
ON [authors].[a_id] = [m2m_books_authors].[a_id] |
|
12 |
|
|
LEFT OUTER JOIN [subscriptions] |
|
13 |
|
|
ON [m2m_books_authors].[b_id] = [sb_book] |
|
14 |
|
GROUP |
BY [authors].[a_id], |
|
15 |
|
|
[authors].[a_name]) |
|
16SELECT [a_id],
17[a_name],
18[books]
19 |
|
FROM |
[prepared_data] |
20 |
|
WHERE |
[rank] = 1 |
1-- Вариант 3: на основе конструкции TOP ... WITH TIES
2WITH [prepared_data]
3AS (SELECT [authors].[a_id],
4 |
|
|
|
[authors].[a_name], |
5 |
|
|
|
COUNT([sb_book]) AS [books] |
6 |
|
|
FROM |
[authors] |
7 |
|
|
|
JOIN [m2m_books_authors] |
8 |
|
|
|
ON [authors].[a_id] = [m2m_books_authors].[a_id] |
9 |
|
|
|
LEFT OUTER JOIN [subscriptions] |
10 |
|
|
|
ON [m2m_books_authors].[b_id] = [sb_book] |
11 |
|
|
GROUP |
BY [authors].[a_id], |
12 |
|
|
|
[authors].[a_name]) |
13 |
|
SELECT TOP 1 WITH TIES [a_id], |
||
14 |
|
|
|
[a_name], |
15 |
|
|
|
[books] |
16 |
|
FROM |
[prepared_data] |
|
17 |
|
ORDER |
BY [books] DESC |
|
Вариант 1 решения для MS SQL Server отличается от варианта 1 для MySQL тем, что благодаря возможности использования общих табличных выражений мы можем избежать двукратного определения количества выдач читателям книг каждого автора: эта информация один раз определяется в общем табличном выражении (строки 2-12), и на этих же данных производится поиск максимального значения выдач книг (строки 17-18).
Вариант 2 основан на ранжировании авторов по количеству выдач их книг читателям с последующим отбором авторов, занявших первое место. Общее табличное выражение в строках 2-15 возвращает следующие данные:
a_id |
a_name |
books |
rank |
6 |
Б. Страуструп |
4 |
1 |
7 |
А.С. Пушкин |
4 |
1 |
2 |
А. Азимов |
2 |
3 |
3 |
Д. Карнеги |
2 |
3 |
1 |
Д. Кнут |
1 |
5 |
4 |
Л.Д. Ландау |
0 |
6 |
5 |
Е.М. Лифшиц |
0 |
6 |
В строке 20 на этот набор налагается ограничение, помещающее в выборку только авторов со значением rank равным 1.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 117/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Вариант 3 основан на использовании специфичного для MS SQL Server синтаксиса TOP ... WITH TIES для выбора ограниченного числа первых записей с присоединением к этому набору ещё нескольких записей, совпадающих с выбранными по значению поля сортировки.
Сначала мы получаем такой набор данных:
a_id |
a_name |
Books |
6 |
Б. Страуструп |
4 |
7 |
А.С. Пушкин |
4 |
2 |
А. Азимов |
2 |
3 |
Д. Карнеги |
2 |
1 |
Д. Кнут |
1 |
4 |
Л.Д. Ландау |
0 |
5 |
Е.М. Лифшиц |
0 |
Затем, благодаря конструкции SELECT TOP 1 WITH TIES ... FROM
[prepared_data] ORDER BY [books] DESC MS SQL Server оставляет только первую запись (TOP 1) и присоединяет к ней (WITH TIES) все другие записи с тем же самым значением в поле books, т.к. по нём идёт сортировка (ORDER BY [books]). В нашем случае это значение — 4. Так получается финальный результат выборки:
a_id |
a_name |
books |
6 |
Б. Страуструп |
4 |
7 |
А.С. Пушкин |
4 |
Представленное ниже решение задачи 2.2.7.b для Oracle полностью эквивалентно решению для MS SQL Server за исключением отсутствия третьего варианта, т.к. Oracle не поддерживает конструкцию TOP ... WITH TIES.
Oracle Решение 2.2.7.b
1-- Вариант 1: на основе функции MAX
2WITH "prepared_data"
3AS (SELECT "a_id",
4 |
|
|
"a_name", |
5 |
|
|
COUNT("sb_book") AS "books" |
6 |
|
FROM |
"authors" |
7 |
|
|
JOIN "m2m_books_authors" USING("a_id") |
8 |
|
|
LEFT OUTER JOIN "subscriptions" |
9 |
|
|
ON "m2m_books_authors"."b_id" = "sb_book" |
10 |
|
GROUP |
BY "a_id", |
11 |
|
|
"a_name") |
12SELECT "a_id",
13"a_name",
14"books"
15 |
|
FROM |
"prepared_data" |
|
16 |
|
WHERE |
"books" = (SELECT |
MAX("books") |
17 |
|
|
FROM |
"prepared_data") |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 118/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Oracle Решение 2.2.7.b
1-- Вариант 2: на основе ранжирования
2WITH "prepared_data"
3AS (SELECT "a_id",
4 |
|
|
"a_name", |
|
5 |
|
|
COUNT("sb_book") |
AS "books", |
6 |
|
|
RANK() |
|
7 |
|
|
OVER ( |
|
8 |
|
|
ORDER BY COUNT("sb_book") DESC) AS "rank" |
|
9 |
|
FROM |
"authors" |
|
10 |
|
|
JOIN "m2m_books_authors" USING("a_id") |
|
11 |
|
|
LEFT OUTER JOIN "subscriptions" |
|
12 |
|
|
ON "m2m_books_authors"."b_id" = "sb_book" |
|
13 |
|
GROUP |
BY "a_id", |
|
14 |
|
|
"a_name") |
|
|
|
|
|
|
15SELECT "a_id",
16"a_name",
17"books"
18 |
|
FROM |
"prepared_data" |
19 |
|
WHERE |
"rank" = 1 |
Исследование 2.2.7.EXP.A: сравним скорость работы решений этой задачи, выполнив все запросы 2.2.7.a на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
|
|
|
|
|
|
MySQL |
MS SQL Server |
Oracle |
Вариант 1 |
( |
MAX() |
) |
|
|
4787.841 |
16.106 |
155.918 |
Вариант 2 |
( |
RANK() |
) |
|
- |
8.138 |
1.407 |
|
Вариант 3 |
( |
TOP ... |
) |
- |
7.312 |
- |
||
|
|
|
|
|
|
|
|
|
Здесь мы наблюдаем ситуацию, обратную полученной в исследовании 2.1.8.EXP.B{47}, где вариант с функций MAX оказался быстрее варианта с RANK. И это совершенно нормально, т.к. эксперименты проводились на разных наборах данных и в контексте разных запросов. Т.е. на скорость выполнения запроса влияет далеко не только лишь использование той или иной функции, но и множество других параметров.
Решение 2.2.7.c{114}.
MySQL |
Решение 2.2.7.c |
|
|
||
1 |
SELECT |
AVG(`books`) |
AS `avg_reading` |
||
2 |
FROM |
(SELECT |
COUNT(`sb_book`) AS `books` |
||
3 |
|
|
FROM |
`authors` |
|
4 |
|
|
|
JOIN |
`m2m_books_authors` USING (`a_id`) |
5 |
|
|
|
LEFT |
OUTER JOIN `subscriptions` |
6 |
|
|
|
|
ON `m2m_books_authors`.`b_id` = `sb_book` |
7 |
|
|
GROUP |
BY `a_id`) AS `prepared_data` |
|
|
|
|
|
||
MS SQL |
Решение 2.2.7.c |
|
|
||
1 |
SELECT |
AVG(CAST([books] AS FLOAT)) AS [avg_reading] |
|||
2 |
FROM |
(SELECT |
COUNT([sb_book]) AS [books] |
||
3 |
|
|
FROM |
[authors] |
|
4 |
|
|
|
JOIN |
[m2m_books_authors] |
5 |
|
|
|
ON |
[authors].[a_id] = [m2m_books_authors].[a_id] |
6 |
|
|
|
LEFT |
OUTER JOIN [subscriptions] |
7 |
|
|
|
|
ON [m2m_books_authors].[b_id] = [sb_book] |
8 |
|
|
GROUP |
BY [authors].[a_id]) AS [prepared_data] |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 119/545