基于hitsTime关联type_：查询cart_id对应的来源点击类型

现有两张业务表：

• 表a：存储用户点击行为数据，字段包含hitsTime（点击时间）、type_（点击类型）、session_id（会话ID）
• 表b：存储购物车添加行为数据，字段包含hitsTime（添加时间）、session_id（会话ID）、cart_id（购物车项ID）

业务规则为：用户点击type_类型内容后，可触发添加cart_id的操作。需查询每个cart_id对应的来源type_，匹配要求：

1. 必须属于同一个session_id
2. 点击行为的hitsTime早于购物车添加行为的hitsTime
3. 排除所有点击时间晚于添加时间的type_（例如某cart_id 304438应对应lego_banner，而非时间更晚的icon-Terdekat）

核心需求 #

每个cart_id需关联到同会话内、添加时间之前的最后一次点击类型（避免一对多的冗余匹配，取最接近添加操作的前置点击）

解决方案 #

方法1：窗口函数实现（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数对每个cart_id的前置点击按时间倒序排序，取排名第一的记录即为目标来源类型。

WITH click_cart_link AS (
    SELECT
        b.cart_id,
        b.session_id,
        b.hitsTime AS cart_add_time,
        a.type_,
        a.hitsTime AS click_time,
        -- 按购物车项分组，对前置点击按时间从晚到早排序
        ROW_NUMBER() OVER (
            PARTITION BY b.cart_id 
            ORDER BY a.hitsTime DESC
        ) AS rank_num
    FROM
        b
    LEFT JOIN
        a ON b.session_id = a.session_id 
        AND a.hitsTime < b.hitsTime
)
SELECT
    cart_id,
    session_id,
    type_ AS source_type,
    cart_add_time,
    click_time
FROM
    click_cart_link
WHERE
    rank_num = 1; -- 取最近的一次前置点击

方法2：子查询实现（兼容低版本数据库，如MySQL 5.x） #

通过子查询找到每个cart_id对应的最大前置点击时间，再关联回表a获取对应type_。

SELECT
    b.cart_id,
    b.session_id,
    a.type_ AS source_type,
    b.hitsTime AS cart_add_time,
    a.hitsTime AS click_time
FROM
    b
LEFT JOIN a 
    ON b.session_id = a.session_id 
    AND a.hitsTime = (
        SELECT MAX(hitsTime)
        FROM a
        WHERE session_id = b.session_id 
          AND hitsTime < b.hitsTime
    );

补充说明 #

• 若某cart_id无对应前置点击，source_type会返回NULL，可通过COALESCE(type_, 'unknown')设置默认值
• 若业务需要保留所有前置点击记录，可去掉窗口函数的rank_num=1条件或子查询的MAX()聚合，返回多对多关联结果

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