Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 9: вычисление среднего значения агрегированных данных
Есть ещё одна проблема с разницами дат, о которой стоит помнить: в повседневной жизни мы привыкли округлять эти значения, в то время как СУБД этой операции не выполняет. Проверьте себя: сколько лет прошло между 2011-01-01 и 2012-01-01? Один год, верно? А между 2011-01-01 и
2012-12-31?
Исследование 2.1.9.EXP.A. Проверка поведения СУБД при вычислении расстояния между двумя датами в годах.
Все шесть запросов 2.1.6.EXP.A вернут один и тот же результат: 1, т.е. между указанными датами с точки зрения СУБД прошёл один год. И это правда: прошёл «один полный год».
Также обратите внимание, насколько по-разному решается задача вычисления разницы между датами в годах в различных СУБД. Особенно интересны строки 2-3 в запросах для MySQL: они позволяют получить корректный результат, когда значения года различны, но на самом деле год ещё не прошёл (например, 20110501 и 2012-04-01).
Задание 2.1.9.TSK.A: показать, сколько в среднем экземпляров книг есть в библиотеке.
Задание 2.1.9.TSK.B: показать в днях, сколько в среднем времени читатели &уже зарегистрированы в библиотеке (временем регистрации считать диапазон
от первой даты получения читателем книги до текущей даты).
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 65/545
Пример 10: использование группировки данных
2.1.10. Пример 10: использование группировки данных
|
Задача 2.1.10.a{61}: показать по каждому году, сколько раз в этот год |
||||
|
читатели брали книги. |
||||
|
Задача 2.1.10.b{62}: показать по каждому году, сколько читателей в год |
||||
|
воспользовалось услугами библиотеки. |
||||
|
Задача 2.1.10.c{63}: показать, сколько книг было возвращено и не возвра- |
||||
|
щено в библиотеку. |
||||
|
Ожидаемый результат 2.1.10.a. |
||||
|
|
|
|
|
|
year |
books_taken |
|
|
||
2011 |
2 |
|
|
|
|
2012 |
3 |
|
|
|
|
2014 |
3 |
|
|
|
|
2015 |
3 |
|
|
|
|
|
Ожидаемый результат 2.1.10.b. |
||||
|
|
|
|
|
|
year |
subscribers |
|
|
||
2011 |
1 |
|
|
|
|
2012 |
3 |
|
|
|
|
2014 |
2 |
|
|
|
|
2015 |
2 |
|
|
|
|
|
Ожидаемый результат 2.1.10.c. |
||||
|
|
|
|||
status |
books |
|
|||
Returned |
6 |
|
|
|
|
Not returned |
5 |
|
|
|
|
Как следует из названия главы, все три задачи будут решаться с помощью группировки данных, т.е. использования GROUP BY.
'IT Решение 2.1.10.a{61}.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 66/545
Пример 10: использование группировки данных |
|
|||||
|
Oracl |
і |
Решение 2.1.10.а |
| |
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
|
SELECT EXTRACT(year FROM "sb start") AS "year" |
||||
2 |
|
|
|
COUNT "sb id") |
AS "books taken" |
|
3 |
|
FROM |
"subscriptions" |
|
||
4 |
|
GROUP |
BY EXTRACT(year FROM "sb start" |
|||
5 |
|
ORDER BY "year" |
|
|
||
Обратите внимание на разницу в 4-й строке в запросах 2.1.10. а: MySQL позволяет в GROUP BY сослаться на имя только что вычисленного выражения, в то
время как MS SQL Server и Oracle не позволяют этого сделать.
Ранее мы уже рассматривали логику работы группировок*20*, но подчеркнём это ещё раз. После извлечения значения года и «объединения ячеек по признаку равенства значений» полученный СУБД результат условно можно представить так:
year |
результат группировки |
сколько ячеек объединено |
|
2011 |
2011 |
2 |
|
2011 |
|||
|
|
||
2012 |
|
|
|
2012 |
2012 |
3 |
|
2012 |
|
|
|
2014 |
|
|
|
2014 |
2014 |
3 |
|
2014 |
|
|
|
2015 |
|
|
|
2015 |
2015 |
3 |
|
2015 |
|
|
ЧР Решение 2.1.10.b{61}.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 67/545
Пример 10: использование группировки данных
Решение 2.1.10.b{62} очень похоже на решение 2.1.10.a{61}: разница лишь в том, что в первом случае нас интересуют все записи за каждый год, а во втором — количество идентификаторов читателей без повторений за каждый год. Покажем это графически:
year |
sb_subscriber |
группировка по году |
число уникальных id читателя |
|
2011 |
1 |
2011 |
1 |
|
2011 |
1 |
|||
|
|
|||
2012 |
1 |
|
|
|
2012 |
3 |
2012 |
3 |
|
2012 |
4 |
|
|
|
2014 |
1 |
|
|
|
2014 |
3 |
2014 |
2 |
|
2014 |
3 |
|
|
|
2015 |
1 |
|
|
|
2015 |
4 |
2015 |
2 |
|
2015 |
4 |
|
|
ЧРешение 2.1.10.c{61}.
Решение 2.1.10.c
1 |
SELECT IF('sb is active' = 'Y' 'Not returned', |
'Returned') AS'status', |
|||
2 |
|
COUNT('sb id') |
|
AS'books' |
|
3 |
FROM |
'subscriptions' |
|
|
|
4 |
GROUP |
BY 'status' |
|
|
|
5 |
ORDER |
BY 'status' DESC |
|
|
|
|
|
|
|
||
MS SQL І Решение 2.1.10.С |
|
|
|
||
1 |
SELECT (CASE |
|
|
|
|
2 |
|
WHEN |
[sb_is_active] |
= |
'Y' |
3 |
|
THEN |
'Not returned' |
|
|
4 |
|
ELSE |
'Returned' |
|
|
5 |
|
END) |
AS [status], |
|
|
6 |
|
COUNT([sb_id]) AS [books] |
|
|
|
|
FROM |
[subscriptions] |
|
|
|
8 |
GROUP |
BY (CASE |
|
|
|
9 |
|
WHEN |
[sb_is_active] = |
'Y' |
|
10 |
|
THEN |
'Not returned' |
|
|
11 |
|
ELSE |
'Returned' |
|
|
12 |
|
END) |
|
|
|
13 ORDER BY [status] DESC |
|
|
|||
|
|
|
|
||
Oracle I Решение 2.1.10.c | |
|
|
|
||
1 |
SELECT (CASE |
|
|
|
|
2 |
|
WHEN |
"sb_is_active" |
= |
'Y' |
3 |
|
THEN |
'Not returned' |
|
|
4 |
|
ELSE |
'Returned' |
|
|
5 |
|
END) |
AS "status", |
|
|
6 |
|
COUNT("sb_id" |
AS "books" |
|
|
|
FROM |
"subscriptions" |
|
|
|
8 |
GROUP |
BY (CASE |
|
|
|
9 |
|
WHEN |
"sb_is_active" = |
'Y' |
|
10 |
|
THEN |
'Not returned' |
|
|
11 |
|
ELSE |
'Returned' |
|
|
12 |
|
END) |
|
|
|
13 |
ORDER BY "status" DESC |
|
|
||
|
|
|
|
|
|
В решении 2.1.10. c есть одна сложность: нужно на основе значений поля sb_is_active Y (книга на руках, т.е. не возвращена) и N (книга возвращена) получить человекочитаемые осмысленные надписи «Not returned» и «Returned».
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 68/545
Пример 10: использование группировки данных
В MySQL все необходимые действия помещаются в одну строку (строка 1), а благодаря способности MySQL ссылаться на имя вычисленного выражения в GROUP
BY, нет необходимости повторять всю эту конструкцию в строке 4.
MS SQL Server и Oracle поддерживают одинаковый, но чуть более громоздкий синтаксис: строки 1-5 запросов для этих СУБД содержат необходимые преобразования, а дублирование этого же кода в строках 8-12 вызвано тем, что эти СУБД не позволяют в GROUP BY сослаться на вычисленное выражение по его имени.
И снова покажем графически, с какими данными приходится работать СУБД:
sb_is_active |
status (результат |
группировка |
подсчёт |
|
(исходное значение) |
преобразования) |
|
|
|
N |
Returned |
|
|
|
N |
Returned |
|
|
|
N |
Returned |
Returned |
6 |
|
N |
Returned |
|||
|
|
|||
N |
Returned |
|
|
|
N |
Returned |
|
|
|
Y |
Not returned |
|
|
|
Y |
Not returned |
|
|
|
Y |
Not returned |
Not returned |
5 |
|
Y |
Not returned |
|
|
|
Y |
Not returned |
|
|
Исследование 2.1.10.EXP.A. Как быстро, в принципе, работает группировка на больших объёмах данных? Используем базу данных «Большая библиотека» и посчитаем, сколько книг находится на руках у каждого читателя.
MySQL і Исследование 2.1.10.EXP.A
1SELECT 'sb_subscriber',
2COUNT('sb_id') AS 'books_taken'
3 |
FROM |
'subscriptions' |
4 |
WHERE |
'sb_is_active' = 'Y' |
5 |
GROUP |
BY 'sb subscriber' |
MS SQL I Исследование 2.1.10.EXP.A |
1SELECT [sb_subscriber],
2COUNT([sb_id]) AS [books_taken]
|
FROM |
[subscriptions] |
|
4 |
WHERE |
[sb_is_active] = |
'Y' |
5 |
GROUP |
BY [sb subscriber] |
|
|
|
|
|
|
Oracle I Исследование 2.1.10.EXP.A |
| |
|
1SELECT "sb_subscriber",
2COUNT("sb_id" AS "books_taken"
3 |
FROM |
"subscriptions" |
4 |
WHERE |
"sb_is_active" = 'Y' |
5 |
GROUP |
BY "sb subscriber" |
Медианы времени после выполнения по сто раз каждого из запросов
2.1.10.EXP.A:
MySQL |
MS SQL Server |
Oracle |
34.333 |
8.189 |
2.306 |
С одной стороны, результаты не выглядят пугающе, но, если объём данных увеличить в 10, 100, 1000 раз и т.д. — время выполнения уже будет измеряться часами или даже днями.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 69/545