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

51–60 of 115 posts

Re: CTEs as lookup tables

#51

Post by acjohnson55 »

pkoird wrote: post #34
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 = ...

It doesn't seem procedural to me, as there is no rebinding. Such a sequence of assignments would look just as home in a functional language like Lisp, ML, or Haskell as Python. In procedural languages, idiomatically, you have mutation, in which variables are re-bound to new values, and side-effects, in which the external environment and the program can interact with each other in ways that are unconstrained.

Re: CTEs as lookup tables

#52

Post by iblaine »

CTEs still give me anxiety due to their inability to be potentially not be optimized. I know many database engines today optimize for this type of thing, I'm thankful for it, but there was as time 10+ years ago when CTE expressions could take down databases. I also blame DBT for convincing people that CTEs are good. CTEs are only good if used correctly.

Re: CTEs as lookup tables

#53

Post by zX41ZdbW »

ClickHouse has the `transform` function for this purpose:

    SELECT transform(id, [1, 2, 11], ['ClickHouse', 'Postgres', 'MongoDB'], '')
It creates a lookup table. When you are using the CASE operator, in simple cases it will use the `transform` function under the hood.

There are more advanced capabilities:

- The `Join` table engine. It is a pre-warmed state for joining, a hash table kept in memory.

- Dictionaries. Pre-warmed, automatically updated lookup data structures from various sources. For example, you can connect a dictionary of company names from your operational database while keeping only the ids in ClickHouse.

And, just in case, the same CTE works as in SQLite:

    WITH countries AS (
      SELECT c1 AS code, c2 AS name FROM VALUES(
        ('us', 'United States'), ('fr', 'France'), ('in', 'India')))
    SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code;
Example:

    milovidov-desktop :) WITH countries AS (
                        SELECT c1 AS code, c2 AS name FROM VALUES(
                            ('us', 'United States'), ('fr', 'France'), ('in', 'India')))
                        SELECT data.code, name FROM (SELECT 'us' AS code) AS data LEFT JOIN countries ON countries.code = data.code;

    ┌─code─┬─name──────────┐
    │ us   │ United States │
    └──────┴───────────────┘
Disclaimer: I'm working on ClickHouse.

Re: CTEs as lookup tables

#54

Post by acjohnson55 »

Izkata wrote: post #36

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))) F…

I'd love to see this in blog post form!

This seems like a case where it would be nice to be able to pass in a parameter `vals (x, y)`.

Re: CTEs as lookup tables

#55

Post by acjohnson55 »

reactivenz wrote: post #39
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, 'col…

In the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.

Re: CTEs as lookup tables

#56

Post by layer8 »

In cases like in the example given, I would also consider using a generated column (based on the CASE expression, in the example). That way the CTE doesn’t need to be repeated on each affected query — any query on the table can directly reference the derived value. Of course, this approach is only applicable for row-based values of a single table, and requires you to be able to modify the schema to add the generated column.

Re: CTEs as lookup tables

#58

Post by emodendroket »

wvenable wrote: post #44
cfeduke wrote: post #13

CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (…

I had some code from a vendor that was hanging in SQL Server and I looked at the code and they had composed everything together using smaller CTE queries and it took forever to run. They had taken various complex criteria and executed one query for each and then combined the results -- I ended up spending a day refactoring the whole thing to single SELECT with all the criteria and it ran instantly. This was for a nig…

I'm also curious how turning some of those into views would compare, but this is also probably dependent on which database.

Re: CTEs as lookup tables

#59

Post by emodendroket »

hobs wrote: post #22
bob1029 wrote: post #15

Earlier quoted context omitted.

I have strongly encouraged reckless use of CTEs throughout our product. We use in-memory instances of SQLite to evaluate any queries which would leverage CTEs. These datasets are usually small enough to reside within L1 (and certainly within L2).

In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.

Readability over optimization is often a reasonable compromise, don't you think?

Re: CTEs as lookup tables

#60

Post by emodendroket »

iblaine wrote: post #52

CTEs still give me anxiety due to their inability to be potentially not be optimized. I know many database engines today optimize for this type of thing, I'm thankful for it, but there was as time 10+ years ago when CTE expressions could take down databases. I also blame DBT for convincing people that CTEs are good. CTEs are only good if used correctly.

Well, even if you are unmoved by the readability thing, I don't think there's a non-CTE answer to recursively finding rows.
Post reply on HN