基于用户与时间匹配同一用户租赁记录的最近搜索记录

需求回顾 #

我们需要把rentals（租赁记录）和同一用户的最近前置搜索记录（即该用户在租赁时间之前最后一次的搜索）进行匹配，非租赁记录的Search Id留空。先看示例：

输入数据 #

期望输出 #

SQL实现方案 #

如果你的数据存在数据库里，这两个方案都能直接用：

方案1：关联子查询（通用型，支持大多数SQL数据库） #

这个方案逻辑直观，先处理租赁记录匹配最近搜索，再合并搜索记录：

-- 先处理租赁记录，匹配最近的前置搜索
SELECT 
    t.Id,
    t.Type,
    t.UserId,
    t.Time,
    -- 子查询找到同一用户、时间更早的最后一条搜索记录
    (SELECT s.Id
     FROM searchs s
     WHERE s.UserId = t.UserId
       AND s.Time < t.Time
     ORDER BY s.Time DESC
     LIMIT 1) AS `Search Id`
FROM rentals t

-- 合并所有搜索记录，Search Id留空
UNION ALL
SELECT 
    Id,
    Type,
    UserId,
    Time,
    NULL AS `Search Id`
FROM searchs

-- 按原Id排序，保持数据顺序
ORDER BY Id;

方案2：窗口函数（适合支持窗口函数的数据库：MySQL8+、PostgreSQL、SQL Server等） #

如果数据库支持窗口函数，用这个方案效率更高，尤其数据量大的时候：

WITH combined_data AS (
    -- 合并租赁和搜索数据，标记租赁记录
    SELECT 
        Id,
        Type,
        UserId,
        Time,
        NULL AS `Search Id`,
        1 AS is_rental
    FROM rentals
    UNION ALL
    SELECT 
        Id,
        Type,
        UserId,
        Time,
        NULL AS `Search Id`,
        0 AS is_rental
    FROM searchs
),
search_lookup AS (
    SELECT 
        *,
        -- 用LAST_VALUE找到当前行之前用户的最后一条搜索Id
        LAST_VALUE(CASE WHEN Type = 'Search' THEN Id END) OVER (
            PARTITION BY UserId 
            ORDER BY Time ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS latest_search_before
    FROM combined_data
)
-- 只给租赁记录填充Search Id，其他留空
SELECT 
    Id,
    Type,
    UserId,
    Time,
    CASE WHEN is_rental = 1 THEN latest_search_before ELSE NULL END AS `Search Id`
FROM search_lookup
ORDER BY Id;

Python Pandas实现方案 #

如果数据是在Python的DataFrame里处理，用这个逻辑：

import pandas as pd

# 加载示例数据（实际中可以用pd.read_csv等读取）
df = pd.DataFrame([
    [1, 'Rental', 1, '15:35'],
    [2, 'Search', 2, '15:34'],
    [3, 'Search', 1, '15:33'],
    [4, 'Search', 1, '15:32']
], columns=['Id', 'Type', 'UserId', 'Time'])

# 先把Time转为时间类型，方便比较大小
df['Time'] = pd.to_datetime(df['Time'], format='%H:%M')

# 定义函数：对每个用户组，匹配租赁记录的最近前置搜索
def match_latest_search(group):
    # 按时间升序排列组内数据
    group = group.sort_values('Time')
    # 提取组内所有搜索记录的Id和时间
    search_records = group[group['Type'] == 'Search'][['Id', 'Time']]
    # 遍历组内的租赁记录，找到最近的前置搜索
    for idx, rental_row in group[group['Type'] == 'Rental'].iterrows():
        # 筛选出时间早于当前租赁的搜索记录
        valid_searches = search_records[search_records['Time'] < rental_row['Time']]
        if not valid_searches.empty:
            # 取最后一条（时间最晚的）的Id
            group.loc[idx, 'Search Id'] = valid_searches.iloc[-1]['Id']
    return group

# 按用户分组处理，然后整理结果
result = df.groupby('UserId').apply(match_latest_search).reset_index(drop=True)
# 把Time转回原格式
result['Time'] = result['Time'].dt.strftime('%H:%M')
# 按原Id排序
result = result.sort_values('Id').reset_index(drop=True)

print(result)

运行这段代码后，就能得到和示例一致的输出结果。

内容的提问来源于stack exchange，提问作者Ece Özçınar