Наша база данных веб-аналитики MySQL содержит сводную таблицу, которая обновляется в течение дня по мере импорта новых действий. Мы используем ON DUPLICATE KEY UPDATE, чтобы при суммировании были перезаписаны более ранние вычисления, но возникли трудности из-за того, что один из столбцов в сводной таблице 'UNIQUE KEY - это необязательный FK, который содержит значения NULL.

Эти NULL предназначены для обозначения "нет, а все такие случаи эквивалентны, Конечно, MySQL обычно рассматривает NULL как значениенеизвестно, и все такие случаи не эквивалентны ".

Основная структура выглядит следующим образом:

"Деятельность" таблица, содержащая запись для каждого сеанса, каждый из которых принадлежит кампании, с необязательным фильтром и идентификаторами транзакций для некоторых записей. "

CREATE TABLE `Activity` (
    `session_id` INTEGER AUTO_INCREMENT
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `transaction_id` INTEGER DEFAULT NULL
    , PRIMARY KEY (`session_id`)
);

Резюме" таблица, содержащая ежедневные сведения о количестве сеансов в таблице активности, и общее число сеансов, содержащих идентификатор транзакции. Эти сводки разделены, по одному на каждую комбинацию кампании и (необязательно) фильтра. Это нетранзакционная таблица, использующая MyISAM.

CREATE TABLE `Summary` (
    `day` DATE NOT NULL
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `sessions` INTEGER UNSIGNED DEFAULT NULL
    , `transactions` INTEGER UNSIGNED DEFAULT NULL
    , UNIQUE KEY (`day`, `campaign_id`, `filter_id`)
) ENGINE=MyISAM;

Фактический запрос суммирования выглядит примерно так: подсчитывает количество сеансов и транзакций, затем группирует по кампании и (необязательно) фильтрует.

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`)
;

Все отлично работает, за исключением краткого описания случаев, когда filter_id имеет значение NULL. В этих случаях предложение ON DUPLICATE KEY UPDATE не соответствует существующей строке, и новая строка записывается каждый раз. Это связано с тем, что "NULL! = NULL ", Однако нам нужно "NULL = NULL " при сравнении уникальных ключей.

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

Удалите все сводные записи, содержащие значение ключа NULL, перед выполнением суммирования. (Это то, что мы делаем сейчас). Это имеет отрицательный побочный эффект - возвращает результаты с отсутствующими данными, если запрос выполняется во время процесса суммирования.

Измените столбец DEFAULT NULL на DEFAULT 0, что позволяет последовательно сопоставлять УНИКАЛЬНЫЙ КЛЮЧ. Это имеет отрицательный побочный эффект чрезмерного усложнения разработки запросов к сводной таблице. Это заставляет нас использовать многоCASE filter_id = 0 THEN NULL ELSE filter_id END "и создает неудобное объединение, поскольку все другие таблицы имеют фактические значения NULL для filter_id.

Создать вид, который возвращает "CASE filter_id = 0 THEN NULL ELSE filter_id END "и используя это представление вместо таблицы напрямую. Сводная таблица содержит несколько сотен тысяч строк, и яМне сказали, что производительность представления довольно плохая.

Разрешить создание повторяющихся записей и удалить старые записи после завершения суммирования. Имеет похожие проблемы с их удалением заранее.

Добавьте суррогатный столбец, который содержит 0 для NULL, и используйте этот суррогат в UNIQUE KEY (на самом деле мы могли бы использовать PRIMARY KEY, если все столбцы NOT NULL).

Это решение кажется разумным, за исключением того, что приведенный выше пример является лишь примером; Фактическая база данных содержит полдюжины сводных таблиц, одна из которых содержит четыре обнуляемых столбца в UNIQUE KEY. Некоторые обеспокоены тем, что накладные расходы слишком велики.

У вас есть лучший обходной путь, структура таблиц, процесс обновления или лучшие практики MySQL, которые могут помочь?

РЕДАКТИРОВАТЬ: чтобы уточнитьзначение ноль "

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

Единственная причина для помещения их в уникальный ключ в сводной таблице - это возможность автоматического обновления (с помощью ON DUPLICATE KEY UPDATE) при пересчете сводных отчетов.

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

Изменение остальных таблиц данных для явногоTHERE_IS_NO_ZIP_CODE» 0-значение и размещение специальной записи в таблице ZipCodePrefix, представляющей это значение, неуместно - это отношение действительно NULL.