R2 SQL - R2 SQL now supports window functions, DISTINCT, and set operations

<p>R2 SQL now supports window functions, <code>SELECT DISTINCT</code>, set operations, and additional aggregates, making it easier to write analytical queries without preprocessing your data elsewhere.</p> <p><a href="https://developers.cloudflare.com/r2-sql/">R2 SQL</a> is Cloudflare's serverless, distributed SQL engine for querying <a href="https://iceberg.apache.org/" target="_blank" rel="noopener">Apache Iceberg<span class="external-link"> ↗</span></a> tables stored in <a href="https://developers.cloudflare.com/r2-data-catalog/">R2 Data Catalog</a>.</p> <div tabindex="-1" class="heading-wrapper level-h4"><h4 id="new-capabilities">New capabilities</h4><a class="anchor-link" href="#new-capabilities"><span aria-hidden="true" class="anchor-icon"><svg width="16" height="16" viewBox="0 0 24 24"><path fill="currentcolor" d="m12.11 15.39-3.88 3.88a2.52 2.52 0 0 1-3.5 0 2.47 2.47 0 0 1 0-3.5l3.88-3.88a1 1 0 0 0-1.42-1.42l-3.88 3.89a4.48 4.48 0 0 0 6.33 6.33l3.89-3.88a1 1 0 1 0-1.42-1.42Zm8.58-12.08a4.49 4.49 0 0 0-6.33 0l-3.89 3.88a1 1 0 0 0 1.42 1.42l3.88-3.88a2.52 2.52 0 0 1 3.5 0 2.47 2.47 0 0 1 0 3.5l-3.88 3.88a1 1 0 1 0 1.42 1.42l3.88-3.89a4.49 4.49 0 0 0 0-6.33ZM8.83 15.17a1 1 0 0 0 1.1.22 1 1 0 0 0 .32-.22l4.92-4.92a1 1 0 0 0-1.42-1.42l-4.92 4.92a1 1 0 0 0 0 1.42Z"></path></svg></span></a></div> <ul> <li><strong>Window functions</strong> — <code>ROW_NUMBER</code>, <code>RANK</code>, <code>DENSE_RANK</code>, <code>PERCENT_RANK</code>, <code>CUME_DIST</code>, <code>NTILE</code>, <code>LAG</code>, <code>LEAD</code>, <code>FIRST_VALUE</code>, <code>LAST_VALUE</code>, <code>NTH_VALUE</code>, and aggregates with an <code>OVER (...)</code> clause, including <code>PARTITION BY</code> and explicit frames</li> <li><strong>QUALIFY</strong> — filter rows based on a window function result</li> <li><strong>DISTINCT</strong> — <code>SELECT DISTINCT</code>, <code>DISTINCT ON (...)</code>, and the <code>DISTINCT</code> modifier on aggregates such as <code>COUNT(DISTINCT ...)</code></li> <li><strong>Set operations</strong> — <code>UNION</code>, <code>UNION ALL</code>, <code>INTERSECT</code>, and <code>EXCEPT</code></li> <li><strong>Grouping extensions</strong> — <code>GROUPING SETS</code>, <code>ROLLUP</code>, and <code>CUBE</code></li> <li><strong>Exact aggregates</strong> — <code>MEDIAN</code>, <code>PERCENTILE_CONT</code>, <code>ARRAY_AGG</code>, and <code>STRING_AGG</code></li> </ul> <div tabindex="-1" class="heading-wrapper level-h4"><h4 id="examples">Examples</h4><a class="anchor-link" href="#examples"><span aria-hidden="true" class="anchor-icon"><svg width="16" height="16" viewBox="0 0 24 24"><path fill="currentcolor" d="m12.11 15.39-3.88 3.88a2.52 2.52 0 0 1-3.5 0 2.47 2.47 0 0 1 0-3.5l3.88-3.88a1 1 0 0 0-1.42-1.42l-3.88 3.89a4.48 4.48 0 0 0 6.33 6.33l3.89-3.88a1 1 0 1 0-1.42-1.42Zm8.58-12.08a4.49 4.49 0 0 0-6.33 0l-3.89 3.88a1 1 0 0 0 1.42 1.42l3.88-3.88a2.52 2.52 0 0 1 3.5 0 2.47 2.47 0 0 1 0 3.5l-3.88 3.88a1 1 0 1 0 1.42 1.42l3.88-3.89a4.49 4.49 0 0 0 0-6.33ZM8.83 15.17a1 1 0 0 0 1.1.22 1 1 0 0 0 .32-.22l4.92-4.92a1 1 0 0 0-1.42-1.42l-4.92 4.92a1 1 0 0 0 0 1.42Z"></path></svg></span></a></div> <div tabindex="-1" class="heading-wrapper level-h4"><h4 id="rank-rows-with-a-window-function">Rank rows with a window function</h4><a class="anchor-link" href="#rank-rows-with-a-window-function"><span aria-hidden="true" class="anchor-icon"><svg width="16" height="16" viewBox="0 0 24 24"><path fill="currentcolor" d="m12.11 15.39-3.88 3.88a2.52 2.52 0 0 1-3.5 0 2.47 2.47 0 0 1 0-3.5l3.88-3.88a1 1 0 0 0-1.42-1.42l-3.88 3.89a4.48 4.48 0 0 0 6.33 6.33l3.89-3.88a1 1 0 1 0-1.42-1.42Zm8.58-12.08a4.49 4.49 0 0 0-6.33 0l-3.89 3.88a1 1 0 0 0 1.42 1.42l3.88-3.88a2.52 2.52 0 0 1 3.5 0 2.47 2.47 0 0 1 0 3.5l-3.88 3.88a1 1 0 1 0 1.42 1.42l3.88-3.89a4.49 4.49 0 0 0 0-6.33ZM8.83 15.17a1 1 0 0 0 1.1.22 1 1 0 0 0 .32-.22l4.92-4.92a1 1 0 0 0-1.42-1.42l-4.92 4.92a1 1 0 0 0 0 1.42Z"></path></svg></span></a></div> <figure class="nb-code-figure" data-nb-lang="sql"><pre class="astro-code astro-code-themes github-light github-dark nb-shiki-c6xiwz" tabindex="0" data-language="sql" data-nb-lang="sql"><code><span class="line"><span class="nb-shiki-1itgoe">SELECT</span><span class="nb-shiki-140thh"> customer_id, region,</span></span> <span class="line"><span class="nb-shiki-dzsirb"> ROW_NUMBER</span><span class="nb-shiki-140thh">() </span><span class="nb-shiki-1itgoe">OVER</span><span class="nb-shiki-140thh"> (</span><span class="nb-shiki-1itgoe">PARTITION</span><span class="nb-shiki-1itgoe"> BY</span><span class="nb-shiki-140thh"> region </span><span class="nb-shiki-1itgoe">ORDER BY</span><span class="nb-shiki-140thh"> total_amount </span><span class="nb-shiki-1itgoe">DESC</span><span class="nb-shiki-140thh">) </span><span class="nb-shiki-1itgoe">AS</span><span class="nb-shiki-140thh"> rank_in_region</span></span> <span class="line"><span class="nb-shiki-1itgoe">FROM</span><span class="nb-shiki-dzsirb"> my_namespace</span><span class="nb-shiki-140thh">.</span><span class="nb-shiki-dzsirb">sales_data</span></span></code></pre></figure> <div tabindex="-1" class="heading-wrapper level-h4"><h4 id="filter-with-qualify">Filter with QUALIFY</h4><a class="anchor-link" href="#filter-with-qualify"><span aria-hidden="true" class="anchor-icon"><svg width="16" height="16" viewBox="0 0 24 24"><path fill="currentcolor" d="m12.11 15.39-3.88 3.88a2.52 2.52 0 0 1-3.5 0 2.47 2.47 0 0 1 0-3.5l3.88-3.88a1 1 0 0 0-1.42-1.42l-3.88 3.89a4.48 4.48 0 0 0 6.33 6.33l3.89-3.88a1 1 0 1 0-1.42-1.42Zm8.58-12.08a4.49 4.49 0 0 0-6.33 0l-3.89 3.88a1 1 0 0 0 1.42 1.42l3.88-3.88a2.52 2.52 0 0 1 3.5 0 2.47 2.47 0 0 1 0 3.5l-3.88 3.88a1 1 0 1 0 1.42 1.42l3.88-3.89a4.49 4.49 0 0 0 0-6.33ZM8.83 15.17a1 1 0 0 0 1.1.22 1 1 0 0 0 .32-.22l4.92-4.92a1 1 0 0 0-1.42-1.42l-4.92 4.92a1 1 0 0 0 0 1.42Z"></path></svg></span></a></div> <figure class="nb-code-figure" data-nb-lang="sql"><pre class="astro-code astro-code-themes github-light github-dark nb-shiki-c6xiwz" tabindex="0" data-language="sql" data-nb-lang="sql"><code><span class="line"><span class="nb-shiki-1itgoe">SELECT</span><span class="nb-shiki-140thh"> customer_id, region, total_amount</span></span> <span class="line"><span class="nb-shiki-1itgoe">FROM</span><span class="nb-shiki-dzsirb"> my_namespace</span><span class="nb-shiki-140thh">.</span><span class="nb-shiki-dzsirb">sales_data</span></span> <span class="line"><span class="nb-shiki-140thh">QUALIFY </span><span class="nb-shiki-dzsirb">ROW_NUMBER</span><span class="nb-shiki-140thh">() </span><span class="nb-shiki-1itgoe">OVER</span><span class="nb-shiki-140thh"> (</span><span class="nb-shiki-1itgoe">PARTITION</span><span class="nb-shiki-1itgoe"> BY</span><span class="nb-shiki-140thh"> region </span><span class="nb-shiki-1itgoe">ORDER BY</span><span class="nb-shiki-140thh"> total_amount </span><span class="nb-shiki-1itgoe">DESC</span><span class="nb-shiki-140thh">) </span><span class="nb-shiki-1itgoe">&lt;=</span><span class="nb-shiki-dzsirb"> 3</span></span></code></pre></figure> <div tabindex="-1" class="heading-wrapper level-h4"><h4 id="combine-tables-with-a-set-operation">Combine tables with a set operation</h4><a class="anchor-link" href="#combine-tables-with-a-set-operation"><span aria-hidden="true" class="anchor-icon"><svg width="16" height="16" viewBox="0 0 24 24"><path fill="currentcolor" d="m12.11 15.39-3.88 3.88a2.52 2.52 0 0 1-3.5 0 2.47 2.47 0 0 1 0-3.5l3.88-3.88a1 1 0 0 0-1.42-1.42l-3.88 3.89a4.48 4.48 0 0 0 6.33 6.33l3.89-3.88a1 1 0 1 0-1.42-1.42Zm8.58-12.08a4.49 4.49 0 0 0-6.33 0l-3.89 3.88a1 1 0 0 0 1.42 1.42l3.88-3.88a2.52 2.52 0 0 1 3.5 0 2.47 2.47 0 0 1 0 3.5l-3.88 3.88a1 1 0 1 0 1.42 1.42l3.88-3.89a4.49 4.49 0 0 0 0-6.33ZM8.83 15.17a1 1 0 0 0 1.1.22 1 1 0 0 0 .32-.22l4.92-4.92a1 1 0 0 0-1.42-1.42l-4.92 4.92a1 1 0 0 0 0 1.42Z"></path></svg></span></a></div> <figure class="nb-code-figure" data-nb-lang="sql"><pre class="astro-code astro-code-themes github-light github-dark nb-shiki-c6xiwz" tabindex="0" data-language="sql" data-nb-lang="sql"><code><span class="line"><span class="nb-shiki-1itgoe">SELECT</span><span class="nb-shiki-140thh"> customer_id </span><span class="nb-shiki-1itgoe">FROM</span><span class="nb-shiki-dzsirb"> my_namespace</span><span class="nb-shiki-140thh">.</span><span class="nb-shiki-dzsirb">sales_data</span></span> <span class="line"><span class="nb-shiki-1itgoe">EXCEPT</span></span> <span class="line"><span class="nb-shiki-1itgoe">SELECT</span><span class="nb-shiki-140thh"> customer_id </span><span class="nb-shiki-1itgoe">FROM</span><span class="nb-shiki-dzsirb"> my_namespace</span><span class="nb-shiki-140thh">.</span><span class="nb-shiki-dzsirb">archived_sales</span></span></code></pre></figure> <p>The named <code>WINDOW</code> clause is not supported — inline the <code>OVER (...)</code> specification at each call site. For the full syntax reference, refer to the <a href="https://developers.cloudflare.com/r2-sql/sql-reference/">SQL reference</a>. For supported features and performance guidance, refer to <a href="https://developers.cloudflare.com/r2-sql/reference/limitations-best-practices/">Limitations and best practices</a>.</p>