Grouping output by week
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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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(*).


This was first published in October 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top