Nuestra base de datos de análisis web MySQL contiene una tabla de resumen que se actualiza a lo largo del día a medida que se importa una nueva actividad. Usamos ON DUPLICATE KEY UPDATE para que el resumen sobrescriba los cálculos anteriores, pero estamos teniendo dificultades porque una de las columnas en la CLAVE ÚNICA de la tabla de resumen es un FK opcional, y contiene valores NULL.

Estos NULL pretenden significar "no está presente, y todos estos casos son equivalentes". Por supuesto, MySQL generalmente trata a los NULL como "desconocido, y todos esos casos no son equivalentes".

La estructura básica es la siguiente:

Una tabla de "Actividad" que contiene una entrada para cada sesión, cada una perteneciente a una campaña, con filtro opcional e ID de transacción para algunas entradas.

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`)
);

Una tabla "Resumen" que contiene resúmenes diarios del número total de sesiones en la tabla de actividades, y el número total de sesiones que contienen un ID de transacción. Estos resúmenes se dividen, con uno para cada combinación de campaña y filtro (opcional). Esta es una tabla no transaccional que utiliza 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;

La consulta de resumen real es algo como lo siguiente, contando el número de sesiones y transacciones, luego agrupando por campaña y filtro (opcional).

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`)
;

Todo funciona bien, excepto por el resumen de casos donde el filter_id es NULL. En estos casos, la cláusula ON DUPLICATE KEY UPDATE no coincide con la fila existente, y se escribe una nueva fila cada vez. Esto se debe a que "NULL! = NULL". Sin embargo, lo que necesitamos es "NULL = NULL" al comparar las claves únicas.

Estoy buscando ideas para soluciones alternativas o comentarios sobre los que hemos llegado hasta ahora. Las soluciones que hemos pensado hasta ahora siguen.

Elimine todas las entradas de resumen que contengan un valor de clave NULL antes de ejecutar el resumen. (Esto es lo que estamos haciendo ahora) Esto tiene el efecto secundario negativo de devolver resultados con datos faltantes si se ejecuta una consulta durante el proceso de resumen.

Cambie la columna DEFAULT NULL a DEFAULT 0, lo que permite que la CLAVE ÚNICA coincida de forma coherente. Esto tiene el efecto secundario negativo de complicar demasiado el desarrollo de consultas en la tabla de resumen. Nos obliga a usar una gran cantidad de "CASE filter_id = 0 THEN NULL ELSE filter_id END", y resulta en una combinación incómoda ya que todas las demás tablas tienen NULL reales para el filter_id.

Cree una vista que devuelva "CASE filter_id = 0 THEN NULL ELSE filter_id END", y use esta vista en lugar de la tabla directamente. La tabla de resumen contiene unos pocos cientos de miles de filas, y me han dicho que el rendimiento de la vista es bastante bajo.

Permita que se creen las entradas duplicadas y elimine las entradas antiguas después de que se complete el resumen. Tiene problemas similares para eliminarlos antes de tiempo.

Agregue una columna sustituta que contenga 0 para NULL, y use esa sustituta en la CLAVE ÚNICA (en realidad podríamos usar la CLAVE PRINCIPAL si todas las columnas NO son NULAS).
Esta solución parece razonable, excepto que el ejemplo anterior es solo un ejemplo; la base de datos real contiene media docena de tablas de resumen, una de las cuales contiene cuatro columnas anulables en la CLAVE ÚNICA. Hay preocupación por algunos de que la sobrecarga es demasiado.

¿Tiene una mejor solución, estructura de tablas, proceso de actualización o las mejores prácticas de MySQL que pueden ayudar?

EDITAR: Para aclarar el "significado de nulo"

Se considera que los datos en las filas de resumen que contienen columnas NULL pertenecen juntos solo en el sentido de que se trata de una única fila "catch-all" en los informes de resumen, que resumen aquellos elementos para los cuales ese punto de datos no existe o es desconocido. Entonces, dentro del contexto de la propia tabla de resumen, el significado es "la suma de aquellas entradas para las cuales no se conoce ningún valor". En las tablas relacionales, por otro lado, estos son resultados NULOS.

La única razón para colocarlos en una clave única en la tabla de resumen es permitir la actualización automática (mediante ACTUALIZACIÓN DE LA CLAVE EN DUPLICADO) al volver a calcular los informes de resumen.

Tal vez una mejor manera de describirlo es mediante el ejemplo específico que una de las tablas de resumen agrupa los resultados geográficamente por el prefijo del código postal de la dirección comercial dada por el encuestado. No todos los encuestados proporcionan una dirección comercial, por lo que la relación entre la transacción y la tabla de direcciones es bastante NULA. En la tabla de resumen de estos datos, se genera una fila para cada prefijo de código postal, que contiene el resumen de los datos dentro de esa área. Se genera una fila adicional para mostrar el resumen de los datos para los que no se conoce ningún prefijo de código postal.

Alterar el resto de las tablas de datos para tener un valor 0 "THERE_IS_NO_ZIP_CODE" explícito, y colocar un registro especial en la tabla ZipCodePrefix que representa este valor, no es correcto: esa relación es NULA.