I would like to group the results of an SQL query that returns a DATE by week. For example, I would like a tally...
of new users on a Web site grouped by the week that the user signed up. How would I go about doing that? I am currently using Oracle 8i.
Grouping output by week is similar to Grouping on the date part of a datetime column (17 October 2002). All you need to do is decide what a week is.
Oracle provides comprehensive functionality to extract date parts, including the ISO week. Without knowing the particulars of what an ISO week is, you can perform a simple grouping as follows:
select to_char(signedupdate,'YYYY') as SignedUpYear , to_char(signedupdate,'IW') as SignedUpWeek , count(*) as NewUsers from yourtable group by to_char(signedupdate,'YYYY') , to_char(signedupdate,'IW')
While this gives you the results you want, it lacks a certain something, namely a date to associate with each week.
Grouping output by week is a special case of Aggregates for date ranges (4 October 2002). In this case the date range would be a week. I like this technique because it has another benefit: if you want all weeks included in the results, even if there were no users signed up for a given week, then it's easy to achieve that by using a left outer join when generating the date ranges.
Let's assume we want to produce the Weekly New Users report for a range of dates, from startdate to enddate. We can generate the necessary range of dates like this:
select startdate + 7 * i as Day0 , startdate + 7 * i + 6 as Day6 from integers where startdate + 7 * i <= enddate
This will produce one row per week, and the number of rows will depend on how many weeks there are between startdate and enddate. Depending on the day of the week of startdate, we will get a Sunday-Saturday week or a Monday-Sunday week, or whatever.
Now all we have to do is create a left outer join to your table of data:
select startdate + 7 * i as Day0 , startdate + 7 * i + 6 as Day6 , count(userid) as NewUsers from integers left outer join yourtable on signedupdate between startdate + 7 * i days and startdate + 7 * i + 6 days where signedupdate between startdate and enddate and startdate + 7 * i <= enddate group by i
Note that since it's an outer join, the number of rows that match a given week can be zero. Therefore we must use count(userid) instead of count(*).