15 ms·

The SQL filter clause: selective aggregates

138 points by MarkusWinand 10y ago

MichaelBurge 10y ago
At an old job, me and another person wrote an in-house SQL-like dialect that compiled to access our C++ data structures. It was kind of nice just throwing whatever junk you wanted into your SQL without having to worry about any standards. Want a filter clause? Got it. Need a weighted distinct count? Now it's part of the language. Also, you can rephrase a lot of these SQL features as subqueries. It's surprising how many database bugs you can find when you do it. Not so much in Postgres, but I probably found a dozen in Redshift. I mean rephrasing this: select sum(x) filter (where x < 5), sum(x) filter (where x < 7) from generate_series(1,10,1) s(x) ; as this(where t is either a permanent table, view, or CTE as appropriate): with t as ( select * from generate_series(1,10,1) s(x) ) select (select sum(x) from t where x < 5), (select sum(x) from t where x < 7) ; Though for Postgres the filter will likely have better performance.
- goldenkey 10y ago
  Arent subselects better performing than comparable joins and aggregates when the query optimizer screws up? I remember reading an HN post not too long ago.
  - MichaelBurge 10y ago
    If you're talking specifically about the comment at the end, that was because the example query will do a seq scan on the table twice. The filter equivalent will only do one scan. I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy. Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).
- brudgers 10y ago
  Depending on the number of years ago, it may have been ahead of its time. C# and Scala have SQL like language abstractions a few years ago via LINQ. Scala has something similar.
  - duaneb 10y ago
    Scala doesn't have anything like this where the query is compiled monadically before execution, although the data structures do expose excellent ways to chain functional combinators. Squeryl (and there's a similar competitor) implement this for SQL, and I'd say it's 80% there--still a lot of rough edges fitting dynamic queries (e.g. number of tables joinable in a single statement) into a static language.
    - rm999 10y ago
      Would Catalyst in Spark SQL count? http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_sql.pdf http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_s...
    - virtualwhys 10y ago
      > Scala doesn't have anything like this where the query is compiled monadically before execution Nonsense, Scala is arguably leading the way in the statically typed composable query dsl department; see Slick [1] and Quill [2] among others. Outside of Haskell's Esqueleto I am not aware of any statically typed query dsl that comes remotely close to the aforementioned. LINQ to SQL/Objects provides static query generation but doesn't compose; beyond that, what is there? [1] https://github.com/slick/slick https://github.com/slick/slick [2] https://github.com/getquill/quill https://github.com/getquill/quill
      - Arnavion 10y ago
        >LINQ to SQL/Objects provides static query generation but doesn't compose How do you mean? The result of a LINQ query is a sequence that can be used as the input of another LINQ query.
- snissn 10y ago
  Could you give a quick sketch of how you designed your sql parser (traspiler?)? Thank you!
Xophmeister 10y ago
Syntactic sugar that's only natively supported in PostgreSQL? The site's banner says, "A lot has changed since SQL-92". Be that as it may, but it seems no one has really bothered catching up. I wonder why that is... My guess is that such extensions, while useful, are somewhat marginalised features in terms of usage. Thus, no one ever learns them formally and just Googles for what they need -- if it comes up -- and get the CASE solution, in this case (pun unintentional). Hence perpetuating that pattern. Also, of course, the CASE solution is a lot more powerful as the returned expression, that gets fed into the aggregate function, can be basically anything.
- MarkusWinand 10y ago
  I see the value of FILTER mostly in ease to read and understand. Many things — e.g., Pivot — can be understood way more easily using FILTER rather than CASE.
- driusan 10y ago
  I think the most well-thought-out explanation I've seen for why widespread support for the SQL standard seemed to stop after SQL-92 is this one: http://www.wiscorp.com/is_sql_a_real_standard.pdf http://www.wiscorp.com/is_sql_a_real_standard.pdf
  - Xophmeister 10y ago
    This was quite interesting; thank you for posting... I may be cheeky and submit it to HN.
    - driusan 10y ago
      I'm fairly sure I originally saw it on HN.
- baq 10y ago
  and in js transpilers are bread and butter tech. sql isn't as cool i guess.
codegeek 10y ago
"with" is not supported by mysql but sqlite supports it ? wow, didn't know that.
- goldenkey 10y ago
  You can still do many subselect queries without with. Its really only an issue with the more complex usages.
  - Xophmeister 10y ago
    I personally find CTEs much easier to read than subqueries.
    - wesd 10y ago
      and you can use them in multiple places. I'm not sure if you have the same subquery twice if the query optimizer uses a single dataset.
      - deleted 10y ago
        [deleted]
    - deleted 10y ago
      [deleted]
    - goldenkey 10y ago
      Be careful though, above poster mentions that a chain of CTE is almost certain to lead to sequential scans rather than parallelized (optimized) execution.
d33 10y ago
It would be nice to see an example for this kind of query. BTW, SQL seems like a terrible language. For example, why would it need to enforce ordering for keywords like ORDER BY, WHERE, GROUP BY? So many times I had made a mistake that boiled down to putting them in the correct order...
- MichaelBurge 10y ago
  One reason is to remind you of the SQL order-of-operations: * The from clause retrieves records from data sources like set functions, tables, or views. * The on/using clause joins tables * The where clause filters out records * The group by clause assigns records to buckets * The having clause filters out records * The select clause runs * The order by sorts * The limit clause picks records In this query: > select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true order by 1 limit 1; The only out-of-place component is the select and window function(which runs after the group by clause). In a more complex query with multiple selects in scope, the order of terms helps remind you which parts are running in which order. Note that the optimizer won't strictly enforce the order; it's just a guide to interpret what the results should be. If you need to do something out-of-order, you can use a subselect: > select * from (select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true limit 1 ) x order by 1; Here, I've swapped the limit clause and the order-by clause, although you can still see the order of operations in the explain plan being separated by a 'Subquery scan'.
  - kbenson 10y ago
    > The having clause filters out records I think you mean "The having clause filters out buckets", right?
- return0 10y ago
  > SQL seems like a terrible language. The reason you give is too trivial to justify calling it "a terrible language"
  - 10y ago
ibejoeb 10y ago
For those on Oracle or MS SQL, be aware of the PIVOT and UNPIVOT features. It's not as general as this, but it's terse and works well for the case, i.e., running an aggregate across specific buckets.
westurner 10y ago
You can do these with Ibis and various SQL engines: * http://docs.ibis-project.org/sql.html#aggregates-considering-table-subsets http://docs.ibis-project.org/sql.html#aggregates-considering... * https://github.com/cloudera/ibis/tree/master/ibis/sql https://github.com/cloudera/ibis/tree/master/ibis/sql (PostgreSQL, Presto, Redshift, SQLite, Vertical) * https://github.com/cloudera/ibis/blob/master/ibis/sql/alchemy.py https://github.com/cloudera/ibis/blob/master/ibis/sql/alchem... (SQLAlchemy)
ams6110 10y ago
If I'm understanding this correctly, you can also do this sort of thing as a UNION of aggregate queries, each one with its own WHERE clause corresponding to the desired FILTER.
rubyfan 10y ago
Is this all that different from Hive's analytic functions? I don't think I can put a WHERE filter right in the OVER clause though. SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T; https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics https://cwiki.apache.org/confluence/display/Hive/LanguageMan...

138 points by MarkusWinand 10y ago

At an old job, me and another person wrote an in-house SQL-like dialect that compiled to access our C++ data structures. It was kind of nice just throwing whatever junk you wanted into your SQL without having to worry about any standards. Want a filter clause? Got it. Need a weighted distinct count? Now it's part of the language. Also, you can rephrase a lot of these SQL features as subqueries. It's surprising how many database bugs you can find when you do it. Not so much in Postgres, but I probably found a dozen in Redshift. I mean rephrasing this: select sum(x) filter (where x < 5), sum(x) filter (where x < 7) from generate_series(1,10,1) s(x) ; as this(where t is either a permanent table, view, or CTE as appropriate): with t as ( select * from generate_series(1,10,1) s(x) ) select (select sum(x) from t where x < 5), (select sum(x) from t where x < 7) ; Though for Postgres the filter will likely have better performance.

goldenkey 10y ago
Arent subselects better performing than comparable joins and aggregates when the query optimizer screws up? I remember reading an HN post not too long ago.
- MichaelBurge 10y ago
  If you're talking specifically about the comment at the end, that was because the example query will do a seq scan on the table twice. The filter equivalent will only do one scan. I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy. Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).
brudgers 10y ago
Depending on the number of years ago, it may have been ahead of its time. C# and Scala have SQL like language abstractions a few years ago via LINQ. Scala has something similar.
- duaneb 10y ago
  Scala doesn't have anything like this where the query is compiled monadically before execution, although the data structures do expose excellent ways to chain functional combinators. Squeryl (and there's a similar competitor) implement this for SQL, and I'd say it's 80% there--still a lot of rough edges fitting dynamic queries (e.g. number of tables joinable in a single statement) into a static language.
  - rm999 10y ago
    Would Catalyst in Spark SQL count? http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_sql.pdf http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_s...
  - virtualwhys 10y ago
    > Scala doesn't have anything like this where the query is compiled monadically before execution Nonsense, Scala is arguably leading the way in the statically typed composable query dsl department; see Slick [1] and Quill [2] among others. Outside of Haskell's Esqueleto I am not aware of any statically typed query dsl that comes remotely close to the aforementioned. LINQ to SQL/Objects provides static query generation but doesn't compose; beyond that, what is there? [1] https://github.com/slick/slick https://github.com/slick/slick [2] https://github.com/getquill/quill https://github.com/getquill/quill
    - Arnavion 10y ago
      >LINQ to SQL/Objects provides static query generation but doesn't compose How do you mean? The result of a LINQ query is a sequence that can be used as the input of another LINQ query.
snissn 10y ago
Could you give a quick sketch of how you designed your sql parser (traspiler?)? Thank you!

Xophmeister 10y ago

Syntactic sugar that's only natively supported in PostgreSQL? The site's banner says, "A lot has changed since SQL-92". Be that as it may, but it seems no one has really bothered catching up. I wonder why that is... My guess is that such extensions, while useful, are somewhat marginalised features in terms of usage. Thus, no one ever learns them formally and just Googles for what they need -- if it comes up -- and get the CASE solution, in this case (pun unintentional). Hence perpetuating that pattern. Also, of course, the CASE solution is a lot more powerful as the returned expression, that gets fed into the aggregate function, can be basically anything.

MarkusWinand 10y ago
I see the value of FILTER mostly in ease to read and understand. Many things — e.g., Pivot — can be understood way more easily using FILTER rather than CASE.
driusan 10y ago
I think the most well-thought-out explanation I've seen for why widespread support for the SQL standard seemed to stop after SQL-92 is this one: http://www.wiscorp.com/is_sql_a_real_standard.pdf http://www.wiscorp.com/is_sql_a_real_standard.pdf
- Xophmeister 10y ago
  This was quite interesting; thank you for posting... I may be cheeky and submit it to HN.
  - driusan 10y ago
    I'm fairly sure I originally saw it on HN.
baq 10y ago
and in js transpilers are bread and butter tech. sql isn't as cool i guess.

codegeek 10y ago

"with" is not supported by mysql but sqlite supports it ? wow, didn't know that.

goldenkey 10y ago
You can still do many subselect queries without with. Its really only an issue with the more complex usages.
- Xophmeister 10y ago
  I personally find CTEs much easier to read than subqueries.
  - wesd 10y ago
    and you can use them in multiple places. I'm not sure if you have the same subquery twice if the query optimizer uses a single dataset.
    - deleted 10y ago
      [deleted]
  - deleted 10y ago
    [deleted]
  - goldenkey 10y ago
    Be careful though, above poster mentions that a chain of CTE is almost certain to lead to sequential scans rather than parallelized (optimized) execution.

d33 10y ago

It would be nice to see an example for this kind of query. BTW, SQL seems like a terrible language. For example, why would it need to enforce ordering for keywords like ORDER BY, WHERE, GROUP BY? So many times I had made a mistake that boiled down to putting them in the correct order...

MichaelBurge 10y ago
One reason is to remind you of the SQL order-of-operations: * The from clause retrieves records from data sources like set functions, tables, or views. * The on/using clause joins tables * The where clause filters out records * The group by clause assigns records to buckets * The having clause filters out records * The select clause runs * The order by sorts * The limit clause picks records In this query: > select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true order by 1 limit 1; The only out-of-place component is the select and window function(which runs after the group by clause). In a more complex query with multiple selects in scope, the order of terms helps remind you which parts are running in which order. Note that the optimizer won't strictly enforce the order; it's just a guide to interpret what the results should be. If you need to do something out-of-order, you can use a subselect: > select * from (select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true limit 1 ) x order by 1; Here, I've swapped the limit clause and the order-by clause, although you can still see the order of operations in the explain plan being separated by a 'Subquery scan'.
- kbenson 10y ago
  > The having clause filters out records I think you mean "The having clause filters out buckets", right?
return0 10y ago
> SQL seems like a terrible language. The reason you give is too trivial to justify calling it "a terrible language"
- 10y ago

ibejoeb 10y ago

For those on Oracle or MS SQL, be aware of the PIVOT and UNPIVOT features. It's not as general as this, but it's terse and works well for the case, i.e., running an aggregate across specific buckets.

westurner 10y ago

You can do these with Ibis and various SQL engines: * http://docs.ibis-project.org/sql.html#aggregates-considering-table-subsets http://docs.ibis-project.org/sql.html#aggregates-considering... * https://github.com/cloudera/ibis/tree/master/ibis/sql https://github.com/cloudera/ibis/tree/master/ibis/sql (PostgreSQL, Presto, Redshift, SQLite, Vertical) * https://github.com/cloudera/ibis/blob/master/ibis/sql/alchemy.py https://github.com/cloudera/ibis/blob/master/ibis/sql/alchem... (SQLAlchemy)

ams6110 10y ago

If I'm understanding this correctly, you can also do this sort of thing as a UNION of aggregate queries, each one with its own WHERE clause corresponding to the desired FILTER.

rubyfan 10y ago

Is this all that different from Hive's analytic functions? I don't think I can put a WHERE filter right in the OVER clause though. SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T; https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics https://cwiki.apache.org/confluence/display/Hive/LanguageMan...