PARTITION BY

Overview

PARTITION BY is the sub-clause inside a window function's OVER (...) that splits the input into independent groups. Unlike GROUP BY, which collapses each group into a single output row, PARTITION BY keeps every input row intact — the window function is simply computed independently within each partition.

Copy code
SELECT
  region,
  salesperson,
  revenue,
  SUM(revenue) OVER (PARTITION BY region) AS region_total,
  revenue / SUM(revenue) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Here every row keeps its own salesperson and revenue, while region_total restarts for each region.

PARTITION BY vs GROUP BY

• GROUP BY region returns one row per region with aggregated values.
• PARTITION BY region (inside OVER) returns one row per input row, annotated with a per-region aggregate.

You can combine both: GROUP BY to aggregate a base query, then window functions with their own PARTITION BY over the aggregated rows.

DuckDB specifics

Beyond window functions, DuckDB reuses the same PARTITION BY keyword for the COPY ... TO statement, which writes Hive-partitioned files to disk or object storage — a genuinely different but related concept (physical partitioning rather than logical grouping):

Copy code
COPY (SELECT * FROM sales)
TO 's3://my-bucket/sales'
(FORMAT parquet, PARTITION_BY (region, year));

This produces a directory structure like sales/region=EMEA/year=2026/*.parquet, which query engines (including DuckDB itself via read_parquet with hive partitioning enabled) can prune on read.