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.<p>Want a filter clause? Got it.<p>Need a weighted distinct count? Now it's part of the language.<p>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:<p><pre><code> select
sum(x) filter (where x < 5),
sum(x) filter (where x < 7)
from
generate_series(1,10,1) s(x)
;
</code></pre>
as this(where t is either a permanent table, view, or CTE as appropriate):<p><pre><code> 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)
;
</code></pre>
Though for Postgres the filter will likely have better performance.
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...<p>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.
You can do these with Ibis and various SQL engines:<p>* <a href="http://docs.ibis-project.org/sql.html#aggregates-considering-table-subsets" rel="nofollow">http://docs.ibis-project.org/sql.html#aggregates-considering...</a><p>* <a href="https://github.com/cloudera/ibis/tree/master/ibis/sql" rel="nofollow">https://github.com/cloudera/ibis/tree/master/ibis/sql</a> (PostgreSQL, Presto, Redshift, SQLite, Vertical)<p>* <a href="https://github.com/cloudera/ibis/blob/master/ibis/sql/alchemy.py" rel="nofollow">https://github.com/cloudera/ibis/blob/master/ibis/sql/alchem...</a> (SQLAlchemy)
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.<p><i>SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;</i><p><a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics" rel="nofollow">https://cwiki.apache.org/confluence/display/Hive/LanguageMan...</a>
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.
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.
It would be nice to see an example for this kind of query.<p>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...