|
|
Imagine that we recived web sites clicks statistics organized as a number of
visitors per dates range, like this:
| siteid | fdate | tdate | visitors_num |
| 23658 | 1999-01-28 | 1999-03-04 | 508 |
| 24991 | 1999-02-03 | 1999-05-29 | 1702 |
| 20380 | 1999-03-18 | 1999-06-30 | 919 |
Our task is to use this data and create daily/monthly statistics of site's
visiting. In order to do this we will use JOIN between recived report (dates
ranges table) and dates sequence table created earlier:
USE colombo;
/* create tables and put in some data */
-- this table stores websites information
CREATE TABLE websites
(
siteid INT NOT NULL,
site_url VARCHAR(100) NULL,
UNIQUE KEY websites_uniq_indx (siteid)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- insert data
INSERT INTO websites (siteid,site_url)
VALUES (20380,'http://www.artofthestorage.co.uk');
INSERT INTO websites (siteid,site_url)
VALUES (23658,'http://www.numberonesimplesale.com');
INSERT INTO websites (siteid,site_url)
VALUES (24991,'http://www.happyholydaysale.biz');
-- this table stores site's visits report
CREATE TABLE site_visits
(
idno INT NOT NULL,
repdate DATETIME NULL,
siteid INT NULL,
fdate DATETIME NULL,
tdate DATETIME NULL,
visitors_num INT NULL,
UNIQUE KEY site_visits_uniq_indx (idno)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- insert data
INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (101,'2000-01-05',23658,'1999-01-28','1999-03-04',508);
INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (102,'2000-01-16',24991,'1999-02-03','1999-05-29',1702);
INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (103,'2000-01-23',20380,'1999-03-18','1999-06-30',919);
|
/*
Run stored procedure that creates dates sequence. Source code of the procedure
is here
*/
CALL sp_init_dates (
'1999-01-01','1999-07-30');
/*
Now we just write join between tables, where essential condition is that
date value from the sequence table have to fall between reported dates range.
Formula:
(a.visitors_num / DateDiff(a.tdate,a.fdate))
gives us daily quantity of visits.
*/
SELECT b.site_url,
Month(c.sdate) AS the_month,
SUM(a.visitors_num / DateDiff(a.tdate,a.fdate)) AS monthly_visits
FROM site_visits AS a, websites AS b, seq_dates AS c
WHERE a.siteid = b.siteid
AND Year(c.sdate) = 1999
AND (c.sdate BETWEEN
a.fdate AND a.tdate)
GROUP BY b.site_url, Month(c.sdate)
ORDER BY b.site_url, Month(c.sdate)
|
The results will be as follows:
| site_url | the_month | monthly_visits |
| http://www.artofthestorage.co.uk | 4 | 265 |
| http://www.artofthestorage.co.uk | 5 | 273 |
| http://www.artofthestorage.co.uk | 6 | 265 |
| http://www.happyholydaysale.biz | 2 | 384 |
| http://www.happyholydaysale.biz | 3 | 458 |
| http://www.happyholydaysale.biz | 4 | 444 |
| http://www.happyholydaysale.biz | 5 | 429 |
|