JA EN
shiichan

R2 SQL Now Speaks Window Functions, DISTINCT, and Set Operations!

Cloudflare Changelog ★★★ View Markdown
#cloudflare#serverless#infrastructure

Hi everyone, it's me, Shiichan! Today R2 SQL got an update that analytics fans are going to love.

Cloudflare Changelog developers.cloudflare.com

What was announced?

According to Cloudflare's Changelog, the serverless SQL engine R2 SQL now supports window functions, DISTINCT, and set operations. You can write these analytical queries directly against Apache Iceberg tables, with no external preprocessing.

The story so far

R2 SQL could already query Iceberg data, but going a step further — ranking rows, dropping duplicates, or combining and subtracting two result sets — often meant reaching for another tool or a preprocessing step. With this update, those bread-and-butter queries now run entirely inside R2 SQL.

What changes

Aggregations like a per-region sales ranking now fit into a single SQL statement. Here's a window function example:

SELECT customer_id, region,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rank_in_region
FROM my_namespace.sales_data

When you want the difference between two tables, the EXCEPT set operation comes in handy:

SELECT customer_id FROM my_namespace.sales_data
EXCEPT
SELECT customer_id FROM my_namespace.archived_sales

Dive Deep

Here's everything that landed:

  • Window functions: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
  • The QUALIFY clause for filtering window function results
  • SELECT DISTINCT and DISTINCT ON (...) syntax
  • Set operations: UNION, UNION ALL, INTERSECT, EXCEPT
  • Grouping extensions: GROUPING SETS, ROLLUP, CUBE
  • Exact aggregates: MEDIAN, PERCENTILE_CONT, ARRAY_AGG, STRING_AGG

The full syntax is covered in the SQL reference, and it's worth reading the limitations and best practices before you write your queries. For where the data lives, pair it with the R2 Data Catalog.

Wrap-up

  • R2 SQL now supports window functions, DISTINCT, and set operations
  • QUALIFY, GROUPING SETS, ROLLUP, CUBE, and exact aggregates like MEDIAN were added too
  • You can write analytical queries directly against Apache Iceberg tables with no preprocessing

If you keep data in R2 and want to analyze it in place — or trim down external transformation pipelines — this one's for you.

Related posts