ClickBench — Domain Extraction

ClickBench benchmark · all ClickBench queries

Regex domain extraction from Referer with HAVING - complex string operation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  REGEXP_REPLACE(Referer, '^https?://(?:www\.)?([^/]+)/.*$', '\\1') AS k,
  AVG(LENGTH(Referer)) AS l,
  COUNT(*) AS c,
  MIN(Referer)
FROM hits
WHERE
  Referer <> ''
GROUP BY
  k
HAVING
  COUNT(*) > 100000
ORDER BY
  l DESC
LIMIT 25

Representative DataFrame

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

def q29_expression_impl(ctx: DataFrameContext) -> Any:
    col, lit = ctx.col, ctx.lit
    return (
        _hits(ctx)
        .filter(col("Referer") != lit(""))
        .with_columns(
            col("Referer").str.replace(lit(r"^https?://(?:www\.)?([^/]+)/.*$"), lit("$1")).alias("k"),
            col("Referer").str.len_chars().alias("ref_len"),
        )
        .group_by("k")
        .agg(col("ref_len").mean().alias("l"), col("k").count().alias("c"), col("Referer").min().alias("min_referer"))
        .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['Q29'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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