postgresql - SqlAlchemy：联合的 groupby 错误：SELECT 构造以包含在 UNION 或其他预期的集合构造中

Question

我试图用不同的表做 2 个查询，将它们联合起来，然后用 sum 做一个 groupby。这是 PostgreSQL 代码片段：

    SELECT      
       to_char(worklist.date,'YYYY-MM') AS year_month,
       department.department_name AS department_name,
       user.username AS user_name,     
       SUM(worklist.hour) AS TotalHour,
       SUM(worklist.overtime_hour) AS overtime_TotalHour,
       CAST(NULL AS numeric(20)) AS total_price
       FROM user, worklist, department
    WHERE user.id = worklist.user_id AND
          user.department_id = department.id
    group BY year_month, user_name, department_name
    UNION
    SELECT 
       to_char(expenditure.date,'YYYY-MM') AS year_month,
       department.department_name AS department_name,
       user.username AS user_name,          
       CAST(NULL AS numeric(20)) AS TotalHour,
       CAST(NULL AS numeric(20)) AS overtime_TotalHour,
       SUM(expenditure.price) AS total_price
    FROM user, department, expenditure
    WHERE user.id = expenditure.user_id AND 
          user.department_id = department.id
    group BY year_month, user_name, department_name
    ORDER BY year_month DESC

查询结果： PostgreSQL查询结果

我想在 sqlalchemy 中实现它，这是我将如何处理它：

def get_allusers_monthly(db: Session):
    qry1 = (db.query(
            func.to_char(workhour.Workhour.date,'YYYY-MM').label('year_month'),
            department.Department.department_name.label('department_name'),
            user.User.username.label('user_name'),
            func.sum(workhour.Workhour.hour).label('total_hour'),
            func.sum(workhour.Workhour.overtime_hour).label('total_overtime_hour'),
            cast(expen.Expenditure.price, Numeric(10)).label('total_pric')
            ).filter(user.User.id == workhour.Workhour.user_id and user.User.department_id == department.Department.id
            ).group_by(
            'year_month',
            'department_name',
            'user_name',
            'total_pric'
            ).all())

    qry2 = (db.query(
            func.to_char(expen.Expenditure.date,'YYYY-MM').label('year_month'),
            department.Department.department_name.label('department_name'),
            user.User.username.label('user_name'),
            cast(workhour.Workhour.hour, Numeric(10)).label('total_hour'),
            cast(workhour.Workhour.overtime_hour, Numeric(10)).label('total_overtime_hour'),
            func.sum(expen.Expenditure.price).label('total_pric')
            ).filter(user.User.id == expen.Expenditure.user_id and user.User.department_id == department.Department.id
            ).group_by(
            'year_month',
            'department_name',
            'user_name',
            'total_hour',
            'total_overtime_hour'
            ).all())    

    all_queries = [qry1, qry2]
    golden_set = union(*all_queries).subquery()
    return golden_set

以下是出现此错误的输出：

sqlalchemy.exc.ArgumentError： SELECT 构造以包含在 UNION 或其他预期的集合构造中

我哪里错了？或者有没有更好的方法来实现这个

有人可以帮忙吗？！

标签： postgresqlsqlalchemy