Apache DataFusion SQL Query Engine
-- Basic Window
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER () AS window_basic
FROM large;
-- Sorted Window
SELECT
id1,
id2,
id3,
v2,
first_value(v2) OVER (ORDER BY id3) AS first_order_by,
row_number() OVER (ORDER BY id3) AS row_number_order_by
FROM large;
-- PARTITION BY
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER (PARTITION BY id1) AS sum_by_id1,
sum(v2) OVER (PARTITION BY id2) AS sum_by_id2,
sum(v2) OVER (PARTITION BY id3) AS sum_by_id3
FROM large;
-- PARTITION BY ORDER BY
SELECT
id1,
id2,
id3,
v2,
first_value(v2) OVER (PARTITION BY id2 ORDER BY id3) AS first_by_id2_ordered_by_id3
FROM large;
-- Lead and Lag
SELECT
id1,
id2,
id3,
v2,
first_value(v2) OVER (ORDER BY id3 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS my_lag,
first_value(v2) OVER (ORDER BY id3 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS my_lead
FROM large;
-- Moving Averages
SELECT
id1,
id2,
id3,
v2,
avg(v2) OVER (ORDER BY id3 ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) AS my_moving_average
FROM large;
-- Rolling Sum
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER (ORDER BY id3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS my_rolling_sum
FROM large;
-- RANGE BETWEEN
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER (ORDER BY v2 RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS my_range_between
FROM large;
-- First PARTITION BY ROWS BETWEEN
SELECT
id1,
id2,
id3,
v2,
first_value(v2) OVER (PARTITION BY id2 ORDER BY id3 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS my_lag_by_id2,
first_value(v2) OVER (PARTITION BY id2 ORDER BY id3 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS my_lead_by_id2
FROM large;
-- Moving Averages PARTITION BY
SELECT
id1,
id2,
id3,
v2,
avg(v2) OVER (PARTITION BY id2 ORDER BY id3 ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) AS my_moving_average_by_id2
FROM large;
-- Rolling Sum PARTITION BY
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER (PARTITION BY id2 ORDER BY id3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS my_rolling_sum_by_id2
FROM large;
-- RANGE BETWEEN PARTITION BY
SELECT
id1,
id2,
id3,
v2,
sum(v2) OVER (PARTITION BY id2 ORDER BY v2 RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS my_range_between_by_id2
FROM large;
-- Window Top-N (ROW_NUMBER top-2 per partition)
SELECT id2, largest2_v2 FROM (
SELECT id2, v2 AS largest2_v2,
ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY v2 DESC) AS order_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE order_v2 <= 2;
-- Window Top-N partition cardinality sweep (id3 % N gives N distinct partitions).
-- These exercise PartitionedTopKExec across cardinalities to validate it stays
-- competitive with the SortExec+Filter baseline as partition count grows.
-- Window Top-N: 100 partitions
SELECT pk, largest2_v2 FROM (
SELECT id3 % 100 AS pk, v2 AS largest2_v2,
ROW_NUMBER() OVER (PARTITION BY id3 % 100 ORDER BY v2 DESC) AS order_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE order_v2 <= 2;
-- Window Top-N: 1,000 partitions
SELECT pk, largest2_v2 FROM (
SELECT id3 % 1000 AS pk, v2 AS largest2_v2,
ROW_NUMBER() OVER (PARTITION BY id3 % 1000 ORDER BY v2 DESC) AS order_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE order_v2 <= 2;
-- Window Top-N: 10,000 partitions
SELECT pk, largest2_v2 FROM (
SELECT id3 % 10000 AS pk, v2 AS largest2_v2,
ROW_NUMBER() OVER (PARTITION BY id3 % 10000 ORDER BY v2 DESC) AS order_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE order_v2 <= 2;
-- Window Top-N: 100,000 partitions
SELECT pk, largest2_v2 FROM (
SELECT id3 % 100000 AS pk, v2 AS largest2_v2,
ROW_NUMBER() OVER (PARTITION BY id3 % 100000 ORDER BY v2 DESC) AS order_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE order_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~100 partitions)
-- The RANK queries below mirror the ROW_NUMBER cardinality sweep
-- above and add heavy-ties variants. RANK semantics retain boundary
-- ties (`WHERE rk <= K` may keep more than K rows per partition), so
-- this exercises PartitionedTopKRank's ties-Vec path.
SELECT pk, largest_v2 FROM (
SELECT (id3 % 100) AS pk, v2 AS largest_v2,
RANK() OVER (PARTITION BY (id3 % 100) ORDER BY v2 DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~1K partitions)
SELECT pkey, largest_v2 FROM (
SELECT (id3 % 1000) AS pkey, v2 AS largest_v2,
RANK() OVER (PARTITION BY (id3 % 1000) ORDER BY v2 DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~1K partitions, heavy ties)
-- v2 % 10 forces 10 distinct OBY values, so most rows tie at the boundary
-- and exercise PartitionedTopKRank's ties-Vec path.
SELECT pkey, largest_v2 FROM (
SELECT (id3 % 1000) AS pkey, v2 AS largest_v2,
RANK() OVER (PARTITION BY (id3 % 1000) ORDER BY (v2 % 10) DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~10K partitions, low ties)
SELECT id2, largest_v2 FROM (
SELECT id2, v2 AS largest_v2,
RANK() OVER (PARTITION BY id2 ORDER BY v2 DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~10K partitions, heavy ties)
SELECT id2, largest_v2 FROM (
SELECT id2, v2 AS largest_v2,
RANK() OVER (PARTITION BY id2 ORDER BY (v2 % 10) DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (RANK top-2 per partition, ~100K partitions)
SELECT pk, largest_v2 FROM (
SELECT (id3 % 100000) AS pk, v2 AS largest_v2,
RANK() OVER (PARTITION BY (id3 % 100000) ORDER BY v2 DESC) AS rk_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE rk_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~100 partitions)
-- The DENSE_RANK queries below mirror the RANK cardinality sweep above.
-- DENSE_RANK semantics keep every row whose ORDER BY value is among the
-- K distinct-greatest values in the partition, so total kept per partition
-- is unbounded in rows-per-distinct-value — exercises PartitionedTopKDenseRank's
-- HashMap-of-groups path.
SELECT pk, largest_v2 FROM (
SELECT (id3 % 100) AS pk, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY (id3 % 100) ORDER BY v2 DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~1K partitions)
SELECT pkey, largest_v2 FROM (
SELECT (id3 % 1000) AS pkey, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY (id3 % 1000) ORDER BY v2 DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~1K partitions, heavy ties)
-- v2 % 10 forces 10 distinct OBY values; most rows share the top-2 distinct
-- values so appends dominate — exercises the "Case A" append-to-existing-Vec
-- fast path in PartitionedTopKDenseRank.
SELECT pkey, largest_v2 FROM (
SELECT (id3 % 1000) AS pkey, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY (id3 % 1000) ORDER BY (v2 % 10) DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~10K partitions, low ties)
SELECT id2, largest_v2 FROM (
SELECT id2, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY id2 ORDER BY v2 DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~10K partitions, heavy ties)
SELECT id2, largest_v2 FROM (
SELECT id2, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY id2 ORDER BY (v2 % 10) DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;
-- Window Top-N (DENSE_RANK top-2 per partition, ~100K partitions)
SELECT pk, largest_v2 FROM (
SELECT (id3 % 100000) AS pk, v2 AS largest_v2,
DENSE_RANK() OVER (PARTITION BY (id3 % 100000) ORDER BY v2 DESC) AS dr_v2
FROM large WHERE v2 IS NOT NULL
) sub_query WHERE dr_v2 <= 2;