Read Primitives — Subquery Materialization

Read Primitives benchmark · all Read Primitives queries

Complex nested aggregation with joins

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  c_mktsegment,
  AVG(order_total) AS avg_segment_order
FROM (
  SELECT
    c.c_mktsegment,
    o.o_orderkey,
    SUM(l.l_extendedprice * (
      1 - l.l_discount
    )) AS order_total
  FROM customer AS c
  JOIN orders AS o
    ON c.c_custkey = o.o_custkey
  JOIN lineitem AS l
    ON o.o_orderkey = l.l_orderkey
  GROUP BY
    c.c_mktsegment,
    o.o_orderkey
) AS subq
GROUP BY
  c_mktsegment

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def aggregation_materialize_subquery_expression_impl(ctx: DataFrameContext) -> Any:
    col, lit = ctx.col, ctx.lit
    totals = (
        ctx.get_table("customer")
        .join(ctx.get_table("orders"), left_on="c_custkey", right_on="o_custkey")
        .join(ctx.get_table("lineitem"), left_on="o_orderkey", right_on="l_orderkey")
        .group_by("c_mktsegment", "o_orderkey")
        .agg((col("l_extendedprice") * (lit(1) - col("l_discount"))).sum().alias("order_total"))
    )
    return totals.group_by("c_mktsegment").agg(col("order_total").mean().alias("avg_segment_order"))

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import ReadPrimitives

bench = ReadPrimitives(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['aggregation_materialize_subquery'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('read_primitives', 'aggregation_materialize_subquery', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/read_primitives/queries.py