Формирование отчета, содержащего итоговую информацию и список

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

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

Обсуждение
Предположим, вы хотите получить отчет, который выглядел бы так:

Name: Ben; days on road: 3; miles driven: 362
date: 2001-11-29, trip length: 131
date: 2001-11-30, trip length: 152
date: 2001-12-02, trip length: 79
Name: Henry; days on road: 5; miles driven: 911
date: 2001-11-26, trip length: 115
date: 2001-11-27, trip length: 96
date: 2001-11-29, trip length: 300
date: 2001-11-30, trip length: 203
date: 2001-12-01, trip length: 197
Name: Suzi; days on road: 2; miles driven: 893
date: 2001-11-29, trip length: 391
date: 2001-12-02, trip length: 502

Для каждого водителя из таблицы driver_log отчет отображает следующую информацию:

• Итоговую строку, в которой приведены имя водителя, количество дней, проведенных в дороге, и количество проделанных миль.

• Список дат и расстояний в милях для отдельных поездок, из которых и получены итоговые значения.

Мы имеем дело с вариацией задачи «различные уровни итоговой информации», которая обсуждалась в предыдущем рецепте.


Сначала это может показаться неочевидным, так как одним из типов выводимой информации является список, а не итог. Но фактически это итог «нулевого уровня». Подобные задачи могут встречаться и в других формах:

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

• Вы хотите подготовить для презентации компании пресс-релиз, в котором приводились бы объемы продаж по регионам со списком объемов продаж в каждом штате региона.

В каждом из случаев вы можете использовать приемы, описанные в предыдущем рецепте:

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

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

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


Сформируем (в Python) отчет, используя один запрос для суммирования дней и миль водителя, а второй – для извлечения записей об отдельных поездках каждого водителя:

# Выбираем общее количество миль для водителя и создаем словарь (dictionary),
# сопоставляющий каждому водителю количество дней в дороге и количество миль.
name_map = { }
cursor = conn.cursor ()
cursor.execute ("""
SELECT name, COUNT(name), SUM(miles)
FROM driver_log GROUP BY name
""")
for (name, days, miles) in cursor.fetchall ():
name_map[name] = (days, miles)
# Выбираем поездки каждого водителя и печатаем отчет,
# отображающий итоговую запись для каждого водителя перед списком его поездок.
cursor.execute ("""
SELECT name, trav_date, miles
FROM driver_log ORDER BY name, trav_date
""")
cur_name = ""
for (name, trav_date, miles) in cursor.fetchall ():
if cur_name != name: # новый водитель; выводим итоги для этого водителя
print "Name: %s; days on road: %d; miles driven: %d" \
% (name, name_map[name][0], name_map[name][1])
cur_name = name
print " date: %s, trip length: %d" % (trav_date, miles)
cursor.close ()

При вычислении итогов в программе можно сократить количество необходимых запросов.


Если вы проходите по списку поездок, получая самостоятельно мили и дни, то достаточно одного запроса:

# Получить список поездок с именами водителей
cursor = conn.cursor ()cursor.execute ("""
SELECT name, trav_date, miles FROM driver_log
ORDER BY name, trav_date
""")
rows = cursor.fetchall ()
cursor.close ()
# Проходим строки один раз, чтобы сформировать словарь, сопоставляющий каждому
# водителю количество дней в дороге и количество миль (записи словаря – это списки,
# а не кортежи, так как нам необходимы записи, которые могли бы изменяться в цикле).
name_map = { }
for (name, trav_date, miles) in rows:
if not name_map.has_key (name): # инициализировать запись, если не существует
name_map[name] = [0, 0]
name_map[name][0] = name_map[name][0] + 1 # посчитать дни
name_map[name][1] = name_map[name][1] + miles # сложить мили
# Проходим строки еще раз, чтобы напечатать отчет,
# отображающий итоговую запись каждого водителя перед списком поездок.
cur_name = ""
for (name, trav_date, miles) in rows:
if cur_name != name: # новый водитель; выводим итоги для этого водителя
print "Name: %s; days on road: %d; miles driven: %d" \
% (name, name_map[name][0], name_map[name][1])
cur_name = name
print " date: %s, trip length: %d" % (trav_date, miles)

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


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

Total miles driven by all drivers combined: 2166
Name: Ben; days on road: 3; miles driven: 362
date: 2001-11-29, trip length: 131
date: 2001-11-30, trip length: 152
date: 2001-12-02, trip length: 79
Name: Henry; days on road: 5; miles driven: 911
date: 2001-11-26, trip length: 115
date: 2001-11-27, trip length: 96
date: 2001-11-29, trip length: 300
date: 2001-11-30, trip length: 203
date: 2001-12-01, trip length: 197
Name: Suzi; days on road: 2; miles driven: 893
date: 2001-11-29, trip length: 391
date: 2001-12-02, trip length: 502

В этом случае необходимо использовать еще один запрос для получения общего итога или еще одно соответствующее вычисление в программе..



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

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

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