mysql - 如何在添加新值之前在 MySQL 中拖动平均结果？

Question

我正在尝试构建一个包含销售额和平均销售额的表格。

每一行都必须是自然日，因此周末将不会显示任何销售额。由于我知道避免周末来计算销售平均值这一事实，我使用了两个查询并链接了结果。

除了我希望查询保留周末的最后销售平均值之外，一切都运行顺利，如下所示，去年 2 月的销售额:

What I get:           What I would like:

day   sales     avg   day   sales     avg
-------------------   -------------------
28   57,544  52,289   28   57,544  52,289
27   24,585  52,013   27   24,585  52,013
26        0       0   26        0  53,537    
25        0       0   25        0  53,537    
24   64,494  53,537   24   64,494  53,537
23   41,593  52,892   23   41,593  52,892
22  119,473  53,598   22  119,473  53,598
21   61,368  49,207   21   61,368  49,207
20   22,739  48,338   20   22,739  48,338
19        0       0   19        0  50,307    
18        0       0   18        0  50,307    
17   60,764  50,307   17   60,764  50,307
16   28,227  49,436   16   28,227  49,436
15   47,697  51,364   15   47,697  51,364
14   21,423  51,730   14   21,423  51,730
13  119,182  55,098   13  119,182  55,098
12        0       0   12        0  47,087    
11        0       0   11        0  47,087    
10   26,382  47,087   10   26,382  47,087
 9   62,140  50,045    9   62,140  50,045
 8   76,742  48,029    8   76,742  48,029
 7   90,080  42,287    7   90,080  42,287
 6   27,865  30,339    6   27,865  30,339
 5        0       0    5        0  31,163
 4        0       0    4        0  31,163
 3   26,577  31,163    3   26,577  31,163
 2   50,259  33,456    2   50,259  33,456
 1   16,653  16,653    1   16,653  16,653
-------------------   -------------------

这是我的代码示例:

select q0.day,
q1.net,
q2.med
from
(
select day(date) as day
from dates
order by date desc
) as q0
left join
(
select day(date) as day,
▸   sum(sales) as net
▸   from file
▸   group by date
) as q1
on q0.day=q1.day
left join
(
select day(q20.date) as day,
avg(q21.neto) as med
from
▸   (select date
▸   from file
▸   group by date
▸   ) as q20,
▸   (select date,
▸   sum(sales) as net
▸   from file
▸   group by date
▸   ) as q21
where q20.date >= q21.date
group by q20.date
) as q2
on q0.day=q2.day

谢谢，

Answer 1

试试这个查询:

SELECT D.day AS day,
       SUM(CASE WHEN N.day = D.day THEN N.net ELSE 0 END) AS net,
       AVG(N.net) AS avg
FROM dates D
LEFT JOIN (SELECT day, SUM(sales) AS net
           FROM file F
           GROUP BY day) N
ON N.day <= D.day
GROUP BY D.day
ORDER BY day DESC

它的工作原理是在文件表中查找每天的净销售额，然后在加入日期表后选择日期、当天的总和以及截至当天的非空值的平均值。

要测试它:请参阅此 SQLfiddle:http://sqlfiddle.com/#!2/b7271/3/0