#💎 #TreasuerData #集計テク #Presto #SQL #構成比

aggregation_technique_at_td.sql

/*********************************************************
 * ID-POS
 *
 *　　　　POSデータのDWH（約80億レコード/1テーブル）を全て回すのはリスク。
 *　　　　「WHERE句」を用いて、レコード取得制限の掛ける絞り込み。
 *********************************************************/
 
with 
    PREP_POS as (
        select
            td_time_parse(
                case
                    when crm_channel_kubun = '2' then substr(ec_juchu_nichiji, 1, 10)
                    else eigyo_bi
                end, 'JST') as time -- UNIXTIME
            , case
                 when crm_channel_kubun = '2' then substr(ec_juchu_nichiji, 1, 10)
                 else eigyo_bi
              end as eigyo_bi
            , case
                 when crm_channel_kubun = '2' then ec_dempyo_key
                 else dempyo_key
              end as dempyo_key
            , kaiin_master_code
            , sotozei_kingaku
            , crm_channel_kubun
        from
            crm_db.crm_getsuji_kaiin_meisai
        where
            td_time_range(time, '2021-08-15', '2021-10-15', 'JST') --※ココで大幅にデータを絞り込む
            and crm_jigyo_kubun = '2'　    --****
            and crm_torihiki_kubun = '2' --****
            and sotozei_kingaku > 0      --****
    )
    , REPORT_POS as (
        select
            eigyo_bi
            , count(distinct case when crm_channel_kubun='1' then kaiin_master_code end) as st_user
            , count(distinct case when crm_channel_kubun='2' then kaiin_master_code end) as ec_user
            , count(distinct case when crm_channel_kubun='1' then dempyo_key end) as st_purchase
            , count(distinct case when crm_channel_kubun='2' then dempyo_key end) as ec_purhase
            , sum(case when crm_channel_kubun='1' then sotozei_kingaku end) as st_amount
            , sum(case when crm_channel_kubun='2' then sotozei_kingaku end) as ec_amount
        from 
            PREP_POS
        where 
            td_time_range(time, '2021-09-01', '2021-10-01', 'JST')
        group by 
            eigyo_bi
        order by 
            eigyo_bi
    )


select * from REPORT_POS
;

/***************************************************
 * 構成比（Composition ratio）
 ***************************************************/
select
    eigyo_bi
    -- 購入回数構成比
    , cast(count(distinct case when crm_channel_kubun='1' then dempyo_key end) as double)
     /cast(sum(count(distinct case when crm_channel_kubun='1' then dempyo_key end))over() as double) as st_purchase_comp
    , cast(count(distinct case when crm_channel_kubun='2' then dempyo_key end) as double)
     /cast(sum(count(distinct case when crm_channel_kubun='2' then dempyo_key end))over() as double) as ec_purchase_comp
    -- 顧客数構成比
    , cast(count(distinct case when crm_channel_kubun='1' then kaiin_master_code end) as double)
     /cast(sum(count(distinct case when crm_channel_kubun='1' then kaiin_master_code end))over() as double) as st_user_comp
    , cast(count(distinct case when crm_channel_kubun='2' then kaiin_master_code end) as double)
     /cast(sum(count(distinct case when crm_channel_kubun='2' then kaiin_master_code end))over() as double) as ec_user_comp
    -- 販売金額構成比
    , sum(case when crm_channel_kubun='1' then sotozei_kingaku end)
     /sum(sum(case when crm_channel_kubun='1' then sotozei_kingaku end))over() as st_amount_comp
    , sum(case when crm_channel_kubun='2' then sotozei_kingaku end)
     /sum(sum(case when crm_channel_kubun='2' then sotozei_kingaku end))over() as ec_amount_comp
from 
    prep_meisai
where 
    td_time_range(time, '2021-09-01', '2021-10-01', 'JST')
group by 
    eigyo_bi
;

▽別解：WITH句を用いてコード省略

,ttl as (
   select
       -- 購入回数（合計）
       cast(count(distinct case when crm_channel_kubun='1' then dempyo_key end) as double)
       + cast(count(distinct case when crm_channel_kubun='2' then dempyo_key end) as double) as purchase
       -- 顧客数（合計）
       , cast(count(distinct case when crm_channel_kubun='1' then kaiin_master_code end) as double)
       + cast(count(distinct case when crm_channel_kubun='2' then kaiin_master_code end) as double) as user
       -- 販売金額（合計）
       , sum(case when crm_channel_kubun='1' then sotozei_kingaku end)
       + sum(case when crm_channel_kubun='2' then sotozei_kingaku end) as amount
   from 
       prep_meisai
)
 
select
   -- 購入回数（構成比）
   cast(count(distinct case when crm_channel_kubun='1' then dempyo_key end) as double)
   / (select ttl.purchase from ttl) as st_user_comp
   , cast(count(distinct case when crm_channel_kubun='2' then dempyo_key end) as double)
   / (select ttl.purchase from ttl) as ec_user_comp
   -- 顧客数（構成比）
   , cast(count(distinct case when crm_channel_kubun='1' then kaiin_master_code end) as double)
   / (select ttl.user from ttl) as st_purchase_comp
   , cast(count(distinct case when crm_channel_kubun='2' then kaiin_master_code end) as double)
   / (select ttl.user from ttl) as ec_purchase_comp
   -- 販売金額（構成比）
   , sum(case when crm_channel_kubun='1' then sotozei_kingaku end)
   / (select ttl.amount from ttl) as st_amount_comp
   , sum(case when crm_channel_kubun='2' then sotozei_kingaku end)
   / (select ttl.amount from ttl) as ec_amount_comp
from
   prep_meisai
where
   td_time_range(time, '2021-09-01', '2021-10-01', 'JST')
;

Cf.

• 分散SQLエンジン“Presto”のクエリチューニング - Qiita
• Japanese (日本のコンテンツ) Product Documentation - TreasuerData

要約統計量

Cf.

• Tips: Prestoで中央値を計算する-Qiita