[
https://issues.apache.org/jira/browse/CALCITE-5692?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5692:
---------------------------------
Description:
In CALCITE-4496 we added measures, and the ability to evaluate a measure in a
context determined by the GROUP BY clause. In this change, we add the ability
to change that context using the AT operator.
When used as a clause of an aggregate function call, {{AT}} is similar to
{{{}FILTER{}}}. For example, in the query
{code:java}
SELECT deptno,
AVG(sal) FILTER (WHERE job <> 'MANAGER') AS a1,
AVG(sal) AT (VISIBLE WHERE job <> 'MANAGER') AS a2,
AVG(sal) AT (WHERE job <> 'MANAGER') AS a3
FROM emp
WHERE sal > 1000
GROUP BY deptno;
{code}
{{a1}} and {{a2}} always return the same value - the average salary of
non-managers who earn more than $1000. But {{a3}} (similar to {{a2}} but
missing the {{VISIBLE}} keyword) computes the average of all non-managers (not
just those earning more than $1000).
h2. Syntax
Sub-clauses of {{{}AT{}}}:
* {{VISIBLE}} adds a filter equivalent to the {{WHERE}} clause (has no effect
when used in the {{WHERE}} or {{ON}} clauses);
* {{WHERE}} adds the given predicate as a filter;
* {{CLEAR GROUP}} clears the predicate of the current group, if any (e.g.
"deptno = 10 AND job = 'ANALYST'" if we are in the SELECT clause of a query
with "GROUP BY deptno, job" evaluating the row (10, 'ANALYST');
* {{CLEAR WHERE}} clears the predicate of the {{WHERE}} clause (has no effect
when used in the {{WHERE}} or {{ON}} clauses);
* {{SET dimension = expression}} clears the predicate on {{dimension}} (if
any) and adds a filter "dimension = expression".
{{AT}} can be applied to any expression (not just aggregate functions like
{{{}FILTER{}}}). For example,
* {{avg_sal AT (CLEAR deptno)}} // applied to a measure
* {{(COUNT(comm) + 4) AT (CLEAR deptno)}} // applied to an aggregateable
expression
If the expression's syntax is not a function call or an identifier, you need to
use parentheses.
h2. Implementation
First, push down expressions, so that the left side of {{AT}} is always a
measure, and the right side is always in terms of (dimension) columns. For
example, given the query
{code:java}
SELECT deptno, (1 + AVG(sal * sal)) AT (SET YEAR(hiredate) = 1981)
FROM Emp
GROUP BY deptno {code}
we transform to the following, with a {{e2}} containing the pushed down
expressions:
{code:java}
WITH e2 AS (
SELECT *, 1 + AVG(sal * sal) AS MEASURE m, YEAR(hiredate) AS y
FROM Emp)
SELECT deptno, m AT (SET y = 1981)
FROM e2
GROUP BY deptno{code}
If {{AT}} occurs in the definition of a measure, the context is modified. For
example, in the query
{code:java}
WITH e2 AS (
SELECT *,
AVG(sal) AS MEASURE avg_sal,
avg_sal AT (SET job = 'MANAGER') AS MEASURE avg_mgr_sal
FROM Emp)
SELECT deptno, job, avg_sal, avg_sal AT (SET job = 'MANAGER'), avg_mgr_sal
FROM e2
GROUP BY deptno, job{code}
the measure {{avg_mgr_sal}} is expanded at the call site as if the user had
written {{{}avg_sal AT (SET job = 'MANAGER'){}}}.
was:
In CALCITE-4496 we added measures, and the ability to evaluate a measure in a
context determined by the GROUP BY clause. In this change, we add the ability
to change that context using the AT operator.
When used as a clause of an aggregate function call, {{AT}} is similar to
{{FILTER}}. For example, in the query
{code}
SELECT deptno,
AVG(sal) FILTER (WHERE job <> 'MANAGER') AS a1,
AVG(sal) AT (VISIBLE WHERE job <> 'MANAGER') AS a2,
AVG(sal) AT (WHERE job <> 'MANAGER') AS a3
FROM emp
WHERE sal > 1000
GROUP BY deptno;
{code}
{{a1}} and {{a2}} always return the same value - the average salary of
non-managers who earn more than $1000. But {{a3}} (similar to {{a2}} but
missing the {{VISIBLE}} keyword) computes the average of all non-managers (not
just those earning more than $1000).
Sub-clauses of {{AT}}:
* {{VISIBLE}} adds a filter equivalent to the {{WHERE}} clause (has no effect
when used in the {{WHERE}} or {{ON}} clauses);
* {{WHERE}} adds the given predicate as a filter;
* {{CLEAR GROUP}} clears the predicate of the current group, if any (e.g.
"deptno = 10 AND job = 'ANALYST'" if we are in the SELECT clause of a query
with "GROUP BY deptno, job" evaluating the row (10, 'ANALYST');
* {{CLEAR WHERE}} clears the predicate of the {{WHERE}} clause (has no effect
when used in the {{WHERE}} or {{ON}} clauses);
* {{SET dimension = expression}} clears the predicate on {{dimension}} (if
any) and adds a filter "dimension = expression".
{{AT}} can be applied to any expression (not just aggregate functions like
{{FILTER}}). For example,
* {{avg_sal AT (CLEAR deptno)}} // applied to a measure
* {{(COUNT(comm) + 4) AT (CLEAR deptno)}} // applied to an aggregateable
expression
If the expression's syntax is not a function call or an identifier, you need to
use parentheses.
> Add AT operator, for context-sensitive expressions
> --------------------------------------------------
>
> Key: CALCITE-5692
> URL: https://issues.apache.org/jira/browse/CALCITE-5692
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> In CALCITE-4496 we added measures, and the ability to evaluate a measure in a
> context determined by the GROUP BY clause. In this change, we add the ability
> to change that context using the AT operator.
> When used as a clause of an aggregate function call, {{AT}} is similar to
> {{{}FILTER{}}}. For example, in the query
> {code:java}
> SELECT deptno,
> AVG(sal) FILTER (WHERE job <> 'MANAGER') AS a1,
> AVG(sal) AT (VISIBLE WHERE job <> 'MANAGER') AS a2,
> AVG(sal) AT (WHERE job <> 'MANAGER') AS a3
> FROM emp
> WHERE sal > 1000
> GROUP BY deptno;
> {code}
> {{a1}} and {{a2}} always return the same value - the average salary of
> non-managers who earn more than $1000. But {{a3}} (similar to {{a2}} but
> missing the {{VISIBLE}} keyword) computes the average of all non-managers
> (not just those earning more than $1000).
> h2. Syntax
> Sub-clauses of {{{}AT{}}}:
> * {{VISIBLE}} adds a filter equivalent to the {{WHERE}} clause (has no
> effect when used in the {{WHERE}} or {{ON}} clauses);
> * {{WHERE}} adds the given predicate as a filter;
> * {{CLEAR GROUP}} clears the predicate of the current group, if any (e.g.
> "deptno = 10 AND job = 'ANALYST'" if we are in the SELECT clause of a query
> with "GROUP BY deptno, job" evaluating the row (10, 'ANALYST');
> * {{CLEAR WHERE}} clears the predicate of the {{WHERE}} clause (has no
> effect when used in the {{WHERE}} or {{ON}} clauses);
> * {{SET dimension = expression}} clears the predicate on {{dimension}} (if
> any) and adds a filter "dimension = expression".
> {{AT}} can be applied to any expression (not just aggregate functions like
> {{{}FILTER{}}}). For example,
> * {{avg_sal AT (CLEAR deptno)}} // applied to a measure
> * {{(COUNT(comm) + 4) AT (CLEAR deptno)}} // applied to an aggregateable
> expression
> If the expression's syntax is not a function call or an identifier, you need
> to use parentheses.
> h2. Implementation
> First, push down expressions, so that the left side of {{AT}} is always a
> measure, and the right side is always in terms of (dimension) columns. For
> example, given the query
> {code:java}
> SELECT deptno, (1 + AVG(sal * sal)) AT (SET YEAR(hiredate) = 1981)
> FROM Emp
> GROUP BY deptno {code}
> we transform to the following, with a {{e2}} containing the pushed down
> expressions:
> {code:java}
> WITH e2 AS (
> SELECT *, 1 + AVG(sal * sal) AS MEASURE m, YEAR(hiredate) AS y
> FROM Emp)
> SELECT deptno, m AT (SET y = 1981)
> FROM e2
> GROUP BY deptno{code}
> If {{AT}} occurs in the definition of a measure, the context is modified. For
> example, in the query
> {code:java}
> WITH e2 AS (
> SELECT *,
> AVG(sal) AS MEASURE avg_sal,
> avg_sal AT (SET job = 'MANAGER') AS MEASURE avg_mgr_sal
> FROM Emp)
> SELECT deptno, job, avg_sal, avg_sal AT (SET job = 'MANAGER'), avg_mgr_sal
> FROM e2
> GROUP BY deptno, job{code}
> the measure {{avg_mgr_sal}} is expanded at the call site as if the user had
> written {{{}avg_sal AT (SET job = 'MANAGER'){}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
