Database Reference
In-Depth Information
The example just shown uses the reference table with a
LEFT
JOIN
to fill holes in the
summary. It's also possible to use the reference table to
detect
holes in the dataset—that
is, to determine which categories are not present in the data to be summarized. The
following statement shows those dates on which no driver was active by looking for
reference rows for which no
driver_log
table rows have a matching category value:
mysql>
SELECT dates.d
->
FROM dates LEFT JOIN driver_log ON dates.d = driver_log.trav_date
->
WHERE driver_log.trav_date IS NULL;
+------------+
| d |
+------------+
| 2014-07-28 |
| 2014-07-31 |
+------------+
Reference tables that contain a list of categories are quite useful in summary context, as
just shown. But creating such tables manually is mind-numbing and error-prone. A
stored procedure that uses the endpoints of the range of category values to generate the
reference table for you helps automate the process. In essence, this type of procedure
acts as an iterator that generates a row for each value in the range. The following pro‐
cedure,
make_date_list()
, shows an example of this approach. It creates a reference
table containing a row for every date in a particular date range. It also indexes the table
so that it will be fast in large joins:
CREATE
PROCEDURE
make_date_list
(
db_name
TEXT
,
tbl_name
TEXT
,
col_name
TEXT
,
min_date
DATE
,
max_date
DATE
)
BEGIN
DECLARE
i
,
days
INT
;
SET
i
=
0
,
days
=
DATEDIFF
(
max_date
,
min_date
)
+
1
;
#
Make
identifiers
safe
for
insertion
into
SQL
statements
.
Use
db_name
#
and
tbl_name
to
create
qualified
table
name
.
SET
tbl_name
=
CONCAT
(
quote_identifier
(
db_name
),
'.'
,
quote_identifier
(
tbl_name
));
SET
col_name
=
quote_identifier
(
col_name
);
CALL
exec_stmt
(
CONCAT
(
'DROP TABLE IF EXISTS '
,
tbl_name
));
CALL
exec_stmt
(
CONCAT
(
'CREATE TABLE '
,
tbl_name
,
'('
,
col_name
,
' DATE NOT NULL, PRIMARY KEY('
,
col_name
,
'))'
));
WHILE
i
<
days
DO
CALL
exec_stmt
(
CONCAT
(
'INSERT INTO '
,
tbl_name
,
'('
,
col_name
,
') VALUES('
,
QUOTE
(
min_date
),
' + INTERVAL '
,
i
,
' DAY)'
));
SET
i
=
i
+
1
;
END
WHILE
;
END
;
Use
make_date_list()
to generate the reference table,
dates
, like this:
CALL
make_date_list
(
'cookbook'
,
'dates'
,
'd'
,
'2014-07-26'
,
'2014-08-02'
);