Don't Underestimate a CTE
-
CTEs are a powerful tool for making your SQL more legible. They also help me model my data and iterate quickly before materializing the final shape of a table, without mucking about modifying a table shape.
The Problem
My wife wants to know when she’ll work a certain number of hours so she can take an exam. There’s a projection forward, but I also want to take into account days/hours she’s already worked and days that are out of the norm.
The Start
I could make one table with entries for all her hours worked, but then I need to track in that table which are a projection and which are “real”. Thinking myself clever, I decided the distinction warranted two tables. One for simple entries of hours worked and one to hold all the projections.
How do I fill in all those projection dates? I came up with a simple algorithm that basically takes all the week days and gives her 8 hours a day. I used a CTE to come up with this, then I was going to insert them into projections and go from there.
The Iteration
I made a table expression to insert data into a real table. But why not just use that expression in my calculation directly? If I have a algorithmic projection, what parameters could I change that would cause the calculation to update? Those parameters are what I want to store in a table!
If she’s works part time, I can tune that parameter directly instead of trying to reconcile what’s in the table with the new data I want. The SQL CTE will just recalculate it for me.
CTEs are super nice to iterate quickly, since there’s nothing stored to clean up. DBeaver has a nice concept of variables I used to help me decide what parameters should be part of my configuration. What I put in the DBeaver variables become columns in the table directly.
The Solution and Next Steps
Now that I have a more stable idea of what I want, the next step is materializing it in the database. A View for my projection and a Table for my configuration. Both make the query a little more complicated, since now I have to get the configuration from a new table. But both will help make my final calculation, when will my wife hit her testing hours, easier. And we can tweak parameters until we’ve run through all the scenarios we want. Even generating a little rails app where she could tweak the parameters herself without wading through SQL code shouldn’t be too hard. If I need to change the projection, I can change the view. If we need more parameters, I can tweak the table.
It wasn’t my initial design, but CTEs helped me find my way to a more powerful solution.