For me, it's CTE's. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.
What construct do you like using on a daily basis?
13 Comments
deegeese@sopuli.xyz · 5 pts · 3y
Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.
luciole@beehaw.org · 3 pts · 3y
I believe recursive CTEs are pretty cool for tree traversal. Anytime you've got a table with a foreign key on its own primary key they might come in handy.
DarkDarkHouse@lemmy.sdf.org · 5 pts · 3y
CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.
DinosaurSr@lemmy.world · 1 pts · 3y
Same here, CTEs were a game changer for me when I learned about them
szeis4cookie@lemmy.world · 3 pts · 3y
I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it's just nicer and easier to read the intermediate step than as a subquery
key@lemmy.keychat.org · 3 pts · 3y
Subqueries in Subqueries in Subqueries
megaman1970@beehaw.org · 1 pts · 3y
Those work, but require a lot of careful structuring to get right, and can be a pain to debug. With a CTE, you can just call on the intermediate steps to trace down problems.
DarkAngelofMusic@lemmy.world · 3 pts · 3y
CTEs can be useful, particularly in PostgreSQL, where there are writable CTEs, but a lot of the time, I prefer using temp tables over CTEs, as they often perform better for larger datasets. I think one of my favorite constructs is window functions. I've found many uses for them, over the years.
megaman1970@beehaw.org · 1 pts · 3y
There was one time a good while back where I used window functions to perform edge detection in a dataset. I'll see if I can dig up that query later.
gam3@feddit.nl · 3 pts · 3y
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression
lwhjp@lemmy.sdf.org · 2 pts · 3y
Yes, CTEs are awesome. Especially when you don't force materialization and the optimizer can work its magic.
I've had a lot of fun with window functions as well.
itty53@vlemmy.net · 1 pts · 3y
You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.
Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.
TitanLaGrange@lemmy.world · 1 pts · 3y
I find the MERGE statement to be extremely useful.
Window functions I really want to like, but I don't have enough uses for them to learn them well enough to feel really comfortable with them.