Home arrow SQL arrow Page 3 - SQL Date Handling and Data Trends
SQL

SQL Date Handling and Data Trends


In this second part of a four-part series explaining how SQL handles date calculations, you will learn how to uncover trends in your data. This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Author Info:
By: O'Reilly Media
Rating: 5 stars5 stars5 stars5 stars5 stars / 6
December 13, 2007
TABLE OF CONTENTS:
  1. · SQL Date Handling and Data Trends
  2. · Turning the Dates into Integers
  3. · Modular Arithmetic
  4. · Changes for SQL Server, Access, and Oracle

print this article
SEARCH DEVARTICLES

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.


blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials