Measures in SQL

TL;DR

This work introduces measures, a new SQL extension that attaches a context-sensitive expression to a table to enable reusable, composable calculations while preserving SQL semantics and closure. By defining context-sensitive expressions and an evaluation context, the approach allows measurements to be expanded into traditional SQL via a planning-time rewrite, avoiding self-joins and improving clarity and AI-friendly query generation. Key contributions include the AS MEASURE construct, the AT modifier for context changes, and a formal separation between measure definitions and usages, enabling safe, modular analytics and secure views. The approach unifies relational and multidimensional analytics within SQL, with practical implications for BI tooling (e.g., Looker), window-like computations, and natural-language query generation, while outlining significant directions for formal semantics, implementation strategies, and time-series analytics.

Abstract

SQL has attained widespread adoption, but Business Intelligence tools still use their own higher level languages based upon a multidimensional paradigm. Composable calculations are what is missing from SQL, and we propose a new kind of column, called a measure, that attaches a calculation to a table. Like regular tables, tables with measures are composable and closed when used in queries. SQL-with-measures has the power, conciseness and reusability of multidimensional languages but retains SQL semantics. Measure invocations can be expanded in place to simple, clear SQL. To define the evaluation semantics for measures, we introduce context-sensitive expressions (a way to evaluate multidimensional expressions that is consistent with existing SQL semantics), a concept called evaluation context, and several operations for setting and modifying the evaluation context.