4 ms·

> One common use case I've found for recursive CTEs is generating tables of dates. If you don't have table-valued functions, just create a table with all the d

> One common use case I've found for recursive CTEs is generating tables of dates. If you don't have table-valued functions, just create a table with all the dates for the next hundred years or so. The number of distinct dates is pretty manageable. This is the approach I use when calculating stats data via MySQL.

shrikant 8y ago
I'd second this for dates. Every data warehouse I've been involved in building has always had a date table for ~100 years depending on the industry use case.
- gmfawcett 8y ago
  This is especially common in data warehouses, as the dimension tables need to be populated with all of the attributes that a user will be searching/drilling on (including dates). I don't think I've ever come across a transactional system with a dedicated date table -- you usually have access to a good suite of date-manipulation operations in SQL -- but maybe there's a case for it.
greggyb 8y ago
And make that table WIIIIIIDE. There are so many useful attributes for query time and to be exposed as filters/parameters in reports for end users in a more self service mode. Flags for whether the day part is YTD, MTD or similar, so you can easily select the set of days that would be in the YTD period for any year. This is super helpful for a multi-year comparison of YTD. So right now, everything from January 1- May 21/22 (depending on your edge case) would be YTD=1 for all years (not just 2018). Contiguous, monotonically increasing indices for any date period - weeks, months, quarters, trimesters, semesters - especially helpful for fiscal calendars where the entirety of built-in date functions become worthless. Last period is always the index of the current period minus one. All date shifting logic becomes trivially expressible as basic arithmetic on these indices. Pre-format dates into all the common display formats you might use. Just extra strings in the table - super useful for your non-technical report authors and self-service analytics consumers - don't worry about the cognitive overhead of teaching dozens-thousands of people how to use format strings effectively (they won't - they'll just pull it into Excel), when you can trade a tiny bit of storage overhead to give them all they want up front. Give a simple "IsWorkDay" flag - include all your company holidays and weekends here. Also separately include an "IsWeekDay" flag and an "IsHolidayFlag". If you're concerned about storage, please count the number of dates - you need that many bits to store each of these flags. This is ~4.5KB per flag field on a 100-year date table. Even if you're storing things inefficiently as BIGINTs, it's ~292KB per flag field. This is pathetically small. I really can't emphasize how useful this sort of thing is for fiscal calendars. A calendar is just a hierarchy whose base unit is a date. Everyone's dates are the same. They just belong to different categories (Month1=January vs Month1="first four full weeks in the calendar year"). Those categories are defined as contiguous and non-overlapping sets of these base units. We care an awful lot about the inequality invariants of members of these sets (months, quarters, etc). All of this stuff can be exposed through a table that is updated for certain flags, or just a base table of static attributes and a view for the "dynamic" components.

shrikant 8y ago

I'd second this for dates. Every data warehouse I've been involved in building has always had a date table for ~100 years depending on the industry use case.

gmfawcett 8y ago
This is especially common in data warehouses, as the dimension tables need to be populated with all of the attributes that a user will be searching/drilling on (including dates). I don't think I've ever come across a transactional system with a dedicated date table -- you usually have access to a good suite of date-manipulation operations in SQL -- but maybe there's a case for it.

greggyb 8y ago

And make that table WIIIIIIDE. There are so many useful attributes for query time and to be exposed as filters/parameters in reports for end users in a more self service mode. Flags for whether the day part is YTD, MTD or similar, so you can easily select the set of days that would be in the YTD period for any year. This is super helpful for a multi-year comparison of YTD. So right now, everything from January 1- May 21/22 (depending on your edge case) would be YTD=1 for all years (not just 2018). Contiguous, monotonically increasing indices for any date period - weeks, months, quarters, trimesters, semesters - especially helpful for fiscal calendars where the entirety of built-in date functions become worthless. Last period is always the index of the current period minus one. All date shifting logic becomes trivially expressible as basic arithmetic on these indices. Pre-format dates into all the common display formats you might use. Just extra strings in the table - super useful for your non-technical report authors and self-service analytics consumers - don't worry about the cognitive overhead of teaching dozens-thousands of people how to use format strings effectively (they won't - they'll just pull it into Excel), when you can trade a tiny bit of storage overhead to give them all they want up front. Give a simple "IsWorkDay" flag - include all your company holidays and weekends here. Also separately include an "IsWeekDay" flag and an "IsHolidayFlag". If you're concerned about storage, please count the number of dates - you need that many bits to store each of these flags. This is ~4.5KB per flag field on a 100-year date table. Even if you're storing things inefficiently as BIGINTs, it's ~292KB per flag field. This is pathetically small. I really can't emphasize how useful this sort of thing is for fiscal calendars. A calendar is just a hierarchy whose base unit is a date. Everyone's dates are the same. They just belong to different categories (Month1=January vs Month1="first four full weeks in the calendar year"). Those categories are defined as contiguous and non-overlapping sets of these base units. We care an awful lot about the inequality invariants of members of these sets (months, quarters, etc). All of this stuff can be exposed through a table that is updated for certain flags, or just a base table of static attributes and a view for the "dynamic" components.