Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 в примерах © Боган Марчук Стр: 130/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Переходим к рассмотрению решения для MS SQL Server.
MS SQL I Решение 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 AVG1[books] AS [med_reading] |
|
||
19 |
FROM |
[meadian_preparation] |
|
|
20 |
WHERE |
[RowNumber] IN ( ( [RowCount] + 1 ) / 2 ( |
+ 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 в примерах © Боган Марчук Стр: 131/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Переходим к рассмотрению решения для Oracle.
Oracle і Решение 2.2.7.d [
1WITH . "popularity" ....................................................
2AS (SELECT COUNT("sb_book") AS "books"
3FROM "authors"
4 |
JOIN |
"m2m_books_authors" USING "a_id") |
5 |
LEFT |
OUTER JOIN "subscriptions" |
6 |
|
ON "m2m_books_authors" "b_id" = "sb_book" |
|
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 I |
|
|
|
|
1 |
SELECT EXISTS (SELECT 'b_id' |
|
|
||
2 |
|
FROM |
'books' |
|
|
3 |
|
|
LEFT OUTER JOIN |
(SELECT 'sb_book', COUNT('sb_book') AS |
|
4 |
|
|
|
|
'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 11 |
|
|
|
12 |
|
AS 'errorexists' |
|
|
|
MySQL трактует значения TRUE и FALSE как 1 и 0 соответственно, потому на верхнем уровне запроса (строка 1) можно просто возвращать значение функции
EXISTS.
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 132/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
Подзапрос в строках 3-9 возвращает следующие данные (количество экземпляров, выданных на руки читателям, по каждой книге, хотя бы один экземпляр которой выдан):
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Подзапрос в строках 1 -11 возвращает не более одного ряда выборки, содержащей идентификаторы книг с отрицательным остатком («не более одного», т.к. нас интересует просто факт наличия или отсутствия таких книг). Если этот подзапрос вернёт ноль рядов, функция EXISTS на пустой выборке вернёт FALSE (0). Если подзапрос вернёт один ряд, множество уже будет непустым, и функция EXISTS вер-
нёт TRUE (1).
Поскольку в MS SQL Server и Oracle нельзя напрямую получить 1 и 0 из результата работы функции EXISTS, решения для этих СУБД будут чуть более сложными.
MS SQL I Решение 2.2.7.e |
| |
|
|
||
1 |
WITH [books taken] |
|
|
||
2 |
|
AS (SELECT [sb book], |
|
|
|
3 |
|
|
COUNT([sb |
AS [taken] |
|
4 |
|
FROM |
[subscriptions] |
|
|
5 |
|
WHERE |
[sb is active] = |
Y' |
|
6 |
|
GROUP |
BY [sb book]) |
|
|
7 |
SELECT TOP 1 CASE |
|
|
||
8 |
|
|
WHEN EXISTS (SELECT TOP 1 [b id] |
|
|
9 |
|
|
FROM |
[books] |
|
10 |
|
|
|
LEFT OUTER JOIN |
[books_taken] |
11 |
|
|
|
ON[b id] = [sb book] |
|
12 |
|
|
WHERE |
( [b quantity] |
ISNULL [taken], 0)) < 0 |
13 |
|
|
THEN 1 |
|
|
14 |
|
|
ELSE 0 |
|
|
15 |
|
END AS [error_exists] |
|
|
|
16 |
FROM |
[books_taken] |
|
|
|
Общее табличное выражение в строках 1-6 возвращает информацию о том, сколько экземпляров каждой книги выдано на руки читателям:
sb_book |
taken |
1 |
2 |
3 |
1 |
4 |
1 |
5 |
1 |
Подзапрос в строках 8-12 возвращает не более одного идентификатора книги с отрицательным остатком и передаёт эту информацию в функцию EXISTS, которая возвращает TRUE или FALSE в зависимости от того, нашлись такие книги или не нашлись.
Конструкция CASE ... WHEN ... THEN ... ELSE ... END в строках 715
позволяет преобразовать логический результат работы функции EXISTS в требуемые по условию задачи 1 и 0.
Указание TOP 1 в строке 7 необходимо для того, чтобы в итоговую выборку попала одна строка, а не такое количество строк, какое возвратит общее табличное
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 133/545
Пример 17: запросы на объединение, функция COUNT и агрегирующие функции
выражение.
Работа с MySQL, MS SQL Server и Oracle в примерах © Боган Марчук Стр: 134/545