Add support for COUNT(DISTINCT(col_1, ..., col_n)) in engine v2
- 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
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