Showing how many page hits for past 40 days 2

3dColor · Feb 1, 2006

I would like to chart the number of times a page has been view for the past 40 days.

Whenever the page gets a hit, a timestamp is recorded for that page.

The following query returns all the hits in the past 40 days (statDuration):

Code:

SELECT		*
FROM		Stats
WHERE		ListingFk = #URL.ad# AND DATE(timeStampStats) > #statDuration#
ORDER BY 	timeStampStats DESC

This query does a great job at returning all the hits for the past 40 days, but I have two problems:

1. Some days a page is viewed more than one time, how do I extract the total times it was view for that day?

2. Some days a page might not be viewed at all, how do I provide a zero for my chart if that day does not exist and I want to show each of the past 40 days?

TonyGroves · Feb 2, 2006

For requirement (1), you could use:
[tt]
SELECT DATE(timestampstats),COUNT(*)
FROM stats
WHERE listingfk=#url.ad# AND DATE(timestampstats)>#statduration#
GROUP BY DATE(timestampstats)
[/tt]
For requirement (2), the most feasible solution I can come up with is to have your program fill in the dates where there are no hits.

r937 · Feb 2, 2006

to show each of the past 40 days, use an integers table

Code:

create table integers (i integer);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

then generate the last 40 days like this:

Code:

select date_sub(current_date
       , interval 10*t.i+u.i day) as thedate
  from integers t
cross 
  join integers u
 where 10*t.i+u.i <= 40
order
    by thedate desc

note this will return 41 dates, because it includes today

naturally, if you prefer, you can skip the cross join which generates the numbers, in favour of simply loading up the integers table with integers from 0 up to 40, instead of just 0 up to 9

i prefer 0 through 9 because it's easy to generate hundreds, even thousands of numbers with multiple cross joins

and if you're worried about performance, don't, because with only 9 rows, the cross join will be done in memory

anyhow, it's then a very simple matter to use the above query together with a LEFT OUTER JOIN to your data

r937.com | rudy.ca

TonyGroves · Feb 2, 2006

If an extra table is acceptable, then the integers table is a good idea, but I
can't see how a left outer join will work, as the count for a day where there
are no hits will be 1, the same as a day with one hit. You would instead need a
UNION:
[tt]
SELECT dt,count(*)-1 cnt
FROM
(
SELECT DATE(timestampstats) dt
FROM stats
WHERE
listingfk=#url.ad#
AND
DATE(timestampstats)
>CURDATE()-INTERVAL 40 DAY
UNION ALL
SELECT CURDATE()-INTERVAL i DAY
FROM integers
WHERE i<40
)
[/tt]

TonyGroves · Feb 2, 2006

That should of course be:
[tt]
SELECT dt,count(*)-1 cnt
FROM
(
SELECT DATE(timestampstats) dt
FROM stats
WHERE
listingfk=#url.ad#
AND
DATE(timestampstats)
>CURDATE()-INTERVAL 40 DAY
UNION ALL
SELECT CURDATE()-INTERVAL i DAY
FROM integers
WHERE i<40
) q
[/tt]

TonyGroves · Feb 2, 2006

Or even:
[tt]
SELECT dt,count(*)-1 cnt
FROM
(
SELECT DATE(timestampstats) dt
FROM stats
WHERE
listingfk=#url.ad#
AND
DATE(timestampstats)
>CURDATE()-INTERVAL 40 DAY
UNION ALL
SELECT CURDATE()-INTERVAL i DAY
FROM integers
WHERE i<40
) q
GROUP BY dt
[/tt]

r937 · Feb 2, 2006

okay, let's say for simplicity you have i<=40 in the integers table

the left outer join works by joining on date

Code:

select thedate
     , count(stats.timestampstats) as cnt
  from (
       select date_sub(current_date
                , interval i day) as thedate
         from integers t
        where i <= 40 
       ) as i_40 
left outer
  join stats
    on stats.timestampstats = i_40.thedate
   and stats.listingfk = 937  
groyup
    by thedate

r937.com | rudy.ca

r937 · Feb 2, 2006

of course, "groyup by" should say GROUP BY

r937.com | rudy.ca

TonyGroves · Feb 2, 2006

Don't worry, I wasn't going to find fault with the "groyup" bit.

With the left join, you will have one record for each day with no hits. You will also have one record for each day with one hit. Is that not the case?

3dColor · Feb 2, 2006

I have been playing with Tony's first post when I noticed Rudy's and Tony's other posts...

I need to keep reading...

r937 · Feb 2, 2006

TonyGroves said:
With the left join, you will have one record for each day with no hits. You will also have one record for each day with one hit. Is that not the case?

close

there will be one row for each of the 40 (or 41, but let's not quibble) days

sometimes the count will be 0, sometimes it will be greater than 0

so it's not "one record for each day with one hit" but "one record for each day with any number of hits"

r937.com | rudy.ca

TonyGroves · Feb 2, 2006

I'm talking about the result of the join before the grouping. After the grouping you will have a count of at least one for each date, even where there were no hits.

r937 · Feb 2, 2006

i think i've misunderstood you, tony

yes, each date will be joined to zero or N stats rows, and then the GROUP BY allows counts to be produced

what was the question again?

r937.com | rudy.ca

TonyGroves · Feb 2, 2006

If you do a left join with the integers table as the left side, the result set will contain at least one record for each date. Each hit will have a record in the result set, but where there were no hits on a particular day, there will still be one record for that day. Then, when you do the grouped count, you will get a count of 1 for each day with no hits, 1 for each day with 1 hit, 2 for each day with 2 hits, and so on.

r937 · Feb 2, 2006

your last interpretation would be correct if you were using COUNT(*)

i'm not, i'm using COUNT(stats.timestampstats)

when a particular date has no matching stats row, all columns from the right (stats) table will be NULL, so COUNT(stats.timestampstats) will give zero

r937.com | rudy.ca

TonyGroves · Feb 2, 2006

Ah yes, you're right. Sorry for casting aspersions!

Both the LEFT JOIN and UNION solutions should work.

3dColor · Feb 2, 2006

Thanks Tony and Rudy. I will give your solutions a try and let you know what i find.

3dColor · Feb 2, 2006

Ok, I have had a little time to test both Tony's and Rudy's methods. I could be very well screwing both methods up since I am a SQL novice, but both are not working perfectly. Tony's comes close by providing the correct hits per day.

Code:

<CFQUERY name="tony" datasource="#DSN#">
	SELECT dt, count(*)-1 cnt
	FROM
	(
	  SELECT DATE(timeStampStats) dt
	    FROM Stats
	    WHERE
	      listingFk=#url.ad# AND DATE(timestampstats) > CURDATE()-INTERVAL 40 DAY
 	 UNION ALL
 	 SELECT CURDATE()-INTERVAL i DAY
  	  FROM Integers
  	  WHERE i < 40
	) q
	GROUP BY dt
	ORDER BY dt DESC
</CFQUERY>

Tony:<br>
<cfoutput query="tony">#DateFormat(dt, 'mm-dd')#, #cnt#<br></cfoutput>


<CFQUERY name="Rudy" datasource="#DSN#">
	select thedate, 
		count(Stats.timeStampStats) as cnt
  		FROM (
       		select date_sub(current_date, interval i day) as thedate
         from Integers t
        where i <= 40 
       ) as i_40 
	LEFT OUTER
 	 join Stats
  	  on Stats.timeStampStats = i_40.thedate
 	  and Stats.listingFk = #url.ad#  
	GROUP BY thedate
	ORDER BY thedate DESC
</CFQUERY>

Rudy:<br>
<cfoutput query="Rudy">#DateFormat(thedate, 'mm-dd')#, #cnt#<br></cfoutput>

Here are the results from the two queries:

Code:

Tony:
02-02, 1
02-01, 2
01-31, 1
01-30, 3
01-29, 1
01-28, 2
01-27, 2
01-26, 1
01-25, 6
01-24, 2
01-23, 4
01-22, 2
01-21, 5
01-15, 2
01-14, 26
01-13, 0

Rudy:
02-02, 0
02-01, 0
01-31, 0
01-30, 0
01-29, 0
01-28, 0
01-27, 0
01-26, 0
01-25, 0
01-24, 0

I am still working on it, but I thought I would post my findings while I figure out what is going on. Thanks!

r937 · Feb 2, 2006

what datatype is timeStampStats? if it's a unix timestamp, you'll have to join ON FROM_UNIXTIME(timeStampStats)=i_40.thedate

r937.com | rudy.ca

3dColor · Feb 3, 2006

Rudy, I am not sure if it's a unix timestamp or not. I am using MySQLFront and here is source code for the table:

Code:

CREATE TABLE `Stats` (
  `statsId` int(11) unsigned NOT NULL auto_increment,
  `listingFk` int(11) unsigned default NULL,
  `timeStampStats` timestamp NULL default CURRENT_TIMESTAMP,
  `ipAddress_stats` varchar(18) default NULL,
  `foundInSearch` tinyint(3) unsigned default '0',
  PRIMARY KEY  (`statsId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Showing how many page hits for past 40 days 2

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor