Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Подзапрос в строках 3-9 возвращает следующие данные (количество экземпляров, выданных на руки читателям, по каждой книге, хотя бы один экземпляр которой выдан):
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Подзапрос в строках 1-11 возвращает не более одного ряда выборки, содержащей идентификаторы книг с отрицательным остатком («не более одного», т.к. нас интересует просто факт наличия или отсутствия таких книг). Если этот подзапрос вернёт ноль рядов, функция EXISTS на пустой выборке вернёт FALSE (0). Если подзапрос вернёт один ряд, множество уже будет непустым, и функция EXISTS вер-
нёт TRUE (1).
Поскольку в MS SQL Server и Oracle нельзя напрямую получить 1 и 0 из результата работы функции EXISTS, решения для этих СУБД будут чуть более сложными.
MS SQL Решение 2.2.7.e
1WITH [books_taken]
2AS (SELECT [sb_book],
3 |
|
|
COUNT([sb_book]) AS [taken] |
|
4 |
|
FROM |
[subscriptions] |
|
5 |
|
WHERE |
[sb_is_active] = 'Y' |
|
6 |
|
GROUP |
BY [sb_book]) |
|
7 |
|
SELECT TOP 1 CASE |
|
|
8 |
|
|
WHEN EXISTS (SELECT |
TOP 1 [b_id] |
9 |
|
|
FROM |
[books] |
10 |
|
|
|
LEFT OUTER JOIN [books_taken] |
11 |
|
|
|
ON [b_id] = [sb_book] |
12 |
|
|
WHERE |
([b_quantity] - ISNULL([taken], 0)) < 0) |
13 |
|
|
THEN 1 |
|
14 |
|
|
ELSE 0 |
|
15 |
|
END AS [error_exists] |
|
|
16 |
|
FROM [books_taken] |
|
|
|
|
|
|
|
Общее табличное выражение в строках 1-6 возвращает информацию о том, сколько экземпляров каждой книги выдано на руки читателям:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Подзапрос в строках 8-12 возвращает не более одного идентификатора книги с отрицательным остатком и передаёт эту информацию в функцию EXISTS, которая возвращает TRUE или FALSE в зависимости от того, нашлись такие книги или не нашлись.
Конструкция CASE ... WHEN ... THEN ... ELSE ... END в строках 7-
15 позволяет преобразовать логический результат работы функции EXISTS в требуемые по условию задачи 1 и 0.
Указание TOP 1 в строке 7 необходимо для того, чтобы в итоговую выборку попала одна строка, а не такое количество строк, какое возвратит общее табличное выражение.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 125/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Oracle Решение 2.2.7.e
1WITH "books_taken"
2AS (SELECT "sb_book",
3 |
|
|
COUNT("sb_book") AS "taken" |
|
4 |
|
FROM |
"subscriptions" |
|
5 |
|
WHERE |
"sb_is_active" = 'Y' |
|
6 |
|
GROUP |
BY "sb_book") |
|
7 |
|
SELECT CASE |
|
|
8 |
|
|
WHEN EXISTS (SELECT |
"b_id" |
9 |
|
|
FROM |
"books" |
10 |
|
|
|
LEFT OUTER JOIN "books_taken" |
11 |
|
|
|
ON "b_id" = "sb_book" |
12 |
|
|
WHERE |
("b_quantity" - NVL("taken", 0)) < 0 |
13 |
|
|
AND ROWNUM = 1) |
|
14 |
|
|
THEN 1 |
|
15 |
|
|
ELSE 0 |
|
16 |
|
END AS "error_exists" |
|
|
17 |
|
FROM "books_taken" |
|
|
18 |
|
WHERE ROWNUM = 1 |
|
|
Решение для Oracle идентично решению для MS SQL Server за исключением использования вместо TOP 1 конструкции WHERE ROWNUM = 1, позволяющей вернуть не более одного ряда выборки.
Задание 2.2.7.TSK.A: показать читаемость жанров, т.е. все жанры и то количество раз, которое книги этих жанров были взяты читателями.
Задание 2.2.7.TSK.B: показать самый читаемый жанр, т.е. жанр (или жанры, если их несколько), относящиеся к которому книги читатели брали чаще всего.
Задание 2.2.7.TSK.C: показать среднюю читаемость жанров, т.е. среднее значение от того, сколько раз читатели брали книги каждого автора.
Задание 2.2.7.TSK.D: показать медиану читаемости жанров, т.е. медианное значение от того, сколько раз читатели брали книги каждого жанра.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 126/545
Пример 18: учёт вариантов и комбинаций признаков
2.2.8. Пример 18: учёт вариантов и комбинаций признаков
Задача 2.2.8.a{127}: показать авторов, одновременно работавших в двух и более жанрах (т.е. хотя бы одна книга автора должна одновременно относиться к двум и более жанрам).
Задача 2.2.8.b{129}: показать авторов, работавших в двух и более жанрах (даже если каждая отдельная книга автора относится только к одному жанру).
Ожидаемый результат 2.2.8.a.
a_id |
a_name |
genres_count |
|
1 |
Д. Кнут |
2 |
|
3 |
Д. Карнеги |
2 |
|
6 |
Б. Страуструп |
2 |
|
7 |
А.С. Пушкин |
2 |
|
|
Ожидаемый результат 2.2.8.b. |
||
a_id |
a_name |
genres_count |
1 |
Д. Кнут |
2 |
3 |
Д. Карнеги |
2 |
6 |
Б. Страуструп |
2 |
7 |
А.С. Пушкин |
2 |
На имеющемся наборе данных ожидаемые результаты 2.2.8.a и 2.2.8.b совпадают, но из этого никоим образом не следует, что они всегда должны быть одинаковыми.
Решение 2.2.8.a{127}.
MySQL Решение 2.2.8.a
1SELECT `a_id`,
2`a_name`,
3MAX(`genres_count`) AS `genres_count`
4 |
|
FROM (SELECT |
`a_id`, |
5 |
|
|
`a_name`, |
6 |
|
|
COUNT(`g_id`) AS `genres_count` |
7 |
|
FROM |
`authors` |
8 |
|
|
JOIN `m2m_books_authors` USING (`a_id`) |
9 |
|
|
JOIN `m2m_books_genres` USING (`b_id`) |
10 |
|
GROUP |
BY `a_id`, |
11 |
|
|
`b_id` |
|
|
|
|
12HAVING `genres_count` > 1) AS `prepared_data`
13GROUP BY `a_id`
Для получения результата нужно выполнить два шага.
Первый шаг представлен подзапросом в строках 4-12: здесь происходит объединение трёх таблиц (из таблицы authors берётся информация об авторах, из таблицы m2m_books_authors — информация о написанных каждым автором книгах, из таблицы m2m_books_genres — информация о жанрах, к которым относится каждая книга) и в выборку попадают авторы, у которых есть книги, относящиеся к двум и более жанрам.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 127/545
Пример 18: учёт вариантов и комбинаций признаков
Второй шаг (представленный основной частью запроса в строках 1-3 и 13) нужен для корректной обработки ситуации, в которой у некоторого автора оказывается несколько книг, относящихся к двум и более жанрам, но каждая из которых относится к разному количеству жанров. Данные, полученные из подзапроса тогда приняли бы, например, такой вид (обратите внимание, что DISTINCT здесь не поможет, т.к. 4-я и 5-я записи отличаются значением поля genres_count):
a_id |
a_name |
genres_count |
1 |
Д. Кнут |
2 |
3 |
Д. Карнеги |
2 |
6 |
Б. Страуструп |
2 |
7 |
А.С. Пушкин |
2 |
7 |
А.С. Пушкин |
3 |
Но благодаря повторной группировке по идентификаторам авторов с поиском максимального количества жанров эти данные приняли бы такой конечный вид:
a_id |
a_name |
genres_count |
1 |
Д. Кнут |
2 |
3 |
Д. Карнеги |
2 |
6 |
Б. Страуструп |
2 |
7 |
А.С. Пушкин |
3 |
Поскольку задачи 2.2.8.a и 2.2.8.b во многом схожи, для лучшего понимания их ключевых отличий сейчас мы рассмотрим внутренние данные, которые возвращает подзапрос в строках 4-12. Перепишем эту часть, добавив в выборку информацию о книгах и убрав условие «два и более жанра»:
MySQL Решение 2.2.8.a (модифицированный код подзапроса)
1SELECT `a_id`,
2`a_name`,
3`b_id`,
4`b_name`,
5COUNT(`g_id`) AS `genres_count`
6 FROM `authors`
7JOIN `m2m_books_authors` USING (`a_id`)
8JOIN `m2m_books_genres` USING (`b_id`)
9JOIN `books` USING (`b_id`)
10GROUP BY `a_id`,
11`b_id`
Благодаря двойной группировке по идентификаторам автора и книги мы получаем информацию о количестве жанров каждой отдельной книги каждого автора:
a_id |
a_name |
b_id |
b_name |
genres_count |
1 |
Д. Кнут |
7 |
Искусство программирования |
2 |
2 |
А. Азимов |
3 |
Основание и империя |
1 |
3 |
Д. Карнеги |
4 |
Психология программирования |
2 |
4 |
Л.Д. Ландау |
6 |
Курс теоретической физики |
1 |
5 |
Е.М. Лифшиц |
6 |
Курс теоретической физики |
1 |
6 |
Б. Страуструп |
4 |
Психология программирования |
2 |
6 |
Б. Страуструп |
5 |
Язык программирования С++ |
1 |
7 |
А.С. Пушкин |
1 |
Евгений Онегин |
2 |
7 |
А.С. Пушкин |
2 |
Сказка о рыбаке и рыбке |
2 |
Иными словами, здесь мы считаем «количество жанров у каждой книги», а в задаче 2.2.8.b мы будем считать «количество жанров у автора».
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 128/545
Пример 18: учёт вариантов и комбинаций признаков
Решение этой задачи для MS SQL Server и Oracle отличается от решения для MySQL только синтаксическими нюансами.
MS SQL Решение 2.2.8.a
1SELECT [a_id],
2[a_name],
3MAX([genres_count]) AS [genres_count]
4 |
|
FROM (SELECT |
[authors].[a_id], |
|
5 |
|
|
[authors].[a_name], |
|
6 |
|
|
COUNT([m2m_books_genres].[g_id]) AS [genres_count] |
|
7 |
|
FROM |
[authors] |
|
8 |
|
|
JOIN |
[m2m_books_authors] |
9 |
|
|
ON |
[authors].[a_id] = [m2m_books_authors].[a_id] |
10 |
|
|
JOIN |
[m2m_books_genres] |
11 |
|
|
ON |
[m2m_books_authors].[b_id] = [m2m_books_genres].[b_id] |
12 |
|
GROUP |
BY [authors].[a_id], |
|
13 |
|
|
[a_name], |
|
14 |
|
|
[m2m_books_authors].[b_id] |
|
|
|
|
|
|
15HAVING COUNT([m2m_books_genres].[g_id]) > 1) AS [prepared_data]
16GROUP BY [a_id],
17[a_name]
Oracle Решение 2.2.8.a
1SELECT "a_id",
2"a_name",
3MAX("genres_count") AS "genres_count"
4 |
|
FROM (SELECT |
"a_id", |
5 |
|
|
"a_name", |
6 |
|
|
COUNT("g_id") AS "genres_count" |
7 |
|
FROM |
"authors" |
8 |
|
|
JOIN "m2m_books_authors" USING ("a_id") |
9 |
|
|
JOIN "m2m_books_genres" USING ("b_id") |
10 |
|
GROUP |
BY "a_id", |
11 |
|
|
"a_name", |
12 |
|
|
"b_id" |
13HAVING COUNT("g_id") > 1) "prepared_data"
14GROUP BY "a_id",
15"a_name"
Решение 2.2.8.b{127}.
Как только что было подчёркнуто в разборе решения{127} задачи 2.2.8.a{127}, здесь нам придётся считать «количество жанров у автора».
MySQL Решение 2.2.8.b
1SELECT `a_id`,
2`a_name`,
3COUNT(`g_id`) AS `genres_count`
4 |
|
FROM (SELECT |
DISTINCT `a_id`, |
5 |
|
|
`g_id` |
6 |
|
FROM |
`m2m_books_genres` |
7 |
|
|
JOIN `m2m_books_authors` USING (`b_id`) |
8) AS `prepared_data`
9JOIN `authors` USING (`a_id`)
10GROUP BY `a_id`
11HAVING `genres_count` > 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 129/545