MariaDB中基于用户-组织维度补全每日历史数据的SQL查询优化问题

我正在使用MariaDB开发网站的统计功能，需要按日聚合获取用户的历史数据。为此创建了仅记录变更的历史表organisation_user_link_status_history，以及存储当前数据的主表organisation_user_link。

需要查询为每个user_id和organisation_id组合，从最近的非空行获取status_id等值。现有当前数据和历史数据如下：

当前数据（organisation_user_link表） #

历史数据（organisation_user_link_status_history表） #

期望得到从今日到指定日期的每日数据，无数据日期沿用前一行的非空值，结果按日期降序排列，当前数据排在首位，示例结果如下（部分省略）：

目前使用的查询语句：

WITH RECURSIVE dates (
    DATE
) AS (
    -- SELECT MIN(DATE(created))
    -- FROM organisation
    SELECT DATE('2024-03-01')
    UNION ALL
    SELECT DATE(date) + INTERVAL 1 DAY
    FROM dates
    WHERE DATE(DATE) < (NOW() - INTERVAL 1 DAY)
),
current_history_data_query AS (
    SELECT 
        current_history_data.*
    FROM (
        SELECT
           DATE(timestamp) AS date,
           user_id,
           organisation_id,
           status_id,
           stopped_reason_id,
           dossier_created,
           'history-data' AS src
         FROM (
           SELECT
               oulsh.user_id,
               oulsh.organisation_id,
               oulsh.timestamp,
               oulsh.status_id,
               oulsh.stopped_reason_id,
               oul.dossier_created,
               ROW_NUMBER() OVER (PARTITION BY oulsh.user_id, oulsh.organisation_id, DATE(oulsh.timestamp) ORDER BY oulsh.timestamp DESC) AS row_num
           FROM organisation_user_link_status_history AS oulsh
           INNER JOIN organisation_user_link AS oul ON oulsh.user_id = oul.user_id AND oulsh.organisation_id = oul.organisation_id
         ) AS numbered_rows
         WHERE row_num = 1 AND DATE(timestamp) != DATE(NOW())
         
         UNION ALL
         
         SELECT DATE(NOW()) AS date, oul.user_id, oul.organisation_id, oul.status_id, oul.stopped_reason_id, oul.dossier_created, 'current-data' AS src
         FROM organisation_user_link AS oul
    ) AS current_history_data
    ORDER BY DATE DESC
)
SELECT
    dates.date AS dates_date,
    COALESCE(user_id, LAG(user_id) OVER (ORDER BY dates_date DESC)) AS user_id,
    COALESCE(organisation_id, LAG(organisation_id) OVER (ORDER BY dates_date DESC)) AS organisation_id,
    COALESCE(status_id, LAG(status_id) OVER (ORDER BY dates_date DESC)) AS status_id,
    COALESCE(stopped_reason_id, LAG(stopped_reason_id) OVER (ORDER BY dates_date DESC)) AS stopped_reason_id,
    COALESCE(dossier_created, LAG(dossier_created) OVER (ORDER BY dates_date DESC)) AS dossier_created
FROM dates
LEFT JOIN current_history_data_query AS chdq ON dates.date = chdq.date
GROUP BY DATE(dates.date)
ORDER BY dates.date DESC;

但存在两个问题：

• 非空行后的第一行能正确填充，但后续行仍为NULL，未延续填充；
• 无法按user_id和organisation_id分区，添加PARTITION BY后LAG()函数完全失效。

请问该如何修改查询以解决这些问题？

问题核心是原查询未为每个user_id+organisation_id组合生成完整日期序列，且LAG()仅能获取前一行值，无法递归填充NULL。以下是修正后的实现：

核心思路 #

1. 生成完整日期-组合序列：为每个用户-组织组合生成目标日期范围内的所有日期，确保每个组合每天都有一行记录；
2. 合并当前与历史数据：保留每日每个组合的最新状态（历史取当日最后一条变更，当前数据视为当日状态）；
3. 递归填充NULL值：使用窗口函数或变量，为每个组合的空白日期填充最近的非空状态。

适用于MariaDB 10.2+的SQL语句 #

WITH RECURSIVE dates AS (
    SELECT DATE('2024-03-01') AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM dates
    WHERE date < DATE(NOW())
),
-- 获取所有用户-组织组合及固定字段
user_org_pairs AS (
    SELECT DISTINCT user_id, organisation_id, dossier_created
    FROM organisation_user_link
),
-- 生成每个组合的完整日期序列
full_date_pairs AS (
    SELECT 
        d.date,
        u.user_id,
        u.organisation_id,
        u.dossier_created
    FROM dates d
    CROSS JOIN user_org_pairs u
),
-- 合并历史数据与当前数据，保留每日最新状态
history_current_combined AS (
    -- 历史数据：每日每个组合的最后一条变更
    SELECT
        DATE(timestamp) AS date,
        user_id,
        organisation_id,
        status_id,
        stopped_reason_id
    FROM (
        SELECT
            oulsh.*,
            ROW_NUMBER() OVER (
                PARTITION BY user_id, organisation_id, DATE(timestamp) 
                ORDER BY timestamp DESC
            ) AS row_num
        FROM organisation_user_link_status_history oulsh
    ) t
    WHERE row_num = 1
    
    UNION ALL
    
    -- 当前数据：视为当日的最新状态
    SELECT
        DATE(NOW()) AS date,
        user_id,
        organisation_id,
        status_id,
        stopped_reason_id
    FROM organisation_user_link
),
-- 关联完整日期序列与状态数据，保留空白行
status_with_gaps AS (
    SELECT
        f.date,
        f.user_id,
        f.organisation_id,
        h.status_id,
        h.stopped_reason_id,
        f.dossier_created
    FROM full_date_pairs f
    LEFT JOIN history_current_combined h
        ON f.date = h.date
        AND f.user_id = h.user_id
        AND f.organisation_id = h.organisation_id
)
-- 使用LAST_VALUE填充NULL，按组合分区、日期倒序
SELECT
    date,
    user_id,
    organisation_id,
    LAST_VALUE(status_id IGNORE NULLS) OVER (
        PARTITION BY user_id, organisation_id 
        ORDER BY date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS status_id,
    LAST_VALUE(stopped_reason_id IGNORE NULLS) OVER (
        PARTITION BY user_id, organisation_id 
        ORDER BY date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS stopped_reason_id,
    dossier_created
FROM status_with_gaps
ORDER BY date DESC, user_id, organisation_id;

关键说明 #

• 笛卡尔积生成完整序列：full_date_pairs确保每个用户-组织组合在目标日期范围内无遗漏；
• LAST_VALUE()填充逻辑：PARTITION BY user_id, organisation_id限定仅在当前组合内填充，IGNORE NULLS跳过空白值取最近非空状态，ORDER BY date DESC保证从最新数据向前填充；
• 历史数据去重：通过ROW_NUMBER()取每日每个组合的最后一条变更，避免同一日期多条数据干扰。

兼容MariaDB 10.2以下版本的SQL语句 #

如果不支持IGNORE NULLS，可以用变量实现填充：

WITH RECURSIVE dates AS (
    SELECT DATE('2024-03-01') AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM dates
    WHERE date < DATE(NOW())
),
user_org_pairs AS (
    SELECT DISTINCT user_id, organisation_id, dossier_created
    FROM organisation_user_link
),
full_date_pairs AS (
    SELECT 
        d.date,
        u.user_id,
        u.organisation_id,
        u.dossier_created
    FROM dates d
    CROSS JOIN user_org_pairs u
),
history_current_combined AS (
    SELECT
        DATE(timestamp) AS date,
        user_id,
        organisation_id,
        status_id,
        stopped_reason_id
    FROM (
        SELECT
            oulsh.*,
            ROW_NUMBER() OVER (
                PARTITION BY user_id, organisation_id, DATE(timestamp) 
                ORDER BY timestamp DESC
            ) AS row_num
        FROM organisation_user_link_status_history oulsh
    ) t
    WHERE row_num = 1
    
    UNION ALL
    
    SELECT
        DATE(NOW()) AS date,
        user_id,
        organisation_id,
        status_id,
        stopped_reason_id
    FROM organisation_user_link
),
status_with_gaps AS (
    SELECT
        f.date,
        f.user_id,
        f.organisation_id,
        h.status_id,
        h.stopped_reason_id,
        f.dossier_created
    FROM full_date_pairs f
    LEFT JOIN history_current_combined h
        ON f.date = h.date
        AND f.user_id = h.user_id
        AND f.organisation_id = h.organisation_id
)
-- 使用变量递归填充NULL值
SELECT
    date,
    user_id,
    organisation_id,
    @prev_status := IF(status_id IS NOT NULL, status_id, @prev_status) AS status_id,
    @prev_reason := IF(stopped_reason_id IS NOT NULL, stopped_reason_id, @prev_reason) AS stopped_reason_id,
    dossier_created
FROM status_with_gaps,
     (SELECT @prev_status := NULL, @prev_reason := NULL) vars
ORDER BY user_id, organisation_id, date DESC;

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