Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets.
In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM".
Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.
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?
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.
EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.
For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.
date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short
...
20200101 2020 1 1 1 1 january jan
20200102 2020 1 2 1 1 january jan
...
20200401 2020 4 1 2 14 april apr
...
I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.
If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly.
Days with sales is easy with a simple SUM or COUNT and GROUP BY.
Days without sales is easy if you think about this dimensionally.
SELECT dim_days.day_of_month,
COUNT(fact_orders.order_id)
/*
Select from dim_days first because you want every day.
*/
FROM dim_days
/*
Outer join to the fact table to pull in the data you have and add that to your dates.
*/
LEFT JOIN fact_orders
ON dim_days.calendar_day = fact_orders.order_day
/*
Filter results by the desired range.
*/
WHERE dim_days.month = 4
AND dim_days.year = 2020
GROUP BY dim_days.day_of_month;
"SQL is easy. Data is hard."
Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."
SELECT -> map
FROM -> stream
JOIN -> flatMap
WHERE -> filter
GROUP BY -> collect
ORDER BY -> sorted
https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei...
x <- ..
y <- ..
return (x, y)This is explained in the blog post I linked, including an example.
List<Integer> s1 = Stream.of(1, 2);
Supplier<Stream<String>> s2 = ()->Stream.of("A", "B");
s1.flatMap(v1 -> s2.get()
.map(v2 -> tuple(v1, v2)))
.forEach(System.out::println);
When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch.
Definitely feel it's me not SQL, but not found the answer.