Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 9: вычисление среднего значения агрегированных данных

Есть ещё одна проблема с разницами дат, о которой стоит помнить: в повседневной жизни мы привыкли округлять эти значения, в то время как СУБД этой операции не выполняет. Проверьте себя: сколько лет прошло между 2011-01-01 и 2012-01-01? Один год, верно? А между 2011-01-01 и

2012-12-31?

Исследование 2.1.9.EXP.A. Проверка поведения СУБД при вычислении расстояния между двумя датами в годах.

MySQL Исследование 2.1.9.EXP.A

1SELECT YEAR('2012-01-01') - YEAR('2011-01-01') - (

2DATE_FORMAT('2012-01-01', '%m%d') <

3DATE_FORMAT('2011-01-01', '%m%d'))

1SELECT YEAR('2012-12-31') - YEAR('2011-01-01') - (

2DATE_FORMAT('2012-12-31', '%m%d') <

3DATE_FORMAT('2011-01-01', '%m%d'))

MS SQL Исследование 2.1.9.EXP.A

1 SELECT DATEDIFF(year, '2011-01-01', '2012-01-01')

1 SELECT DATEDIFF(year, '2011-01-01', '2012-12-31')

Oracle Исследование 2.1.9.EXP.A

1SELECT FLOOR(MONTHS_BETWEEN(DATE '2012-01-01', DATE '2011-01-01') / 12)

2FROM dual;

1SELECT FLOOR(MONTHS_BETWEEN(DATE '2012-12-31', DATE '2011-01-01') / 12)

2FROM dual;

Все шесть запросов 2.1.6.EXP.A вернут один и тот же результат: 1, т.е. между указанными датами с точки зрения СУБД прошёл один год. И это правда: прошёл «один полный год».

Также обратите внимание, насколько по-разному решается задача вычисления разницы между датами в годах в различных СУБД. Особенно интересны строки 2-3 в запросах для MySQL: они позволяют получить корректный результат, когда значения года различны, но на самом деле год ещё не прошёл (например, 2011-05-

01 и 2012-04-01).

Задание 2.1.9.TSK.A: показать, сколько в среднем экземпляров книг есть в библиотеке.

Задание 2.1.9.TSK.B: показать в днях, сколько в среднем времени читатели уже зарегистрированы в библиотеке (временем регистрации считать диапазон от первой даты получения читателем книги до текущей даты).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 60/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.

Как следует из названия главы, все три задачи будут решаться с помощью

группировки данных, т.е. использования GROUP BY.

Решение 2.1.10.a{61}.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 61/545

Пример 10: использование группировки данных

Обратите внимание на разницу в 4-й строке в запросах 2.1.10.a: MySQL позволяет в GROUP BY сослаться на имя только что вычисленного выражения, в то время как MS SQL Server и Oracle не позволяют этого сделать.

Ранее мы уже рассматривали логику работы группировок{20}, но подчеркнём это ещё раз. После извлечения значения года и «объединения ячеек по признаку равенства значений» полученный СУБД результат условно можно представить так:

Решение 2.1.10.b{61}.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 62/545

Пример 10: использование группировки данных

Решение 2.1.10.b{62} очень похоже на решение 2.1.10.a{61}: разница лишь в том, что в первом случае нас интересуют все записи за каждый год, а во втором — количество идентификаторов читателей без повторений за каждый год. Покажем это графически:

Решение 2.1.10.c{61}.

MS SQL Решение 2.1.10.c

1SELECT (CASE

2WHEN [sb_is_active] = 'Y'

3THEN 'Not returned'

4ELSE 'Returned'

Oracle Решение 2.1.10.c

1SELECT (CASE

2WHEN "sb_is_active" = 'Y'

3THEN 'Not returned'

4ELSE 'Returned'

13ORDER BY "status" DESC

Врешении 2.1.10.c есть одна сложность: нужно на основе значений поля sb_is_active Y (книга на руках, т.е. не возвращена) и N (книга возвращена) полу-

чить человекочитаемые осмысленные надписи «Not returned» и «Returned».

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 63/545

Пример 10: использование группировки данных

В MySQL все необходимые действия помещаются в одну строку (строка 1), а благодаря способности MySQL ссылаться на имя вычисленного выражения в

GROUP BY, нет необходимости повторять всю эту конструкцию в строке 4.

MS SQL Server и Oracle поддерживают одинаковый, но чуть более громоздкий синтаксис: строки 1-5 запросов для этих СУБД содержат необходимые преобразования, а дублирование этого же кода в строках 8-12 вызвано тем, что эти СУБД

не позволяют в GROUP BY сослаться на вычисленное выражение по его имени.

И снова покажем графически, с какими данными приходится работать СУБД:

Исследование 2.1.10.EXP.A. Как быстро, в принципе, работает группировка на больших объёмах данных? Используем базу данных «Большая библиотека» и посчитаем, сколько книг находится на руках у каждого читателя.

MySQL Исследование 2.1.10.EXP.A

1SELECT `sb_subscriber`,

2COUNT(`sb_id`) AS `books_taken`

MS SQL Исследование 2.1.10.EXP.A

1SELECT [sb_subscriber],

2COUNT([sb_id]) AS [books_taken]

Oracle Исследование 2.1.10.EXP.A

1SELECT "sb_subscriber",

2COUNT("sb_id") AS "books_taken"

3 FROM "subscriptions"

4WHERE "sb_is_active" = 'Y'

5GROUP BY "sb_subscriber"

Медианы времени после выполнения по сто раз каждого из запросов

2.1.10.EXP.A:

С одной стороны, результаты не выглядят пугающе, но, если объём данных увеличить в 10, 100, 1000 раз и т.д. — время выполнения уже будет измеряться часами или даже днями.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 64/545