Заполнение пустых мест в списке с помощью соединения

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

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

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

mysql> SELECT * FROM mail;

+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
...

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

mysql> SELECT HOUR(t) AS hour, COUNT(HOUR(t)) AS count
-> FROM mail GROUP BY hour;

+------+-------+
| hour | count |
+------+-------+
| 7 | 1 |
| 8 | 1 |
| 9 | 2 |
| 10 | 2 |
| 11 | 1 |
| 12 | 2 |
| 13 | 1 |
| 14 | 1 |
| 15 | 1 |
| 17 | 2 |
| 22 | 1 |
| 23 | 1 |
+------+-------+

Однако эти итоги не полные в том смысле, что они содержат только записи для тех часов, которые представлены в таблице mail.


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

mysql> CREATE TABLE ref (h INT);
mysql> INSERT INTO ref (h)
-> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
-> (12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);
Затем объедините справочную таблицу и таблицу mail, используя LEFT JOIN:
mysql> SELECT ref.h AS hour, COUNT(HOUR(mail.t)) AS count
-> FROM ref LEFT JOIN mail ON ref.h = HOUR(mail.t)
-> GROUP BY hour;

+-----+--------+
| hour | count |
+-----+--------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 2 |
| 10 | 2 |
| 11 | 1 |
| 12 | 2 |
| 13 | 1 |
| 14 | 1 |
| 15 | 1 |
| 16 | 0 |
| 17 | 2 |
| 18 | 0 |
| 19 | 0 |
| 20 | 0 |
| 21 | 0 |
| 22 | 1 |
| 23 | 1 |
+-----+-------+

Теперь в итогах присутствует запись для каждого часа дня.


Вывод запроса LEFT JOIN содержит строку для каждой записи справочной таблицы вне зависимости от содержимого таблицы mail.

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

mysql> SELECT ref.h AS hour, COUNT(HOUR(mail.t)) AS count
-> FROM ref LEFT JOIN mail ON ref.h = HOUR(mail.t)
-> GROUP BY hour
-> HAVING count = 0;

+-----+--------+
| hour | count |
+-----+--------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 16 | 0 |
| 18 | 0 |
| 19 | 0 |
| 20 | 0 |
| 21 | 0 |
+-----+--------+

В этом случае можно упростить запрос, используя тот факт, что каждое значение часа присутствует в справочной таблице ровно один раз.


То есть инструкция GROUP BY не нужна; будем просто искать строки справочной таблицы, которым не соответствует никакая строка mail:

mysql> SELECT ref.h AS hour
-> FROM ref LEFT JOIN mail ON ref.h = HOUR(mail.t)-> WHERE mail.t IS NULL;

+------+
| hour |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 16 |
| 18 |
| 19 |
| 20 |
| 21 |
+------+

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

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

Следующий сценарий на Perl, make_date_list.pl, иллюстрирует такой подход, создавая справочную таблицу, которая содержит строку для каждой даты определенного диапазона:

#! /usr/bin/perl -w
# make_date_list.pl – создание таблицы с записью для каждой даты указанного
# диапазона. Таблицу можно использовать в LEFT JOIN с таблицей данных при
# формировании итогов, чтобы убедиться в том, что в итогах присутствуют все даты,
# вне зависимости от наличия в таблице значений данных для указанного дня.
# Используются: make_date_list.pl tbl_name col_name min_date max_date
# Сценарий предполагает, что используется база данных cookbook.
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;
# Проверить количество аргументов; представлены минимальные тесты на ISO-формат дат
@ARGV == 4
or die "Usage: make_date_list.pl tbl_name col_name min_date max_date\n";
my ($tbl_name, $col_name, $min_date, $max_date) = (@ARGV);
$min_date =~ /^\d+\D\d+\D\d+$/
or die "Minimum date $min_date is not in ISO format\n";$max_date =~ /^\d+\D\d+\D\d+$/
or die "Maximum date $max_date is not in ISO format\n";
my $dbh = Cookbook::connect ();
# Определить количество дней, относящихся к диапазону дат.
my $days = $dbh->selectrow_array (qq{ SELECT TO_DAYS(?) - TO_DAYS(?) + 1 },
undef, $max_date, $min_date);
print "Minimum date: $min_date\n";
print "Maximum date: $max_date\n";
print "Number of days spanned by range: $days\n";
die "Date range is too small\n" if $days < 1;
# Удалить таблицу, если она существует, затем – пересоздать.
$dbh->do ("DROP TABLE IF EXISTS $tbl_name");
$dbh->do (qq{
CREATE TABLE $tbl_name
($col_name DATE NOT NULL, PRIMARY KEY ($col_name))
});
# Заполнить таблицу записями обо всех датах диапазона.
my $sth = $dbh->prepare (qq{
INSERT INTO $tbl_name ($col_name) VALUES(DATE_ADD(?,INTERVAL ? DAY))
});
for (my $i = 0; $i < $days; $i++)
{
$sth->execute ($min_date, $i);
}
$dbh->disconnect ();
exit (0);

Таблицы, генерируемые сценарием make_date_list.pl, можно использовать для итогов по дням или для поиска дней, не представленных в таблице.

Справочную таблицу дат можно применять и для итогов по календарным дням. Например, можно использовать ее совместно с таблицей master базы данных baseball1.com чтобы определить количество игроков, родившихся в каждый день года, или найти дни, в которые нет ни одного дня рождения.

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

% make_date_list.pl ref d 2004-01-01 2004-12-31

Таблица master хранит даты рождения в трех столбцах с именами birthday, birthmonth, birthyear. После создания справочной таблицы используйте следующий запрос для суммирования дней рождений из таблицы master для каждого календарного дня:

SELECT
MONTH(ref.d) AS month, DAYOFMONTH(ref.d) AS day,COUNT(master.lahmanid) AS count
FROM ref LEFT JOIN master
ON MONTH(ref.d) = master.birthmonth
AND DAYOFMONTH(ref.d) = master.birthday
GROUP BY month, day;

Чтобы проверить, есть ли дни, в которые ни у кого нет дня рождения, выполните:

SELECT MONTH(ref.d) AS month, DAYOFMONTH(ref.d) AS day
FROM ref LEFT JOIN master
ON MONTH(ref.d) = master.birthmonth
AND DAYOFMONTH(ref.d) = master.birthday
WHERE master.birthmonth IS NULL and master.birthday IS NULL;

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

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей