[SPARK-30395] When one or more DISTINCT aggregate expressions operate on the same field, the DISTINCT aggregate expression allows the use of the FILTER clause

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Jiaan Geng
• Created: 31/Dec/19 08:15
• Updated: 16/Jan/20 09:54
• Resolved: 16/Jan/20 09:54

Description

This ticket is related to https://issues.apache.org/jira/browse/SPARK-27986

This ticket will support:

When one or more DISTINCT aggregate expressions operate on the same field, the DISTINCT aggregate expression allows the use of the FILTER clause

such as:

 

select sum(distinct id) filter (where sex = 'man') from student;
select class_id, sum(distinct id) filter (where sex = 'man') from student group by class_id;
select count(id) filter (where class_id = 1), sum(distinct id) filter (where sex = 'man') from student;
select class_id, count(id) filter (where class_id = 1), sum(distinct id) filter (where sex = 'man') from student group by class_id;
select sum(distinct id), sum(distinct id) filter (where sex = 'man') from student;
select class_id, sum(distinct id), sum(distinct id) filter (where sex = 'man') from student group by class_id;
select class_id, count(id), count(id) filter (where class_id = 1), sum(distinct id), sum(distinct id) filter (where sex = 'man') from student group by class_id;

 

 but not support:

 

select class_id, count(distinct sex), sum(distinct id) filter (where sex = 'man') from student group by class_id;
select class_id, count(distinct sex) filter (where class_id = 1), sum(distinct id) filter (where sex = 'man') from student group by class_id;

https://issues.apache.org/jira/browse/SPARK-30396 used for later.

Issue Links

• links to: GitHub Pull Request #27058