Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 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.c и 2.1.9.d состоит в том, что для решения задачи 2.1.9.c достаточно данных из таблицы, а для решения задачи 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.c.
avg_days
46
Ожидаемый результат 2.1.9.d.
avg_days
560.6364
Обратите внимание: ожидаемый результат 2.1.9.d зависит от даты, в которую выполнялся запрос. Потому у вас он обязательно будет другим!
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 55/545
Пример 9: вычисление среднего значения агрегированных данных
Решение 2.1.9.a{55}.
См. пояснение ниже после решения 2.1.9.b{56}.
MySQL |
Решение 2.1.9.a |
|
|
||
1 |
SELECT |
AVG(`books_per_subscriber`) |
AS `avg_books` |
||
2 |
FROM |
(SELECT |
COUNT(`sb_book`) AS |
`books_per_subscriber` |
|
3 |
|
|
FROM |
`subscriptions` |
|
4 |
|
|
WHERE |
`sb_is_active` = 'Y' |
|
5 |
|
|
GROUP |
BY `sb_subscriber`) |
AS `count_subquery` |
|
|
|
|
||
MS SQL |
Решение 2.1.9.a |
|
|
||
1 |
SELECT |
AVG(CAST([books_per_subscriber] AS FLOAT)) AS [avg_books] |
|||
2 |
FROM |
(SELECT |
COUNT([sb_book]) AS |
[books_per_subscriber] |
|
3 |
|
|
FROM |
[subscriptions] |
|
4 |
|
|
WHERE |
[sb_is_active] = 'Y' |
|
5 |
|
|
GROUP |
BY [sb_subscriber]) |
AS [count_subquery] |
|
|
|
|
|
|
Oracle |
|
Решение 2.1.9.a |
|
|
|
1 |
SELECT |
AVG("books_per_subscriber") |
AS "avg_books" |
||
2 |
FROM |
(SELECT |
COUNT("sb_book") AS |
"books_per_subscriber" |
|
3 |
|
|
FROM |
"subscriptions" |
|
4 |
|
|
WHERE |
"sb_is_active" = 'Y' |
|
5 |
|
|
GROUP |
BY "sb_subscriber") |
|
|
|
Решение 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' |
|
5 |
|
|
GROUP |
BY `sb_subscriber`) |
AS `count_subquery` |
|
|
|
|
||
MS SQL |
Решение 2.1.9.b |
|
|
||
1 |
SELECT |
AVG(CAST([books_per_subscriber] AS FLOAT)) 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]) |
AS [count_subquery] |
|
|
|
|
|
|
Oracle |
|
Решение 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' |
|
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 56/545
Пример 9: вычисление среднего значения агрегированных данных
Для наглядности покажем, какие данные были возвращены подзапросами (строки 2-5 всех шести запросов 2.1.9.a-2.1.9.b):
books_per_subscriber
3
2
Решение 2.1.9.c{55}.
MySQL |
Решение 2.1.9.c |
||
1 |
SELECT |
AVG(DATEDIFF(`sb_finish`, `sb_start`)) AS `avg_days` |
|
2 |
FROM |
`subscriptions` |
|
3 |
WHERE |
`sb_is_active` = 'N' |
|
|
|
|
|
MS SQL Решение 2.1.9.c
1SELECT AVG(CAST (DATEDIFF(day, [sb_start], [sb_finish]) AS FLOAT))
2AS [avg_days]
|
3 |
|
FROM |
[subscriptions] |
|
|
|
4 |
|
WHERE |
[sb_is_active] = 'N' |
|
|
|
|
|
|
|
|
|
|
Oracle |
|
Решение 2.1.9.c |
|
||
|
1 |
|
SELECT |
AVG("sb_finish" - "sb_start") AS "avg_days" |
|
|
|
2 |
|
FROM |
"subscriptions" |
|
|
|
3 |
|
WHERE |
"sb_is_active" = 'N' |
|
|
Для всех трёх СУБД решение задачи 2.1.9.c{55} является одинаковым за исключением синтаксиса вычисления разницы в днях между двумя датами (строка 1 в каждом из трёх запросов 2.1.9.c). Результаты вычисления разницы дат выглядят следующим образом (эти данные поступают на вход функции 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 57/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 |
( |
|
|
|
|
|
3 |
|
|
|
SELECT |
DATEDIFF(`sb_finish`, `sb_start`) AS `diff` |
||
|
4 |
|
|
|
FROM |
`subscriptions` |
|
|
|
5 |
|
|
|
WHERE |
( |
`sb_finish` |
<= CURDATE() AND `sb_is_active` = 'N' ) |
|
6 |
|
|
|
|
OR ( |
`sb_finish` |
> CURDATE() AND `sb_is_active` = 'Y' ) |
7UNION ALL
8SELECT DATEDIFF(CURDATE(), `sb_start`) AS `diff`
9 |
|
FROM |
`subscriptions` |
|
|
10 |
|
WHERE |
( |
`sb_finish` |
<= CURDATE() AND `sb_is_active` = 'Y' ) |
11 |
|
|
OR ( |
`sb_finish` |
> CURDATE() AND `sb_is_active` = 'N' ) |
12 |
|
) AS `diffs` |
|
||
Несмотря на громоздкость этого запроса, он прост. В строке 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 58/545
Пример 9: вычисление среднего значения агрегированных данных
|
MS SQL |
Решение 2.1.9.d |
|||
|
1 |
|
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' ) |
|
7 |
|
|
|
OR ( [sb_finish] > CONVERT(date, GETDATE()) |
|
8 |
|
|
|
AND [sb_is_active] = 'Y' ) |
|
|
|
|
|
|
9UNION ALL
10SELECT 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 |
|
Решение 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' ) |
|
|
|
|
|
|
|
|
7UNION ALL
8SELECT (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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 59/545