代码之家 › 专栏 › 技术社区 › Wilhelm Murdoch

为一周中的特定日期或日期范围创建的记录的累积平均数

statistics mysql sql

Wilhelm Murdoch · 技术社区 · 16 年前

对于这种情况,最好的数据源是我们的logs表,因为我们几乎记录了应用程序中发生的每个事务。

现在,问题来了,我对MySql没有太多的经验来整理累计和和运行平均值。我已经抛出了下面的查询,这对我来说是有意义的,但它只是一直锁定命令控制台。这件事要花很长时间才能执行,测试样本中只有80k条记录。

因此,考虑到以下基本表格结构:

id   | action | date_created
1    | 'merp' | 2007-06-20 17:17:00
2    | 'foo'  | 2007-06-21 09:54:48
3    | 'bar'  | 2007-06-21 12:47:30
... thousands of records ...
3545 | 'stab' | 2007-07-05 11:28:36

如何计算一周中每一天创建的记录的平均数量?

day_of_week | average_records_created
1           | 234
2           | 23
3           | 5
4           | 67
5           | 234
6           | 12
7           | 36

我有以下疑问,这让我想通过把我的身体扔下电梯井来谋杀我自己。。。还有一些子弹:

SELECT
    DISTINCT(DAYOFWEEK(DATE(t1.datetime_entry))) AS t1.day_of_week,
    AVG((SELECT COUNT(*) FROM VMS_LOGS t2 WHERE DAYOFWEEK(DATE(t2.date_time_entry)) = t1.day_of_week)) AS average_records_created
FROM VMS_LOGS t1
GROUP BY t1.day_of_week;

哈尔普斯?求你了,别再让我割伤自己了(

3 回复 | 直到 16 年前

OMG Ponies 16 年前

我将您的查询改写为:

  SELECT x.day_of_week,
         AVG(x.count) 'average_records_created'
    FROM (SELECT DAYOFWEEK(t.datetime_entry) 'day_of_week',
                 COUNT(*) 'count'
            FROM VMS_LOGS t
        GROUP BY DAYOFWEEK(t.datetime_entry)) x
GROUP BY x.day_of_week

Community Mohan Dere 9 年前

查询耗时如此之长的原因是由于您的内部选择,您基本上正在运行6400000000个查询。对于这样的查询,您最好的解决方案可能是开发一个定时报告系统,在该系统中,用户在完成查询和构建报告时会收到一封电子邮件,或者用户登录后检查报告。

OMG Ponies (贝娄)您仍在查看相同数量的查询。

  SELECT x.day_of_week,
         AVG(x.count) 'average_records_created'
    FROM (SELECT DAYOFWEEK(t.datetime_entry) 'day_of_week',
                 COUNT(*) 'count'
            FROM VMS_LOGS t
        GROUP BY DAYOFWEEK(t.datetime_entry)) x
  GROUP BY x.day_of_week

scwagner 16 年前

因为记录的星期日和星期数是常量,所以创建一个包含ID、WeekNumber和DayOfWeek的伴随表。无论何时,只要从主表生成“缺失”记录,就可以运行此统计。

然后,您的报告可以大致如下:

select
  DayOfWeek
, count(*)/count(distinct(WeekNumber)) as Average
from
  MyCompanionTable
group by
  DayOfWeek

当然,如果表太大,那么您可以每天预先汇总数据并使用它,并在运行报告时从主表中添加“今日”数据。