Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
MS SQL I Решение 2.2.7.b |
1-- Вариант 2: на основе ранжирования
2WITH [prepared data]
3AS (SELECT [authors] [a id],
4 |
|
[authors] [a name] |
|
5 |
|
COUNT [sb book]) |
AS[books] |
6 |
|
RANK() |
|
7 |
|
OVER ( |
|
8 |
|
ORDER BY COUNT([sb book]) DESC) |
AS[rank] |
9 |
FROM |
[authors] |
|
10 |
|
JOIN [m2m books authors] |
|
11 |
|
ON [authors] [a id] = [m2m books authors] [a id] |
|
12 |
|
LEFT OUTER JOIN [subscriptions] |
|
13 |
|
ON [m2m books |
[bid] = [sb book] |
14 |
GROUP |
BY [authors] [a id], |
|
15 |
|
[authors] [a name] |
|
16SELECT [a id],
17[a name],
18[books]
19 |
FROM |
[prepared data] |
20 |
WHERE |
[rank] = 1 |
1-- Вариант 3: на основе конструкции TOP ... WITH TIES
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 |
[bid] = [sb book] |
11 |
|
GROUP |
BY [authors] [a id], |
|
12 |
|
|
[authors] [a name] |
|
13 |
SELECT TOP 1 WITH TIES [a id], |
|
||
14 |
|
|
[a name], |
|
15 |
|
|
[books] |
|
16 |
FROM |
[prepared data] |
|
|
17 |
ORDER BY [books] DESC |
|
||
Вариант 1 решения для MS SQL Server отличается от варианта 1 для MySQL тем, что благодаря возможности использования общих табличных выражений мы можем избежать двукратного определения количества выдач читателям книг каждого автора: эта информация один раз определяется в общем табличном выражении (строки 2-12), и на этих же данных производится поиск максимального значения выдач книг (строки 17-18).
Вариант 2 основан на ранжировании авторов по количеству выдач их книг читателям с последующим отбором авторов, занявших первое место. Общее табличное выражение в строках 2-15 возвращает следующие данные:
a_id |
a_name |
books |
rank |
6 |
Б. Страуструп |
4 |
1 |
7 |
А.С. Пушкин |
4 |
1 |
2 |
А. Азимов |
2 |
3 |
3 |
Д. Карнеги |
2 |
3 |
1 |
Д. Кнут |
1 |
5 |
4 |
Л.Д. Ландау |
0 |
6 |
5 |
Е.М. Лифшиц |
0 |
6 |
В строке 20 на этот набор налагается ограничение, помещающее в выборку только авторов со значением rank равным 1.
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 125/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Вариант 3 основан на использовании специфичного для MS SQL Server синтаксиса TOP ... WITH TIES для выбора ограниченного числа первых записей с
присоединением к этому набору ещё нескольких записей, совпадающих с выбранными по значению поля сортировки.
Сначала мы получаем такой набор данных:
a_id |
a_name |
Books |
6 |
Б. Страуструп |
4 |
7 |
А.С. Пушкин |
4 |
2 |
А. Азимов |
2 |
3 |
Д. Карнеги |
2 |
1 |
Д. Кнут |
1 |
4 |
Л.Д. Ландау |
0 |
5 |
Е.М. Лифшиц |
0 |
Затем, благодаря конструкции SELECT TOP 1 WITH TIES ... FROM [prepared_data] ORDER BY [books] DESC MS SQL Server оставляет только первую запись (TOP 1) и присоединяет к ней (WITH TIES) все другие записи с тем же самым значением в поле books, т.к. по нём идёт сортировка (ORDER BY [books]). В нашем случае это значение — 4. Так получается финальный результат выборки:
a_id |
a_name |
books |
6 |
Б. Страуструп |
4 |
7 |
А.С. Пушкин |
4 |
Представленное ниже решение задачи 2.2.7. b для Oracle полностью эквивалентно решению для MS SQL Server за исключением отсутствия третьего варианта, т.к. Oracle не поддерживает конструкцию TOP ... WITH TIES.
Oracl |
і |
Решение 2.2.7.b |
I |
|
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
-- Вариант 1: на основе функции MAX |
|||||
2 |
WITH "prepared data" |
|
|
|||
3 |
|
|
AS (SELECT "a id", |
|
|
|
4 |
|
|
|
"a name", |
|
|
5 |
|
|
|
COUNT "sb book" |
AS "books" |
|
6 |
|
|
FROM |
"authors" |
|
|
7 |
|
|
|
JOIN "m2m books authors" USING("a id" |
||
8 |
|
|
|
LEFT OUTER JOIN "subscriptions" |
||
9 |
|
|
|
|
ON "m2m books authors" "b id" = "sb book" |
|
10 |
|
|
GROUP |
BY "a id", |
|
|
11 |
|
|
|
"a name" |
|
|
12 |
SELECT "a id", |
|
|
|
||
13 |
|
|
"a name", |
|
|
|
14 |
|
|
"books" |
|
|
|
15 |
FROM |
|
"prepared data" |
|
|
|
16 |
WHERE |
"books" = (SELECT MAX "books") |
||||
17 |
|
|
|
FROM |
"prepared data") |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 126/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Oracle I Решение 2.2.7.b
1-- Вариант 2: на основе ранжирования
2WITH "prepared data"
3AS (SELECT "a id"
4 |
|
"a name", |
|
5 |
|
COUNT "sb book") |
AS "books", |
6 |
|
RANK() |
|
7 |
|
OVER ( |
|
8 |
|
ORDER BY COUNT("sb book") DESC) AS "rank" |
|
9 |
FROM |
"authors" |
|
10 |
|
JOIN "m2m books authors" USING("a id" |
|
11 |
|
LEFT OUTER JOIN "subscriptions" |
|
12 |
|
ON "m2m books authors" "b id" = "sb book" |
|
13 |
GROUP |
BY "a id", |
|
14 |
|
"a name" |
|
15SELECT "a id",
16"a name"
17"books"
18 |
FROM |
"prepared data" |
19 |
WHERE |
"rank" = 1 |
Исследование 2.2.7.EXP.A: сравним скорость работы решений этой задачи, выполнив все запросы 2.2.7.а на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
|
|
MySQL |
MS SQL Server |
Oracle |
Вариант 1 |
(MAX() ) |
4787.841 |
16.106 |
155.918 |
Вариант 2 |
(RANK() ) |
- |
8.138 |
1.407 |
Вариант 3(TOP ...) |
- |
7.312 |
- |
|
Здесь мы наблюдаем ситуацию, обратную полученной в исследовании 2.1.8. EXP.B{47}, где вариант с функций MAX оказался быстрее варианта с RANK. И
это совершенно нормально, т.к. эксперименты проводились на разных наборах данных и в контексте разных запросов. Т.е. на скорость выполнения запроса влияет далеко не только лишь использование той или иной функции, но и множество других параметров.
Решение 2.2.7.c{114}.
MySQL |
|
Решение 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') AS 'prepared data' |
|
MS SQL |
Решение 2.2.7.c |
|
|
||
1 |
SELECT AVG(CAST[books] AS FLOAT)) AS [avg reading] |
||||
2 |
FROM |
(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]) AS [prepared data] |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 127/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
|
Oracl |
і |
Решение 2.2.7.c |
I |
|
|
|
e |
|
|
|
||||
|
|
|
|
|
|
|
|
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 |
^ медиана = 65 |
2 |
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 в примерах © Боган Марчук Стр: 128/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' |
|
11 |
|
ORDER BY 'books') AS 'popularity', |
|||
12 |
|
(SELECT |
|
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 ) / 2l ) |
|
|
|
|
|
|
|
Начнём с самых глубоко вложенных подзапросов в строках 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 в примерах © Боган Марчук Стр: 129/545