Tricky SQL SELECT statement

[headsnet]

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

[edshuck]

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.

[CrazyCrane]

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 .

[headsnet]

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.