Window functions, DISTINCT, and set operations land in R2 SQL

R2 SQL now supports window functions, SELECT DISTINCT, set operations, and additional aggregates, making it easier to write analytical queries without preprocessing your data elsewhere.

R2 SQL is Cloudflare's serverless, distributed SQL engine for querying Apache Iceberg tables stored in R2 Data Catalog.

New capabilities

• Window functions — ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and aggregates with an OVER (...) clause, including PARTITION BY and explicit frames
• QUALIFY — filter rows based on a window function result
• DISTINCT — SELECT DISTINCT, DISTINCT ON (...), and the DISTINCT modifier on aggregates such as COUNT(DISTINCT ...)
• Set operations — UNION, UNION ALL, INTERSECT, and EXCEPT
• Grouping extensions — GROUPING SETS, ROLLUP, and CUBE
• Exact aggregates — MEDIAN, PERCENTILE_CONT, ARRAY_AGG, and STRING_AGG

Examples

Rank rows with a window function

<div><div><span>SELECT</span><span> customer_id, region,</span></div></div><div><div><span>       </span><span>ROW_NUMBER</span><span>() </span><span>OVER</span><span> (</span><span>PARTITION</span><span> </span><span>BY</span><span> region </span><span>ORDER BY</span><span> total_amount </span><span>DESC</span><span>) </span><span>AS</span><span> rank_in_region</span></div></div><div><div><span>FROM</span><span> my_namespace.sales_data</span></div></div>

Filter with QUALIFY

<div><div><span>SELECT</span><span> customer_id, region, total_amount</span></div></div><div><div><span>FROM</span><span> my_namespace.sales_data</span></div></div><div><div><span>QUALIFY </span><span>ROW_NUMBER</span><span>() </span><span>OVER</span><span> (</span><span>PARTITION</span><span> </span><span>BY</span><span> region </span><span>ORDER BY</span><span> total_amount </span><span>DESC</span><span>) </span><span><=</span><span> </span><span>3</span></div></div>

Combine tables with a set operation

<div><div><span>SELECT</span><span> customer_id </span><span>FROM</span><span> my_namespace.sales_data</span></div></div><div><div><span>EXCEPT</span></div></div><div><div><span>SELECT</span><span> customer_id </span><span>FROM</span><span> my_namespace.archived_sales</span></div></div>

The named WINDOW clause is not supported — inline the OVER (...) specification at each call site. For the full syntax reference, refer to the SQL reference. For supported features and performance guidance, refer to Limitations and best practices.