如何基于交易日期左关联Transactions与User History表获取交易前最新积分余额？

数据老陈

2026-8-10

解决方案：关联Transactions与User History表获取交易前最新积分余额

核心需求

以Transactions为左表，新增points_start列，取值为对应user_id在transaction_date之前的最新time_updated记录中的point_balance，最终保留Transactions原字段及新增列，支持20万级数据量的自动化执行。

通用SQL实现方案

利用窗口函数ROW_NUMBER()先对User History表按用户分组、更新时间倒序排序，再关联Transactions表筛选出交易前的最新记录：

WITH ranked_user_history AS (
    SELECT
        user_id,
        time_updated,
        point_balance,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_updated DESC) AS rn
    FROM User_History
)
SELECT
    t.*,
    r.point_balance AS points_start
FROM Transactions t
LEFT JOIN ranked_user_history r
    ON t.user_id = r.user_id
    AND r.time_updated < t.transaction_date
    AND r.rn = 1;

大表性能优化方案

针对20万行数据量，为避免全表扫描，先给User History表添加复合索引提升查询效率：

CREATE INDEX idx_user_history_id_time ON User_History(user_id, time_updated DESC);

如果使用支持LATERAL JOIN（如PostgreSQL）或OUTER APPLY（如SQL Server）的数据库，可使用更高效的关联方式，直接为每一条交易记录匹配最新的历史积分：

PostgreSQL 版本

SELECT
    t.*,
    uh.point_balance AS points_start
FROM Transactions t
LEFT JOIN LATERAL (
    SELECT point_balance
    FROM User_History
    WHERE user_id = t.user_id
      AND time_updated < t.transaction_date
    ORDER BY time_updated DESC
    LIMIT 1
) uh ON true;

SQL Server 版本

SELECT
    t.*,
    uh.point_balance AS points_start
FROM Transactions t
OUTER APPLY (
    SELECT TOP 1 point_balance
    FROM User_History
    WHERE user_id = t.user_id
      AND time_updated < t.transaction_date
    ORDER BY time_updated DESC
) uh;