[PostgreSQL] FILTER_SELECT문 속의 조건

FILTER

 
기본 구조

SELECT COUNT(1) FILTER (WHERE 조건) AS alias_name
FROM 테이블명;

# 지정된 조건을 만족하는 행의 수를 세어 alias_name이라는 별칭으로 결과를 반환

참고 : https://www.postgresql.org/docs/current/sql-expressions.html

---

[활용 문제]

 

https://datalemur.com/questions/odd-even-measurements

 

Google Interview Question | DataLemur

 

[문제] measurements 테이블에서 날짜별 인덱스 넘버가 홀수인 것을 odd_sum, 짝수인 것을 even_sum으로 출력하기

출력값 예시

 

-- 정답 쿼리
WITH sub AS(
SELECT CAST(measurement_time AS DATE) AS measurement_day,
  measurement_value,
  ROW_NUMBER() OVER (PARTITION BY CAST(measurement_time AS DATE) 
      ORDER BY measurement_time) AS measurement_num
FROM measurements
)
SELECT measurement_day,
  SUM(measurement_value) FILTER (WHERE measurement_num % 2 != 0 ) AS odd_sum,
  SUM(measurement_value) FILTER (WHERE measurement_num % 2 = 0 ) AS even_sum
FROM sub
GROUP BY 1