[PostgreSQL] 다중 컬럼 평균 구하기 (Average Multiple Columns)

특정 일자까지 건수를 구한 다음 평균을 구해야하는 경우가 있었습니다.

 

예시 테이블 구조 ( 테이블명: trades )

거래금액 (컬럼명:amount / 타입: bigint)	거래일자 (컬럼명: trade_date / 타입: date)
10,000	2023-04-28

* 해당 예시 테이블은 실제 운영하는 서비스와 전혀 상관없는 참고용 예시 테이블입니다.

 

특정기간 특정금액 구간 건수

SELECT
    (SELECT COUNT(*) FROM trades WHERE amount > 0 AND amount <= 1000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a0b1000,
    (SELECT COUNT(*) FROM trades WHERE amount > 1000 AND amount <= 2000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a1000b2000,
    (SELECT COUNT(*) FROM trades WHERE amount > 2000 AND amount <= 3000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a2000b3000

결과

a0b1000	a1000b2000	a2000b3000
5	10	15

 

 

그런데 이제 뒤에 평균을 추가로 구하고 싶었습니다. 이때 방법이 여러가지가 있겠지만 PostgreSQL에서 사용하는 with문과 unnest를 사용해서 해결했습니다.

 

특정기간 특정금액 구간 건수 (평균 추가)

WITH counts AS (
    SELECT
        (SELECT COUNT(*) FROM trades WHERE amount > 0 AND amount <= 1000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a0b1000,
        (SELECT COUNT(*) FROM trades WHERE amount > 1000 AND amount <= 2000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a1000b2000,
        (SELECT COUNT(*) FROM trades WHERE amount > 2000 AND amount <= 3000 AND trade_date >= '2022-04-28' AND  trade_date <= '2023-04-28') AS a2000b3000
)

SELECT *,
	(SELECT FLOOR(AVG(c) FROM UNNEST(ARRAY[a0b1000, a1000b2000, a2000b3000] AS c) AS average
FROM counts;

 

결과

a0b1000	a1000b2000	a2000b3000	average
5	10	15	10

 

 

참조

[PostgreSQL functions-array]: https://www.postgresql.org/docs/15/functions-array.html

9.19. Array Functions and Operators