Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 16: запросы на объединение и функция COUNT
Oracle і Решение 2.2.6.b (продолжение) [
1— Вариант 2: использование общего табличного выражения
2-- и коррелирующего подзапроса
3 |
WITH |
"books_taken" |
|
|
|
|
|||
AS (SELECT |
"sb_book" |
AS |
"b_id" |
|
|||||
4 |
|
||||||||
|
|
COUNT("sb_book") |
AS |
"taken" |
|
||||
5 |
|
|
|
||||||
FROM |
"subscriptions" |
|
|
|
|
||||
6 |
|
|
|
|
|||||
WHERE |
"sb_is_active" = |
'Y' |
|
|
|||||
7 |
|
|
|||||||
GROUP BY |
"sb_book") |
|
|
|
|
||||
|
|
|
|
|
|||||
12 |
|
|
|
FROM |
"books_taken" |
|
|||
|
|
|
WHERE |
"books" "b_id" |
= |
||||
13 |
|
|
|
||||||
|
|
|
"books_taken" |
|
"b_id"), |
0 |
|||
14 |
|
|
|
|
|||||
|
|
|
|
) ) AS |
|
||||
15 |
|
|
|
|
|
||||
"real_count" |
|
|
|
|
|
||||
16 |
|
|
|
|
|
||||
FROM "books" |
|
|
|
|
|
||||
17 |
|
|
|
|
|
||||
ORDER BY |
|
|
"real_count" |
DESC |
|
||||
18 |
|
|
|
||||||
|
|
|
|
|
|
|
|
||
1 |
-- Вариант 3: пошаговое применение общего табличного выражения и |
||||||||
подзапроса |
|
|
|
|
|
|
|
||
2 |
|
|
|
|
|
|
|
||
WITH |
"books_taken" |
|
|
|
|
||||
3 |
|
|
|
|
|||||
AS (SELECT "sb_book" |
|
|
|
|
|||||
4 |
|
|
|
|
|||||
FROM |
"subscriptions" |
|
|
|
|
||||
5 |
|
|
|
|
|||||
WHERE |
"sb_is_active" = |
'Y'), |
|
|
|||||
6 |
|
|
|||||||
"real_taken" |
|
|
|
|
|
||||
7 |
|
|
|
|
|
||||
AS (SELECT |
"b_id" |
|
|
|
|
||||
8 |
|
|
|
|
|||||
|
|
COUNT("sb_book") AS "taken" |
|
||||||
9 |
|
|
|
||||||
FROM "books" |
|
|
|
|
|||||
10 |
|
|
|
|
|||||
|
|
LEFT OUTER JOIN |
"books_taken" |
|
|||||
11 |
|
|
|
||||||
|
|
|
ON |
"b_id" = "sb_book" |
|
||||
12 |
|
|
|
|
|||||
GROUP BY |
"b_id" |
|
|
|
|
||||
13 |
|
|
|
|
|||||
SELECT "b_id", |
|
|
|
|
|
||||
14 |
|
|
|
|
|
||||
"b_name", |
|
|
|
|
|
|
|||
15 |
|
|
|
|
|
|
|||
( "b_quantity" - (SELECT "taken" |
|
|
|||||||
16 |
|
|
|||||||
|
|
|
FROM |
|
"real_taken" |
|
|||
17 |
|
|
|
|
|
||||
|
|
|
WHERE "books" |
"b_id" = "real_taken" "b_id" ) AS |
|||||
18 |
|
|
|
||||||
"real_count" |
|
|
|
|
|
||||
19 |
|
|
|
|
|
||||
FROM "books" |
|
|
|
|
|
||||
20 |
|
|
|
|
|
||||
ORDER BY "real_count" DESC |
|
|
|
|
|||||
|
|
|
|
|
|||||
1 |
-- Вариант 4: без подзапросов |
|
|
|
|||||
2 |
|
|
|
||||||
WITH |
"books_taken" |
|
|
|
|
||||
3 |
|
|
|
|
|||||
AS (SELECT "sb_book", |
|
|
|
|
|||||
4 |
|
|
|
|
|||||
|
|
COUNT("sb_book") AS "taken" |
|
||||||
5 |
|
|
|
||||||
FROM |
"subscriptions" |
|
|
|
|
||||
6 |
|
|
|
|
|||||
WHERE |
"sb_is_active" = |
'Y' |
|
|
|||||
7 |
|
|
|||||||
GROUP BY |
"sb_book") |
|
|
|
|
||||
8 |
|
|
|
|
|||||
SELECT |
"b_id", |
|
|
|
|
|
|||
9 |
|
|
|
|
|
||||
"b_name", |
|
|
|
|
|
|
|||
10 |
|
|
|
|
|
|
|||
( "b_quantity" - NVL "taken", 0 |
) AS "real_count" |
||||||||
11 |
|||||||||
FROM "books" |
|
|
|
|
|
||||
8 |
SELECT |
"b_id", |
|
|
|
|
|
||
9 |
|
|
|
|
|
|
|
|
|
10 |
"b_name", |
|
( "b_quantity" - NVL((SELECT "taken" |
||
11 |
||
|
||
12 |
LEFT OUTER JOIN "books_taken" |
|
13 |
||
ON "b_id" = "sb_book" |
||
14 |
||
ORDER BY "real count" DESC |
||
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 120/545
Пример 16: запросы на объединение и функция COUNT
Исследование 2.2.6.EXP.A: сравним скорость работы каждого из четырёх вариантов запросов 2.2.6. b для всех трёх СУБД на базе данных «Большая библиотека».
Выполнив по сто раз каждый запрос для каждой СУБД, получаем такие медианы времени:
|
MySQL |
MS SQL Server |
Oracle |
Вариант 1 |
0.545 |
50.575 |
1.393 |
Вариант 2 |
16240.234 |
5.814 |
0.430 |
Вариант 3 |
220.918 |
5.733 |
0.342 |
Вариант 4 |
19061.824 |
5.309 |
0.383 |
Обратите внимание, насколько по-разному ведут себя различные СУБД. Для MS SQL Server и Oracle ожидаемо вариант с коррелирующим подзапросом (вариант 1) оказался самым медленным, но в MySQL он оказался намного быстрее, чем «эмуляция общего табличного выражения».
Задание 2.2.6.TSK.A: показать авторов, написавших более одной книги.
Задание 2.2.6.TSK.B: показать книги, относящиеся к более чем одному жанру.
Задание 2.2.6.TSK.C: показать читателей, у которых сейчас на руках больше одной книги.
Задание 2.2.6.TSK.D: показать, сколько экземпляров каждой книги сейчас выдано читателям.
Задание 2.2.6.TSK.E: показать всех авторов и количество экземпляров книг по каждому автору.
Задание 2.2.6.TSK.F: показать всех авторов и количество книг (не экземпляров книг, а «книг как изданий») по каждому автору.
Задание 2.2.6.TSK.G: показать всех читателей, не вернувших книги, и количество невозвращённых книг по каждому такому читателю.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 121/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
2.2.7.Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Задача 2.2.7.a{115}: показать читаемость авторов, т.е. всех авторов и то количество раз, которое книги этих авторов были взяты читателями.
Задача 2.2.7.b{116}: показать самого читаемого автора, т.е. автора (или авторов, если их несколько), книги которого читатели брали чаще всего.
Задача 2.2.7.c{119}: показать среднюю читаемость авторов, т.е. среднее значение от того, сколько раз читатели брали книги каждого автора.
Задача 2.2.7.d{120}: показать медиану читаемости авторов, т.е. медианное значение от того, сколько раз читатели брали книги каждого
автора.
Задача 2.2.7.e{124}: написать запрос, проверяющий, не была ли допущена ошибка в заполнении документов, при которой оказывается, что на руках сейчас большее количество экземпляров некоторой книги, чем их было в библиотеке. Вернуть 1, если ошибка есть и 0, если ошибки нет.
Ожидаемый результат 2.2.7.a.
a_id |
a_name |
books |
7 |
А.С. Пушкин |
4 |
6 |
Б. Страуструп |
4 |
3 |
Д. Карнеги |
2 |
2 |
А. Азимов |
2 |
1 |
Д. Кнут |
1 |
5 |
Е.М. Лифшиц |
0 |
4 |
Л.Д. Ландау |
0 |
Ожидаемый результат 2.2.7.b.
a_id |
a_name |
books |
6 |
Б. Страуструп |
4 |
7 |
А.С. Пушкин |
4 |
Ожидаемый результат 2.2.7.c: количество знаков после запятой по умол-
чанию различается в MySQL, MS SQL Server и Oracle.
MySQL |
MS SQL Server |
_____________ Oracle ____________ |
avg_reading |
avg_reading |
___________avg_reading __________ |
1.8571 |
1.85714285714286 |
1.85714285714285714285714285714285714286 |
Ожидаемый результат 2.2.7.d: количество знаков после запятой по умол-
чанию различается в MySQL, MS SQL Server и Oracle.
MySQL |
MS SQL Server |
med_reading |
||||
|
|
|
|
|
|
|
med_reading |
med_reading 2 |
2 |
||||
2.0000 |
|
|
Oracle |
|
||
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 122/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Ожидаемый результат 2.2.7.Є.
error_exists
0
Решение 2.2.7.a<114\
MySQL |
Решение 2.2.7.a |
|
|
|||
|
|
SELECT ' a_id' , |
|
|
||
1 |
2 |
|
|
'a name' , |
|
|
3 |
|
|
|
COUNT('sb |
book') AS 'books' |
|
4 |
|
FROM |
'authors' |
|
|
|
5 |
|
|
|
JOIN 'm2m |
books authors' USING ( 'a id' ) |
|
6 |
|
|
|
LEFT 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 |
|
|
|||
1 |
|
SELECT [authors] |
[a id] |
|
||
2 |
|
|
|
[authors] |
[a name], |
|
3 |
|
|
|
COUNT([sb |
book] |
AS [books] |
4 |
|
FROM |
[authors] |
|
|
|
5 |
|
|
|
JOIN [m2m |
books authors] |
|
6 |
|
|
|
ON [authors] [a id] = [m2m books authors] [a id] |
||
7 |
|
|
|
LEFT OUTER JOIN [subscriptions] |
||
8 |
|
|
|
|
ON [m2m books authors] [b id] = [sb book] |
|
9 |
|
GROUP |
BY [authors] [a id], |
|||
10 |
|
|
|
[authors] [a name] |
||
11 |
|
ORDER |
BY COUNT([sb book]) DESC |
|||
|
|
|
|
|
|
|
Oracl |
|
|
|
|
|
|
e |
|
і |
Решение 2.2.7.a |
| |
|
|
1 |
|
SELECT "a id", |
|
|
||
2 |
|
|
|
"a name" |
|
|
3 |
|
|
|
COUNT("sb |
book" |
AS "books" |
4 |
|
FROM |
"authors" |
|
|
|
5 |
|
|
|
JOIN "m2m |
books authors" USING ( "a id" ) |
|
6 |
|
|
|
LEFT 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 в примерах © Боган Марчук Стр: 123/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
MySQL і Решение 2.2.7.b
1 |
-- Вариант 1: на основе функции MAX |
|
|
2 |
SELECT 'a id', |
|
|
3 |
|
'a name', |
|
4 |
|
COUNT('sb book') AS 'books' |
|
5 |
FROM |
'authors' |
|
6 |
|
JOIN 'm2m books authors' USING ('a id') |
|
7 |
|
LEFT OUTER JOIN 'subscriptions' |
|
8 |
|
ON 'm2m books authors' 'b id' = 'sb book' |
|
9 |
GROUP |
BY 'a id' |
|
10 |
HAVING '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 I Решение 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 в примерах © Боган Марчук Стр: 124/545