SQLAlchemy的查询基于时间戳返回分组的产品从销售

Question

所以我就来了所需的逻辑有点卡住返回以下：

+--------------+------------+------------+------------+ 
| Product Name | 2016-05-10 | 2016-05-11 | 2016-05-12 | 
+--------------+------------+------------+------------+ 
| Big Chair | 29.00 | 78.00 | 900.00 | 
+--------------+------------+------------+------------+ 
| Small Chair | 11.00 | 65.00 | 12.00 | 
+--------------+------------+------------+------------+ 
| Box of bees | 5.00 | 20.00 | 80.00 | 
+--------------+------------+------------+------------+ ... 

这也是为什么 - 我的当前设置在SQLAlchemy的models.py文件（请从我抛砖，如果你发现这是不是最佳避免..）：

class Sale(db.Model): 
    __tablename__ = 'sales' 
    id = Column(Integer, primary_key=True) 
    sale_date = Column(DateTime) 
    total_price = Column(Numeric(12, 2), default=0) 
    products = relationship('Product', secondary='sale_product') 

class Product(db.Model): 
    __tablename__ = 'products' 
    id = Column(Integer, primary_key=True) 
    name = Column(Text) 
    price = Column(Numeric(12, 2), default=0) 
    sales = relationship('Sale', secondary='sale_product') 


class SaleProduct(db.Model): 
    __tablename__ = 'sale_product' 
    sale_id = Column(Integer, ForeignKey('sales.id'), primary_key=True) 
    product_id = Column(Integer, ForeignKey('products.id'), primary_key=True) 
    product_quantity = Column(Numeric(12, 1), default=0) 
    sale = relationship('Sale', backref=backref("sale_assoc")) 
    product = relationship('Product', backref=backref("product_assoc")) 

但是我很努力拿出一个查询这将返回上述结果或更具体地说，是一个对象然后我可以用它来达到上述结果。 我可以使用python“破解”这个处理，但我认为让Postgres完成繁重的工作会更有效率。

这里是我的想法至今：

我需要通过同时使用SaleProduct作为参考点（？），然后问的SQLAlchemy到组产品通过某种方式加入该产品，并销售表来开始名称（在这种情况下，假设是名称始终是唯一的，所以没有两个产品会具有相同的名称）。 但我碰到的问题是，当我试图找出如何加入已分组的产品，以返回他们为每个分组花费的总和，然后将它们分成具有日期的列。

这样可以很清楚：

1. 可我甚至达到使用或者Postgres的或SQL炼金术顶端示例输出？
2. 整个交易是否像我认为的那样复杂？我在这里错过了什么？
3. 最后 - 什么是最好的方法来结束一种方式来返回这样的结果呢？从数据库中获取原始数据，然后使用python进行处理（例如，将返回转换为字典或列表，然后输出用于打印的“行”），还是应该将数据库本身用于这样的任务？
4. 在这里很厚脸皮，但如果有人有空闲时间，他们能否提供某种模拟（在一个非常小的范围内）甚至如何工作？ - 不是必需的，但我希望尽可能地选择实施。

谢谢大家提前。

Answer 1

你缺少的是crosstab（又名“数据透视表”）。它允许您将列中的值转换为多列。

让我们的工作向后，并拿出我们需要给crosstab给我们我们想要的结果的数据：

+--------------+------------+----------------+ 
| Product Name | Sale Date | Total Quantity | 
+--------------+------------+----------------+ 
| Big Chair | 2016-05-10 | 29.00   | 
+--------------+------------+----------------+ 
| Big Chair | 2016-05-11 | 78.00   | 
+--------------+------------+----------------+ 
| Big Chair | 2016-05-12 | 900.00   | 
+--------------+------------+----------------+ 
| Small Chair | 2016-05-10 | 11.00   | 
+--------------+------------+----------------+ 
| Small Chair | 2016-05-11 | 65.00   | 
+--------------+------------+----------------+ 
| Small Chair | 2016-05-12 | 12.00   | 
+--------------+------------+----------------+ 
| Box of Bees | 2016-05-10 | 5.00   | 
+--------------+------------+----------------+ 
| Box of Bees | 2016-05-11 | 20.00   | 
+--------------+------------+----------------+ 
| Box of Bees | 2016-05-12 | 80.00   | 
+--------------+------------+----------------+ 

我们写什么查询给我们吗？很简单，加入了三个表和GROUP BY销售的产品和日期：

SELECT 
    products.name, 
    sale_date::date, 
    sum(product_quantity) 
FROM 
    sales 
    JOIN sale_product ON sales.id = sale_id 
    JOIN products ON product_id = products.id 
GROUP BY products.name, sale_date::date 

现在我们需要转动的日期表。

SELECT * FROM crosstab($$ <the previous query> $$, $$ SELECT DISTINCT sale_date::date FROM sales ORDER BY sale_date::date DESC LIMIT 3 $$) 
AS sales_numbers(product_name text, today int, yesterday int, ereyesterday int); 

对此的限制是必须预定义列，特别是列数。要获得动态列，您可以使用动态构建的查询EXECUTE。当然，为了获得最大的灵活性，您可以在Python中进行数据透视，但在SQL，IMO中执行它稍微有效。

将此与SQLAlchemy配合使用仅供读者参考。