?
Solved

Query Help for Statistics Table

Posted on 2012-08-25
4
Medium Priority
?
911 Views
Last Modified: 2012-08-25
My Table Structure looks like this:

CREATE TABLE IF NOT EXISTS `statistics` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `section` varchar(32) NOT NULL,
    `action` varchar(32) NOT NULL,
    `when` int(11) NOT NULL,
    `uri` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
)

I am tracking page views in my MVC application ( tracking the controller, and the method , along with the entire URL ). Also, notice I am tracking the TIME.

Now that I have this information, I need to know how to USE it.

1. What is the most popular controller (section)?

2. How many people visited on a particular date?

3. Which method is being used the least?

and finally,

4. TOTALS - for each controller, for a particular day

I am not the best at queries. I hope I am collecting enough info to get the data I need.

Can someone help me with the queries please?

Thank you.
0
Comment
Question by:edvinson
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38333319
I am assuming that the column "when" holds the date related data ... as it is INT and not DATETIME - what does it hold?  

And just to confirm:
    controller => section
    method => action
0
 
LVL 1

Author Comment

by:edvinson
ID: 38333463
You are correct in your assumptions.

Disregarding the TIME portion of my question, could you assist from there?

Thank you.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38333519
In the most simplest form ... as query like:
    select `when`, count(*) as visitors
    from statistics
    group by `when`
counts the number of statistic rows (=visits? ... perhaps you need a source ip address for that).

If you only want a specific day ... add a WHERE clause (change <some value> to be the day or something that you are searching for, e.g.
    select count(*) as visitors
    from statistics
    where `when`= <some value>
 or, as you are tracking time you need to use a range:
    select count(*) as visitors
    from statistics
    where `when`between <low value> and <high value>

Similar methods can be used for `section` and `action` with a GROUP BY.  e.g.
    select `section`, count(*)
    from statistics
    where `when`between <low value> and <high value>
    group by `section`
    order by count(*) desc
    limit 1

If you only want the topmost value ... use LIMIT:
    select `section`, count(*)
    from statistics
    where `when`between <low value> and <high value>
    group by `section`
    order by count(*) desc
    limit 1


To put them all together into a report style result at once requires more complex SQL like:
select visit_date, visitors, 
       s.`section` as most_used_section, s.uses as most_used_section_cnt,
       am.`section` as most_used_section_action, am.`action` as most_used_action, am.uses as most_used_section_action_cnt,
       al.`section` as least_used_section_action, al.`action` as least_used_action, al.uses as least_used_section_action_cnt
from (
select `when` as visit_date, count(*) as visitors
from statistics
group by `when`
) v
inner join (
select `when`, `section`, uses, if(@l=`when`,@c:=@c+1,@c:=1) rank, @l:=`when`
from (select `when`, `section`, count(*) as uses
      from statistics
      group by `when`, `section`
      order by `when`, count(*) desc)v1, (select @c:=0,@l:=now())v2
) s ON v.visit_date = s.`when` and s.rank = 1
inner join (
select `when`, `section`, `action`, uses, if(@l=`when`,@c:=@c+1,@c:=1) rank, @l:=`when`
from (select `when`, `section`, `action`, count(*) as uses
      from statistics s
      group by `when`, `section`, `action`
      order by `when`, count(*) desc)v1, (select @c:=0,@l:=now())v2
) am ON v.visit_date = am.`when` and am.rank = 1
inner join (
select `when`, `section`, `action`, uses, if(@l=`when`,@c:=@c+1,@c:=1) rank, @l:=`when`
from (select `when`, `section`, `action`, count(*) as uses
      from statistics s
      group by `when`, `section`, `action`
      order by `when`, count(*))v1, (select @c:=0,@l:=now())v2
) al ON v.visit_date = al.`when` and al.rank = 1
order by visit_date desc

Open in new window

But for my testing - I had `when` populated with dates only (no time component) ... in your situation the `when` needs to be handled to use the date portion only ... this is why it mattered.


test data:
CREATE TABLE IF NOT EXISTS `statistics` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `section` varchar(32) NOT NULL,
    `action` varchar(32) NOT NULL,
    `when` datetime NOT NULL,
    `uri` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
);
insert into `statistics` (`section`,`action`,`when`) values
('section1','actionA',date_sub(curdate(), interval 3 day)),
('section1','actionA',date_sub(curdate(), interval 3 day)),
('section1','actionA',date_sub(curdate(), interval 3 day)),
('section1','actionA',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section1','actionB',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionC',date_sub(curdate(), interval 3 day)),
('section2','actionD',date_sub(curdate(), interval 3 day)),
('section2','actionD',date_sub(curdate(), interval 3 day)),

('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionA',date_sub(curdate(), interval 2 day)),
('section1','actionB',date_sub(curdate(), interval 2 day)),
('section1','actionB',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionC',date_sub(curdate(), interval 2 day)),
('section2','actionC',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionC',date_sub(curdate(), interval 2 day)),
('section2','actionC',date_sub(curdate(), interval 2 day)),
('section2','actionC',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day)),
('section2','actionD',date_sub(curdate(), interval 2 day));

Open in new window

0
 
LVL 1

Author Closing Comment

by:edvinson
ID: 38333542
One of the most comprehensive answers I have received in my many, many years here on EE.

Thank you. Extremely helpful.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question