SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot headsnet's Avatar
    Join Date
    Feb 2001
    Location
    London, UK
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Tricky SQL SELECT statement

    I have a MySQL table that contains userID's and dates. A row is created each time a user logs in, with the current date added.

    I then end up with several rows with the same userID, and either the same or different dates, depending on if they logged in multiple times on the same day or on different days.

    I need to find out how many users are logging in (i.e. active users) per week. I can create a select statement to give me a result for the number of active users in the last 7 days for example by doing:

    SELECT COUNT(DISTINCT USER_ID) AS NUMENTRIES FROM $table WHERE CREATED_DATE > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

    etc

    However, if I want to get this info but for every week since the logging began, I don't want to have to do a separate query for each week as this will take forever and bog down my server...Some of the tables that this has to work with have over a million records !!

    What I need is a single select statement that will give me a total number of unique users that logged in each week. I have been looking into the GROUP BY feature, but am not sure as to how this can be integrated to give me a result set that is basically an array of totals for each week.

    Hope this makes sense. Anyone got any ideas.

    Thanks

  2. #2
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    looks like you already have the answer to your question.

    is the traffic increasing or decreasing? when was the last visit for each user? was there a time when they left in droves? was there a growth bump?

    all of these are very much just a mod of the select you already have.

    i must not be seeing the question.

    please help me see the problem.

  3. #3
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    this is the kind of thing you'd use a cursor for in another database (MS SQL, Oracle etc.) It's certainly not trivial to select what you want (a weekly breakdown of users) - as you say selecting it once it easy enough, but selecting it for all time split by week is difficult without writing a massive query that abuses the UNION command or something.

    Honest answer is that I don't know; as far as I'm aware, MySQL doesn't support cursors so that avenue is out. The UNION method would be very very unweildy, and would be a hog on server resources too (which you're trying to avoid); plus you'd have to edit the command every week if you planned to run this query regularly.

    I'd be very interested to see a decent solution to this; sorry I can't be of any more help to you though .
    Nick Wilson [ - email - ]

  4. #4
    SitePoint Zealot headsnet's Avatar
    Join Date
    Feb 2001
    Location
    London, UK
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have now worked out a quick solution which gives day by day results:

    SELECT CREATED_DATE, COUNT(DISTINCT USER_ID) AS NUMENTRIES FROM $table GROUP BY CREATED_DATE DESC

    Then you can loop through the result set, which gives you figures for each day.

    To obtain a weekly result, I assume it would be easy enough to group the elements of this array into blocks of 7.

    Cheers for all your suggestions anyways.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •