4 ms·

One common use case I've found for recursive CTEs is generating tables of dates. Very frequently, I have a question along the lines of "how many x have occurre

One common use case I've found for recursive CTEs is generating tables of dates. Very frequently, I have a question along the lines of "how many x have occurred per day/week/month?", where x has a timestamp column, where the data could be sparse. One neat way to do this is to create a recursive CTE that begins by selecting the first relevant date, then unions that with the period (day/week/month) plus one up to the last date you're interested in. Once done, you can left join your date CTE against a COUNT aggregation grouped by the period you care about on your data table.

blattimwind 8y ago
Why not just GROUP BY to_char(timestamp, some_format) / extract(something from timestamp)?
- dpcx 8y ago
  I assume because that doesn't give the 0 results in the query. The RCTE would.
  - blattimwind 8y ago
    Ah right. generate_series() might be another, possibly simpler way, to generate a time-series independent of what data in the relation actually exists and then join and group that series to the data relation.
barrkel 8y ago
> 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.
meritt 8y ago
assuming you're on postgres, generate_series() is a far easier option to do exactly what you've described.
- Amezarak 8y ago
  I'm not sure how much easier it is - the CTE definition and the series definition are extremely similar. Certainly if you have generate_series() or something similar, it's an appropriate solution.
rosser 8y ago
If you're using postgres, take a look at the generate_series() set-returning function for this purpose. It might be a lot cheaper. EDIT: Looks like someone already suggested this, down-thread.
- Amezarak 8y ago
  I don't see a measurable performance difference on my machine. In my experience, set-based SQL is fast even on 100s of GB databases as long as everything is reasonably normalized and indexed.
  - rosser 8y ago
    It is, but by materializing the date series into a table, rather than "lazy-loading" it with the SRF, you're consuming disk IO that other backends could be using. Disk is the narrowest pipe in servicing user queries. That's the sense in which I was thinking it's cheaper.
    - Amezarak 8y ago
      An RCTE is not materialized into a table on disk. There is no disk IO. I would also argue that network is the narrowest pipe. The sibling solution, where it is materialized into a table, is an excellent one. The amount of disk IO is negligible - the table is tiny.
      - rosser 8y ago
        Ack. I just realized I replied to you when I meant to reply to the sibling post, not yours. I'm sorry for the confusion. That solution uses a resolution of dates. If that meets your use-case, sure; if you need finer, the IO (and cache) burden grows with that. Which is the narrowest pipe depends on your plumbing. My application-facing databases have 10 gigabit NICs, and narrower to the storage; I'm absolutely not network-constrained. I also don't really consider the size or shape of the data returned to the application a cost in the same sense I do the disk; application queries routinely denormalize the data into a less compact form. They presumably need the data in that format for a reason (even if that's simply the engineer's cognitive bandwidth). It's also not a thing I'm generally in control of, so other than offering guidance, there's not a lot I can do about it.

blattimwind 8y ago

Why not just GROUP BY to_char(timestamp, some_format) / extract(something from timestamp)?

dpcx 8y ago
I assume because that doesn't give the 0 results in the query. The RCTE would.
- blattimwind 8y ago
  Ah right. generate_series() might be another, possibly simpler way, to generate a time-series independent of what data in the relation actually exists and then join and group that series to the data relation.

barrkel 8y ago

> 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.

meritt 8y ago

assuming you're on postgres, generate_series() is a far easier option to do exactly what you've described.

Amezarak 8y ago
I'm not sure how much easier it is - the CTE definition and the series definition are extremely similar. Certainly if you have generate_series() or something similar, it's an appropriate solution.

rosser 8y ago

If you're using postgres, take a look at the generate_series() set-returning function for this purpose. It might be a lot cheaper. EDIT: Looks like someone already suggested this, down-thread.

Amezarak 8y ago
I don't see a measurable performance difference on my machine. In my experience, set-based SQL is fast even on 100s of GB databases as long as everything is reasonably normalized and indexed.
- rosser 8y ago
  It is, but by materializing the date series into a table, rather than "lazy-loading" it with the SRF, you're consuming disk IO that other backends could be using. Disk is the narrowest pipe in servicing user queries. That's the sense in which I was thinking it's cheaper.
  - Amezarak 8y ago
    An RCTE is not materialized into a table on disk. There is no disk IO. I would also argue that network is the narrowest pipe. The sibling solution, where it is materialized into a table, is an excellent one. The amount of disk IO is negligible - the table is tiny.
    - rosser 8y ago
      Ack. I just realized I replied to you when I meant to reply to the sibling post, not yours. I'm sorry for the confusion. That solution uses a resolution of dates. If that meets your use-case, sure; if you need finer, the IO (and cache) burden grows with that. Which is the narrowest pipe depends on your plumbing. My application-facing databases have 10 gigabit NICs, and narrower to the storage; I'm absolutely not network-constrained. I also don't really consider the size or shape of the data returned to the application a cost in the same sense I do the disk; application queries routinely denormalize the data into a less compact form. They presumably need the data in that format for a reason (even if that's simply the engineer's cognitive bandwidth). It's also not a thing I'm generally in control of, so other than offering guidance, there's not a lot I can do about it.