Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Oracle |
|
Решение 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") "prepared_data" |
|
Решение этой задачи для MS SQL Server и Oracle может быть представлено в виде общего табличного выражения, но из соображений совместимости оставлено в том же виде, что и решение для MySQL. Подзапрос в секции FROM играет роль источника данных и производит подсчёт количества выдач книг по каждому автору. Затем в основной секции запроса (строка 1 для всех трёх СУБД) из подготовленного набора извлекается искомое среднее значение.
Решение 2.2.7.d{114}.
Обратите внимание, насколько просто решается эта задача в Oracle (который поддерживает функцию MEDIAN), и насколько нетривиальны решения для
MySQL и MS SQL Server.
Логика решения для MySQL и MS SQL Server построена на математическом определении медианного значения: набор данных сортируется, после чего для наборов с нечётным количеством элементов медианой является значение центрального элемента, а для наборов с чётным значением элементов медианой является среднее значение двух центральных элементов. Поясним на примере.
Пусть у нас есть следующий набор данных с нечётным количеством элемен-
тов:
Номер элемента |
Значение элемента |
|
1 |
40 |
|
2 |
65 |
медиана = 65 |
3 |
90 |
|
Центральным элементом является элемент с номером 2, и его значение 65 является медианой для данного набора.
Если у нас есть набор данных с чётным количеством элементов:
Номер элемента |
Значение элемента |
|
|
1 |
40 |
|
|
2 |
65 |
медиана = ( 65 + 90 ) / 2 = 77.5 |
|
3 |
90 |
||
|
|||
4 |
95 |
|
Центральными элементами являются элементы с номерами 2 и 3, и среднее арифметическое их значений ( 65 + 90 ) / 2 является медианой для данного набора.
Таким образом нам нужно:
•Получить отсортированный набор данных.
•Определить количество элементов в этом наборе.
•Определить центральные элементы набора.
•Получить среднее арифметическое этих элементов.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 120/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Мы можем не различать случаи, когда центральный элемент один, и когда их два, вычисляя среднее арифметическое в обоих случаях, т.к. среднее арифметическое от любого одного числа — это и есть само число.
Рассмотрим решение для MySQL.
|
MySQL |
Решение 2.2.7.d |
|
|
|
|||
|
1 |
|
SELECT |
AVG(`books`) AS |
`med_reading` |
|||
|
2 |
|
FROM |
(SELECT @rownum |
:= @rownum |
+ 1 AS `RowNumber`, |
||
|
3 |
|
|
|
|
`books` |
|
|
|
4 |
|
|
|
FROM |
(SELECT |
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`) |
AS `inner_data`, |
10 |
|
|
|
|
(SELECT |
@rownum := |
0) AS `rownum_initialisation` |
|
11ORDER BY `books`) AS `popularity`,
12(SELECT COUNT(*) AS `RowCount`
13 |
|
FROM |
(SELECT |
COUNT(`sb_book`) AS |
`books` |
|
|
|
14 |
|
|
FROM |
`authors` |
|
|
|
|
15 |
|
|
|
JOIN `m2m_books_authors` USING |
(`a_id`) |
|||
16 |
|
|
|
LEFT OUTER JOIN `subscriptions` |
|
|||
17 |
|
|
|
ON `m2m_books_authors`.`b_id` = `sb_book` |
||||
18 |
|
|
GROUP |
BY `a_id`) AS `inner_data`) |
AS |
`total_rows` |
||
19 |
|
WHERE `RowNumber` IN ( FLOOR(( `RowCount` |
+ 1 |
) / |
2), |
|
||
20 |
|
|
|
FLOOR(( `RowCount` |
+ 2 |
) / |
2) |
) |
|
|
|
|
|
|
|
|
|
Начнём с самых глубоко вложенных подзапросов в строках 4-9 и 13-18: легко заметить, что они полностью дублируются (увы, подготовить эти данные один раз и использовать многократно в MySQL не получится). Оба подзапроса возвращают следующие данные:
books
1
2
2
0
0
4
4
Подзапрос в строках 12-18 определяет количество рядов в этом наборе данных и возвращает одно число: 7.
Подзапрос в строках 2-11 упорядочивает этот набор данных и нумерует его строки. Поскольку в MySQL нет готовых встроенных функций для нумерации строк выборки, приходится получать необходимый эффект в несколько шагов:
•Конструкция SELECT @rownum := 0 в строке 10 инициализирует переменную @rownum значением 0.
• Конструкция SELECT @rownum := @rownum + 1 AS `RowNumber` в строке
2 увеличивает на 1 значение переменной @rownum для каждого следующего ряда выборки. Колонка, в которой будут располагаться номера рядов, будет называться RowNumber.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 121/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Результат выполнения подзапроса в строках 2-11 таков:
RowNumber |
books |
1 |
0 |
2 |
0 |
3 |
1 |
4 |
2 |
5 |
2 |
6 |
4 |
7 |
4 |
Поднимемся на уровень выше и посмотрим, что вернёт весь подзапрос в строках 2-18 целиком:
RowNumber |
books |
RowCount |
1 |
0 |
7 |
2 |
0 |
7 |
3 |
1 |
7 |
4 |
2 |
7 |
5 |
2 |
7 |
6 |
4 |
7 |
7 |
4 |
7 |
Повторяющееся значение RowCount выглядит несколько раздражающе, но для данного варианта решения такой подход является наиболее простым.
Конструкция WHERE в строках 19 и 20 указывает на необходимость взять для финального анализа только те ряды, значения RowNumber которых удовлетворяют условиям
• Условие 1: FLOOR(( `RowCount` + 1 ) / 2).
• Условие 2: FLOOR(( `RowCount` + 2 ) / 2).
В нашем конкретном случае RowCount = 7, получается:
• Условие 1: FLOOR(( 7 + 1 ) / 2) = FLOOR (8 / 2) = 4.
• Условие 2: FLOOR(( 7 + 2 ) / 2) = FLOOR (9 / 2) = 4.
Оба условия указывают на один и тот же ряд — 4-й. Значение 2 поля books из 4-го ряда передаётся в функцию AVG (первая строка запроса), и т.к. AVG(2) = 2, мы получаем конечный результат: медиана равна 2.
|
Если бы количество рядом было чётным (например, 8), условия в строках 19 |
|||||||||
и 20 приняли бы следующие значения: |
|
|
|
|
|
|||||
• |
Условие 1: |
FLOOR(( |
8 |
+ |
1 ) / 2) |
= |
FLOOR |
(9 / 2) = 4 |
. |
|
• |
Условие 2: |
FLOOR(( |
8 |
+ |
2 ) / 2) |
= |
FLOOR |
(10 / 2) = 5 |
. |
|
|
|
|
|
|
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 122/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Переходим к рассмотрению решения для MS SQL Server.
MS SQL Решение 2.2.7.d
1WITH [popularity]
2AS (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]), |
|
9[meadian_preparation]
10AS (SELECT CAST([books] AS FLOAT) AS [books],
11 |
|
|
|
ROW_NUMBER() |
12 |
|
|
|
OVER ( |
13 |
|
|
|
ORDER BY [books]) AS [RowNumber], |
14 |
|
|
|
COUNT(*) |
15 |
|
|
|
OVER ( |
16 |
|
|
|
PARTITION BY NULL) AS [RowCount] |
17 |
|
|
FROM |
[popularity]) |
18 |
|
SELECT |
AVG([books]) AS [med_reading] |
|
19 |
|
FROM |
[meadian_preparation] |
|
20 |
|
WHERE |
[RowNumber] IN ( ( [RowCount] + 1 ) / 2, ( [RowCount] + 2 ) / 2 ) |
|
|
|
|
|
|
Благодаря наличию общих табличных выражений и функций нумерации рядов выборки, решение для MS SQL Server получается намного проще.
Первое общее табличное выражение в строках 1-8 возвращает такие дан-
ные:
books
1
2
2
0
0
4
4
Второе общее табличное выражение дорабатывает этот набор данных, в результате чего получается:
books |
RowNumber |
RowCount |
0 |
1 |
7 |
0 |
2 |
7 |
1 |
3 |
7 |
2 |
4 |
7 |
2 |
5 |
7 |
4 |
6 |
7 |
4 |
7 |
7 |
Основная часть запроса в строках 18-20 действует совершенно аналогично основной части запроса в решении для MySQL (строки 1 и 19-20): определяются номера центральных рядов и вычисляется среднее арифметическое значений поля books этих рядов, что и является искомым значением медианы.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 123/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Переходим к рассмотрению решения для Oracle.
Oracle Решение 2.2.7.d
1WITH "popularity"
2AS (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") |
8 |
|
SELECT MEDIAN("books") AS "med_reading" FROM "popularity" |
|
Благодаря наличию в Oracle функции MEDIAN решение сводится к подготовке множества значений, медиану которого мы ищем, и… вызову функции MEDIAN. Общее табличное выражение в строках 1-7 подготавливает уже очень хорошо знакомый нам по решениях для двух других СУБД набор данных:
books
1
2
2
0
0
4
4
Решение 2.2.7.e{114}.
Для решения этой задачи необходимо:
Определить по каждой книге количество её экземпляров, выданных на руки читателям.
Вычесть полученное значение и количества экземпляров книги, зарегистрированных в библиотеке.
Проверить, существуют ли книги, для которых результат такого вычитания отказался отрицательным, и вернуть 0, если таких книг нет, и 1, если такие книги есть.
MySQL |
Решение 2.2.7.e |
|
|
|
1 |
SELECT EXISTS (SELECT |
`b_id` |
|
|
2 |
|
FROM |
`books` |
|
3 |
|
|
LEFT OUTER JOIN (SELECT |
`sb_book`, |
4 |
|
|
|
COUNT(`sb_book`) AS `taken` |
5 |
|
|
FROM |
`subscriptions` |
6 |
|
|
WHERE |
`sb_is_active` = 'Y' |
7 |
|
|
GROUP |
BY `sb_book` |
8 |
|
|
) AS `books_taken` |
|
9 |
|
|
ON `b_id` = `sb_book` |
|
10 |
|
WHERE |
( `b_quantity` - IFNULL(`taken`, 0) ) < 0 |
|
11 |
|
LIMIT 1) |
|
|
12 |
|
AS `error_exists` |
|
|
MySQL трактует значения TRUE и FALSE как 1 и 0 соответственно, потому на верхнем уровне запроса (строка 1) можно просто возвращать значение функции
EXISTS.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 124/545