Downloads 4.4kthis week

GitHub stars 59

Extension descriptor (YAML)

Measure-aware SQL implementing Julian Hyde's 'Measures in SQL' paper

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL yardstick FROM community;
LOAD yardstick;

Example

-- Create a view with measures
CREATE VIEW sales_v AS
SELECT
    year,
    region,
    SUM(amount) AS MEASURE revenue,
    COUNT(*) AS MEASURE order_count
FROM sales;

-- Query with AGGREGATE() and AT modifiers
SELECT
    year,
    region,
    AGGREGATE(revenue) AS revenue,
    AGGREGATE(revenue) AT (ALL region) AS year_total,
    AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL region) AS pct_of_year
FROM sales_v;

About yardstick

yardstick implements Julian Hyde's "Measures in SQL" paper (arXiv:2406.00251), adding measure-aware SQL to DuckDB.

Measures are aggregations that know how to re-aggregate themselves when the query context changes. This enables:

Percent of total calculations without CTEs or window functions:

SELECT region, AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL) AS pct
FROM sales_v;

Year-over-year comparisons with simple syntax:

SELECT year, AGGREGATE(revenue) AT (SET year = year - 1) AS prior_year
FROM sales_v;

AT Modifiers:

• AT (ALL) - Grand total across all dimensions
• AT (ALL dim) - Total excluding specific dimension
• AT (SET dim = val) - Fix dimension to specific value
• AT (SET dim = expr) - Fix dimension to expression
• AT (WHERE cond) - Pre-aggregation filter
• AT (VISIBLE) - Use query's WHERE clause

For more details, visit the extension repository.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings