8.15 基于日期的摘要 Monday, June 23, 2025

mysql> use cookbook
Database changed
mysql> select trav_date,-> count(*) as 'number of drivers', sum(miles) as 'miles logged'-> from driver_log group by trav_date;
+------------+-------------------+--------------+
| trav_date  | number of drivers | miles logged |
+------------+-------------------+--------------+
| 2014-07-30 |                 2 |          355 |
| 2014-07-29 |                 3 |          822 |
| 2014-07-27 |                 1 |           96 |
| 2014-07-26 |                 1 |          115 |
| 2014-08-02 |                 2 |          581 |
| 2014-08-01 |                 1 |          197 |
+------------+-------------------+--------------+
6 rows in set (0.01 sec)mysql> select hour(t ) as hour,-> count(*) as 'number of messages',-> sum(size) as 'number of bytes sent'-> from mail-> group by hour;
+------+--------------------+----------------------+
| hour | number of messages | number of bytes sent |
+------+--------------------+----------------------+
|   10 |                  2 |              1056806 |
|   12 |                  2 |               195798 |
|   15 |                  1 |                 1048 |
|   13 |                  1 |                  271 |
|    9 |                  2 |                 2904 |
|   11 |                  1 |                 5781 |
|   14 |                  1 |                98151 |
|   17 |                  2 |              2398338 |
|    7 |                  1 |                 3824 |
|    8 |                  1 |                  978 |
|   23 |                  1 |                10294 |
|   22 |                  1 |                23992 |
+------+--------------------+----------------------+
12 rows in set (0.01 sec)mysql> select dayofweek(t) as weekday,-> count(*) as 'number of messages',-> sum(size) as 'number of bytes sent'-> from mail-> group by weekday;
+---------+--------------------+----------------------+
| weekday | number of messages | number of bytes sent |
+---------+--------------------+----------------------+
|       5 |                  1 |                58274 |
|       6 |                  3 |               219965 |
|       7 |                  1 |                  271 |
|       1 |                  4 |              2500705 |
|       2 |                  4 |              1007190 |
|       3 |                  2 |                10907 |
|       4 |                  1 |                  873 |
+---------+--------------------+----------------------+
7 rows in set (0.00 sec)mysql> SELECT dayname(t) as weekday,->        count(*) as 'number of messages',->        sum(size) as 'number of bytes sent'-> FROM mail-> GROUP BY dayname(t), dayofweek(t);
+-----------+--------------------+----------------------+
| weekday   | number of messages | number of bytes sent |
+-----------+--------------------+----------------------+
| Thursday  |                  1 |                58274 |
| Friday    |                  3 |               219965 |
| Saturday  |                  1 |                  271 |
| Sunday    |                  4 |              2500705 |
| Monday    |                  4 |              1007190 |
| Tuesday   |                  2 |                10907 |
| Wednesday |                  1 |                  873 |
+-----------+--------------------+----------------------+
7 rows in set (0.00 sec)

8.16 同時使用每一組的摘要和全體的摘要

mysql> select @total := sum(miles) as 'total miles' from driver_log;
+-------------+
| total miles |
+-------------+
|        2166 |
+-------------+
1 row in set, 1 warning (0.00 sec)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.7128 |
| Suzi  |          893 |                41.2281 |
| Henry |          911 |                42.0591 |
+-------+--------------+------------------------+
3 rows in set (0.00 sec)mysql> select name,-> sum(miles) as 'miles/driver',-> (sum(miles)* 100)/(select sum(miles) from driver_log)-> as 'percent of total miles'-> from driver_log group by name;
+-------+--------------+------------------------+
| name  | miles/driver | percent of total miles |
+-------+--------------+------------------------+
| Ben   |          362 |                16.7128 |
| Suzi  |          893 |                41.2281 |
| Henry |          911 |                42.0591 |
+-------+--------------+------------------------+
3 rows in set (0.00 sec)mysql> select name, avg(miles) as driver_avg from driver_log-> group by name-> having driver_avg < (select avg(miles) from driver_log);
+-------+------------+
| name  | driver_avg |
+-------+------------+
| Ben   |   120.6667 |
| Henry |   182.2000 |
+-------+------------+
2 rows in set (0.00 sec)mysql> select name, sum(miles) as 'miles/driver'-> from driver_log group by name with rollup;
+-------+--------------+
| name  | miles/driver |
+-------+--------------+
| Ben   |          362 |
| Henry |          911 |
| Suzi  |          893 |
| NULL  |         2166 |
+-------+--------------+
4 rows in set (0.00 sec)mysql> select name, avg(miles) as driver_avg from driver_log-> group by name with rollup;
+-------+------------+
| name  | driver_avg |
+-------+------------+
| Ben   |   120.6667 |
| Henry |   182.2000 |
| Suzi  |   446.5000 |
| NULL  |   216.6000 |
+-------+------------+
4 rows in set (0.00 sec)mysql> select srcuser, dstuser, count(*)-> from mail group by srcuser, dstuser;
+---------+---------+----------+
| srcuser | dstuser | count(*) |
+---------+---------+----------+
| barb    | tricia  |        2 |
| tricia  | gene    |        1 |
| phil    | phil    |        2 |
| gene    | barb    |        2 |
| phil    | tricia  |        2 |
| barb    | barb    |        1 |
| tricia  | phil    |        1 |
| gene    | gene    |        3 |
| gene    | tricia  |        1 |
| phil    | barb    |        1 |
+---------+---------+----------+
10 rows in set (0.00 sec)mysql> select srcuser, dstuser, count(*)-> from mail group by srcuser, dstuser with rollup;
+---------+---------+----------+
| srcuser | dstuser | count(*) |
+---------+---------+----------+
| barb    | barb    |        1 |
| barb    | tricia  |        2 |
| barb    | NULL    |        3 |
| gene    | barb    |        2 |
| gene    | gene    |        3 |
| gene    | tricia  |        1 |
| gene    | NULL    |        6 |
| phil    | barb    |        1 |
| phil    | phil    |        2 |
| phil    | tricia  |        2 |
| phil    | NULL    |        5 |
| tricia  | gene    |        1 |
| tricia  | phil    |        1 |
| tricia  | NULL    |        2 |
| NULL    | NULL    |       16 |
+---------+---------+----------+
15 rows in set (0.00 sec)

8.17 生成包括摘要和列表的報告

import os
import configparser
import mysql.connector
from mysql.connector import Error
import loggingdef query_mail_data():# Method: Read from config file (recommended)config_path = 'D:/sql/Mysql_learning/config.ini'# Initialize logginglogging.basicConfig(level=logging.INFO)# Read configurationconfig = configparser.ConfigParser()if os.path.exists(config_path):config.read(config_path)try:db_config = {'host': config.get('database', 'host', fallback='localhost'),'user': config.get('database', 'user'),'password': config.get('database', 'password'),'database': config.get('database', 'database', fallback='cookbook')}except configparser.NoSectionError:logging.error("配置文件缺少 [database] 部分")raiseexcept configparser.NoOptionError as e:logging.error(f"配置選項缺失: {e}")raiseelse:logging.error(f"配置文件 {config_path} 不存在")raise FileNotFoundError(f"配置文件 {config_path} 不存在")connection = Nonecursor = Nonetry:# Establish database connectionconnection = mysql.connector.connect(**db_config)if connection.is_connected():cursor = connection.cursor(dictionary=True)# First query: get summary data per drivername_map = {}cursor.execute("""SELECT name, COUNT(name) as days, SUM(miles) as total_milesFROM driver_log GROUP BY name""")for row in cursor:name_map[row['name']] = (row['days'], row['total_miles'])# Second query: get detailed trips per drivercursor.execute("""SELECT name, trav_date, milesFROM driver_log ORDER BY name, trav_date""")current_name = ""for row in cursor:if current_name != row['name']:print(f"Name: {row['name']}; days on road: {name_map[row['name']][0]}; miles driven: {name_map[row['name']][1]}")current_name = row['name']print(f"Date: {row['trav_date']}, trip length: {row['miles']}")except Error as e:logging.error(f"數據庫錯誤: {e}")raisefinally:# Clean up resourcesif cursor:cursor.close()if connection and connection.is_connected():connection.close()# Call the function
query_mail_data()

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