CODEWITHSUNDEEP
phpmysql
Patrick
Patrick

Reputation: 328

Count records grouped by weeks of the year

My table is basically an access log that records every page hit. What I want to display is the page hits per week for each of the weeks of the year, comparing year to year (so I have week 1 of 2013, 2014, 2015, week 2 of 2013, 2014 and so forth). I am currently accomplishing this by just querying all the records, and letting PHP do the heavy lifting as follows:

$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]++;
}

While it works, it takes a solid 7 seconds to load the page it displays the graph on, and I imagine there is not only a better way with PHP, but even more so with MySQL (which is what I'm looking for, but I'll take anything that gets the page load time down and makes the whole thing more efficient.

So, with that, the best I've come up with is:

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

This looks like it's going to work as it returns 53 rows with the "week" column from 1-52 and the "count" column with random values in them ranging from 2 to 1100. The problem is that the numbers should be way higher as the highest week I have is about 27,000 hits, and the first record returned in this query is a null "week" value with a "count" value of about 398,000. This is telling me that for most of the records, it's not calculating the weekofyear() value.

My guess is that I will in the end run this as separate queries for each year, as it may be too much to have it return the year, week number, and count, all grouped the way I want. But who knows!

Upvotes: 1

Views: 78

Answers (2)

Sanna Keller-Heikkila
Sanna Keller-Heikkila

Reputation: 2594

First of all, it's not a good idea to use keywords or functions as your column names, as it leads to confusion and risk for bugs (and having to remember all the ` characters). I think this would do what you want, but with the year added to the results.

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

For troubleshooting the strange results you're seeing, you should query for specific records and see how they match up to the aggregate. For example: 

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

Compare the row count from that query to what you get in the aggregate query. They should match, and if they don't you can better see where the discrepancy is. Naturally, I'd suggest choosing a week that has a smaller number of rows for troubleshooting, or rows that are showing results you feel are wrong.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Your queries are not comparable. In particular, the first one has this condition:

`time` > 1357030861

This suggests that time is a unix time. I would suggest that you try:

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

Upvotes: 2

Related Questions