按一年中的星期分组统计记录

Question

我的表基本上是一个记录每个页面命中的访问日志。我想要显示的是一年中每一周每周的页面点击率，并与每年进行比较(所以我有2013年第1周、2014年、2015年、2013年第2周、2014年等等)。我目前通过查询所有记录来完成这个任务，并让PHP完成繁重的工作，如下所示:

$result=$db->query("select `time` from accessTracking where `uID` is not null and `time`>1357030861 order by `time`");
foreach($result as $r){
    $y=date("Y",$r['time']);
    $w=ltrim(date("W",$r['time']),'0');
    $accessArray[$w][$y]++;
}

当它工作时，它需要一个坚实的7秒来加载它显示图形的页面，我想不仅有一个更好的方法与PHP，但更多的是与MySQL(这是我正在寻找的，但我会采取任何得到页面加载时间下降，使整个事情更有效率。

所以，我想到的最好的是:

select weekofyear(`time`) as week, count(*) as count from `accessTracking` where `uID` is not null group by week

这看起来像它将工作，因为它返回53行，"周"列从1-52和"计数"列的随机值范围从2到1100。问题是，这些数字应该更高，因为我拥有的最高周点击量约为27,000次，而在此查询中返回的第一个记录是null"week"值，"count"值约为398,000。这告诉我，对于大多数记录，它不计算weekofyear()值。

我的猜测是，我将在最后运行这作为每年单独的查询，因为它可能太多，让它返回年，周数和计数，所有分组我想要的方式。但谁知道呢!

Answer 1

您的查询是不可比较的。特别是，第一个有这样的条件:

`time` > 1357030861

说明time是unix时间。我建议你试试:

select weekofyear(from_unixtime(`time`)) as week, count(*) as count
from `accessTracking`
where `uID` is not null and `time` > 1357030861
group by week;

Answer 2

首先，使用关键字或函数作为列名不是一个好主意，因为这会导致混淆和bug风险(并且必须记住所有的'字符)。我想这是你想要的，但是在结果中加上年份。

select weekofyear(from_unixtime(`time`)) as wk,
       year(from_unixtime(`time`)) as yr,
       count(*) as ct
from `accessTracking`
where `uID` is not null
group by wk, yr

要排除您看到的奇怪结果，您应该查询特定的记录，并查看它们如何与聚合匹配。例如:

select `time` as access_time
from `accessTracking`
where `uID` is not null and
      weekofyear(from_unixtime(`time`)) = 1 and
      year(from_unixtime(`time`)) = 2015

将该查询的行数与聚合查询中的行数进行比较。它们应该匹配，如果不匹配，你可以更好地看到差异在哪里。当然，我建议选择行数较少的一周进行故障排除，或者选择显示您认为错误的结果的行。