SQLite结合pandas实现含同ID多记录的两个表并排拼接

直接按ID关联会产生笛卡尔积，同ID下N条A记录和M条B记录会生成N*M条结果，GROUP BY只会保留每组第一条，自然不符合要求。核心解决思路是给同ID下的每条记录生成唯一的顺序编号，用ID+编号双字段关联，同时用全连接保留两侧多余的记录。

SQLite 实现方案 #

SQLite 3.25及以上版本支持窗口函数，可直接用以下语句实现：

WITH A_rn AS (
    -- 给表A同ID的记录按日期排序生成行号，要保留原表顺序可换成按自增主键排序
    SELECT 
        id,
        date AS dateA,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
    FROM A
),
B_rn AS (
    -- 给表B同ID的记录按日期排序生成行号
    SELECT 
        id,
        date AS dateB,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
    FROM B
)
SELECT 
    COALESCE(A_rn.id, B_rn.id) AS id, -- 保证ID列不会为空
    A_rn.dateA,
    B_rn.dateB
FROM A_rn
FULL OUTER JOIN B_rn 
    ON A_rn.id = B_rn.id 
    AND A_rn.rn = B_rn.rn -- 按ID+行号双字段关联
ORDER BY id, COALESCE(A_rn.rn, B_rn.rn);

如果使用的SQLite版本不支持窗口函数，可改用关联子查询生成行号：

SELECT 
    COALESCE(A_rn.id, B_rn.id) AS id,
    A_rn.dateA,
    B_rn.dateB
FROM (
    SELECT 
        id,
        date AS dateA,
        (SELECT COUNT(*) FROM A AS sub WHERE sub.id = A.id AND sub.date <= A.date) AS rn
    FROM A
) AS A_rn
FULL OUTER JOIN (
    SELECT 
        id,
        date AS dateB,
        (SELECT COUNT(*) FROM B AS sub WHERE sub.id = B.id AND sub.date <= B.date) AS rn
    FROM B
) AS B_rn ON A_rn.id = B_rn.id AND A_rn.rn = B_rn.rn
ORDER BY id, COALESCE(A_rn.rn, B_rn.rn);

Pandas 实现方案 #

你也可以将两张表读取到DataFrame后直接在pandas侧处理，代码更简洁：

import pandas as pd
import sqlite3

conn = sqlite3.connect('你的数据库路径.db')
# 读取两张表
df_a = pd.read_sql("SELECT * FROM A", conn)
df_b = pd.read_sql("SELECT * FROM B", conn)
conn.close()

# 给同ID的记录生成行号，默认保留原表顺序
df_a['rn'] = df_a.groupby('id').cumcount()
df_b['rn'] = df_b.groupby('id').cumcount()

# 全连接合并
df_result = pd.merge(
    df_a.rename(columns={'date': 'dateA'}),
    df_b.rename(columns={'date': 'dateB'}),
    on=['id', 'rn'],
    how='outer'
).sort_values(['id', 'rn']).drop('rn', axis=1).reset_index(drop=True)

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