[SQL] COUNT() OVER()함수

집계 함수를 사용해 데이터의 값을 구할 수 있다. 하지만 집계 함수는 결과를 하나의 행으로 출력하기 때문에 전체 데이터와 함께 집계의 결과를 보고 싶은 경우에는 전체 데이터를 추가해야 한다. 이때 OVER() 함수를 사용하면 별도의 테이블을 추가하지 않고도 집계 값과 전체 데이터를 한 번에 볼 수 있다. 

 

1. 집계 함수

데이터를 통계내는 데 유용한 집계 함수에는 SUM(), COUNT(), MAX(), MIN(). AVG() 등이 있으며, COUNT()를 예시로 사용하겠다.

SELECT 뒤에 단독으로 COUNT()가 쓰일 경우, 합계의 결과를 하나의 행으로 보여준다. 전체 데이터가 같이 나타나지 않으므로 COUNT()의 구성요소들이 무엇이고 어떤 비중으로 더해 있는지 알 수 없다. 

 

 

 

다른 데이터들과 함께 보기 위해서는 GROUP BY를 통해 카테고리 별로 COUNT를 구하거나 COUNT() OVER()을 통해 전체 데이터 값을 같이 가져올 수 있다.

 

2. OVER() 함수

SUM() OVER()를 사용할 경우 하나의 열이 아니라, 전체 ROW를 표시한다. 집계의 결괏값 + 전체 데이터를 같이 볼 필요가 있을 경우 유용하다. OVER() 함수 안에 partition by 또는 order by를 넣어 구체적인 데이터를 뽑아낼 수 있다.

 

-  OVER( partition by A)을 사용하면 특정 그룹(A)을 지정하여 데이터를 추출할 수 있다. 

 

- OVER( order by B)을 사용하여 B를 기준으로 누적 된 값을 추출할 수 있다.

3. DUNE 쿼리문

블록체인 온 체인 데이터를 서비스하는 DUNE의 쿼리문을 통해 OVER() 함수가 어떻게 사용되는지 살펴보겠다. 

https://dune.com/subinium/metamask-dex

🦊Metamask DEX

WITH stats as (SELECT
    date_trunc('month', block_time) as month, 
    COUNT(DISTINCT tx_from) as mau,
    COUNT(*) as txs,
    SUM(usd_amount) as amount
FROM dex.trades
WHERE tx_to = '\x881d40237659c251811cec9c364ef91dc08d300c'
GROUP BY 1)

SELECT 
    *,
    amount * 0.00875 as revenue,
    SUM(amount) OVER(order by month asc) * 0.00875 as revenue_cum
FROM 
    stats

사용된 달러의 가치를 나타내는 usd_amount의 합계를 날짜별로(month) amount열로 만들고,  with구문을 사용해 stats라는 새로운 테이블을 생성한다. SUM() OVER(order by)을 통해 usd_amount의 합계를 누적 값으로 나타낸다. 값을 누적하는 기준은 month이다.