如何在SQL查询中伪造这些数据

Question

我不知道如何从查询中获取此表

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

如果这些值是平均值，“今天”是今天的平均值，“WTD”是指本周迄今为止的平均值，“MTD”是指本月到目前为止的平均值，“YTD”是指今年迄今为止的平均值。在

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

^{pr2}$

目前，我所能得到的是产品的平均价值：

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

Answer 1

基于前面的答案，我宁愿使用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;

Answer 2

在纯SQL中，您可以实现如下相同的效果（相应地指定日期）：

select product.name
, avg(case when product_assessment.record_date = <todays date> then product_assessment.score else 0 end) as today
, avg(case when product_assessment.record_date between <start date> and <end date> then product_assessment.score else 0 end) as wtd
, avg(case when product_assessment.record_date between <start date> and <end date> then product_assessment.score else 0 end) as mtd
, avg(case when product_assessment.record_date between <start date> and <end date> then product_assessment.score else 0 end) as ytd
from product, product_assessment
where product.id = product_assessment.product_id
group by product.name;