help me, chris
The following query is intended to show the number of revisions to the wiki over the past 14 days:
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS dom, COUNT(id)
AS revisions FROM version WHERE DATE_SUB(CURDATE(),
INTERVAL 14 DAY) <= date GROUP by dom;
Because a few days during the last two weeks had no activity at all, the results look like this:
+------------+-----------+
| dom | revisions |
+------------+-----------+
| 2005-10-17 | 11 |
| 2005-10-18 | 2 |
| 2005-10-19 | 9 |
| 2005-10-21 | 11 |
| 2005-10-22 | 2 |
| 2005-10-23 | 13 |
| 2005-10-24 | 11 |
| 2005-10-25 | 3 |
| 2005-10-26 | 10 |
| 2005-10-27 | 4 |
| 2005-10-28 | 3 |
+------------+-----------+
11 rows in set (0.03 sec)
But I need my results to look like this:
+------------+-----------+
| dom | revisions |
+------------+-----------+
| 2005-10-15 | 0 |
| 2005-10-16 | 0 |
| 2005-10-17 | 11 |
| 2005-10-18 | 2 |
| 2005-10-19 | 9 |
| 2005-10-20 | 0 |
| 2005-10-21 | 11 |
| 2005-10-22 | 2 |
| 2005-10-23 | 13 |
| 2005-10-24 | 11 |
| 2005-10-25 | 3 |
| 2005-10-26 | 10 |
| 2005-10-27 | 4 |
| 2005-10-28 | 3 |
+------------+-----------+
14 rows in set (0.03 sec)
That is, I want the days with no activity to show up in the results. How do I do that?
Comments
posted 2005-10-28 17:40:04 by fhazel:
Basically the problem is that SQL can’t provide counts for rows that don’t exist in the table. So what you want to do is create a temporary table of all the dates between today and two weeks ago and join it against the version table.
posted 2005-10-28 17:43:18 by fhazel:
Which will be easier to do in PHP.
posted 2005-10-28 17:56:32 by cobra libre:
Wow, that’s both more interesting and more complicated than I had expected. I was vaguely aware that you could create temporary tables in general, but didn’t know you could do that with MySQL (and you can, I just tried). But I’ll probably take the easy route. Thanks!
posted 2005-10-28 18:02:42 by fhazel:
Yeah, easier in PHP but if you want the elegance of it all being taken care of in the SQL, I could probably write a stored procedure that would generate the temporary table of dates based on the current date, join it with the version table and then return a result set with a count for every date.
posted 2005-10-28 18:35:59 by cobra libre:
Yeah, that would rock. It’s no big deal if you don’t want to, though.
posted 2005-10-28 19:49:38 by fhazel:
Basically the stored procedure does the following:
1. Create a temporary table called “temp_calendar”
CREATE TEMPORARY TABLE temp_calendar (date date);
2. Populate it with the last fourteen days dates
INSERT INTO temp_calendar VALUES (‘2005-10-15’);
INSERT INTO temp_calendar VALUES (‘2005-10-16’);
(…)
INSERT INTO temp_calendar VALUES (‘2005-10-27’);
INSERT INTO temp_calendar VALUES (‘2005-10-28’);
3. Perform the following query:
SELECT temp_calendar.date, revisions
FROM
(SELECT DATE_FORMAT(date, ‘%Y-%m-%d’) AS dom, count(id) AS revisions
FROM version
WHERE DATE_SUB(CURDATE(), INTERVAL 14 DAY)
posted 2005-10-28 19:51:41 by fhazel:
ok, the query got killed. let’s try again:
SELECT temp_calendar.date, revisions
FROM
(SELECT DATE_FORMAT(date, ‘%Y-%m-%d’) AS dom, count(id) AS revisions
FROM version
WHERE DATE_SUB(CURDATE(), INTERVAL 14 DAY) <= date GROUP BY dom)
as tempy
RIGHT JOIN temp_calendar ON temp_calendar.date = dom;
posted 2005-10-28 19:55:32 by fhazel:
Now all I need to do is figure out MySQL’s stored procedure syntax. Which will be clunky if it’s anything like MS SQL Server’s.
posted 2005-10-31 01:00:16 by fhazel:
Well, the MySQL syntax isn’t too hard, but sadly only version 5 supports stored procedures. Back to the drawing board, or rather, the PHP script.
posted 2005-11-01 10:58:55 by cobra libre:
Lousy database. Okay, I’ve implemented the solution in PHP. Thanks.
Post a comment
You are not currently logged in. You need to have a registered SNF account to post a comment. (log in)
News
posted 2005-10-28 17:14:46 by fhazel:
I’m working on it. Probably a subselect or a join.