SQLAlchemy ORM:产品总和 [英] SQLAlchemy ORM: Sum of products

问题描述

假设我有一个带有width和length列的ROOMS表，以及一个对应的SQLAlchemy模型.是否有一种干净有效的方法来获取所有房间的总面积，即sum(长x宽)?通过在客户端循环很容易做到，但是如果可以通过服务器上的查询来获取它，肯定一定要更有效率.

Let's say I have a ROOMS table with width and length columns, and a corresponding SQLAlchemy model. Is there a clean and efficient way to get a total area of all rooms, i.e. sum(length x width)? It's easy enough to do by looping at the client but it must surely be more efficient if it can be fetched by a query at the server.

我认为我可以通过将问题简化为一个简单的示例而有所帮助，但是现在我意识到我只是在开枪，因为我的困难显然是由于对SQLAlchemy的更根本的了解以及与ORM的合作.

I thought I was being helpful by reducing the problem to a simple, clean example but I now realise I was just shooting myself in the foot because my difficulties evidently stem from a more fundamental lack of understanding of SQLAlchemy and working with ORMs.

我的模型(flask-sqlalchemy)实际上涉及三个相关的表:持有量，商品和价格.大宗商品有很多价格，每种持有量都是给定商品的数量.我将其设置如下:

My model (flask-sqlalchemy) actually involves three related tables: holdings, commodities and prices. Commodities have many prices, and each holding is a quantity of a given commodity. I have it set up as follows:

class Holding(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    time = db.Column(db.TIMESTAMP, index=True)
    quantity = db.Column(db.DECIMAL(10,5))
    commodity_id = db.Column(db.Integer, db.ForeignKey('commodity.id'))

    commodity = db.relationship('Commodity', back_populates='holdings')

class Commodity(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    symbol = db.Column(db.String(20))
    name = db.Column(db.String(150))

    holdings = db.relationship('Holding', back_populates='commodity', lazy='dynamic')
    prices = db.relationship('Price', back_populates='commodity', lazy='dynamic', order_by='Price.time.desc()')

class Price(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    time = db.Column(db.TIMESTAMP, index=True)
    amount = db.Column(db.DECIMAL(10,5), index=True)
    commodity_id = db.Column(db.Integer, db.ForeignKey('commodity.id'))

    commodity = db.relationship('Commodity', back_populates='prices')

我想要Holding.quantity * Holding.commodity.[最近的价格]的总和.

I want the sum of Holding.quantity * Holding.commodity.[most recent price].

由于Commodity.prices是降序排列的，所以我可以在Holding Loop检查中轻松计算出该值:

Since Commodity.prices is in descending time order I can easily calculate the value in a Holding loop examining:

h.commodity.prices.first().amount * h.quantity

...但是我看不到如何从单个查询中获得相关的价格明细，所以我不知道如何应用@leovp的解决方案.

... but I can't see how to get at the related Price details from a single query, so I don't know how to apply @leovp's solution.

我希望现在能正确地描述问题，对错误的开始表示歉意.

I hope that properly describes the problem now, apologies for the false start.

推荐答案

关于您问题的更有趣的部分是解决 greatest-n-per-group 问题.现在，我对MySQL相当了解，因此可能有比这更有效的解决方案:

The more interesting part about your question is solving the greatest-n-per-group problem. Now, I'm pretty green when it comes to MySQL, so there might be more efficient solutions to that than this:

In [43]: price_alias = db.aliased(Price)

In [44]: latest_price = db.session.query(Price.commodity_id, Price.amount).\
    ...:     outerjoin(price_alias,
    ...:               db.and_(price_alias.commodity_id == Price.commodity_id,
    ...:                       price_alias.time > Price.time)).\
    ...:     filter(price_alias.id == None).\
    ...:     subquery()

自左联接尝试联接具有更大时间的行，这些行不存在最新价格，因此为filter(price_alias.id == None).

The self left join tries to join rows with greater time, which do not exist for the latest price, hence the filter(price_alias.id == None).

剩下的就是将Holding与子查询结合起来了:

What's left then is to just join Holdings with the subquery:

In [46]: sum_of_products = db.session.query(
    ...:         db.func.sum(Holding.quantity * latest_price.c.amount)).\
    ...:     join(latest_price,
    ...:          latest_price.c.commodity_id == Holding.commodity_id).\
    ...:     scalar()

这篇关于SQLAlchemy ORM:产品总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！