Read Primitives — OLAP ROLLUP Analysis

Read Primitives benchmark · all Read Primitives queries

ROLLUP operation for hierarchical aggregation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  r.r_name AS region,
  n.n_name AS nation,
  c.c_mktsegment AS market_segment,
  COUNT(DISTINCT c.c_custkey) AS customer_count,
  COUNT(*) AS order_count,
  SUM(o.o_totalprice) AS total_revenue,
  AVG(o.o_totalprice) AS avg_order_value
FROM orders AS o
JOIN customer AS c
  ON o.o_custkey = c.c_custkey
JOIN nation AS n
  ON c.c_nationkey = n.n_nationkey
JOIN region AS r
  ON n.n_regionkey = r.r_regionkey
WHERE
  o.o_orderdate >= CAST('1995-01-01' AS DATE)
GROUP BY
  ROLLUP (
    r.r_name,
    n.n_name,
    c.c_mktsegment
  )
ORDER BY
  r.r_name,
  n.n_name,
  c.c_mktsegment

Representative DataFrame

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

def olap_rollup_analysis_expression_impl(ctx: DataFrameContext) -> Any:
    orders, customer, nation, region = _tables(ctx, "orders", "customer", "nation", "region")
    col = ctx.col
    lit = ctx.lit

    import polars as pl

    del col, lit
    joined = (
        orders.native.filter(pl.col("o_orderdate") >= date(1995, 1, 1))
        .join(customer.native, left_on="o_custkey", right_on="c_custkey")
        .join(nation.native, left_on="c_nationkey", right_on="n_nationkey")
        .join(region.native, left_on="n_regionkey", right_on="r_regionkey")
        .with_columns(
            pl.col("r_name").alias("region"),
            pl.col("n_name").alias("nation"),
            pl.col("c_mktsegment").alias("market_segment"),
        )
    )
    dims = ["region", "nation", "market_segment"]
    measures = [
        pl.col("o_custkey").n_unique().alias("customer_count"),
        pl.len().alias("order_count"),
        pl.col("o_totalprice").sum().alias("total_revenue"),
        pl.col("o_totalprice").mean().alias("avg_order_value"),
    ]
    frames = []
    for k in range(len(dims), -1, -1):
        grp = dims[:k]
        sub = joined.group_by(grp).agg(*measures) if grp else joined.select(*measures)
        for dim in dims:
            if dim not in grp:
                sub = sub.with_columns(pl.lit(None, dtype=pl.Utf8).alias(dim))
        frames.append(sub.select(*dims, "customer_count", "order_count", "total_revenue", "avg_order_value"))
    return pl.concat(frames, how="vertical_relaxed").sort(dims, nulls_last=True)

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['olap_rollup_analysis'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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