如何在SQL查询中构造此数据

splbuddy 发表于 Dev

挥舞着

我对如何从查询中获取此表感到困惑

+-------------+---------+----------+---------+-------+
|Product Name |Today    |WTD       | MTD     |YTD    |
+-------------+---------+----------+---------+-------+
|Name1        |78       |80        |89       |89     |
+-------------+---------+----------+---------+-------+
|Name2        |56       |78        |88       |78     |
+-------------+---------+----------+---------+-------+

其中的值是平均值，“今天”是今天的平均值，“ WTD”是指本周至今的平均值，“ MTD”是指本月至今的平均值，“ YTD”是指本年迄今的平均值。

老实说，我不太了解SQL，也正在使用SQLALchemy（我也不太了解）。我要从中获取数据的表是：

class Product(Base):
    __tablename__ = 'product'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(80))

class ProductAssessment(Base):
    __tablename__ = "product_assessment"
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('product.id'), nullable=False)
    product_name = relationship(Product, backref=backref('assessments'),
                   cascade="all, delete, delete-orphan")
    score = Column(Integer, nullable=False)
    record_date = Column(Datetime, default=func.now())

现在，我已经能够通过执行以下操作获得该产品的平均值：

result = DBSession.query(Product.name.label("Product name"), func.avg(Product_Assessment.score).label("YTD")).filter(Product.id==Product_Assessment.product_id).group_by(Product.name)

如果有人可以帮助我，请多谢

编辑

样本数据：产品

+-------+--------+
|id     |name    |
+-------+--------+
|1      |Name1   |
+-------+--------+
|2      |Name2   |
+-------+--------+

产品评估

+----------+---------+--------+----------+
|product_id|id       |score   |Date      |
+----------+---------+--------+----------+
|1         |1        |80.16   |2015/1/5  |
+----------+---------+--------+----------+
|2         |2        |85.19   |2015/1/18 |
+----------+---------+--------+----------+
|1         |3        |81.70   |2015/1/18 |
+----------+---------+--------+----------+
|1         |4        |70.11   |2015/1/18 |
+----------+---------+--------+----------+

预期产量：

+------------+--------+-------+-------+------+
|Product name|Today   |WTD    |MTD    |YTD   |
+------------+--------+-------+-------+------+
|Name1       |70.11   |70.11  |77.32  |77.32 |
+------------+--------+-------+-------+------+
|Name2       |85.19   |85.19  |85.19  |85.19 |
+------------+--------+-------+-------+------+

那就是数据@ mandeep_m19

里里

在上一个答案的基础上，我宁愿使用一个INNER JOIN。我确实同意这种CASE WHEN构造，但是由于每个值要考虑的行数都不相同，因此我们需要自己计算平均值（AVG不起作用）。这是一个相当沉重的结构，但我看不到更简单的方法。

SELECT 
   P.name
   ,SUM(CASE WHEN PA.record_date = <todays date> THEN PA.score ELSE 0 END) 
   / SUM(CASE WHEN PA.record_date = <todays date> THEN 1 ELSE 0 END) AS today
   ,SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END) 
   / SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS wtd
   ,SUM(CASE when PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END)
   / SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS mtd
   ,SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END)
   / SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS ytd
FROM product P
INNER JOIN product_assessment PA
ON (P.id = PA.product_id)
GROUP BY P.name;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。