Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

CTEs as lookup tables

misfra.me

Post reply on HN
Print view View original

31–40 of 115 posts

Re: CTEs as lookup tables

#31

Post by rco8786 »

charlie0 wrote: post #27

Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL. In what world is this WITH countries (code, name) AS ( ...> SELECT \* FROM (VALUES ...> ('us', 'United States'), ('fr', 'France'), ('in', 'India') ...> ) AS codes ...> ) ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; easier to read than this SELECT code…

Yea this is a common problem with contrived code samples like these. In my experience the author is right in that real world examples of using CASE can often get out of hand - especially if you need it in more than one place. But the contrived, simple example is clearly easier than the CTE.

It is perhaps not surprising that we end up with this sort of stuff in production code, because the original author only needed one simple CASE statement and then it organically grew from there.

Re: CTEs as lookup tables

#32

Post by preetamjinka »

mcdonje wrote: post #29
pophenat wrote: post #19

Well, if you have multiple queries, you'd still have to write the cte multiple times. You could also use views for the same thing, and those are much more reusable.

In that case, you might as well use tables. But a lot of people don't have write access for either tables or views.

Yeah. In many cases I've had to use CTEs like this in BI tools. When you're experimenting with datasets for dashboards it's much faster to work with CTEs than to try to make production DB changes.

Re: CTEs as lookup tables

#34

Post by pkoird »

drx wrote: post #4

CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as: with source as ( select * from wherever ), transformed as ( ... ), joined as ( ... ), final as ( ... ) select * from final You can switch `final` to `transformed` to see what the query is doing internally. Almost like having good control flow. Almost.

Almost seems like a procedural syntax like

source = ...

transformed = ...

joined = ...

final = ...

Re: CTEs as lookup tables

#35

Post by VincentEvans »

For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE.

‘’’ WITH CTE AS ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SELECT * FROM CTE ‘’’

… likewise you can also do that in a subquery that you can immediately join:

‘’’ SELECT * FROM ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SUB INNER JOIN AnotherTable AT ON SUB.code = AT.code ‘’’

Re: CTEs as lookup tables

#36

Post by Izkata »

sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to calculate "b", here's how to do it with CTEs:

  CREATE TABLE vals (x, y);
  INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0);
  
  WITH
     m(v) AS (
        SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2)))
        FROM vals
     ),
     b(v) AS (
        SELECT (SUM(y) - (m.v * SUM(x))) / COUNT(*)
        FROM vals JOIN m
     )
  SELECT
     x AS real_x,
     y AS real_y,
     m.v AS m,
     b.v AS b,
     x * m.v + b.v AS interp_y
  FROM vals JOIN m JOIN b;

  real_x  real_y  m      b     interp_y           
  ------  ------  -----  ----  -------------------
  1       1       -0.24  1.12  0.88               
  2       0.5     -0.24  1.12  0.64               
  3       0.4     -0.24  1.12  0.4                
  4       0.1     -0.24  1.12  0.16               
  5       0       -0.24  1.12  -0.0800000000000001
If all you want is "m" and "b" the final query can just be "FROM m JOIN b" and the result will be 1 row.

Re: CTEs as lookup tables

#37

Post by hackernewds »

drx wrote: post #4

CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as: with source as ( select * from wherever ), transformed as ( ... ), joined as ( ... ), final as ( ... ) select * from final You can switch `final` to `transformed` to see what the query is doing internally. Almost like having good control flow. Almost.

I prefer actually materializing the tables so then I can check the output for what the transform tables and the joined tables look like.

generally can't just swap transformed in final because final depends on the output of transformed?

Re: CTEs as lookup tables

#38

Post by garfij »

hobs wrote: post #21
pcblues wrote: post #17

Removing CTEs from your codebase by replacing them with the creation of a temp table and then using separate queries with as few joins as possible to populate it will give you low-locking performance hundreds of times better nearly every time. No problem with readability.

I find that anyone who praises chaining CTEs has not dealt with how terrible they can become, each sub table being able to take on any dependency in the parent tables is not a feature of going fast.

I've used CTEs fairly extensively so I'm generally familiar with their pitfalls, but I don't quite grok your point here. Can you go into a little more detail about what you mean by "take on any dependency in the parent tables"?

Re: CTEs as lookup tables

#39

Post by reactivenz »

VincentEvans wrote: post #35

For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE. ‘’’ WITH CTE AS ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SELECT * FROM CTE ‘’’ … likewise you can also do that in a subquery that you can immediately join: ‘’’ SELECT * FROM ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SUB INNER JOIN A…

those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form

  WITH my_cte AS (
    SELECT \* FROM VALUES
       (1, 'column 2 value', 3.0),
       (2, 'column 2 value', 3.0),
       (3, 'column 2 value', 3.0),
       (4, 'column 2 value', 3.0)
  )
you can often alias the VALUES values like:

  WITH my_cte AS (
    SELECT \* FROM VALUES
       (1, 'column 2 value', 3.0),
       (2, 'column 2 value', 3.0),
       (3, 'column 2 value', 3.0),
       (4, 'column 2 value', 3.0)
        as t(col1_name, col2_name, col3_name)
   )
and some DB's allow you to alias via the cte name:

  WITH my_cte(col1_name, col2_name, col3_name) AS (
    SELECT \* FROM VALUES
       (1, 'column 2 value', 3.0),
       (2, 'column 2 value', 3.0),
       (3, 'column 2 value', 3.0),
       (4, 'column 2 value', 3.0)
  )

Re: CTEs as lookup tables

#40

Post by garfij »

jbrwn wrote: post #14
fphhotchips wrote: post #10

Fun fact, this can also be more performant, depending on the engine.

CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.

My understanding was that Postgres fixed this back in version 12. Are there still limitations here?
Post reply on HN