postgresql – 如何使用SQLAlchemy执行SELECT DISTINCT ON查询

SELECT DISTINCT ON (date) date(time) AS date,resource_id,time
FROM spend_estimation
WHERE
  resource_id = '<id>'
  and time > now() - interval '30 days'
ORDER BY date DESC,time DESC;

不幸的是,我似乎无法使用SQLAlchemy做同样的事情.它总是在所有列上创建select distinct.生成的查询不包含distinct.

query = session.query(
    func.date(SpendEstimation.time).label('date'),SpendEstimation.resource_id,SpendEstimation.time
).distinct(
    'date'
).order_by(
    'date',SpendEstimation.time
)

SELECT DISTINCT
    date(time) AS date,time 
FROM spend
ORDER BY date,time

它缺少ON(日期)位.如果我用户query.group_by – 则SQLAlchemy添加了明显的.虽然我想不出解决使用group by的给定问题.

尝试使用不同部分的功能和按部件顺序.

query = session.query(
    func.date(SpendEstimation.time).label('date'),SpendEstimation.time
).distinct(
    func.date(SpendEstimation.time).label('date')
).order_by(
    func.date(SpendEstimation.time).label('date'),SpendEstimation.time
)

这导致了这个SQL：

SELECT DISTINCT
       date(time) AS date,time,date(time) AS date # only difference
FROM spend
ORDER BY date,time

哪个仍然缺少DISTINCT ON.