如何用SQL或SQLAlchemy获取同session_id下的唯一物品配对

问题说明 #

初始表（表名假设为session_items）结构及数据：

需要生成同session_id下的所有唯一物品配对，要求每个配对仅出现一次（避免(a,b)和(b,a)重复），目标表结构及结果如下：

Python SQLAlchemy 实现 #

1. 定义数据模型

from sqlalchemy import Column, Integer, String, create_engine, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class SessionItem(Base):
    __tablename__ = 'session_items'
    id = Column(Integer, primary_key=True, autoincrement=True)
    session_id = Column(Integer, nullable=False)
    item = Column(String, nullable=False)

# 初始化数据库连接（以SQLite为例，可替换为其他数据库URL）
engine = create_engine('sqlite:///session_data.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
db_session = Session()

2. 执行配对查询

通过自连接并过滤item_a < item_b来确保配对唯一：

# 给表起别名避免冲突
si1 = SessionItem.alias()
si2 = SessionItem.alias()

# 查询生成唯一配对
unique_pairs = db_session.query(
    si1.session_id,
    si1.item.label('item_a'),
    si2.item.label('item_b')
).join(
    si2,
    and_(
        si1.session_id == si2.session_id,
        si1.item < si2.item
    )
).all()

# 遍历输出结果
for pair in unique_pairs:
    print(f"session_id: {pair.session_id}, item_a: {pair.item_a}, item_b: {pair.item_b}")

SQL 原生实现 #

直接使用自连接查询，通过WHERE子句过滤重复配对：

SELECT 
    s1.session_id,
    s1.item AS item_a,
    s2.item AS item_b
FROM 
    session_items s1
INNER JOIN 
    session_items s2 
    ON s1.session_id = s2.session_id
WHERE 
    s1.item < s2.item
ORDER BY 
    s1.session_id, s1.item, s2.item;

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