Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 9: вычисление среднего значения агрегированных данных
2.1.9.Пример 9: вычисление среднего значения агрегированных данных
Задача 2.1.9.a{56}: показать, сколько в среднем экземпляров книг сейчас на руках у каждого читателя.
Задача 2.1.9.b{56}: показать, сколько в среднем книг сейчас на руках у каждого читателя.
Задача 2.1.9.c{57}: показать, на сколько в среднем дней читатели берут книги (учесть только случаи, когда книги были возвращены).
Задача 2.1.9.d{57}: показать, сколько в среднем дней читатели читают книгу (учесть оба случая — и когда книга была возвращена, и когда книга не была возвращена).
Разница между задачами 2.1.9.a и 2.1.9.b состоит в том, что первая учитывает случаи «у читателя на руках несколько экземпляров одной и той же книги», а вторая любое количество таких дубликатов будет считать одной книгой.
Разница между задачами 2.1.9.С и 2.1.9.d состоит в том, что для решения задачи 2.1.9.С достаточно данных из таблицы, а для решения задачи 2.1.9.d придётся определять текущую дату.
Ожидаемый результат 2.1.9.a.
avg books
2.5
Ожидаемый результат 2.1.9.b.
avg books 2.5
Ожидаемые результаты 2.1.9.a и 2.1.9.b совпадают на имеющемся наборе данных, т.к. ни у кого из читателей сейчас нет на руках двух и более экземпляров одной и той же книги, но вы можете изменить данные в базе данных и посмотреть, как изменится результат выполнения запроса.
Ожидаемый результат 2.1.9.С.
avg days 46
Ожидаемый результат 2.1.9.d.
Обратите внимание: ожидаемый результат 2.1.9.d зависит от даты, в которую выполнялся запрос. Потому у вас он обязательно будет другим!
avg days
560.6364
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 60/545
Пример 9: вычисление среднего значения агрегированных данных
Решение 2.1.9.a{55}.
См. пояснение ниже после решения 2.1.9.b{56}.
уЦ7
ЧР Решение 2.1.9.b{55}.
MySQL і Решение 2.1.9.b [ |
|
|
|
1 |
SELECT AVG('books_per_subscriber') AS 'avg_books' |
||
2 |
FROM (SELECT COUNT(DISTINCT 'sb_book') AS |
'books_per_subscriber' |
|
3 |
FROM |
'subscriptions' |
|
4 |
WHERE |
'sb_is_active' = 'Y' |
|
|
GROUP BY 'sb subscriber') AS 'count subquery' |
||
MS SQL і Решение 2.1.9.b |
[ |
|
|
1SELECT. AVG (CAST ([books^per^subscriber]. AS..... FLOAT)) ....... AS [aVgTbooks]............................
2 |
FROM (SELECT COUNT(DISTINCT [sb_book]| AS |
[books_per_subscriber] |
|
3 |
FROM |
[subscriptions] |
|
4 |
WHERE |
[sb_is_active] = 'Y' |
|
|
GROUP BY [sb subscriber]) AS [count subquery] |
||
|
Oracl |
і |
Решение 2.1.9.b |
I |
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
|
SELECT AVG("books_per_subscriber" |
AS "avg_books" |
|||
2 |
|
FROM |
(SELECT COUNT(DISTINCT "sb book") AS "books_per subscriber" |
|||
3 |
|
|
|
FROM |
"subscriptions" |
|
4 |
|
|
|
WHERE |
"sb is active" = 'Y' |
|
5 |
|
|
|
GROUP |
BY "sb_subscriber" |
|
Суть решений 2.1.9.a{56} и 2.1.9.b{56} состоит в том, чтобы сначала подготовить агрегированные данные (подзапрос в строках 2-5 всех шести представленных выше запросов 2.1.9.a-2.1.9.b), а затем вычислить среднее значение от этих заранее подготовленных значений.
Разница в решении задач 2.1.9.a{55} и 2.1.9.b{55} состоит в использовании во втором случае ключевого слова DISTINCT (строка 2 всех шести запросов 2.1.9.a-
2.1.9. b), позволяющего проигнорировать дубликаты книг.
Разница в решениях для трёх разных СУБД состоит в необходимости предварительного приведения аргумента функции AVG к дроби в MS SQL Server и отсут-
ствию необходимости именовать подзапрос в Oracle. В остальном решения 2.1.9. a{56} и 2.1.9.b{56} идентичны для всех трёх СУБД.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 61/545
Пример 9: вычисление среднего значения агрегированных данных
Для наглядности покажем, какие данные были возвращены подзапросами (строки 2-5 всех шести запросов 2.1.9.a-2.1.9.b): books_per_subscriber
3 _________________
2
•Ч Решение 2.1.9.c55.
Для всех трёх СУБД решение задачи 2.1.9.c{55} является одинаковым за исключением синтаксиса вычисления разницы в днях между двумя датами (строка 1 в каждом из трёх запросов 2.1.9.С). Результаты вычисления разницы дат выглядят следующим образом (эти данные поступают на вход функции AVG):
data
31 _
61 _
61 _
61 _
31 _
31
Ч р Решение 2.1.9.d{57}.
Здесь самый большой вопрос состоит в том, как определить учитываемый диапазон времени. Его начало хранится в поле sb_start, а вот с завершением не всё так просто. Если книга была возвращена, датой завершения периода чтения можно считать значение поля sb_finish, если оно находится в прошлом. Если книга не была возвращена, и значение sb_finish находится в прошлом, датой завершения чтения можно считать текущую дату.
Но что делать, если значение sb_finish находится в будущем? Правильный ответ на подобный вопрос в реальной жизни можно получить только от заказчика разрабатываемого приложения. Мы же в учебных целях решим, что в такой ситуации будем использовать текущую дату, если книга уже возвращена, и значение sb_finish, если она ещё не возвращена.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 62/545
Пример 9: вычисление среднего значения агрегированных данных
Итого, у нас есть четыре варианта расчёта времени чтения книги:
• sb_finish в прошлом, книга возвращена: sb_finish - sb_start.
• sb_finish в прошлом, книга не возвращена: текущая_дата - sb_start.
•sb_finish в будущем, книга возвращена: текущая_дата - sb_start.
•sb_finish в будущем, книга не возвращена: sb_finish - sb_start.
Легко заметить, что алгоритмов вычисления всего два, но активируется каждый из них двумя независимыми условиями. Самым простым способом решения здесь является объединение результатов двух запросов с помощью оператора UN-
ION. Важно помнить, что UNION по умолчанию работает в DISTINCT-РЄЖИМЄ, т.е. нужно явно писать UNION ALL.
MySQL і Решение 2.1.9.d
1 |
SELECT |
AVG('diff') AS 'avg_days' ( |
|
|
|
2 |
FROM |
SELECT DATEDIFF('sb_finish', 'sb_start') AS 'diff' |
|
||
3 |
|
|
|||
|
FROM |
'subscriptions' |
|
|
|
4 |
|
|
|
||
|
WHERE |
( 'sb_finish' <= CURDATE() AND 'sb_is_active' = |
'N' ) |
||
5 |
|
||||
|
|
OR ( 'sb_finish' > CURDATE() AND |
'sb_is_active' = |
'Y' ) |
|
6 |
|
|
|||
|
UNION ALL |
|
|
||
7 |
|
|
|
||
|
SELECT DATEDIFF(CURDATE(), 'sb_start') AS 'diff' |
|
|||
8 |
|
|
|||
|
FROM |
'subscriptions' |
|
|
|
9 |
|
|
|
||
|
WHERE |
( 'sb_finish' <= CURDATE() AND 'sb_is_active' = |
'Y' ) |
||
10 |
|
||||
|
|
OR ( 'sb_finish' > CURDATE() AND |
'sb_is_active' = |
'N' ) |
|
11 |
|
|
|||
|
) AS 'diffs' |
|
|
||
12 |
|
|
|
||
|
|
|
|
|
|
Несмотря на громоздкость этого запроса, он прост. В строке 1 решается основная задача — вычисление среднего значения, строки 2-13 лишь подготавливают необходимые данные. В строке 7 используется только что упомянутый оператор UNION ALL, с помощью которого объединяются результаты двух отдельных запро-
сов, представленных в строках 3-6 и 8-11 соответственно. Объёмные конструкции WHERE в строках 5-6 и 10-11 определяют условия, по которым активируется один из двух алгоритмов вычисления времени чтения книги.
Вот такие наборы данных возвращают запросы в строках 3-6 и 8-11. Первый запрос (строки 3-6) возвращает:
diff
31 __
61 __
61 __
61 __
3684
31
Второй запрос (строки 8-11) возвращает.
diff
1266
458
458
28
28
Решения для MS SQL Server и Oracle следуют той же логике и отличаются только синтаксисом получения разницы в днях между датами (и необходимостью приведения аргумента функции AVG к дроби для MS SQL Server).
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 63/545
Пример 9: вычисление среднего значения агрегированных данных
MS SQL I Решение 2.1.9.d |
|
SELECT AVG(CAST([diff] AS FLOAT)) AS [avg_days] |
|
||||
2 |
FROM |
( |
|
|
|
|
3 |
|
SELECT DATEDIFF(day, [sb_start], [sb_finish]) AS [diff] |
|
|||
4 |
|
FROM |
[subscriptions] |
|
|
|
5 |
|
WHERE |
( [sb_finish] <= CONVERT(date, GETDATE()) |
|
||
6 |
|
|
|
AND [sb_is_active] = |
'N' ) |
|
|
|
|
OR ( [sb_finish] > CONVERT(date, GETDATE()) |
|
||
8 |
|
|
|
AND [sb_is_active] = |
'Y' ) |
|
9 |
|
UNION ALL |
|
|
|
|
10 |
|
SELECT DATEDIFF(day, [sb_start], CONVERT(date, GETDATE())) AS [diff] |
||||
11 |
|
FROM |
[subscriptions] |
|
|
|
12 |
|
WHERE |
( [sb_finish] <= CONVERT(date, GETDATE()) |
|
||
13 |
|
|
|
AND [sb_is_active] = |
'Y' ) |
|
14 |
|
OR ( |
[sb_finish] > CONVERT(date, GETDATE()) |
|
||
15 |
|
|
|
AND [sb_is_active] = |
'N' ) |
|
16 |
|
) AS [diffs] |
|
|
||
|
|
|
|
|
||
|
|
|
|
|
||
Oracle I |
Решение 2.1.9.d |
| |
|
|
||
1 |
SELECT AVG "diff") AS "avg_days" |
|
|
|||
2 |
FROM |
( |
|
|
|
|
3 |
|
SELECT |
"sb_finish" - "sb_start" |
AS "diff" |
|
|
4 |
|
FROM |
"subscriptions" |
|
|
|
5 |
|
WHERE |
( "sb_finish" <= TRUNC(SYSDATE) AND "sb_is_active" = |
'N' ) |
||
6 |
|
OR ( |
"sb_finish" > TRUNC(SYSDATE) AND "sb_is_active" = |
'Y' ) |
||
|
|
UNION ALL |
|
|
|
|
8 |
|
SELECT (TRUNC(SYSDATE) - "sb_start" AS "diff" |
|
|||
9 |
|
FROM |
"subscriptions" |
|
|
|
10 |
|
WHERE |
( "sb_finish" <= TRUNC(SYSDATE) AND "sb_is_active" = |
'Y' ) |
||
11 |
|
OR ( |
"sb_finish" > TRUNC(SYSDATE) AND "sb_is_active" = |
'N' ) |
||
12 |
|
) |
|
|
|
|
В решении для Oracle также стоит отметить, что там не существует такого понятия, как «просто дата без времени», потому мы вынуждены использовать конструкцию TRUNC (SYSDATE) , чтобы «отрезать» время от даты. Иначе результат
вычитания в строке 8 вернёт не целое число дней, а дробное (что отличается от поведения MySQL и MS SQL Server), а также могут неожиданным образом работать все условия, в которых фигурирует дата.
И ещё один очевидный, но достойный упоминания факт: во всех запросах для всех СУБД в условиях, связанных с текущей датой, мы использовали <= текущая_дата и > текущая дата, т.е. включали «сегодня» в один из диапазонов. Если использовать два строгих неравенства или два нестрогих, мы рискуем либо «потерять» записи со значением sb_finish, совпадающим с текущей датой, либо учесть такие случаи дважды.
Если вы внимательно изучили только что рассмотренное решение, у вас обязан был возникнуть вопрос о том, как на корректность вычислений влияет запись из таблицы subscriptions с идентификатором 91 (в ней дата возврата книги находится в прошлом по отношению к дате выдачи книги):
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
91 |
4 |
1 |
2015-10-07 |
2015-03-07 |
Y |
Ответ прост и неутешителен: да, из-за этой ошибки результат получается искажённым. Что делать? Ничего. Мы не можем позволить себе роскошь в каждом запросе на выборку учитывать возможность возникновения таких ошибок (например, дата выдачи книги могла оказаться в будущем). Контроль таких ситуаций должен быть возложен на операции вставки и обновления данных. Соответствующий пример (см. задачу 4.2.1.a{315}) будет рассмотрен в разделе{272}, посвящённом триггерам.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 64/545