Impala에서는 Over와 Distinct를 같이 쓸 수 없다. (대부분의 SQL 쿼리 엔진이 그럴 것 같다)
기준 항목별 Distinct 를 Count하는 쉬운 방법이 없을까 고민하다가
구글링 해보니 아래와 같은 심플한(?) 방법으로 해결이 가능하다고 한다
dense_rank() over (partition by [기준1] order by [카운트하고싶은값])
+ dense_rank() over (partition by [기준1] order by [카운트하고싶은값] desc)
- 1
봐도 이게 뭔소리냐 싶어서 테스트를 해봤다.
장르별/타이틀별로 좋아요를 누른 유저를 보여주는 dummy 테이블이다.
Genre | Title | User | expected_column(Genre, Title별 Distinct User 수) |
Fantasy | Harry Potter | abc | 2 |
Fantasy | Harry Potter | abc | 2 |
Fantasy | Harry Potter | def | 2 |
Fantasy | Dune | def | 1 |
그리고 이런 코드를 써본다.
SELECT genre,title
,dense_rank() over (partition by genre,title order by user_id) as a
,dense_rank() over (partition by genre,title order by user_id desc) as b
,dense_rank() over (partition by genre,title order by user_id)
+dense_rank() over (partition by genre,title order by user_id desc) -1 as c
FROM dummy
놀랍게도 c 칼럼은 Genre / Title 별 COUNT DISTINCT 값이다!!
dense_rank의 unique 개수는 해당 칼럼의 값의 unique 값과 같다는 사실를 이용한 방법이다.
dense_rank의 unique 개수를 구하기 위해 처음값과 끝 값을 더한 뒤 -1을 해준것이다.
Genre | Title | User | a | b | c |
Fantasy | Harry Potter | abc | 1 | 2 | 2 |
Fantasy | Harry Potter | abc | 1 | 2 | 2 |
Fantasy | Harry Potter | def | 2 | 1 | 2 |
Fantasy | Dune | def | 1 | 1 | 1 |
놀라운 방법이다.
이제 이걸 서브 쿼리로 묶어 SELECT DISTINCT Genre, Title, c를 해주면 된다.
SELECT DISTINCT f.genre,f.title, f.c
FROM (SELECT genre,title
dense_rank() over (partition by genre,title order by user_id)
+dense_rank() over (partition by genre,title order by user_id desc) -1 as c
FROM dummy) f