4 ms·

> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 6

> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted the last 60, 90 or 120 days? I suppose you could first create a temporary table to be the "numbers table", custom made for the range you want to fetch - is there an alternative?

EvanAnderson 7y ago
In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.
will_pseudonym 7y ago
There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.
Amezarak 7y ago
I have done this many times. My preferred solution was to create a recursive CTE beginning with the desired start date and ending with the desired end date. I often based these on MIN and MAX subqueries, but obviously you could also pick arbitrary dates. You can use days, months, weeks, years, or whatever you wanted as the "increment" in the CTE using the DATEADD function. Then (for nulls) you simply LEFT JOIN your CTE with the desired date part (aggregated) of your table. This gives you your first answer, and a simply filter will get you your second answer. It's also common for people to just create all these date tables beforehand as actual, materialized tables. In my opinion, this is less elegant (what happens in 2101?! Somebody had better remember to add to the table!) but it naturally works just as well and probably saves some perf.

EvanAnderson 7y ago

In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.

will_pseudonym 7y ago

There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.

Amezarak 7y ago

I have done this many times. My preferred solution was to create a recursive CTE beginning with the desired start date and ending with the desired end date. I often based these on MIN and MAX subqueries, but obviously you could also pick arbitrary dates. You can use days, months, weeks, years, or whatever you wanted as the "increment" in the CTE using the DATEADD function. Then (for nulls) you simply LEFT JOIN your CTE with the desired date part (aggregated) of your table. This gives you your first answer, and a simply filter will get you your second answer. It's also common for people to just create all these date tables beforehand as actual, materialized tables. In my opinion, this is less elegant (what happens in 2101?! Somebody had better remember to add to the table!) but it naturally works just as well and probably saves some perf.