Python 如何构造一个 SQLAlchemy 查询，该查询首先在两列之间执行算术运算并将其作为新列返回

我在使用炼金术，我有下面的简化模型，我用它来跟踪我一整天完成的任务.我试图实现的是计算每个记录的任务所花费的总时间，这些任务按任务id和客户端id分组.

S = TypeVar("S", bound="Tracker")

class Tracker(db.Model):

id = db.Column(Integer)
datetime_start = db.Column(DateTime)
datetime_end = db.Column(DateTime)
task_id = db.Column(Integer, ForeignKey)
client_id = db.Column(Integer, ForeignKey)

我拥有的原始SQL查询将给出我想要的结果，如下所示:

SELECT client_id , task_id, sum(difference)
FROM (
    SELECT id, 
        datetime_start, 
        datetime_end, 
        client_id, 
        task_id , 
        datetime_end-datetime_start as difference
    FROM trackers
    WHERE client_id NOTNULL
) AS s
GROUP BY task_id , client_id 
ORDER BY client_id 

Raw Data
"id","datetime_start","datetime_end","client_id","task_id"
1,2022-02-21 12:00:00.00000+0800,2022-02-21 12:30:00.00000+0800,347,3
2,2022-02-21 12:30:00.00000+0800,2022-02-21 12:50:00.00000+0800,271,4
3,2022-02-21 13:00:00.00000+0800,2022-02-21 13:20:00.00000+0800,34,1
4,2022-02-21 13:20:00.00000+0800,2022-02-21 13:30:00.00000+0800,347,1
7,2022-02-21 14:50:00.00000+0800,2022-02-21 15:40:00.00000+0800,271,4
8,2022-02-21 15:45:00.00000+0800,2022-02-21 16:45:00.00000+0800,271,6
9,2022-02-21 18:00:00.00000+0800,2022-02-21 19:30:00.00000+0800,29,3

Post-Calculation Data
"client_id","task_id","sum"
29,3,"01:30:00"
34,1,"00:20:00"
271,4,"01:10:00"
271,6,"01:00:00"
347,3,"00:30:00"
347,1,"00:10:00"

我当前的类方法查询代码如下:(灵感来源于:https://stackoverflow.com/a/33509857和https://stackoverflow.com/a/27423865)，但它不起作用(BaseQuery的错误是不可调用的——这导致我陷入另一个兔子洞)，我怀疑这是因为我使用的是基于模型的查询，但链接中显示的示例是基于声明的.)

@classmethod
def custom_report(cls: Type[S], filters: Union[list, BooleanClauseList]):
        return cls.query((cls.datetime_end - cls.datetime_start).label("difference"))
            .filter(*filters)
            .group_by(cls.task_id, cls.client_id)
            .order_by(cls.client_id)

我知道我的自定义_报告查询是不完整的，因为它仍然不能处理聚合和函数，但在我到达那里之前，我已经陷入了我目前的困境.不过，我确实计划使用subquery()来完成剩下的部分:https://stackoverflow.com/a/38880249

Update 1:根据@snakecharmerb和@CAMILO JOSÉCRUZ RIVERA的回复，以下是有效的修订查询:

@classmethod
def custom_report(cls: Type[S], filters: Union[list, BooleanClauseList]):
    return (
        db.session.query(
            cls.task_id,
            cls.client_id,
            func.sum((cls.datetime_end - cls.datetime_start).label("difference")),
        )
        .filter(*filters)
        .group_by(cls.task_id, cls.client_id)
        .order_by(cls.client_id)
    )