Measures in XTDB

[refset]

I was reminded yesterday (after seeing this DuckDB post) of this work/paper: "Measures in SQL" https://arxiv.org/pdf/2406.00251v1 - a way of extending SQL to improve its ability to serve as a 'semantic layer' without requiring the use of a templating engine on top of SQL (e.g. dbt)

I just had a quick play around and managed to demonstrate that XTQL's with operator effectively solves this already:

-- anonymous, duplicated expressions ...not ideal
SELECT region,
       SUM(sales - cost) AS total_profit,
       AVG((sales - cost) / sales) AS avg_margin
FROM orders
GROUP BY region;

vs.

-- compositional, but without first-class measures, resulting in a mess of subqueries
SELECT region,
       SUM(profit) AS total_profit,
       AVG(margin) AS avg_margin
FROM (
  SELECT (sales - cost) AS profit,
         ((sales - cost) / sales) AS margin,
         region
  FROM orders
) AS measures
GROUP BY region;

vs.

;; XTQL
(-> (from :orders [*])
    (with {:profit (- sales cost)})
    (with {:margin (/ profit sales)})
    (aggregate region
               {:total-profit (sum profit)
                :avg-margin   (avg margin)}))

[Play]

i.e. a whole library of measure definitions can be trivially added to a query (assuming they're topologically sorted) as a series of with operators, whilst keeping things easy to read

Does that seem useful?

[jarohen]

Yes - and also in XT's 'pipeline SQL':

FROM orders
SELECT region, sales, sales - cost AS profit
SELECT *, profit / sales as margin
SELECT region,
       SUM(profit) AS total_profit,
       AVG(margin) AS avg_margin