[Impala SQL] Over 함수로 Count Distinct 하는 법

 

 

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