BigQuery窗口函数问题：将点击数据匹配到对应打开记录

核心需求是让每条点击记录仅关联到时间/序号上最近且早于点击时间的打开记录，避免匹配所有符合条件的打开。以下是几种适用于不同数据库的实现方案：

方案1：窗口函数ROW_NUMBER()（通用型，支持PostgreSQL、MySQL 8.0+、SQL Server等） #

先关联所有符合时间条件的打开记录，再为每个点击的关联结果按打开时间倒序排序，取第一条即为最近的打开：

WITH click_open_matches AS (
    SELECT 
        c.click_id,
        c.user_id,
        c.click_time,
        o.open_id,
        o.open_time,
        o.row_num,
        -- 按点击分组，打开时间倒序排，最近的排第1
        ROW_NUMBER() OVER (PARTITION BY c.click_id ORDER BY o.open_time DESC) AS rn
    FROM clicks c
    LEFT JOIN opens o 
        ON c.user_id = o.user_id 
        AND o.open_time <= c.click_time -- 仅关联点击发生前的打开
)
-- 只保留每个点击对应的最近打开记录
SELECT click_id, user_id, click_time, open_id, open_time, row_num
FROM click_open_matches
WHERE rn = 1;

如果你的row_num是按打开时间递增生成的（比如row_num=3是最晚的打开），可以把ORDER BY o.open_time DESC替换为ORDER BY o.row_num DESC，效果一致。

方案2：LATERAL JOIN/OUTER APPLY（高效型，PostgreSQL、MySQL 8.0+用LATERAL，SQL Server用OUTER APPLY） #

直接为每条点击记录单独查询最近的打开，避免全量关联后过滤，性能更优：

-- PostgreSQL/MySQL 8.0+
SELECT 
    c.click_id,
    c.user_id,
    c.click_time,
    o.open_id,
    o.open_time,
    o.row_num
FROM clicks c
LEFT JOIN LATERAL (
    SELECT open_id, open_time, row_num
    FROM opens o
    WHERE o.user_id = c.user_id 
      AND o.open_time <= c.click_time
    ORDER BY o.open_time DESC
    LIMIT 1 -- 只取最近的一条打开
) o ON true;

SQL Server版本只需把LATERAL换成OUTER APPLY即可。

方案3：子查询+MAX()（兼容老版本数据库） #

先找到每个点击对应的最新打开时间，再关联到具体的打开记录：

SELECT 
    c.click_id,
    c.user_id,
    c.click_time,
    o.open_id,
    o.open_time,
    o.row_num
FROM clicks c
-- 第一步：找到每个点击对应的用户的最新打开时间
LEFT JOIN (
    SELECT 
        user_id,
        click_time,
        MAX(open_time) AS latest_open_time
    FROM clicks c
    JOIN opens o ON c.user_id = o.user_id AND o.open_time <= c.click_time
    GROUP BY user_id, click_time
) latest_opens 
    ON c.user_id = latest_opens.user_id 
    AND c.click_time = latest_opens.click_time
-- 第二步：通过最新打开时间关联到具体的打开记录
LEFT JOIN opens o 
    ON o.user_id = c.user_id 
    AND o.open_time = latest_opens.latest_open_time;

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