Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 16: запросы на объединение и функция COUNT
MS SQL Решение 2.2.6.b (продолжение)
1-- Вариант 4: без подзапросов
2WITH [books_taken]
3AS (SELECT [sb_book],
4 |
|
|
COUNT([sb_book]) |
AS [taken] |
5 |
|
FROM |
[subscriptions] |
|
6 |
|
WHERE |
[sb_is_active] = |
'Y' |
7 |
|
GROUP |
BY [sb_book]) |
|
8SELECT [b_id],
9[b_name],
10( [b_quantity] - ISNULL([taken], 0) ) AS [real_count]
11 |
|
FROM |
[books] |
|
12 |
|
|
LEFT OUTER JOIN |
[books_taken] |
13 |
|
|
ON |
[b_id] = [sb_book] |
14 |
|
ORDER |
BY [real_count] |
DESC |
|
|
|
|
|
Вариант 2 основан на том, что общее табличное выражение в строках 3-8 подготавливает информацию о количестве экземпляров книг, находящихся на руках у читателей. Выполним отдельно соответствующий фрагмент запроса:
MS SQL Решение 2.2.6.b (модифицированный фрагмент запроса)
1-- Вариант 2: использование общего табличного выражения
2-- и коррелирующего подзапроса
3WITH [books_taken]
4 |
|
AS (SELECT |
[sb_book] |
AS [b_id], |
5 |
|
|
COUNT([sb_book]) |
AS [taken] |
6 |
|
FROM |
[subscriptions] |
|
7 |
|
WHERE |
[sb_is_active] = |
'Y' |
8 |
|
GROUP |
BY [sb_book]) |
|
9 |
|
SELECT * FROM [books_taken] |
|
|
|
|
|
|
|
Результат выполнения этого фрагмента запроса таков:
b_id |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Далее в строках 11-16 исходного запроса выполняется коррелирующий подзапрос, возвращающий для каждой книги количество выданных на руки читателям экземпляров или NULL, если ни один экземпляр не выдан. Чтобы иметь возможность корректно использовать такой результат в арифметическом выражении, в строке 11 исходного запроса мы используем функцию ISNULL, преобразующую NULL-значения в 0.
Вариант 3, основанный на пошаговом применении двух общих табличных выражений, подготавливает для коррелирующего подзапроса полностью готовый набор данных.
Первое общее табличное выражение (строки 2-5) возвращает следующие данные:
sb_book
3
5
1
1
4
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 110/545
Пример 16: запросы на объединение и функция COUNT
Второе общее табличное выражение (строки 6-12) возвращает следующие данные:
b_id |
taken |
1 |
2 |
2 |
0 |
3 |
1 |
4 |
1 |
5 |
1 |
6 |
0 |
7 |
0 |
На основе полученных данных коррелирующий подзапрос в строках 15-18 вычисляет реальное количество экземпляров книг в библиотеке. Поскольку из второго общего табличного выражения данные поступают с «готовыми нулями» для книг, ни один экземпляр которых не выдан читателям, здесь нет необходимости использовать функцию ISNULL.
Вариант 4 основан на предварительной подготовке в общем табличном выражении информации о том, сколько книг выдано читателям, с последующим вычитанием этого количества из количества зарегистрированных в библиотеке книг. Общее табличное выражение возвращает следующие данные:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Поскольку при группировке для книг, ни один экземпляр которых не выдан читателям, значение taken будет равно NULL, мы применяем в 10-й строке запроса функцию ISNULL, преобразующую значения NULL в 0.
Рассмотрим решение задачи 2.2.6.b для Oracle. Единственное заметное отличие этого решения от решения для MS SQL Server заключается в том, что в Oracle используется функция NVL для получения поведения, аналогичного функции ISNULL в MS SQL Server (подстановка значения 0 вместо NULL).
Oracle Решение 2.2.6.b
1-- Вариант 1: использование коррелирующего подзапроса
2SELECT DISTINCT "b_id",
3 |
|
"b_name", |
|
4 |
|
( "b_quantity" - (SELECT |
COUNT("int"."sb_book") |
5 |
|
FROM |
"subscriptions" "int" |
6 |
|
WHERE |
"int"."sb_book" = "ext"."sb_book" |
7 |
|
|
AND "int"."sb_is_active" = 'Y') ) |
8 |
|
AS |
|
9 |
|
"real_count" |
|
10 |
|
FROM |
"books" |
|
11 |
|
|
LEFT OUTER JOIN |
"subscriptions" "ext" |
12 |
|
|
ON |
"books"."b_id" = "ext"."sb_book" |
13 |
|
ORDER |
BY "real_count" |
DESC |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 111/545
Пример 16: запросы на объединение и функция COUNT
Oracle Решение 2.2.6.b (продолжение)
1-- Вариант 2: использование общего табличного выражения
2-- и коррелирующего подзапроса
3WITH "books_taken"
4 |
|
AS (SELECT |
"sb_book" |
AS |
"b_id", |
5 |
|
|
COUNT("sb_book") |
AS |
"taken" |
6 |
|
FROM |
"subscriptions" |
|
|
7 |
|
WHERE |
"sb_is_active" = |
'Y' |
|
8 |
|
GROUP |
BY "sb_book") |
|
|
|
|
|
|
|
|
9SELECT "b_id",
10"b_name",
11( "b_quantity" - NVL((SELECT "taken"
12 |
|
|
FROM |
"books_taken" |
13 |
|
|
WHERE |
"books"."b_id" = |
14 |
|
|
|
"books_taken"."b_id"), 0 |
15 |
|
|
) ) AS |
|
16 |
|
|
"real_count" |
|
17 |
|
FROM |
"books" |
|
18 |
|
ORDER |
BY "real_count" DESC |
|
|
|
|
|
|
1-- Вариант 3: пошаговое применение общего табличного выражения и подзапроса
2WITH "books_taken"
3AS (SELECT "sb_book"
4 |
|
FROM |
"subscriptions" |
5 |
|
WHERE |
"sb_is_active" = 'Y'), |
6"real_taken"
7AS (SELECT "b_id",
8 |
|
|
COUNT("sb_book") AS "taken" |
|
9 |
|
FROM |
"books" |
|
10 |
|
|
LEFT OUTER JOIN |
"books_taken" |
11 |
|
|
ON |
"b_id" = "sb_book" |
12GROUP BY "b_id")
13SELECT "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: без подзапросов
2WITH "books_taken"
3AS (SELECT "sb_book",
4 |
|
|
COUNT("sb_book") |
AS "taken" |
5 |
|
FROM |
"subscriptions" |
|
6 |
|
WHERE |
"sb_is_active" = |
'Y' |
7 |
|
GROUP |
BY "sb_book") |
|
8SELECT "b_id",
9"b_name",
10( "b_quantity" - NVL("taken", 0) ) AS "real_count"
11 |
|
FROM |
"books" |
|
12 |
|
|
LEFT OUTER JOIN |
"books_taken" |
13 |
|
|
ON |
"b_id" = "sb_book" |
14 |
|
ORDER |
BY "real_count" |
DESC |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 112/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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 113/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 |
Oracle |
||
med_reading |
|
med_reading |
|
med_reading |
2.0000 |
|
2 |
|
2 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 114/545