将排名和分区查询转换为 SqlAlchemy [英] Convert rank and partition query to SqlAlchemy

问题描述

我想将以下查询转换为 SqlAlchemy，但文档不是很有帮助:

I would like to convert the following query to SqlAlchemy, but the documentation isn't very helpful:

select * from (
select *, 
RANK() OVER (PARTITION BY id ORDER BY date desc) AS RNK 
from table1
) d
where RNK = 1

有什么建议吗?

推荐答案

use over 表达式

use over expression

from sqlalchemy import func

subquery = db.session.query(
    table1,
    func.rank().over(
        order_by=table1.c.date.desc(),
        partition_by=table1.c.id
    ).label('rnk')
).subquery()

query = db.session.query(subquery).filter(
    subquery.c.rnk==1
)

这篇关于将排名和分区查询转换为 SqlAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！