Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Oracle |
Решение 2.2.7.Є |
|
|
|
1 |
WITH "books taken" |
|
||
2 |
|
AS (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 в примерах © Боган Марчук Стр: 135/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}.
Решение 2.2.8.a
1SELECT ' a_id' ,
2'a name',
3MAX('genres_count') AS 'genres_count' (SELECT
4 |
FROM '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 Стр: 136/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.а и 2.2.8.b во многом схожи, для лучшего понимания их ключевых отличий сейчас мы рассмотрим внутренние данные, которые возвращает подзапрос в строках 4-12. Перепишем эту часть, добавив в выборку информацию о книгах и убрав условие «два и более жанра»:
MySQL Решение 2.2.8.а (модифицированный код подзапроса)
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 Стр: 137/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] |
|
|
Oracl |
і |
Решение 2.2.8.a |
| |
|
|
e |
|
||||
|
|
|
|
|
|
1 |
SELECT "a id", |
|
|
||
2 |
|
|
"a name" |
|
|
3 |
|
|
MAX("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" |
|
13 |
|
|
HAVING COUNT "g id" |
> 1) "prepared data" |
|
14 |
GROUP |
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 Стр: 138/545
Пример 18: учёт вариантов и комбинаций признаков
Снова доработаем подзапрос (строки 4-8) и посмотрим, какие данные он возвращает:
MySQL I |
Решение 2.2.8.b (модифицированный код подзапроса) |
| |
||
1 |
SELECT DISTINCT 'a id', |
|
||
2 |
|
|
'a name', |
|
3 |
|
|
'g_id', |
|
4 |
|
|
'g name' |
|
5 |
FROM |
'm2m books genres' |
|
|
6 |
|
JOIN |
'm2m books authors' USING ('b id') |
|
7 |
|
JOIN |
'authors' USING ('a id') |
|
8 |
|
JOIN |
'genres' USING ('g id') |
|
9 |
ORDER BY 'a |
id', |
|
|
10 |
|
'g id' |
|
|
|
|
|
|
|
Данные получаются такие (список без повторений всех жанров, в которых работал автор):
a_id |
a_name |
g id |
g_name |
1 |
Д. Кнут |
2 |
Программирование |
1 |
Д. Кнут |
5 |
Классика |
2 |
А. Азимов |
6 |
Фантастика |
3 |
Д. Карнеги |
2 |
Программирование |
3 |
Д. Карнеги |
3 |
Психология |
4 |
Л.Д. Ландау |
5 |
Классика |
5 |
Е.М. Лифшиц |
5 |
Классика |
6 |
Б. Страуструп |
2 |
Программирование |
6 |
Б. Страуструп |
3 |
Психология |
7 |
А.С. Пушкин |
1 |
Поэзия |
7 |
А.С. Пушкин |
5 |
Классика |
В основной части запроса (строки 1-3 и 9-11) остаётся только посчитать количество элементов в списке жанров для каждого автора, а затем оставить в выборке тех авторов, у которых это количество больше единицы. Так мы получаем итоговый результат.
Решение этой задачи для MS SQL Server и Oracle отличается от решения для MySQL только синтаксическими нюансами.
MS SQL І Решение 2.2.8.b
іSELECT [prepared data] [a id],
2[a name]
3COUNT([g id] AS [genres count]
4 |
FROM (SELECT DISTINCT [m2m books authors] [a id] |
|
5 |
|
[m2m books genres] [g id] |
6 |
FROM |
[m2m books genres] |
7 |
|
JOIN [m2m books authors] |
8 |
|
ON [m2m books genres] [b id] = [m2m books authors] [b id]) |
9AS
10[prepared data]
11JOIN [authors]
12ON [prepared data] [a id] = [authors] [a id]
13GROUP BY [prepared data] [a id],
14 |
[a name] |
15 |
HAVING COUNT([g_id] > 1 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 139/545