[
https://issues.apache.org/jira/browse/CALCITE-4496?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17283449#comment-17283449
]
Julian Hyde commented on CALCITE-4496:
--------------------------------------
Work in progress:
[julianhyde/4496-measure|https://github.com/julianhyde/calcite/tree/4496-measure].
> Measure columns ("SELECT ... AS MEASURE")
> -----------------------------------------
>
> Key: CALCITE-4496
> URL: https://issues.apache.org/jira/browse/CALCITE-4496
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> In multi-dimensional languages such as MDX, DAX, Tableau, you can define
> calculations in your models that can be re-evaluated in other dimensional
> contexts. (The models are often called cubes, and the calculations are often
> called measures.)
> In SQL, the model is a view (or a sub-query in the FROM clause) but the
> columns are just values. Suppose you have a private {{Employees}} table, a
> {{Departments}} view that rolls {{Employees}} up to department level and has
> an {{averageSalary}} column. Now suppose you wish to roll up
> {{averageSalary}} to the region level. The values that went into
> {{averageSalary}} are not available to you, either directly or indirectly, so
> the best you can do is to average-the-averages.
> In this proposed (and experimental) feature, you can define a special kind of
> column - a measure - in the SELECT list of a view (or sub-query in a FROM
> clause), and it remains a calculation. When a query uses a measure column,
> the calculation is re-evaluated in the context of that query.
> To some extent, this breaches the "black box" property of SQL views.
> Hitherto, a SQL view can be replaced with a table that has the same contents,
> and all queries that use that view will return the same results. That
> property no longer holds. But the view remains a useful "hiding" abstraction,
> and the rows that compose that view cannot be viewed directly.
> Like dimensional models, measures in SQL would allow high-level abstractions
> such as key-performance indicators (KPIs) to be shared and composed. Unlike
> dimensional models, the models remain relational, namely, it is still
> possible to enumerate and count the rows in a model.
> Consider the following view and query that uses it:
> {code:sql}
> CREATE VIEW EmpSummary AS
> SELECT deptno,
> job,
> AVG(sal) AS avg_sal,
> AVG(sal) AS MEASURE avg_sal_measure,
> COUNT(*) + 1 AS MEASURE count_plus_one_measure
> FROM Emp
> GROUP BY deptno, job;
> SELECT deptno,
> AVG(avg_sal) AS a1,
> AGGREGATE(avg_sal_measure) AS a2,
> AGGREGATE(count_plus_one_measure) AS c1
> FROM EmpSummary
> GROUP BY deptno;{code}
> Note that there is a special aggregate function, {{AGGREGATE}}, that rolls up
> measures. Columns {{a1}} and {{a2}} will contain different values; the first
> averages the averages, and the second computes the average from the raw data.
> Column {{c1}} will return the number of employees in each department plus
> one, not rolling up the "plus one" for each distinct job in the department.
> This is just a brief sketch illustrating the purpose of measures. This
> feature is experimental, the syntax will no doubt change, and much of the
> semantics (for example, what expressions are valid as measures, whether
> measures remain measures they appear in the SELECT clause of an enclosing
> query, and what is the "context" in which a measure is evaluated) need to be
> ironed out.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
