6 ms·
I mean that pattern would be a single select statement, just using the psychotic inconsistencies of normal SQL SELECT FROM WHERE GROUP BY H
by setr 2y ago
I mean that pattern would be a single select statement, just using the psychotic inconsistencies of normal SQL
SELECT
FROM
WHERE
GROUP BY
HAVING
JOIN (
SELECT
FROM
GROUP BY
)
ON
Only reason to use a CTE here would be that JOIN-SELECT is ugly, so it competes in that sense, but the usual usage of deduplication-queries-without-making-views isn’t resolved by the new syntax. So you’d still be using CTEs in either syntax.
If you imagine it in normal code, the pipe syntax is just a function call chain, and CTEs are variables. You can mix and match as needed for clarity. And you can define your CTE using the function-call chain
- tqi 2y agoSo for me, the example query in pipe syntax (up to the enrich: FROM orders |> WHERE order_date >= '2024-01-01' |> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id |> WHERE total_spent > 1000 |> INNER JOIN customers USING(customer_id) |> CALL ENRICH.APOLLO(EMAIL > customers.email) |> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country is easier to parse (for me) as something like: WITH customer_order_totals AS ( SELECT customer_id, SUM(order_amount) AS total_spent FROM orders GROUP BY 1 ) ,enriched_customers AS ( SELECT cus.customer_id, ENRICH.APOLLO(EMAIL > cus.email) enrichment FROM customer cus ) SELECT ec.country, COUNT(*) high_value_customer FROM customer_order_totals cot JOIN enriched_customers ec ON ec.customer_id = cot.customer_id WHERE cot.total_spent > 1000 GROUP BY ec.country largely because I can understand the components in isolation first, before bringing together. yes, it's more verbose, but I am of the opinion that the brevity comes at the expense of having to keep more information in my working memory.
- setr 2y agoThat’s what I mean by CTE being variable-naming. There’s nothing stopping you from using CTE’s and you’d probably make the same decision under this syntax, using CTE’s to give “names” to the partial solution And like function-call chaining, you would presumably keep it longer where the items being manipulated are “obvious” — such that giving names is more noise than it’s worth — and CTE/variables where it becomes complicated. That is, there’s not really a conflict of features, pipe syntax just lets you keep extending the SELECT. How much you stuff into a single unbroken pipe chain is a matter of taste, just as much as stuffing a normal SELECT with a series of subselects. You break it down the same way — with CTEs. It only competes with CTEs in that you’re more likely to find scenarios where you could reasonably get away without it, and still be sufficiently clear and obvious, if not more so Pipe syntax just lets you go further without having to breakdown simply due to the amount of syntax noise WITH customer_order_totals AS ( FROM orders AGGREGATE customer_id, SUM(order_amount) as total_spent GROUP BY 1 ) ,enriched_customers AS ( FROM customer cus SELECT cus.customer_id, ENRICH.APOLLO(EMAIL > cus.email) enrichment ) FROM customer_order_totals cot JOIN enriched_customers ec ON ec.customer_id = cot.customer_id WHERE cot.total_spent > 1000 AGGREGATE ec.country, COUNT(*) high_value_customer GROUP BY etc.country