Databases should have some built-in “virtual” tables filled with universal sequential data, against which you can join during queries. For instance, there should be a table called “dates” which is just a single-field table with a sequential list of dates, stetching into eternity in either direction.
How would you use this? Say I wanted to get a list of the number of comments posted every day on this blog. I could just use COUNT and GROUP BY to roll them up, but if there were no comments on a particular date, then that date just doesn’t show up (since there’s noting to count).
To get a “0” on date where there were no comments, I would need to join my original query on a table of dates. As it sits, I need to create this table and populate it and maintain it myself. Why couldn’t this be built into a database platform?
There should be a table for numbers too. I was thinking about letters of the alphabet (but then you get into localization issues), days of the week, and months of the year. However, you can create those and maintain them easily, since they’re finite. Dates and numbers stretch into infinity in both directions, so they’re harder to deal with.
Does something like this exist on any platforms that you know of?
You could do something like this in SQL server to build out a temp table (starting with a #) In the below sql code, I create a temp table (#tmpDate), populate it with all the days between the min/max of messageDate of the messages table. You can then do a join from there:
Brian K. Wharton Catalina Technology http://www.catalinatechnology.com Microsoft Solomon ISV
/*/
declare @beginDate smallDateTime declare @endDate smallDateTime declare @tmpDate smallDateTime
select @beginDate = min(messageDate) from messages select @endDate = max(messageDate) from messages
select @beginDate, @endDate
set @tmpDate = @beginDate
create table #tmpDate (myDate smallDateTime)
while @tmpDate <= @endDate BEGIN set @tmpDate = dateadd(dd, 1, @tmpDate) insert into #tmpDate(myDate) values(@tmpDate) END
/* -- do your query here */
drop table #tmpDate
/*/
btw. my comment above doesnt seem to have carriage returns where they are supposed to be. Hopefully you can gleam from the code what I was doing. When in doubt, put a carriage return.
brian
That's a pretty good idea.
I've found myself having to resort to creating and maintaining such tables or massive and union all views on several occasions.
Seems like a reasonable thing to propose for the next version of the SQL standard. Either such virtual tables themselves or preferably a facility to define such.
I would use this every day. Who do we need to talk to?
As a matter of fact in Oracle you can do something like:
CREATE OR REPLACE TYPE datetbltype AS TABLE OF DATE /
CREATE OR REPLACE FUNCTION daterangetbl(FromDt IN DATE, ToDt IN DATE) RETURN datetbltype DETERMINISTIC PIPELINED AS --Returns a table containing a range of dates, non-inclusive. BEGIN IF ToDt <= FromDt THEN RETURN; END IF; FOR i IN 0..(ToDt - FromDt - 1) LOOP PIPE ROW(FromDt + i); END LOOP; RETURN; END; /
