Наша база данных веб-аналитики 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. В сводной таблице для этих данных для каждого префикса почтового индекса создается строка, содержащая сводку данных в этой области. Создается дополнительная строка для отображения сводки данных, для которых не известен префикс почтового индекса.

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