#🔎 #BigQuery #クロス集計 #DS100本ノック #SQL

pivot.sql

#standaardSQL

with gender_era_amount as (
    select
        c.gender_cd as gender
        , trunc(c.age/10) * 10 as era
        , sum(r.amount) as amount
    from `prj-test3.100knocks.customer` as c
    join `prj-test3.100knocks.receipt` as r
    using(customer_id)
    group by 
        gender
        , era
)
select 
    era 
    , max(case gender when 0 then amount else 0 end) as male
    , max(case gender when 1 then amount else 0 end) as female
    , max(case gender when 9 then amount else 0 end) as unknown
from gender_era_amount
group by era
order by era
;

自身が記述した💩 コード

with
    tb as (
        select
            sales_ymd
            , gender
            , age
            , case  
                when age < 20 then "10's"
                when age between 20 and 29 then "20's"
                when age between 30 and 39 then "30's"
                when age between 40 and 49 then "40's"
                when age between 50 and 59 then "50's"
                when age between 60 and 69 then "60's"
                when age between 70 and 79 then "70's"
                else "Over80's"
             end as age_category
            , amount
        from
            `prj-test3.100knocks.receipt`
        left join
            `prj-test3.100knocks.customer`
            using(customer_id)
        where gender is not null
    )
select
    *
from
    (select
        age_category
        , sum(amount) as male
    from tb
    where gender = "男性"
    group by age_category)
    left join
    (select
        age_category
        , sum(amount) as female
    from tb
    where gender = "女性"
    group by age_category) using(age_category)
    left join
    (select
        age_category
        , sum(amount) as unknown
    from tb
    where gender = "不明"
    group by age_category) using(age_category)
order by age_category
;