[CALCITE-704] FILTER clause for aggregate functions

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.3.0-incubating
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 28/Apr/15 00:01
• Updated: 21/Jul/15 14:51
• Resolved: 02/May/15 01:45

Description

The SQL standard provides a means to control which values are accumulated by an aggregate function.

aggregate function> ::=
    COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
  | <general set function> [ <filter clause> ]
  | <binary set function> [ <filter clause> ]
  | <ordered set function> [ <filter clause> ]
  | <array aggregate function> [ <filter clause> ]

<filter clause> ::=
  FILTER <left paren> WHERE <search condition> <right paren>

For example, the following query sums the salary of all employees and female employees in a particular department:

SELECT deptno,
  SUM(sal) AS all_sal,
  SUM(sal) FILTER ( WHERE gender = 'F' ) AS female_sal
FROM emp
GROUP BY deptno

You could previously achieve this using SUM(CASE WHEN gender = 'F' THEN sal ELSE null END) but that trick only worked on certain aggregate functions.

Issue Links

• relates to: CALCITE-703 Obsolete AggregateCall.name
• relates to: KYLIN-899 Support aggregate(col) FILTER (WHERE condition)