apache / apache/pinot

Add support for COUNT(DISTINCT(col_1, ..., col_n)) in engine v2

Open
#11,504 1 comment 0 reactions 0 assignees View on GitHub
feature
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

## Current
As of today,
`COUNT DISTINCT` is only supported for a single column.
For instance, `COUNT(DISTINCT col1)`.

## Ask
Add support for multiple columns:
```
COUNT(DISTINCT(col_1, ..., col_n))
```

Examples:
1) Simple:
```
SELECT
COUNT(DISTINCT(col1, col2, col3))
FROM table
```

2) With a group by:
```
SELECT
col_x,
COUNT(DISTINCT(col1, col2, col3))
FROM table
GROUP BY col_x
```

## Current workaround
*not tested, please correct if need be*

1) Simple:
```
with t as (SELECT DISTINCT col1, col2, col3)
SELECT
COUNT(*)
FROM t
```

2) With a group by:
**NOT SURE - is it possible in an efficient manner?**
```
with t as (SELECT DISTINCT col_x, col1, col2, col3)
SELECT
col_x
COUNT(col1)
FROM t
GROUP BY col_x
```
here doing the distinct on col_x could be very unnefficient.

## Why
This will make it easy to count duplicates along some columns: `SELECT COUNT(*) - COUNT(DISTINCT(col1, col2, col3))`

This will makes it easy to do count distinct along multiple columns in complex queries.
When using the workaround of the sub query, some operations (eg filters) have to be moved in the inner query, while some have to be kept in the outer query. This is hard to write and can be unnefficient.

Contributor guide

Open the contributing guide

Research direction

No implementation files or tests are named. Start by tracing engine v2's existing single-column COUNT(DISTINCT ...) handling, then verify the desired behavior against the simple and GROUP BY examples; done means both multi-column forms work in complex queries.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.