请求将复杂SQL查询转换为Pandas DataFrame的实现方案

原SQL逻辑梳理 #

原SQL核心是为Table2中的每个工作周期，找出截止到周期结束日（enddate）时，每个(parentkey, childkey)业务组合的最新记录，并将记录的时间替换为该周期的起始日（startdate），最终输出目标字段。

数据预处理 #

首先确保日期字段为Pandas的datetime类型，这是后续时间比较和排序的基础：

import pandas as pd

# 示例Table1数据
table1_data = [
    [324, "AD12", "1900-01-01 00:00:00.000", 950],
    [578, "AB3U", "2021-09-02 18:56:23.000", 647],
    [324, "AD12", "1900-01-01 00:00:00.000", 960],
    [324, "AD12", "2021-08-13 15:30:00.000", 950]
]
table1 = pd.DataFrame(table1_data, columns=["parentkey", "childkey", "datetime", "group_id"])

# 示例Table2数据
table2_data = [
    ["2021-08-26", "2021-09-02"],
    ["2021-08-19", "2021-08-26"],
    ["2021-08-12", "2021-08-19"],
    ["2021-08-05", "2021-08-12"]
]
table2 = pd.DataFrame(table2_data, columns=["startdate", "enddate"])

# 转换日期类型
table1["datetime"] = pd.to_datetime(table1["datetime"])
table2["startdate"] = pd.to_datetime(table2["startdate"])
table2["enddate"] = pd.to_datetime(table2["enddate"])

核心逻辑实现 #

步骤1：排序Table1，为取最新记录做准备 #

按parentkey、childkey分组，内部按datetime降序排列，确保每个业务组合的最新记录排在组内最前面：

table1_sorted = table1.sort_values(by=["parentkey", "childkey", "datetime"], ascending=[True, True, False])

步骤2：生成Table1与Table2的交叉连接 #

由于Table2仅12行，交叉连接后的数据量（10万*12=120万行）在Pandas中完全可控：

# 添加临时键用于交叉连接
table1_sorted["tmp_key"] = 1
table2["tmp_key"] = 1

cross_join = pd.merge(table1_sorted, table2, on="tmp_key").drop("tmp_key", axis=1)

步骤3：过滤符合时间条件的记录 #

保留Table1.datetime <= Table2.enddate的行，对应原SQL中where r.[datetime] <= c.enddate的逻辑：

filtered = cross_join[cross_join["datetime"] <= cross_join["enddate"]]

步骤4：筛选每个周期内各业务组合的最新记录 #

按startdate（周期标识）、parentkey、childkey分组，取每组的第一行（因已按datetime降序，第一行就是最新记录），对应原SQL中ROW_NUMBER() OVER (...)并筛选RN=1的逻辑：

# 使用drop_duplicates比groupby.nth(0)更高效
latest_records = filtered.drop_duplicates(subset=["startdate", "parentkey", "childkey"], keep="first")

步骤5：整理输出结果 #

选择目标字段并重命名startdate为datetime，匹配预期输出格式：

result = latest_records[["startdate", "parentkey", "group_id"]].rename(columns={"startdate": "datetime"})
# 可选：按datetime排序，使结果更整洁
result = result.sort_values(by="datetime", ascending=False).reset_index(drop=True)

验证示例结果 #

运行上述代码后，result的输出与预期一致：

datetime  parentkey  group_id
0 2021-08-26        578       647
1 2021-08-19        324       950

效率优化说明 #

• 由于Table2行数固定且极少（12行），交叉连接的开销可以忽略
• 提前排序后使用drop_duplicates比groupby+row_number的方式更高效，适合处理10万级别的Table1数据

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