如何使用sqlalchemy使func.sum和group\ by输出行的总和并合并重复的行

Question

我想生成一个表，它将在给定的时间段内售出的图书数量和为不同的图书支付的总金额相加。我需要它来显示一份出售书籍的报告

我的子查询是：

bp = db.session.query(CustomerPurchase.book_category_id, 
func.sum(CustomerPurchase.amount).label('amount'),
func.sum(CustomerPurchase.total_price).label('total_price'))\
.filter(CustomerPurchase.created_on >= start_date)\
.filter(CustomerPurchase.created_on <= end_date)\
.group_by(CustomerPurchase.book_category_id).subquery()

带子查询的组合查询：

cp = CustomerPurchase.query\
.join(bp, bp.c.category_id == CustomerPurchase.category_id)\
.distinct(bp.c.category_id)\
.order_by(bp.c.category_id)

我的CustomerPurchase表如下所示，我的查询的输出看起来相同：

id | book_category_id | book_title | amount | total_price |
---+------------------+------------+--------+-------------+
 1 |        1         | Book A     |   10   |    35.00    |
 2 |        1         | Book A     |   20   |    70.00    |
 3 |        2         | Book B     |   40   |    45.00    |

查询运行后所需的输出应如下所示：

id | book_category_id | book_title | amount | total_price |
---+------------------+------------+--------+-------------+
 1 |        1         | Book A     |   30   |  105.00     |
 2 |        2         | Book B     |   40   |   45.00     |

上面的查询显示CustomerPurchase表中卖给客户的所有图书，但它不SUM、amount和total_price，也不合并副本

我见过许多例子，但没有一个对我有用。非常感谢您的帮助！提前谢谢

Answer 1

所以经过大量的研究和试验，我提出了一个问题，解决了我的问题。基本上，我在sqlalchemy中使用了add_column属性，它给了我想要为报表显示的确切行

bp = db.session.query(CustomerPurchase.book_store_category_id,
func.sum(CustomerPurchase.quantity).label('quantity'),
func.sum(CustomerPurchase.total_price).label('total'))\
.filter(CustomerPurchase.created_on >= start_date)\
.filter(CustomerPurchase.created_on <= end_date)

bp = bp.add_column(BookStore.book_amount)\
.filter(BookStore.category_id == CustomerPurchase.book_store_category_id)

bp = bp.add_columns(Category.category_name, Category.total_stock_amount)\
.filter(Category.id == CustomerPurchase.book_store_category_id)

bp = bp.add_column(Category.unit_cost)\
.filter(Category.id == CustomerPurchase.book_store_category_id)

bp = bp.add_column(Book.stock_amount)\
.filter(Book.category_id == CustomerPurchase.book_store_category_id)\
.group_by(BookStore.book_amount, CustomerPurchase.book_store_category_id, Category.category_name, Category.unit_cost, Category.total_stock_amount, Book.stock_amount)

bp = bp.all()