如何使用sqlalchemy计算avg?

cbeh67ev  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(413)

如何在sqlalchemy中计算平均值?我试过很多东西,但都失败了。代码:

month = sqlalchemy.func.date_trunc('month', Complaint.date)
records_count = sqlalchemy.sql.func.count(ClientGroupRecord.id)
complained_clients = session.query(Friend, records_count.label('count'), month). \
    filter(Friend.friend_id == friend_id). \
    join(Complaint, Complaint.friend == Friend.friend_id). \
    join(ClientGroup, Complaint.client_group == ClientGroup.client_group_id). \
    join(ClientGroupRecord, ClientGroup.client_group_id == ClientGroupRecord.client_group_id). \
    join(Client, Client.client_id == ClientGroupRecord.client_id). \
    group_by(month, Friend.friend_id)

records_avg = sqlalchemy.sql.func.avg(records_count)

result = ???

在一个 result 我不需要 Friend ,但是想要 avg 在列上,该列被标记为 count 分组依据 month 列。

e0bqpujr

e0bqpujr1#

一种选择是使用窗口函数,如果您的dbms支持的话——基于 date_trunc 我假设您使用的是postgresql。这样做的原因是,在groupby生成组行之后,窗口函数被求值。所以把伯爵的名字写进去 AVG(...) OVER () ,其中窗口是整个结果集:

from sqlalchemy import func

month = func.date_trunc('month', Complaint.date)
records_count = func.count(ClientGroupRecord.id)

# Create a window of the whole set of results

records_avg = func.avg(records_count).over()

complained_clients = session.query(records_count.label('count'),
                                   records_avg.label('avg'),
                                   month.label('month')). \
    select_from(Friend). \
    join(Complaint, Complaint.friend == Friend.friend_id). \
    join(ClientGroup, Complaint.client_group == ClientGroup.client_group_id). \
    join(ClientGroupRecord, ClientGroup.client_group_id == ClientGroupRecord.client_group_id). \
    join(Client, Client.client_id == ClientGroupRecord.client_id). \
    filter(Friend.friend_id == friend_id). \
    group_by(month). \
    all()

请注意 Friend 是从 query(...) 显式用作第一个连接的左侧 Query.select_from() . 这样就不需要使用 Friend.id 在GROUPBY子句中,即使您仅选择具有特定id的朋友。
如果您对每月计数一点都不感兴趣,而只想得到平均值,那么更传统的子查询方法也同样适用:

from sqlalchemy import func

month = func.date_trunc('month', Complaint.date)
records_count = func.count(ClientGroupRecord.id)

complained_clients = session.query(records_count.label('count')). \
    select_from(Friend). \
    join(Complaint, Complaint.friend == Friend.friend_id). \
    join(ClientGroup, Complaint.client_group == ClientGroup.client_group_id). \
    join(ClientGroupRecord, ClientGroup.client_group_id == ClientGroupRecord.client_group_id). \
    join(Client, Client.client_id == ClientGroupRecord.client_id). \
    filter(Friend.friend_id == friend_id). \
    group_by(month). \
    subquery()

result = session.query(func.avg(complained_clients.c.count)).scalar()

…或使用窗口方法:

from sqlalchemy import func

month = func.date_trunc('month', Complaint.date)
records_count = func.count(ClientGroupRecord.id)

# Create a window of the whole set of results

records_avg = func.avg(records_count).over()

# Windows are evaluated for each row, but here we have a single window spanning

# the entire result, so the use of DISTINCT collapses this to a single value.

# Knowing what the query does LIMIT 1 / FETCH FIRST 1 ROW ONLY would work as well.

result = session.query(records_avg.label('avg')). \
    select_from(Friend). \
    join(Complaint, Complaint.friend == Friend.friend_id). \
    join(ClientGroup, Complaint.client_group == ClientGroup.client_group_id). \
    join(ClientGroupRecord, ClientGroup.client_group_id == ClientGroupRecord.client_group_id). \
    join(Client, Client.client_id == ClientGroupRecord.client_id). \
    filter(Friend.friend_id == friend_id). \
    group_by(month). \
    distinct(). \
    scalar()

相关问题