I've found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? https://twitter.com/Ivan73965858/status/1622487080088600576 As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. … Continue reading The Performance Impact of SQL’s FILTER Clause
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: Stream.of(2, 4, 3, 1, 6, 5) .reduce((i, j) -> i * j) .ifPresent(System.out::println); // Prints 720 SQL doesn't have this, yet it would be very useful to be … Continue reading Implementing a generic REDUCE aggregate function with SQL
In my previous article, I showed what the very useful percentile functions (also known as inverse distribution functions) can be used for. Unfortunately, these functions are not ubiquitously available in SQL dialects. As of jOOQ 3.11, they are known to work in these dialects: DialectAs aggregate functionAs window functionMariaDB 10.3.3NoYesOracle 18cYesYesPostgreSQL 11YesNoSQL Server 2017NoYesTeradata 16YesNo … Continue reading How to Emulate PERCENTILE_DISC in MySQL and Other RDBMS
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom Aggregate Functions in SQL Just Like a Java 8 Stream Collector
You heard right. Up until now, the awesome window functions were a feature uniquely reserved to SQL. Even sophisticated functional programming languages still seem to lack this beautiful functionality (correct me if I'm wrong, Haskell folks). We've written tons of blog posts about window functions, evangelising them to our audience, in articles like: Probably the … Continue reading 2016 Will be the Year Remembered as When Java Finally Had Window Functions!
Some databases are awesome enough to implement the MEDIAN() aggregate function. Remember that the MEDIAN() is sligthly different from (and often more useful than) the MEAN() or AVG() (average). While the average is calculated as the SUM(exp) / COUNT(exp), the MEDIAN() tells you that 50% of all values in the sample are higher than the … Continue reading How to Emulate the MEDIAN() Aggregate Function Using Inverse Distribution Functions
Sometimes when aggregating data with SQL, we'd love to add some additional filters. For instance, consider the following world bank data: GDP per capita (current US$) 2009 2010 2011 2012 CA 40,764 47,465 51,791 52,409 DE 40,270 40,408 44,355 42,598 FR 40,488 39,448 42,578 39,759 GB 35,455 36,573 38,927 38,649 IT 35,724 34,673 36,988 33,814 … Continue reading The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions