SQL Date Handling and Data Trends - Modular Arithmetic
(Page 3 of 4 )
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.
Next: Changes for SQL Server, Access, and Oracle >>
More SQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.
|
|
