MySQL - 平均忽略空值并基于工作日

Question

我正在尝试对以下数据进行一些分析

WeekDay Date    Count   
5   06/09/2018  20  
6   07/09/2018  Null    
7   08/09/2018  19  
1   09/09/2018  16  
2   10/09/2018  17  
3   11/09/2018  24  
4   12/09/2018  25  
5   13/09/2018  24  
6   14/09/2018  23  
7   15/09/2018  23  
1   16/09/2018  9   
2   17/09/2018  23  
3   18/09/2018  33  
4   19/09/2018  22  
5   20/09/2018  31  
6   21/09/2018  17  
7   22/09/2018  10  
1   23/09/2018  12  
2   24/09/2018  26  
3   25/09/2018  29  
4   26/09/2018  27  
5   27/09/2018  24  
6   28/09/2018  29  
7   29/09/2018  27  
1   30/09/2018  19  
2   01/10/2018  26  
3   02/10/2018  39  
4   03/10/2018  32  
5   04/10/2018  37  
6   05/10/2018  Null    
7   06/10/2018  26  
1   07/10/2018  11  
2   08/10/2018  32  
3   09/10/2018  41  
4   10/10/2018  37  
5   11/10/2018  25  
6   12/10/2018  20  

我想解决的问题是：我想创建一个表，其中包含与当天相关的最后 3 个相同工作日的平均值。但是，当工作日出现 NULL 时，我想忽略并仅对剩余数字进行平均值，而不是将 NULL 计为 0。我在这里给你举个例子：

此表中的日期是日/月/年:)

例如：在 2018 年 12 月 10 日，我需要来自 天 05/10/2018;28/09/2018;21/09/2018. 这些是最近 3 个与 12/10/2018 相同的工作日(6(。 .它们的值为 Null;29;17. 那么这个平均值的结果必须是 23，因为我需要忽略 NULL，而不是 15,333。

我该怎么做？

Answer 1

count()函数忽略空值(即如果遇到空值，则不会递增(，所以我建议你简单地计算值，然后可能包含你想忽略的空值。

dow datecol     value
6   21/09/2018  17
6   28/09/2018  29
6   05/10/2018  Null

例如 上面的sum(value)= 46，count(value)= 2，因此平均值为 23.0(avg(value)也将返回 23.0，因为它也忽略了空值(

select
weekday
, `date`
, `count`
, (select (sum(`count`) * 1.0) / (count(`count`) * 1.0)
from atable as t2
where t2.weekday = t1.weekday
and t2.`date` < t1.`date
order by t2.`date` DESC
limit 3
) as average
from atable as t1

您可以在上面的查询中使用avg(count)，并获得相同的结果。

附言。我希望您不要使用count作为列名！我还建议您也不要使用date作为列名。即避免使用 SQL 术语作为名称。

Answer 2

SELECT WeekDay, AVG(Count)
FROM myTable
WHERE Count IS NOT NULL
GROUP BY WeekDay

Answer 3

在选择中使用IsNULL(Count,0)

SELECT WeekDay, AVG(IsNULL(Count,0))
FROM myTable
GROUP BY WeekDay

Answer 4

首先，您需要在数据中获取该工作日的实例数，因为您只需要最近 3 个相同的工作日

create table table2
as
select
row_number() over(partition by weekday order by date desc) as rn
,weekday
,date
,count
from table

从这里，你可以得到你想要的。有了你的解释，你不需要筛选出计数的 NULL 值。只做 avg(( 聚合只会忽略它。

select
weekday
,avg(count)
from table2
where rn in (1,2,3)
group by weekday