SQL Date Handling and Data Trends

Look at the values forwhn%7andFLOOR(whn/7). You can see that day number 1,622 (counting from 2001-01-01) is day number 5 of week number 231:

  mysql> SELECT whn, whn%7, whn/7, FLOOR(whn/7)
      ->   FROM webalizer2;   

 

whn	whn%7 whn/7		FLOOR(whn/7)
1622		5 231.7143		231
1623		6 231.8571		231
1624		0  232.0000		232
1625		1232.1429		232
1626		2  232.2857		232
1627		3 232.4286		232
1628		4 232.5714		232
1629		5  232.7143		232
1630		6  232.8571		232
1631		0 233.0000		233
1632		1 233.1429		233
...

You need toGROUP BYthewhn%7column to see the weekly cycle andGROUP BYtheFLOOR(whn/7)column to see the trend.

To look at the intra-week pattern shown back in Figure 4-2, you take the average withGROUP BY whn%7:

  mysql> SELECT whn%7, AVG(pages)
     ->   FROM webalizer2 GROUP BY
whn%7;

  +-------+------------+
  | whn%7 | AVG(pages) |
  +-------+------------+
  |     0 | 21391.6731 |
  |     1 | 23695.1538 |
  |     2 | 23026.2308 |
  |     3 | 24002.8077 |
  |     4 | 19773.9808 |
  |     5 | 10353.5472 |
  |     6 | 10173.9423 |
  +-------+------------+

To smooth out the data over the whole year, as shown in Figure 4-3, you can divide by 7 and take the integer value using theFLOORfunction:

mysql> SELECT FLOOR(whn/7), AVG(pages)
    ->  FROM webalizer2 GROUP BY FLOOR(whn/7);

  +--------------+------------+
  | FLOOR(whn/7) | AVG(pages) |
  +--------------+------------+
  |          231 | 10748.5000 |
  |          232 | 23987.8571 |
  |          233 | 19321.1429 |
  |          234 | 15347.0000 |
  ...

The value for the first week is artificially low—by chance, it includes two on only two days, and they are on weekends. Something similar might happen at the end of the interval, so it is safest to exclude any week that does not have seven entries. TheHAVINGclause will take care of that:

  mysql> SELECT FLOOR(whn/7), AVG(pages)
      ->  FROM webalizer2 GROUP BY FLOOR(whn/7)
     ->  HAVING COUNT(*)=7;

  +--------------+------------+
  | FLOOR(whn/7) | AVG(pages) |
  +--------------+------------+
  |          232 | 23987.8571 |
  |          233 | 19321.1429 |
  |          234 | 15347.0000 |
  ...

This will work fine with MySQL and PostgreSQL, but you need to make a few alterations for SQL Server, Access, and Oracle.