2 ms·

Even just learning how to use CTEs effectively has been a major improvement for my daily work. Building up a dataset iteratively in clearly defined steps and p

Even just learning how to use CTEs effectively has been a major improvement for my daily work. Building up a dataset iteratively in clearly defined steps and performing aggregations, transformations, orderings, etc along the way is just a very clean way to work. And it's easy to short-circuit at any step for debugging if you're not getting the results you expect. I often work with datasets larger than the amount of memory on my computer so has benefits there as well instead of trying to load it all into Python memory to work with it. [0] https://www.postgresql.org/docs/9.1/queries-with.html https://www.postgresql.org/docs/9.1/queries-with.html

chubot 3y ago
Yes CTEs are nice for sure ... But for fine-grained analysis, I still would use them to do any joins I don't want to do on the client, cut down the data size, then export to a "real language" for the details IMO SQL and tidyverse in R are an ideal combo; this post compares Pandas and tidyverse: https://www.oilshell.org/blog/2018/11/30.html https://www.oilshell.org/blog/2018/11/30.html I agree that using Python for everything is suboptimal. I'm a big Python user, but for analytics I mostly skip Python, and use SQL and R