How to count in SQL
Your average programming language has a way of doing the same thing over and over again multiple times. Maybe a few different ways. After all, that’s why god invented computers. I mean what’s programming language without iteration? Python has a neat way of doing this. You can say:
-
for i in xrange(x, y, s):
-
<stuff you want to do from x to y with step s>
xrange generates a list of integers (conceptually) from x to y, incrementing by s, so it’s a quick and clean way to do your repetitive business. True to Python minimalism, the first and last arguments are optional. If you leave them out the loop goes from 0 to y.
Sadly, SQL has no analogue. A local SQL veteran here has confirmed to me that SQL has no way of generating lists … you have to put stuff into a table. Give that this is the case, I decided to put my small but useful sequence (integer dates representing the past 48 hours, hour by hour) into a temporary memory table. Here’s how to do it:
-
CREATE TEMPORARY TABLE hourlycal ( id INT AUTO_INCREMENT PRIMARY KEY, date int(11)) ENGINE = MEMORY;
-
INSERT INTO hourlycal (date) SELECT NULL FROM reporting.Session LIMIT 48;
-
UPDATE hourlycal SET date = FROM_UNIXTIME(UNIX_TIMESTAMP(),“%Y%m%d%H”) - id*3600;
-
DROP TEMPORARY TABLE hourlycal;
There you go. Very handy. At least for me. Note that it takes a table to make a table :-). You have to select from something in order to insert the NULL’s. This is based on ideas from Artful Software.
Link to Artful Software
