# aggregate functions Published articles for aggregate functions. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Emulating SQL FILTER with Oracle JSON Aggregate Functions DevFeed: [Emulating SQL FILTER with Oracle JSON Aggregate Functions]() Original publisher: [Read original article]() Author: lukaseder Published: 2024-06-03T12:17:41Z Content type: article Language: en Sources: [jOOQ]() Topics: [Oracle Database](), [SQL](), [JSON]() Tags: [aggregate-functions](), [filter](), [filter-clause](), [jooq-3-20](), [json](), [json-aggregate-functions](), [json-arrayagg](), [json-objectagg](), [oracle](), [sql](), [sql-json]() ### AI overview This article explains how to emulate the SQL FILTER clause for JSON aggregate functions, particularly in Oracle. It presents a workaround that wraps legitimate data in arrays, filters out emulation-generated rows, and then unwraps the arrays. jOOQ 3.20 will implement these emulations. ### Source excerpt A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types ... Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions -> ## Getting Top 1 Values Per Group in Oracle DevFeed: [Getting Top 1 Values Per Group in Oracle]() Original publisher: [Read original article]() Author: lukaseder Published: 2024-03-01T09:55:41Z Content type: tutorial Language: en Sources: [jOOQ]() Topics: [ordering](), [JSON]() Tags: [aggregate-functions](), [any-value](), [function](), [json](), [keep](), [oracle](), [ordering](), [sql](), [syntax](), [top-1-per-category](), [type-safety](), [types]() ### AI overview A tutorial on selecting the top one or top n values per group in Oracle. It discusses ordering and ties, repetition when projecting multiple columns, Oracle object types and ANY_VALUE in Oracle 21c, and JSON as an alternative workaround. ### Source excerpt I've blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say ... Continue reading Getting Top 1 Values Per Group in Oracle -> ## The Performance Impact of SQL's FILTER Clause DevFeed: [The Performance Impact of SQL's FILTER Clause]() Original publisher: [Read original article]() Author: lukaseder Published: 2023-02-06T07:51:38Z Content type: article Language: en Sources: [jOOQ]() Topics: [SQL](), [PostgreSQL](), [Benchmark]() Tags: [aggregate-functions](), [benchmark](), [filter](), [filter-clause](), [performance](), [postgresql](), [sql]() ### AI overview This article benchmarks SQL FILTER syntax against equivalent CASE expressions in PostgreSQL. On the author's machine, the benchmark found an 8% performance penalty for FILTER in the tested queries, while noting that results can vary with hardware and data sets. ### Source excerpt 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? As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. This ... Continue reading The Performance Impact of SQL's FILTER Clause -> ## The Mode Ordered-Set Aggregate Function DevFeed: [The Mode Ordered-Set Aggregate Function]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-11-13T17:15:51Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [Databases](), [data]() Tags: [aggregate](), [aggregate-functions](), [analytics](), [cte](), [postgresql](), [sort](), [sql](), [statistics]() ### AI overview A tutorial explains how PostgreSQL's ordered-set aggregate function mode() WITHIN GROUP selects the most frequent genre for albums in the Chinook sample database, including ties and the use of a CTE. ### Source excerpt In our article Exploring a Data Set in SQL we discovered a data set related to music: the Chinook sample database. Our discovery led us to find albums containing tracks of multiple genres, and for the analytics we were then pursuing, we wanted to clean the data set and assign a single genre per album. We did that in SQL of course, and didn't actually edit the data. Finding the most frequent input value in a group is a job for the mode() WITHIN GROUP (ORDER BY sort_expression) Ordered-Set Aggregate Function, as documented in the PostgreSQL page about Aggregate Functions. ## Examining Postgres 9.4 - A first look DevFeed: [Examining Postgres 9.4 - A first look]() Original publisher: [Read original article]() Author: Map Published: 2014-02-02T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Caching](), [Databases](), [functions]() Tags: [aggregate-functions](), [cache](), [failover](), [pg-stat-statements](), [postgres](), [postgresql](), [read-replica]() ### AI overview A first look at PostgreSQL 9.4 while it is still in its final commit fest. The article discusses pg_prewarm for loading data into memory, concurrent materialized-view refreshes, and ordered-set aggregates, while noting that additional features may still change before release. ### Source excerpt PostgreSQL is currently entering its final commit fest. While its still going, which means there could still be more great features to come, we can start to take a look at what you can expect from it now. This release seems to bring a lot of minor increments versus some bigger highlights of previous ones. At the same time there's still a lot on the bubble that may or may not make it which could entirely change the shape of this one. For a peek back of some of the past ones: Highlights of 9.2 pg_stat_statements Index only scans JSON Support Range types Huge performance improvements Highlights of 9.3 Postgres foreign data wrapper Materialized views Checksums On to 9.4 With 9.4 instead of a simply list lets dive into a little deeper to the more noticable one. pg_prewarm I'll lead with one that those who need it should see huge gains (read larger apps that have a read replica they eventually may fail over to). Pg_prewarm will pre-warm your cache by loading data into memory. You may be interested in running pg_prewarm before bringing up a new Postgres DB or on a replica to keep it fresh. Why it matters - If you have a read replica it won't have the same cache as the leader. This can work great as you can send queries to it and it'll optimize its own cache. However, if you're using it as a failover when you do have to failover you'll be running in a degraded mode while your cache warms up. Running pg_pregwarm against it on a periodic basis will make the experience when you do failover a much better one. Refresh materialized view concurrently Materialized views just came into Postgres in 9.3. The problem with them is they were largely unusable. This was because they 1. Didn't auto-refresh and 2. When you did refresh them it would lock the table while it ran the refresh making it unreadable during that time. Materialized views are often most helpful on large reporting tables that can take some time to generate. Often such a query can take 10-30 minutes or even more to run.