Odd and Even Measurements

Odd and Even Measurements

문제

1. 하루 동안 측정 중 홀수 번째 측정값

2. 하루 동안 측정 중 짝수 번째 측정값

 

풀이

SELECT
  measurement_day,
  sum(case when rn % 2 = 1 then measurement_value else 0 end) as odd_sum ,
  sum(case when rn % 2 = 0 then measurement_value else 0 end) as even_sum
from
(
  select 
    measurement_time::date as measurement_day,
    measurement_value,
    row_number() over (PARTITION by measurement_time::date order by measurement_time) as rn
  from measurements
) sub1
group by measurement_day

1️⃣ sub1: 날짜별 순서 매기기

2️⃣ group by: 날짜 그룹

3️⃣ 홀수 이면 measurement_value 더하기

4️⃣ 짝수 이면 measurement_value 더하기