如何通过SQL查找日志表中每日未记录的用户-日期组合

我有一张每日追加数据的日志表table1，需要找出column3（用户）与column1（日期）中未被记录的组合。

原表数据 #

期望输出 #

解决方案 #

核心思路是先生成所有日期-用户的可能组合，再筛选出原表中不存在的组合。

通用SQL写法 #

-- 生成所有日期和用户的笛卡尔积
WITH all_combinations AS (
    SELECT DISTINCT t1.column1, t2.column3
    FROM table1 t1
    CROSS JOIN table1 t2
),
-- 筛选出原表中已存在的组合
existing_combinations AS (
    SELECT DISTINCT column1, column3
    FROM table1
)
-- 找出不在已存在组合中的记录
SELECT ac.column1, ac.column3
FROM all_combinations ac
LEFT JOIN existing_combinations ec 
    ON ac.column1 = ec.column1 
    AND ac.column3 = ec.column3
WHERE ec.column1 IS NULL
ORDER BY ac.column1, ac.column3;

优化写法（针对日期序列完整场景） #

如果需要确保覆盖原表中所有日期区间（包括原表可能遗漏的日期），可以用数据库自带的日期生成函数生成完整日期序列，再和所有用户做笛卡尔积：

PostgreSQL 示例

WITH all_dates AS (
    SELECT generate_series(
        (SELECT MIN(column1) FROM table1),
        (SELECT MAX(column1) FROM table1),
        INTERVAL '1 day'
    )::DATE AS column1
),
all_users AS (
    SELECT DISTINCT column3 FROM table1
),
all_combinations AS (
    SELECT ad.column1, au.column3
    FROM all_dates ad
    CROSS JOIN all_users au
)
SELECT ac.column1, ac.column3
FROM all_combinations ac
LEFT JOIN table1 t 
    ON ac.column1 = t.column1 
    AND ac.column3 = t.column3
WHERE t.column1 IS NULL
ORDER BY ac.column1, ac.column3;

MySQL 8.0+ 示例

WITH RECURSIVE all_dates AS (
    SELECT MIN(column1) AS column1 FROM table1
    UNION ALL
    SELECT DATE_ADD(column1, INTERVAL 1 DAY)
    FROM all_dates
    WHERE column1 < (SELECT MAX(column1) FROM table1)
),
all_users AS (
    SELECT DISTINCT column3 FROM table1
),
all_combinations AS (
    SELECT ad.column1, au.column3
    FROM all_dates ad
    CROSS JOIN all_users au
)
SELECT ac.column1, ac.column3
FROM all_combinations ac
LEFT JOIN table1 t 
    ON ac.column1 = t.column1 
    AND ac.column3 = t.column3
WHERE t.column1 IS NULL
ORDER BY ac.column1, ac.column3;

内容的提问来源于stack exchange，提问作者Kurasao