如何基于Unique id分区并按timestamp排序连接两张用户数据表？

要实现两张表按Unique id分区、timestamp排序并合并所有时间点记录的需求，核心是用**全外连接（FULL OUTER JOIN）**保留两张表的所有数据，再处理字段值的统一与排序。以下是具体实现方案：

基础合并与排序（支持FULL OUTER JOIN的数据库：PostgreSQL、SQL Server等） #

直接通过全外连接匹配Unique id和timestamp，确保所有时间点都被保留，再用COALESCE处理连接后可能出现的NULL值，最后按要求排序：

SELECT
    COALESCE(t1.Unique_id, t2.Unique_id) AS Unique_id,
    COALESCE(t1.timestamp, t2.timestamp) AS timestamp,
    t1.val1,
    t1.val2,
    t2.val3,
    t2.val4
FROM Table1 t1
FULL OUTER JOIN Table2 t2
    ON t1.Unique_id = t2.Unique_id
    AND t1.timestamp = t2.timestamp
ORDER BY Unique_id, timestamp;

关键说明： #

• FULL OUTER JOIN：保留两张表中所有Unique id+timestamp的组合，不管是否存在匹配项
• COALESCE：当某张表无对应记录时，取另一张表的非NULL值，确保Unique_id和timestamp字段始终有效
• ORDER BY Unique_id, timestamp：实现按用户分区、时间排序的效果

带行号的版本 #

如果需要像单表操作一样添加分区内的行号，将上述查询作为子查询，再添加窗口函数：

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY Unique_id ORDER BY timestamp) AS row_num
FROM (
    SELECT
        COALESCE(t1.Unique_id, t2.Unique_id) AS Unique_id,
        COALESCE(t1.timestamp, t2.timestamp) AS timestamp,
        t1.val1,
        t1.val2,
        t2.val3,
        t2.val4
    FROM Table1 t1
    FULL OUTER JOIN Table2 t2
        ON t1.Unique_id = t2.Unique_id
        AND t1.timestamp = t2.timestamp
) combined_data
ORDER BY Unique_id, timestamp;

MySQL兼容方案（无FULL OUTER JOIN支持） #

MySQL不支持全外连接，可通过LEFT JOIN+反向LEFT JOIN+UNION ALL模拟：

-- 取表1所有记录，匹配表2对应数据
SELECT
    t1.Unique_id,
    t1.timestamp,
    t1.val1,
    t1.val2,
    t2.val3,
    t2.val4
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.Unique_id = t2.Unique_id
    AND t1.timestamp = t2.timestamp

UNION ALL

-- 取表2中未在表1出现的记录
SELECT
    t2.Unique_id,
    t2.timestamp,
    NULL AS val1,
    NULL AS val2,
    t2.val3,
    t2.val4
FROM Table2 t2
LEFT JOIN Table1 t1
    ON t1.Unique_id = t2.Unique_id
    AND t1.timestamp = t2.timestamp
WHERE t1.Unique_id IS NULL

ORDER BY Unique_id, timestamp;

执行以上任意方案，都能得到你期望的合并输出表，且满足按Unique id分区、timestamp排序的要求。

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