在SQLAlchemy中对总数进行分组

Question

我似乎找不到任何好的文档。我有一个用户和订单金额列表，我想显示订单金额总计前10名的用户。我一直在努力创建一个在SQLAlchemy中充分提取此数据的查询。是否有更好的方法来处理这个问题？

customers, amount = DBSession.query(Order.customer, func.sum(Order.amount).label('totalamount')).\
    group_by(Order.customer).\
    order_by(func.desc(totalamount)).\
    limit(10)

for a, b in zip(customers, amount):
    print a.name, str(amount)

- Jonathan Ong

Answer 1

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import random

Base= declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode)
    orders = relationship("Order", backref="customer")

class Order(Base):
    __tablename__ = "order"

    id = Column(Integer, primary_key=True)
    customer_id= Column(Integer, ForeignKey('customer.id'))
    amount = Column(Integer)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

session.add_all([
    Customer(name="c%d" % i, orders=[
        Order(amount=random.randint(10, 100))
        for j in xrange(random.randint(0, 5))
    ])
    for i in xrange(100)
])

amount_sum = func.sum(Order.amount).label('totalamount')
amount = session.query(Order.customer_id, amount_sum).\
            group_by(Order.customer_id).\
            order_by(amount_sum.desc()).\
            limit(10).\
            subquery()

for a, b in session.query(Customer, amount.c.totalamount).\
    join(amount, amount.c.customer_id==Customer.id):
    print a.name, b

有关此模式的一些指南可以在http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries找到，但总体上首先从SQL开始。

- zzzeek

---

谢谢，同时也回答了我的问题，帮助我理解了我对SQLAlchemy其他方面的疑惑。 - Jonathan Ong