Одновременная работа с итогами по группам и общим итогом

Задача
Вы хотите вывести отчет, который требует нескольких уровней итоговой информации. Или хотите сравнить итоговые значения по группам с общим итоговым значением.

Решение
Выполните два запроса, извлекающие различные уровни итоговой информации. Или используйте для выполнения некоторых действий язык программирования, чтобы можно было обойтись одним запросом.

Обсуждение
Иногда в отчете необходимо несколько уровней итоговой информации. Например, выведем общее количество миль для каждого водителя из таблицы driver_log, а также отношение этого количества для каждого водителя к общему количеству миль по всем водителям:

+-------+--------------+---------------------------+
| name | miles/driver | percent of total miles |
+-------+--------------+---------------------------+
| Ben | 362 | 16.712834718375 |
| Henry | 911 | 42.059095106187 |
| Suzi | 893 | 41.228070175439 |
+-------+--------------+---------------------------+

Для вычисления процентного вклада каждого водителя в общее количество проделанных миль необходимы итоги для каждого водителя, а также общий итог.


Для формирования отчета в SQL необходимы два запроса, так как невозможно получить оба вида итогов в одном запросе. Сначала выполним запрос, вычисляющий общее количество миль по всей таблице:

mysql> SELECT @total := SUM(miles) AS 'total miles' FROM driver_log;

+-------------+
| total miles |
+-------------+
| 2166 |
+-------------+

Затем вычислим значения итогов по группам и используем общий итог для получения отношения:

mysql> SELECT name,
-> SUM(miles) AS 'miles/driver',
-> (SUM(miles)*100)/@total AS 'percent of total miles'
-> FROM driver_log GROUP BY name;

+-------+---------------+--------------------------+
| name | miles/driver | percent of total miles |
+-------+---------------+--------------------------+
| Ben | 362 | 16.712834718375 |
| Henry | 911 | 42.059095106187 |
| Suzi | 893 | 41.228070175439 |
+-------+---------------+--------------------------+

Можно предложить и другое решение, не использующее переменную, – извлечем общий итог в отдельную таблицу, затем соединим ее с исходной:

mysql> CREATE TEMPORARY TABLE t
-> SELECT SUM(miles) AS total FROM driver_log;
mysql> SELECT driver_log.name,
-> SUM(driver_log.miles) AS 'miles/driver',
-> (SUM(driver_log.miles)*100)/t.total AS 'percent of total miles'
-> FROM driver_log, t GROUP BY driver_log.name;

+-------+--------------+--------------------------+
| name | miles/driver | percent of total miles |
+-------+--------------+--------------------------+
| Ben | 362 | 16.71 |
| Henry | 911 | 42.06 |
| Suzi | 893 | 41.23 |
+-------+--------------+--------------------------+

Если вы формируете отчет в программе, то можете выполнить некоторые арифметические операции при помощи языка программирования, избавившись от второго запроса.




Рассмотрим пример для Python:

# выдаем запрос для вычисления итогов по водителям
cursor = conn.cursor () cursor.execute ("SELECT name, SUM(miles) FROM driver_log GROUP BY name") rows = cursor.fetchall () cursor.close ()

# осуществляем один проход для вычисления общего количества миль
total = 0
for (name, miles) in rows:
total = total + miles
# повторяем снова для вывода отчета
print "name miles/driver percent of total miles"
for (name, miles) in rows:
print "%-8s %5d %f" \
% (name, miles, (100*miles)/total)

Еще одним типом задач, требующих нескольких уровней итоговой информации, являются задачи сравнения итоговых значений по группам с соответствующим общим итогом. Предположим, вы хотите узнать, кто из водителей в среднем за день проехал меньше миль, чем общее среднее значение для группы. Если использовать только SQL, задачу не удастся решить средствами одного запроса, но легко можно решить при помощи двух. Сначала вычислим общее среднее значение и сохраним его в переменной:

mysql> SELECT @overall_avg := AVG(miles) FROM driver_log;

+----------------------------------+
| @overall_avg := AVG(miles) |
+----------------------------------+
| 216.6000 |
+----------------------------------+

Затем будем сравнивать среднее значение для каждого водителя с сохраненным общим средним, используя инструкцию HAVING:

mysql> SELECT name, AVG(miles) AS driver_avg FROM driver_log
-> GROUP BY name
-> HAVING driver_avg < @overall_avg;

+-------+--------------+
| name | driver_avg |
+-------+--------------+
| Ben | 120.6667 |
| Henry | 182.2000 |
+-------+--------------+

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

1.


Выдайте запрос на извлечение итоговой информации по группе.
2. Вычислите в цикле общий итог.
3. Выполните еще один проход по результирующему множеству, сравнивая каждое групповое итоговое значение с общим и выводя только те записи, для которых сравнение верно..



Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Выбор групп только с определенными характеристиками
Группирование по результатам выражения
Использование ключевого слова DISTINCT для удаления дубликатов
Итоги и значения NULL
Итоги по датам

Вернуться в раздел: MySQL / 7. Формирование итогов