MySql如何获取每小时平均计数

Question

我一直在寻找与每小时平均查询相关的几个不同问题，但我找不到任何解决以下问题的问题。

我有一个日志表，用于跟踪用户访问页面的次数:

ID   USERID   PAGEID  SECNO    DATE

1    123      120     14      6/08/2013 10:07:29 AM
1    124      438     1       6/08/2013 11:00:01 AM
1    123      211     18      6/09/2013 14:07:59 PM
1    123      120     14      6/10/2013 05:07:18 PM
1    124      312     4       6/10/2013 08:04:32 PM
1    128      81      54      6/11/2013 07:02:15 AM

我正在尝试获取两个不同的查询。一个看起来像这样的:

HOURLY      Count     Average   

12am        0        0
1am         0        0
2am         0        0
3am         0        0
4am         0        0
5am         1        0
6am         0        0
7am         1        0
8am         0        0
9am         0        0
10am        1        0
11am        1        0
12pm        0        0
1pm         0        0
2pm         1        0
3pm         0        0
4pm         0        0
5pm         1        0
6pm         0        0
7pm         0        0
8pm         1        0
9pm         0        0
10pm        0        0
11pm        0        0

第二个查询是这样的:

DAY      PERCENTAGE

Monday   10%
Tuesday   16%
Wednesday   14%
Thursday   22%
Friday   21%
Saturday   14%
Sunday   3%

**请注意平均值只是一个样本

到目前为止，对于第一个查询，我有这样的内容:

SELECT 
   HOUR(date) AS hourly, 
   Count(*)  
FROM 
   logs
GROUP BY 
   hourly

我尝试在 Count() 之后添加 AVG() 但没有成功。

我的日志表没有每个小时的数据，但我仍然需要在我的报告中显示所有时间。如果小时为空，则值为 0。有什么想法可以实现吗？

Answer 1

对第一个查询试试这个:

SELECT
    h.hour,
    IFNULL(tmp.the_count,0),
    IFNULL(tmp.the_avg,0)
FROM 
    hourly h
LEFT JOIN (
    SELECT
        hourly,
        SUM(visits) the_count,
        SUM(visits)/COUNT(DISTINCT userid) as the_avg
    FROM (
        SELECT 
           HOUR(date) AS hourly, 
           COUNT(*) as visits,
           userid
        FROM 
           logs
        GROUP BY 
           hourly,
           userid
    ) as tmp
    GROUP BY
        hourly
) as tmp
    ON tmp.hourly = h.hour

尝试第二个查询:

SELECT
    theday,
    IFNULL(percentage,0) as percentage
FROM (
    SELECT  DATE_FORMAT('2013-06-16','%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 1 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 2 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 3 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 4 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 5 DAY,'%W') as theday UNION
    SELECT  DATE_FORMAT('2013-06-16' - INTERVAL 6 DAY,'%W') as theday 
) as weekt
LEFT JOIN (
    SELECT 
        DATE_FORMAT(date,'%W') AS daily, 
        (COUNT(*)/(SELECT COUNT(*) FROM logs))/100 as percentage
    FROM 
        logs 
    WHERE 
        date >= '2013-06-10'
        AND date <= '2013-06-16'
    GROUP BY 
        daily
) as logdata
    ON logdata.daily = weekt.theday