4 ms·
I consider myself a fan of SQL, but my nitpick is more around named calculated columns. For instance: SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
by wenc 6y ago
I consider myself a fan of SQL, but my nitpick is more around named calculated columns. For instance:
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE AVG(col1) OVER (PARTITION BY col2) > 10.0
I wish I could just do:
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
WHERE partcol1 > 10.0
But I can't, because the WHERE clause is processed before the SELECT clause.
So if I have a bunch of these and want a convenient way to work with the named columns, I have to wrap them into a common table expression:
WITH cte AS (
SELECT AVG(col1) OVER (PARTITION BY col2) AS partcol1
FROM tbl
)
SELECT partcol1
FROM cte
WHERE partcol1 > 10.0
So wordy.
- SPBS 6y agoYeah it's mentioned in this blog post https://blog.jooq.org/2014/01/06/the-sql-languages-most-missing-feature/ https://blog.jooq.org/2014/01/06/the-sql-languages-most-miss... that SQL is missing Common Column Expressions, and that using Common Table Expressions reuse columns is simply a band-aid fix over the lack of those. For example, your example would have been more naturally expressed as SELECT partcol1 FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2)) WHERE partcol1 > 10.0
- wenc 6y agoWow thanks. I've never heard it expressed as a Common Column Expression. It makes sense to include it in the FROM statement because that's usually parsed and executed first.