7.16 Date-Based Summaries
7.16.1 Problem
You
want to produce a summary based on date or time values.
7.16.2 Solution
Use GROUP BY to categorize
temporal values into bins of the appropriate duration. Often this
will involve using expressions to extract the significant parts of
dates or times.
7.16.3 Discussion
To put records in time order, you use an ORDER
BY clause to sort a column that has a temporal
type. If instead you want to summarize records based on groupings
into time intervals, you need to determine how to categorize each
record into the proper interval and use GROUP
BY to group them accordingly.
Sometimes you can use temporal values directly if they group
naturally into the desired categories. This is quite likely if a
table represents date or time parts using separate columns. For
example, the baseball1.com master ballplayer
table represents birth dates using separate year, month, and day
columns. To see how many ballplayers were born on each day of the
year, perform a calendar date summary that uses the month and day
values but ignores the year:
mysql> SELECT birthmonth, birthday, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
-> GROUP BY birthmonth, birthday;
+------------+----------+----------+
| birthmonth | birthday | COUNT(*) |
+------------+----------+----------+
| 1 | 1 | 47 |
| 1 | 2 | 40 |
| 1 | 3 | 50 |
| 1 | 4 | 38 |
...
| 12 | 28 | 33 |
| 12 | 29 | 32 |
| 12 | 30 | 32 |
| 12 | 31 | 27 |
+------------+----------+----------+
A less fine-grained summary can be obtained by using only the month
values:
mysql> SELECT birthmonth, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL
-> GROUP BY birthmonth;
+------------+----------+
| birthmonth | COUNT(*) |
+------------+----------+
| 1 | 1311 |
| 2 | 1144 |
| 3 | 1243 |
| 4 | 1179 |
| 5 | 1118 |
| 6 | 1105 |
| 7 | 1244 |
| 8 | 1438 |
| 9 | 1314 |
| 10 | 1438 |
| 11 | 1314 |
| 12 | 1269 |
+------------+----------+
Sometimes temporal values can be used directly, even when not
represented as separate columns. To determine how many drivers were
on the road and how many miles were driven each day, group the
records in the driver_log table by date:
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 |
+------------+-------------------+--------------+
| 2001-11-26 | 1 | 115 |
| 2001-11-27 | 1 | 96 |
| 2001-11-29 | 3 | 822 |
| 2001-11-30 | 2 | 355 |
| 2001-12-01 | 1 | 197 |
| 2001-12-02 | 2 | 581 |
+------------+-------------------+--------------+
However, this summary will grow lengthier as you add more records to
the table. At some point, the number of distinct dates likely will
become so large that the summary fails to be useful, and
you'd probably decide to change the category size
from daily to weekly or monthly.
When a temporal column
contains so many distinct values that it fails to categorize well,
it's typical for a summary to group records using
expressions that map the relevant parts of the date or time values
onto a smaller set of categories. For example, to produce a
time-of-day summary for records in the mail table,
do this:
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 |
+------+--------------------+----------------------+
| 7 | 1 | 3824 |
| 8 | 1 | 978 |
| 9 | 2 | 2904 |
| 10 | 2 | 1056806 |
| 11 | 1 | 5781 |
| 12 | 2 | 195798 |
| 13 | 1 | 271 |
| 14 | 1 | 98151 |
| 15 | 1 | 1048 |
| 17 | 2 | 2398338 |
| 22 | 1 | 23992 |
| 23 | 1 | 10294 |
+------+--------------------+----------------------+
To produce a day-of-week summary instead, use the DAYOFWEEK(
)
function:
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 |
+---------+--------------------+----------------------+
| 1 | 1 | 271 |
| 2 | 4 | 2500705 |
| 3 | 4 | 1007190 |
| 4 | 2 | 10907 |
| 5 | 1 | 873 |
| 6 | 1 | 58274 |
| 7 | 3 | 219965 |
+---------+--------------------+----------------------+
To make the output more meaningful, you might want to use
DAYNAME( ) to display weekday names instead. However,
because day names sort lexically (for example,
"Tuesday" sorts after
"Friday"), use DAYNAME(
) only for display purposes. Continue to group on the
numeric day values so that output rows sort that way:
mysql> SELECT DAYNAME(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY DAYOFWEEK(t);
+-----------+--------------------+----------------------+
| weekday | number of messages | number of bytes sent |
+-----------+--------------------+----------------------+
| Sunday | 1 | 271 |
| Monday | 4 | 2500705 |
| Tuesday | 4 | 1007190 |
| Wednesday | 2 | 10907 |
| Thursday | 1 | 873 |
| Friday | 1 | 58274 |
| Saturday | 3 | 219965 |
+-----------+--------------------+----------------------+
A similar technique can be used for summarizing month-of-year
categories that are sorted by numeric value but displayed by month
name.
Uses for temporal categorizations are plentiful:
DATETIME or
TIMESTAMP columns have the potential to contain
many unique values. To produce daily summaries, strip off the time of
day part to collapse all values occurring within a given day to the
same value. Any of the following GROUP
BY clauses will do this, though the last one is
likely to be slowest:
GROUP BY FROM_DAYS(TO_DAYS(col_name))
GROUP BY YEAR(col_name), MONTH(col_name), DAYOFMONTH(col_name)
GROUP BY DATE_FORMAT(col_name,'%Y-%m-%e')
To produce monthly or quarterly sales reports, group by
MONTH(col_name)
or
QUARTER(col_name)
to place dates into the correct part of the year.
To summarize web server activity, put your server's
logs into MySQL and run queries that collapse the records into
different time categories. Chapter 18 discusses how
to do this for Apache.
| 
No comments:
Post a Comment