如何在ClickHouse中关联两张日志表并按时间排序得到目标结果？

要实现将两张日志表的所有事件整合、按updated_at排序，同时为每个事件填充对应关联状态的需求，你可以采用双向ASOF JOIN + UNION ALL的方案，或者UNION ALL + 窗口函数填充的方案，以下是具体实现：

方案一：双向ASOF JOIN（推荐，性能更优） #

ASOF JOIN适合按时间序列匹配最近的关联记录，这里我们需要分别为两张表的记录匹配对方的最新状态，再合并结果：

-- 为main表的每条记录匹配最近的exporter记录（时间不晚于当前main记录）
SELECT 
    m.aws_id,
    m.zip,
    m.is_mail_approved,
    m.created_at,
    COALESCE(e.aws_exporter_id, 0) AS aws_exporter_id,
    m.updated_at
FROM main_table m
LEFT ASOF JOIN exporter_table e 
ON m.aws_id = e.aws_id AND e.updated_at <= m.updated_at

-- 为exporter表的每条记录匹配最近的main记录（时间不晚于当前exporter记录）
UNION ALL

SELECT 
    e.aws_id,
    m.zip,
    m.is_mail_approved,
    m.created_at,
    e.aws_exporter_id,
    e.updated_at
FROM exporter_table e
LEFT ASOF JOIN main_table m 
ON e.aws_id = m.aws_id AND m.updated_at <= e.updated_at

-- 按时间排序得到最终结果
ORDER BY updated_at;

逻辑说明： #

1. 第一部分：main_table的每条记录，通过LEFT ASOF JOIN匹配exporter_table中同aws_id且updated_at不晚于当前main记录的最新记录，没有匹配到则用COALESCE填0。
2. 第二部分：exporter_table的每条记录，同理匹配main_table中最新的前置状态记录。
3. 合并两部分结果后按updated_at排序，完全符合你期望的输出结构。

方案二：UNION ALL + 窗口函数填充 #

如果更习惯用窗口函数处理状态延续，也可以先合并所有事件，再用窗口函数填充缺失字段：

WITH 
-- 格式化main表数据，aws_exporter_id设为0
main_events AS (
    SELECT 
        aws_id,
        zip,
        is_mail_approved,
        created_at,
        0 AS aws_exporter_id,
        updated_at,
        'main' AS source
    FROM main_table
),
-- 格式化exporter表数据，其他状态字段暂为NULL
exporter_events AS (
    SELECT 
        aws_id,
        NULL AS zip,
        NULL AS is_mail_approved,
        NULL AS created_at,
        aws_exporter_id,
        updated_at,
        'exporter' AS source
    FROM exporter_table
),
-- 合并所有事件
all_events AS (
    SELECT * FROM main_events
    UNION ALL
    SELECT * FROM exporter_events
)
-- 按时间排序并填充缺失状态
SELECT 
    aws_id,
    -- 填充最近的非NULL zip值
    last_value(zip IGNORE NULLS) OVER (
        PARTITION BY aws_id 
        ORDER BY updated_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS zip,
    -- 填充最近的非NULL is_mail_approved值
    last_value(is_mail_approved IGNORE NULLS) OVER (
        PARTITION BY aws_id 
        ORDER BY updated_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS is_mail_approved,
    -- 填充最近的非NULL created_at值
    last_value(created_at IGNORE NULLS) OVER (
        PARTITION BY aws_id 
        ORDER BY updated_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS created_at,
    -- 为main事件填充最近的exporter_id，无则为0
    CASE 
        WHEN source = 'main' THEN COALESCE(
            last_value(aws_exporter_id IGNORE NULLS) OVER (
                PARTITION BY aws_id 
                ORDER BY updated_at 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0
        )
        ELSE aws_exporter_id
    END AS aws_exporter_id,
    updated_at
FROM all_events
ORDER BY updated_at;

逻辑说明： #

1. 先将两张表统一结构后合并，标记每条记录的来源。
2. 用last_value(IGNORE NULLS)窗口函数，按时间顺序为每条记录填充之前最近的非空状态值。
3. 对main来源的记录，单独处理aws_exporter_id：取当前记录之前的最新exporter ID，无则填0。

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