ClickBench — URL Length Analysis

ClickBench benchmark · all ClickBench queries

Average URL length by counter with HAVING > 100000

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  CounterID,
  AVG(LENGTH(URL)) AS l,
  COUNT(*) AS c
FROM hits
WHERE
  URL <> ''
GROUP BY
  CounterID
HAVING
  COUNT(*) > 100000
ORDER BY
  l DESC
LIMIT 25

Representative DataFrame

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

def q28_expression_impl(ctx: DataFrameContext) -> Any:
    col, lit = ctx.col, ctx.lit
    return (
        _hits(ctx)
        .filter(col("URL") != lit(""))
        .with_columns(col("URL").str.len_chars().alias("url_len"))
        .group_by("CounterID")
        .agg(col("url_len").mean().alias("l"), col("CounterID").count().alias("c"))
        .filter(col("c") > lit(100000))
        .sort("l", descending=True)
        .limit(25)
    )

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 ClickBench

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('clickbench', 'Q28', family="expression").source)  # or family="pandas"

Source

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