조건 분기

CASE

CASE 예제

연도별 연령대별 가입자 수

select
  extract(year from created_at) as year
  count(case when age between 10 and 19 then id end) as age_10s
  count(case when age < 30 then id end) as age_20s
  count(case when age < 40 then id end) as age_30s
  count(case when age < 50 then id end) as age_40s
  count(case when age < 60 then id end) as age_50s
from `thelook_ecommerce.users`
select
  extract(year from created_at) as year,
  count(case when age between 10 and 19 then id end) as age_10s,
  count(case when age between 20 and 29 then id end) as age_20s,
  count(case when age between 30 and 39 then id end) as age_30s,
  count(case when age between 40 and 49 then id end) as age_40s,
  count(case when age between 50 and 59 then id end) as age_50s,
  count(id) as total
from `thelook_ecommerce.users`
group by year

연도별 분기별 가입자수

-- YEAR  Q1  Q2  Q3  Q4  TOTAL
-- 2019  111 111 111 111 444
-- 2020  111 111 111 111 444
-- 2021  111 111 111 111 444
-- 2022  111 111 111 111 444

이런식으로 나오게 해!!

🐰져니 코드

select
  extract(year from created_at) as year,
  count(case when extract(QUARTER from created_at)=1 then id end) as Q1,
  count(case when extract(QUARTER from created_at)=2 then id end) as Q2,
  count(case when extract(QUARTER from created_at)=3 then id end) as Q3,
  count(case when extract(QUARTER from created_at)=4 then id end) as Q4,
  count(id) as total
from `thelook_ecommerce.users`
group by year
order by year

🧑‍🏫 강사님 코드랑 같음