PostgreSQL使用DISTINCT ON时如何合并两个查询替代for循环

实现方案 #

你可以用CTE（公用表表达式）把两个用户的最新ranking_id对应id分别提取后直接关联，一次查询就能拿到所有匹配的(id_one, id_two)元组，避免了两次查询和内存中循环匹配的开销。

单查询SQL写法 #

WITH user_one_data AS (
    SELECT DISTINCT ON (ranking_id) ranking_id, id AS id_one
    FROM {my_table}
    WHERE user_id = %s
    ORDER BY ranking_id, updated_at DESC
),
user_two_data AS (
    SELECT DISTINCT ON (ranking_id) ranking_id, id AS id_two
    FROM {my_table}
    WHERE user_id = %s
    ORDER BY ranking_id, updated_at DESC
)
SELECT id_one, id_two
FROM user_one_data
INNER JOIN user_two_data ON user_one_data.ranking_id = user_two_data.ranking_id;

Python侧调用示例 #

注意不要用字符串拼接传递用户参数，用psycopg2的参数化查询能力避免SQL注入风险：

# 参数按顺序传递，对应SQL里的两个%s占位符
cur.execute(
    f'''
    WITH user_one_data AS (
        SELECT DISTINCT ON (ranking_id) ranking_id, id AS id_one
        FROM {my_table}
        WHERE user_id = %s
        ORDER BY ranking_id, updated_at DESC
    ),
    user_two_data AS (
        SELECT DISTINCT ON (ranking_id) ranking_id, id AS id_two
        FROM {my_table}
        WHERE user_id = %s
        ORDER BY ranking_id, updated_at DESC
    )
    SELECT id_one, id_two
    FROM user_one_data
    INNER JOIN user_two_data ON user_one_data.ranking_id = user_two_data.ranking_id;
    ''',
    (user_id_one, user_id_two)
)
result = cur.fetchall()
# 直接得到的result就是你需要的(id_one, id_two)格式元组列表

效率说明 #

1. 原有DISTINCT ON逻辑完全保留，依然取每个ranking_id下更新时间最新的行，完全符合需求
2. 关联逻辑直接在数据库层完成，比拉取两份全量数据后在Python侧循环匹配性能高很多，数据量越大优势越明显
3. 仅需要一次数据库交互，减少了网络IO开销

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