MySQL+SQLAlchemy中DISTINCT与别名排序兼容问题解决

使用MySQL 8和SQLAlchemy 1.4，现有两张表：

CREATE TABLE user (
    id BIGINT auto_increment NOT NULL,
    offset BIGINT NULL,
    CONSTRAINT user_PK PRIMARY KEY (id)
);
INSERT INTO user (offset) values (1);
INSERT INTO user (offset) values (2);
INSERT INTO user (offset) values (3);
INSERT INTO user (offset) values (4);

CREATE TABLE score (
    user_id BIGINT NOT NULL,
    score BIGINT NOT NULL,
    CONSTRAINT score_FK FOREIGN KEY (user_id) REFERENCES user(id)
);

INSERT INTO score (user_id, score) values (1, 10);
INSERT INTO score (user_id, score) values (1, 9);
INSERT INTO score (user_id, score) values (1, 8);
INSERT INTO score (user_id, score) values (2, 7);
INSERT INTO score (user_id, score) values (2, 6);
INSERT INTO score (user_id, score) values (2, 5);
INSERT INTO score (user_id, score) values (3, 10);
INSERT INTO score (user_id, score) values (3, 9);
INSERT INTO score (user_id, score) values (3, 8);
INSERT INTO score (user_id, score) values (4, 7);
INSERT INTO score (user_id, score) values (4, 6);
INSERT INTO score (user_id, score) values (4, 5);

希望生成如下SQL：

select distinct
    id,
    max(score) OVER (PARTITION BY id) AS max_score,
    offset
from
    user
left join score on id = user_id
order by max_score + offset desc;

预期输出：

| id | max_score | offset |
| -- | --------- | ------ |
|  3 |        10 |      3 |
|  1 |        10 |      1 |
|  4 |         7 |      4 |
|  2 |         7 |      2 |

尝试的代码如下：

max_score = func.max(Score.score).over(partition_by=User.id).label("max_score")

q = (
    session.query(
        User.id,
        User.offset,
        max_score,
    )
    .outerjoin(Score)
    .order_by((max_score + User.offset).desc())
).distinct().all()

运行时报错SELECT list incompatible with DISTINCT，原因是SQLAlchemy生成的SQL中，ORDER BY子句使用的是max(score.score) OVER (PARTITION BY user.id) + user.offset而非别名max_score，替换为别名后即可正常运行。

方案一：使用子查询/CTE #

先通过子查询计算出包含max_score的结果集，再在外层进行去重和排序，此时可以直接引用子查询中的别名：

from sqlalchemy import func

max_score = func.max(Score.score).over(partition_by=User.id).label("max_score")

# 定义子查询
subquery = (
    session.query(
        User.id,
        User.offset,
        max_score
    )
    .outerjoin(Score)
).subquery()

# 外层查询去重并排序
result = (
    session.query(subquery.c.id, subquery.c.offset, subquery.c.max_score)
    .distinct()
    .order_by((subquery.c.max_score + subquery.c.offset).desc())
).all()

方案二：直接引用别名（使用literal_column） #

通过literal_column直接指定排序时使用SELECT列表中的别名，绕过SQLAlchemy对表达式的展开：

from sqlalchemy import func, literal_column

max_score = func.max(Score.score).over(partition_by=User.id).label("max_score")

result = (
    session.query(
        User.id,
        User.offset,
        max_score,
    )
    .outerjoin(Score)
    .order_by((literal_column("max_score") + User.offset).desc())
).distinct().all()

方案三：调整查询逻辑（提前聚合） #

既然是按user.id分区求最大分数，也可以先对score表按user_id聚合出最大分数，再关联user表，这样可以避免窗口函数和DISTINCT的冲突：

from sqlalchemy import func

# 先聚合score表得到每个用户的最大分数
score_subq = (
    session.query(
        Score.user_id,
        func.max(Score.score).label("max_score")
    )
    .group_by(Score.user_id)
).subquery()

# 关联user表并排序
result = (
    session.query(
        User.id,
        User.offset,
        score_subq.c.max_score
    )
    .outerjoin(score_subq, User.id == score_subq.c.user_id)
    .order_by((score_subq.c.max_score + User.offset).desc())
).all()

这个方案的查询逻辑更简洁，而且不需要使用DISTINCT，因为聚合后每个用户只有一条记录。

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