6 ms·

This feels like the perfect level of SQL abstraction. SQL is great in theory, but gets really hard too read once you throw in joins with subqueries.

• smartmic 3y ago
  Why not break these subqueries into their own common table expressions and prepend them to your join statements?
  • mulmen 3y ago
    Or their own tables or views.
    • aidos 3y ago
      Adding tables doesn’t sound like a solution at all and views come with operational overheads that you need to be aware of. CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data.
      • mulmen 3y ago
        Depends on the situation. Creating tables is a space/CPU tradeoff. Views can be managed the same way as the rest of your data pipeline. CREATE TEMPORARY VIEW and CREATE TEMPORARY TABLE are both valid SQL. > CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data. Sure but they aren't a silver bullet. Hence the "or" in my comment. I use Redshift a lot and temporary tables are useful there for defining the sort and distribution keys when those are not favorable to the current query. Think joining several demographic tables into a single customer dimension, distributing it on the customer key then filtering the product_id distributed sales and re-distributing it on customer before joining. You can't do that with a CTE. Views are handy when you have ugly CASE statements or other derived logic and need a convenience column that does something like string concatenation. I have a whole repository of them I deploy on top of the default system views for simplifying admin tasks. If you are struggling to keep your SQL clean it's because you are doing too much at once. Take a step back and re-evaluate your data model. If your physical model is bad then no number of CTEs will save you.
        • aidos 3y ago
          CTEs are basically temporary views and tables. I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data management trade-off. There are cases for duplicating data in the system but I’d leave that to indexes and generated columns unless you really need to materialise it in a different structure.
          • mulmen 3y ago
            > CTEs are basically temporary views and tables. Not in any system I have ever used. CTEs aren't materialized so they're not tables. Tables (or materialized views if you prefer) are literally space/CPU tradeoffs. I have a data pipeline that combines website logs into a table before I join it with product data. If you tried to do both in one query then the DB falls over. You seem to be coming at this from a OLTP perspective but in OLAP data "duplication" is the name of the game. But what naively looks like wasteful duplication is actually creating coherent analytical models from disparate data sources. This leads to much simpler analytical queries.
            • aidos 3y ago
              I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readability when you have joins and subqueries - which is the usecase for CTEs.
              • mulmen 3y ago
                Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.
• mulmen 3y ago
  Yes poorly written code is hard to read. That flaw is not unique to SQL.