Read Primitives — OLAP CUBE Analysis

Read Primitives benchmark · all Read Primitives queries

CUBE operation for multidimensional analysis

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  n.n_name AS nation,
  r.r_name AS region,
  EXTRACT(YEAR FROM o.o_orderdate) AS order_year,
  EXTRACT(QUARTER FROM o.o_orderdate) AS order_quarter,
  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)
  AND o.o_orderdate < CAST('1997-01-01' AS DATE)
GROUP BY
  CUBE (
    n.n_name,
    r.r_name,
    EXTRACT(YEAR FROM o.o_orderdate),
    EXTRACT(QUARTER FROM o.o_orderdate)
  )

Representative DataFrame

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

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

    import itertools

    import polars as pl

    del col, lit
    joined = (
        orders.native.filter((pl.col("o_orderdate") >= date(1995, 1, 1)) & (pl.col("o_orderdate") < date(1997, 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("n_name").alias("nation"),
            pl.col("r_name").alias("region"),
            pl.col("o_orderdate").dt.year().cast(pl.Int64).alias("order_year"),
            pl.col("o_orderdate").dt.quarter().cast(pl.Int64).alias("order_quarter"),
        )
    )
    dims = ["nation", "region", "order_year", "order_quarter"]
    measures = [
        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):
        for grp in itertools.combinations(dims, k):
            sub = joined.group_by(list(grp)).agg(*measures) if grp else joined.select(*measures)
            for dim in dims:
                if dim not in grp:
                    dtype = pl.Int64 if dim in ("order_year", "order_quarter") else pl.Utf8
                    sub = sub.with_columns(pl.lit(None, dtype=dtype).alias(dim))
            frames.append(sub.select(*dims, "order_count", "total_revenue", "avg_order_value"))
    return pl.concat(frames, how="vertical_relaxed")

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_cube_analysis'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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