mysql - 如何在mysql中查找一段时间内的每日平均值？

Question

我有一个表，其中有两列:

标记

CREAT_TS

我想要两个日期范围(例如开始日期和结束日期)之间的每日平均分数

我做了以下查询:

select SUM(MARKS)/ COUNT(date(CREAT_TS)) AS DAILY_AVG_MARKS, 

date(CREAT_TS) AS DATE 

from TABLENAME

group by date(CREAT_TS)

通过此查询，只有当数据库中有日期的行时，我才能获得每日平均值。但我的要求是，即使没有行，我也想为该日期显示 0。我的意思是，如果 (startDate, endDate) 之间有 X 天，我希望查询返回 X 行

谁能帮帮我。 :(

Answer 1

您需要创建一组可以添加到日期中的整数。以下内容将为您提供一个思路:

select thedate, avg(Marks) as DAILY_AVG_MARKS
from (select startdate+ interval num day as thedate
      from (select d1.d + 10 * d2.d + 100*d3.d as num
            from (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d1 cross join
                 (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d2 cross join
                 (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d3
            ) n cross join
            (select XXX as startdate, YYY as enddate) const
       where startdate + num <= enddate
      ) left outer join
      tablename t
      on date(CREAT_TS) = thedate
group by thedate

所有复杂的事情都在于为报告创建一组连续的日期。如果您有一个 numbers 表或一个 calendar 表，那么 SQL 看起来会简单得多。

这是如何运作的？第一个大子查询有两部分。第一个只是通过交叉连接数字 0-9 并进行一些算术运算来生成 0 到 999 之间的数字。第二个将此连接到两个日期，startdate 和 enddate - 您需要为 XXX 和 YYY 输入正确的值。使用此表，您将拥有两个值之间的所有日期。如果您需要超过 999 天，只需添加另一个交叉连接。

这是左连接到您的数据表。结果是所有日期都显示为分组依据。

就报告而言，在表示层这样做有优点也有缺点。基本上，用 SQL 来做的好处是报表层更简单。在报告层做的好处是 SQL 更简单。局外人很难做出这样的判断。

我的建议是创建一个数字表，您可以像这样在报告中使用它。这样查询看起来会更简单，而且您不必更改报告层。