day11 新零售课程笔记

0 昨日的用户作业
1 DM层实现操作
- 1.1 销售主题统计宽表
- 1.2 商品主题统计宽表
2 RPT层实现
3- 数据导出操作
- 3.1 Presto整合Mysql (本地Presto集成)
- 3.2 使用Presto完成数据导出
4.展示:
5. 相关的面试题: 写成话术

今日内容:

1- DM层销售主题的统计宽表的剩余上卷维度的实现 (操作)
2- DM层商品主题统计宽表的上卷维度实现(操作)
3- RPT层实现 (操作)
4- 数据导出操作 (操作)
5- 数据展示相关内容

6- 项目总结以及相关面试题

0 昨日的用户作业

insert into yp_dws.dws_user_daycount 
  -- 登录次数 
with login_count as ( 
  select 
      count(id) as login_count, 
      login_user as user_id, 
      dt 
  from yp_dwd.fact_user_login 
  group by login_user, dt
),
-- 店铺收藏数 
store_collect_count as ( 
  select 
      count(id) as store_collect_count, 
      user_id, 
      substring(create_time, 1, 10) as dt 
  from yp_dwd.fact_store_collect 
  where end_date='9999-99-99' 
  group by user_id, substring(create_time, 1, 10) 
),
-- 商品收藏数 
goods_collect_count as ( 
  select 
      count(id) as goods_collect_count, 
      user_id, 
      substring(create_time, 1, 10) as dt 
  from yp_dwd.fact_goods_collect 
  where end_date='9999-99-99' 
  group by user_id, substring(create_time, 1, 10) 
),
-- 加入购物车次数和金额 
cart_count_amount as ( 
  select count(cart.id) as cart_count, 
      sum(coalesce(g.goods_promotion_price,0)) as cart_amount, 
      buyer_id as user_id, 
      substring(cart.create_time, 1, 10) as dt 
  from yp_dwd.fact_shop_cart cart join yp_dwb.dwb_goods_detail g 
      on cart.goods_id=g.id and cart.end_date='9999-99-99'
  group by buyer_id, substring(cart.create_time, 1, 10) 
),
-- 订单宽表，为后面的下单次数和金额准备数据 
order_base as ( 
  select 
      buyer_id, 
      create_date, 
      order_id, 
      order_amount, 
      row_number() over(partition by orderd.order_id) rn 
  from yp_dwb.dwb_order_detail orderd where orderd.is_valid=1 
),
-- 下单次数和金额 
order_count_amount as ( 
  select
      o.buyer_id user_id, 
      substring(o.create_date,1,10) dt, 
      count(o.order_id) order_count, 
      sum(coalesce(o.order_amount,0)) order_amount 
  from order_base o 
  where o.rn=1 
  group by o.buyer_id, substring(o.create_date,1,10) 
),
-- 支付次数和金额 
payment_count_amount as ( 
  select count(id) as payment_count, 
      sum(coalesce(order_amount,0)) as payment_amount, 
      create_user user_id, 
      substring(create_time, 1, 10) as dt 
  from yp_dwd.fact_shop_order_address_detail 
  where is_valid = 1 and pay_time is not null and end_date='9999-99-99' 
  group by create_user, substring(create_time, 1, 10) 
),
fulljoin as ( 
  select
      coalesce(lc.dt, scc.dt, gcc.dt, cc.dt, oc.dt, pc.dt) dt ,
      coalesce(lc.user_id, scc.user_id, gcc.user_id, cc.user_id, oc.user_id, pc.user_id) user_id ,
      coalesce(login_count,0) login_count ,
      coalesce(store_collect_count,0) store_collect_count ,
      coalesce(goods_collect_count,0) goods_collect_count ,
      coalesce(cart_count,0) cart_count ,
      coalesce(cart_amount,0) cart_amount ,
      coalesce(order_count,0) order_count ,
      coalesce(order_amount,0) order_amount ,
      coalesce(payment_count,0) payment_count ,
      coalesce(payment_amount,0) payment_amount 
  from login_count lc 
      full join store_collect_count scc 
          on lc.dt=scc.dt and lc.user_id=scc.user_id 
      full join goods_collect_count gcc 
          on lc.dt=gcc.dt and lc.user_id=gcc.user_id 
      full join cart_count_amount cc 
          on lc.dt=cc.dt and lc.user_id=cc.user_id 
      full join order_count_amount oc 
          on lc.dt=oc.dt and lc.user_id=oc.user_id 
      full join payment_count_amount pc 
          on lc.dt=pc.dt and lc.user_id=pc.user_id 
)
select
  dt, 
  user_id, 
  -- 登录次数
  sum(coalesce(login_count,0)) login_count, 
  -- 店铺收藏数 
  sum(coalesce(store_collect_count,0)) store_collect_count, 
  -- 商品收藏数 
  sum(coalesce(goods_collect_count,0)) goods_collect_count, 
  -- 加入购物车次数和金额 
  sum(coalesce(cart_count,0)) cart_count, 
  sum(coalesce(cart_amount,0)) cart_amount, 
  -- 下单次数和金额 sum(coalesce(order_count,0)) order_count,
  sum(coalesce(order_amount,0)) order_amount, 
  -- 支付次数和金额 sum(coalesce(payment_count,0)) payment_count, 
  sum(coalesce(payment_amount,0)) payment_amount 
from fulljoin 
group by dt, user_id 
;

1 DM层实现操作

DM层的作用: 进行上卷细化统计操作

创建DM层的库(hive中执行)
```
create database IF NOT EXISTS xxx;
```
1.1 销售主题统计宽表
可分析的主要指标有：销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量。
维度有：日期(天(已经统计过), 周, 月, 年)、城市、商圈、店铺、品牌、大类、中类、小类
建表语句: ```sql CREATE TABLE xxx.dm_sale( date_time string COMMENT ‘统计日期,不能用来分组统计’, time_type string COMMENT ‘统计时间维度：year、month、week、date’, year_code string COMMENT ‘年code’, year_month string COMMENT ‘年月’, month_code string COMMENT ‘月份编码’, day_month_num string COMMENT ‘一月第几天’, dim_date_id string COMMENT ‘日期’, year_week_name_cn string COMMENT ‘年中第几周’,

group_type string COMMENT ‘分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all’, city_id string COMMENT ‘城市id’, city_name string COMMENT ‘城市name’, trade_area_id string COMMENT ‘商圈id’, trade_area_name string COMMENT ‘商圈名称’, store_id string COMMENT ‘店铺的id’, store_name string COMMENT ‘店铺名称’, brand_id string COMMENT ‘品牌id’, brand_name string COMMENT ‘品牌名称’, max_class_id string COMMENT ‘商品大类id’, max_class_name string COMMENT ‘大类名称’, mid_class_id string COMMENT ‘中类id’, mid_class_name string COMMENT ‘中类名称’, min_class_id string COMMENT ‘小类id’, min_class_name string COMMENT ‘小类名称’, — =======统计======= — 销售收入 sale_amt DECIMAL(38,2) COMMENT ‘销售收入’, — 平台收入 plat_amt DECIMAL(38,2) COMMENT ‘平台收入’, — 配送成交额 deliver_sale_amt DECIMAL(38,2) COMMENT ‘配送成交额’, — 小程序成交额 mini_app_sale_amt DECIMAL(38,2) COMMENT ‘小程序成交额’, — 安卓APP成交额 android_sale_amt DECIMAL(38,2) COMMENT ‘安卓APP成交额’, — 苹果APP成交额 ios_sale_amt DECIMAL(38,2) COMMENT ‘苹果APP成交额’, — PC商城成交额 pcweb_sale_amt DECIMAL(38,2) COMMENT ‘PC商城成交额’, — 成交单量 order_cnt BIGINT COMMENT ‘成交单量’, — 参评单量 eva_order_cnt BIGINT COMMENT ‘参评单量comment=>cmt’, — 差评单量 bad_eva_order_cnt BIGINT COMMENT ‘差评单量negtive-comment=>ncmt’, — 配送成交单量 deliver_order_cnt BIGINT COMMENT ‘配送单量’, — 退款单量 refund_order_cnt BIGINT COMMENT ‘退款单量’, — 小程序成交单量 miniapp_order_cnt BIGINT COMMENT ‘小程序成交单量’, — 安卓APP订单量 android_order_cnt BIGINT COMMENT ‘安卓APP订单量’, — 苹果APP订单量 ios_order_cnt BIGINT COMMENT ‘苹果APP订单量’, — PC商城成交单量 pcweb_order_cnt BIGINT COMMENT ‘PC商城成交单量’ ) COMMENT ‘销售主题宽表’ ROW format delimited fields terminated BY ‘\t’ stored AS orc tblproperties (‘orc.compress’ = ‘SNAPPY’);

<a name="xU011"></a>
#### 统计分析: 先按照天来统计 (前面已经统计过了)
```sql
insert into xxxxx.dm_sale 
select 
    t1.dt as date_time,
    'date' as time_type ,
    t2.year_code ,
    t2.year_month ,
    t2.month_code ,
    t2.day_month_num ,
    t2.dim_date_id ,
    t2.year_week_name_cn ,
    -- 维度
      t1.group_type,
      t1.city_id ,
      t1.city_name ,
      t1.trade_area_id ,
      t1.trade_area_name,
      t1.store_id,
      t1.store_name ,
      t1.brand_id,
      t1.brand_name,
      t1.max_class_id,
      t1.max_class_name,
      t1.mid_class_id,
      t1.mid_class_name,
      t1.min_class_id,
    t1.min_class_name,
    -- 指标
    t1.sale_amt,
    t1.plat_amt,
    t1.deliver_sale_amt ,
    t1.mini_app_sale_amt ,
    t1.android_sale_amt ,
    t1.ios_sale_amt ,
    t1.pcweb_sale_amt,
    t1.order_cnt,
    t1.eva_order_cnt ,
    t1.bad_eva_order_cnt ,
    t1.deliver_order_cnt ,
    t1.refund_order_cnt ,
    t1.miniapp_order_cnt ,
    t1.android_order_cnt,
    t1.ios_order_cnt ,
    t1.pcweb_order_cnt
from xxxxx.dws_sale_daycount  t1 
    left join  xxxx.dim_date  t2 on t1.dt = t2.date_code ;

统计分析:按周来统计

上卷统计的分析流程:

insert into xxxx.dm_sale 
with  dim_date as (
    select
        date_code,
        year_code,
        year_month,
        month_code ,
        day_month_num ,
        dim_date_id ,
        year_week_name_cn 
    from hive.bj59_yp_dwd_jiale.dim_date
),
t1 as (
    select 
        '2022-05-10' as  date_time,
        'week' as time_type,
        year_code ,
        null as year_month,
        null as month_code,
        null as day_month_num ,
        null as dim_date_id,
        year_week_name_cn ,

        -- 处理维度:
        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then 'store'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then 'trade_area'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then 'city'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then 'brand'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then 'min_class'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then 'mid_class'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then 'max_class'
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then 'all'
            else null
        end as group_type,
        s.city_id ,
          s.city_name ,
          s.trade_area_id ,
          s.trade_area_name,
          s.store_id,
          s.store_name ,
          s.brand_id,
          s.brand_name,
          s.max_class_id,
          s.max_class_name,
          s.mid_class_id,
          s.mid_class_name,
          s.min_class_id,
        s.min_class_name,

        -- 指标:  
            case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.sale_amt)
            else null
        end as sale_amt,
        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.plat_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.plat_amt)
            else null
        end as plat_amt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_sale_amt)
            else null
        end as deliver_sale_amt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.mini_app_sale_amt)
            else null
        end as mini_app_sale_amt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_sale_amt)
            else null
        end as android_sale_amt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_sale_amt)
            else null
        end as ios_sale_amt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_sale_amt)
            else null
        end as pcweb_sale_amt,


        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.order_cnt)
            else null
        end as order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.eva_order_cnt)
            else null
        end as eva_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.bad_eva_order_cnt)
            else null
        end as bad_eva_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_order_cnt)
            else null
        end as deliver_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.refund_order_cnt)
            else null
        end as refund_order_cnt,


        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.miniapp_order_cnt)
            else null
        end as miniapp_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_order_cnt)
            else null
        end as android_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_order_cnt)
            else null
        end as ios_order_cnt,

        case 
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_week_name_cn,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_order_cnt)
            else null
        end as pcweb_order_cnt
    from xxxxx.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
    group by
        grouping sets(
            -- 日期  + group_type
            (d.year_code,d.year_week_name_cn,s.group_type) ,
            -- 日期 + 城市 + group_type
            (d.year_code,d.year_week_name_cn,s.city_id ,s.city_name ,s.group_type), 
            -- 日期 + 城市 + 商圈  + group_type
            (d.year_code,d.year_week_name_cn,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
            -- 日期 + 城市 + 商圈 + 店铺  + group_type
            (d.year_code,d.year_week_name_cn,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
            -- 日期 + 品牌  + group_type
            (d.year_code,d.year_week_name_cn,s.brand_id ,s.brand_name ,s.group_type) ,
            -- 日期 + 大类  + group_type
            (d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.group_type) ,
            -- 日期 + 大类 + 中类  + group_type
            (d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
            -- 日期 + 大类 + 中类 + 小类  + group_type
            (d.year_code,d.year_week_name_cn,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
        )
)
select *  from t1 where group_type is not null;

统计分析: 按月来统计

insert into xxxxx_yp_dm_jiale.dm_sale 
with  dim_date as (
    select
        date_code,
        year_code,
        year_month,
        month_code ,
        day_month_num ,
        dim_date_id ,
        year_week_name_cn 
    from xxxxx_yp_dwd_jiale.dim_date
),
t1 as (
    select 
        '2022-05-10' as  date_time,
        'month' as time_type,
        d.year_code ,
        d.year_month,
        d.month_code,
        null as day_month_num ,
        null as dim_date_id,
        null as year_week_name_cn ,

        -- 处理维度:
        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then 'store'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then 'trade_area'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then 'city'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then 'brand'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then 'min_class'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then 'mid_class'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then 'max_class'
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then 'all'
            else null
        end as group_type,
        s.city_id ,
          s.city_name ,
          s.trade_area_id ,
          s.trade_area_name,
          s.store_id,
          s.store_name ,
          s.brand_id,
          s.brand_name,
          s.max_class_id,
          s.max_class_name,
          s.mid_class_id,
          s.mid_class_name,
          s.min_class_id,
        s.min_class_name,

        -- 指标:  
            case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.sale_amt)
            else null
        end as sale_amt,
        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.plat_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.plat_amt)
            else null
        end as plat_amt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_sale_amt)
            else null
        end as deliver_sale_amt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.mini_app_sale_amt)
            else null
        end as mini_app_sale_amt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_sale_amt)
            else null
        end as android_sale_amt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_sale_amt)
            else null
        end as ios_sale_amt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_sale_amt)
            else null
        end as pcweb_sale_amt,


        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.order_cnt)
            else null
        end as order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.eva_order_cnt)
            else null
        end as eva_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.bad_eva_order_cnt)
            else null
        end as bad_eva_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_order_cnt)
            else null
        end as deliver_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.refund_order_cnt)
            else null
        end as refund_order_cnt,


        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.miniapp_order_cnt)
            else null
        end as miniapp_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_order_cnt)
            else null
        end as android_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_order_cnt)
            else null
        end as ios_order_cnt,

        case 
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_month,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_order_cnt)
            else null
        end as pcweb_order_cnt
    from xxxxx.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
    group by
        grouping sets(
            -- 日期  + group_type
            (d.year_code,d.year_month,d.month_code,s.group_type) ,
            -- 日期 + 城市 + group_type
            (d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name ,s.group_type), 
            -- 日期 + 城市 + 商圈  + group_type
            (d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
            -- 日期 + 城市 + 商圈 + 店铺  + group_type
            (d.year_code,d.year_month,d.month_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
            -- 日期 + 品牌  + group_type
            (d.year_code,d.year_month,d.month_code,s.brand_id ,s.brand_name ,s.group_type) ,
            -- 日期 + 大类  + group_type
            (d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.group_type) ,
            -- 日期 + 大类 + 中类  + group_type
            (d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
            -- 日期 + 大类 + 中类 + 小类  + group_type
            (d.year_code,d.year_month,d.month_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
        )
)
select *  from t1 where group_type is not null;

统计分析: 按年来统计

insert into xxxxx_yp_dm_jiale.dm_sale 
with  dim_date as (
    select
        date_code,
        year_code,
        year_month,
        month_code ,
        day_month_num ,
        dim_date_id ,
        year_week_name_cn 
    from xxxxx_yp_dwd_jiale.dim_date
),
t1 as (
    select 
        '2022-05-10' as  date_time,
        'year' as time_type,
        d.year_code ,
        null as year_month,
        null as month_code,
        null as day_month_num ,
        null as dim_date_id,
        null as year_week_name_cn ,

        -- 处理维度:
        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then 'store'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then 'trade_area'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then 'city'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then 'brand'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then 'min_class'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then 'mid_class'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then 'max_class'
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then 'all'
            else null
        end as group_type,
        s.city_id ,
          s.city_name ,
          s.trade_area_id ,
          s.trade_area_name,
          s.store_id,
          s.store_name ,
          s.brand_id,
          s.brand_name,
          s.max_class_id,
          s.max_class_name,
          s.mid_class_id,
          s.mid_class_name,
          s.min_class_id,
        s.min_class_name,

        -- 指标:  
            case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.sale_amt)
            else null
        end as sale_amt,
        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.plat_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.plat_amt)
            else null
        end as plat_amt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_sale_amt)
            else null
        end as deliver_sale_amt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.mini_app_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.mini_app_sale_amt)
            else null
        end as mini_app_sale_amt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_sale_amt)
            else null
        end as android_sale_amt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_sale_amt)
            else null
        end as ios_sale_amt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_sale_amt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_sale_amt)
            else null
        end as pcweb_sale_amt,


        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.order_cnt)
            else null
        end as order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.eva_order_cnt)
            else null
        end as eva_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.bad_eva_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.bad_eva_order_cnt)
            else null
        end as bad_eva_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.deliver_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.deliver_order_cnt)
            else null
        end as deliver_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.refund_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.refund_order_cnt)
            else null
        end as refund_order_cnt,


        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.miniapp_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.miniapp_order_cnt)
            else null
        end as miniapp_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.android_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.android_order_cnt)
            else null
        end as android_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.ios_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.ios_order_cnt)
            else null
        end as ios_order_cnt,

        case 
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 15  and  s.group_type = 'store'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 31  and  s.group_type = 'trade_area'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 63  and  s.group_type = 'city'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 119  and  s.group_type = 'brand'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 120  and  s.group_type = 'min_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 121  and  s.group_type = 'mid_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 123  and  s.group_type = 'max_class'
                then sum(s.pcweb_order_cnt)
            when grouping(d.year_code,s.city_id ,s.trade_area_id ,s.store_id,s.brand_id ,s.max_class_id ,s.mid_class_id ,s.min_class_id ) = 127  and  s.group_type = 'all'
                then sum(s.pcweb_order_cnt)
            else null
        end as pcweb_order_cnt
    from xxxxx_yp_dws_jiale.dws_sale_daycount s left join dim_date d on s.dt = d.date_code
    group by
        grouping sets(
            -- 日期  + group_type
            (d.year_code,s.group_type) ,
            -- 日期 + 城市 + group_type
            (d.year_code,s.city_id ,s.city_name ,s.group_type), 
            -- 日期 + 城市 + 商圈  + group_type
            (d.year_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.group_type),
            -- 日期 + 城市 + 商圈 + 店铺  + group_type
            (d.year_code,s.city_id ,s.city_name,s.trade_area_id ,s.trade_area_name ,s.store_id,s.store_name ,s.group_type),
            -- 日期 + 品牌  + group_type
            (d.year_code,s.brand_id ,s.brand_name ,s.group_type) ,
            -- 日期 + 大类  + group_type
            (d.year_code,s.max_class_id ,s.max_class_name ,s.group_type) ,
            -- 日期 + 大类 + 中类  + group_type
            (d.year_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name, s.group_type),
            -- 日期 + 大类 + 中类 + 小类  + group_type
            (d.year_code,s.max_class_id ,s.max_class_name ,s.mid_class_id ,s.mid_class_name,s.min_class_id ,s.min_class_name ,s.group_type)
        )
)
select *  from t1 where group_type is not null;

如果是增量, 如何处理呢? 是否可以直接获取最近上一天的数据, 数据年月周即可呢? SQL加上where 过滤上一天的数据, 然后计算年月周?

肯定是不行的, 因为通过where, 锁定上一天的数据, 那么不管计算周 月 年 都是基于一天的数据进行计算, 这肯定不对

正常逻辑, 按照周统计, 那应该是将这最新一周数据进行统计, 按照月, 那应该将最新这一个月进行统计, 按照年, 应该将最新这一年的数据进行统计

所以, 就得思考一下, 如何处理呢?
    最简单的方案:(弊端: 导致一些不需要计算的月份, 周, 天也重新计算一遍)
        影响最大肯定就是这一年的数据, 对过去的年的数据肯定不会有任何影响, 那么我在增量处理的时候,先将当年的数据全部删除,  然后重新统计这一年的所有的年 月 周 日的数据即可,where条件筛选当年的数据即可

   精准的方案: -- 在后续项目实战中, 可以尝试试一试
       在删除的时候, 我只删除,当月维度, 当周维度, 当年的维度的统计结果的数据, 这样后续统计的时候, 只需要统计当月 当周 当日的数据


在presto中,大家可以直接通过presto提供的delete语句删除数据即可

有一个事情:

通过presto写入的数据到HIVE上, 然后通过HIEV查询, 是无法查询的, 只能通过presto查询, 原因:  CDH HIVE 和 presto不兼容, 不支持查询 (CDH不希望客户使用presto 希望客户使用impala, 所以对presto做了禁用)  但是如果使用apache版本的hive, 其实没有问题

1.2 商品主题统计宽表

主要指标有：下单次数、下单件数、下单金额、被支付次数、被支付件数、被支付金额、被退款次数、被退款件数、被退款金额、被加入购物车次数、被加入购物车件数、被收藏次数、好评数、中评数、差评数。
维度有：商品+日期(总累计值,近30天的数据)。

构建DM层商品主题统计宽表(hive中执行):

create table xxx.dm_sku
(

  sku_id string comment 'sku_id',
  sku_name string comment '商品名称',
  order_last_30d_count bigint comment '最近30日被下单次数',
  order_last_30d_num bigint comment '最近30日被下单件数',
  order_last_30d_amount decimal(38,2)  comment '最近30日被下单金额',
  order_count bigint comment '累积被下单次数',
  order_num bigint comment '累积被下单件数',
  order_amount decimal(38,2) comment '累积被下单金额',
  payment_last_30d_count   bigint  comment '最近30日被支付次数',
  payment_last_30d_num bigint comment '最近30日被支付件数',
  payment_last_30d_amount  decimal(38,2) comment '最近30日被支付金额',
  payment_count   bigint  comment '累积被支付次数',
  payment_num bigint comment '累积被支付件数',
  payment_amount  decimal(38,2) comment '累积被支付金额',
  refund_last_30d_count bigint comment '最近三十日退款次数',
  refund_last_30d_num bigint comment '最近三十日退款件数',
  refund_last_30d_amount decimal(38,2) comment '最近三十日退款金额',
  refund_count bigint comment '累积退款次数',
  refund_num bigint comment '累积退款件数',
  refund_amount decimal(38,2) comment '累积退款金额',
  cart_last_30d_count bigint comment '最近30日被加入购物车次数',
  cart_last_30d_num bigint comment '最近30日被加入购物车件数',
  cart_count bigint comment '累积被加入购物车次数',
  cart_num bigint comment '累积被加入购物车件数',
  favor_last_30d_count bigint comment '最近30日被收藏次数',
  favor_count bigint comment '累积被收藏次数',
  evaluation_last_30d_good_count bigint comment '最近30日好评数',
  evaluation_last_30d_mid_count bigint comment '最近30日中评数',
  evaluation_last_30d_bad_count bigint comment '最近30日差评数',
  evaluation_good_count bigint comment '累积好评数',
  evaluation_mid_count bigint comment '累积中评数',
  evaluation_bad_count bigint comment '累积差评数'
)
COMMENT '商品主题宽表'
ROW format delimited fields terminated BY '\t' 
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');

计算总累计值

-- 计算总累计值
select 
  sku_id,
  sku_name,
  sum(order_count) as order_count,
  sum(order_num) as order_num,
  sum(order_amount) as order_amount,
  sum(payment_count) as payment_count,
  sum(payment_num) as payment_num,
  sum(payment_amount) as payment_amount,
  sum(refund_count) as refund_count,
  sum(refund_num) as refund_num,
  sum(refund_amount) as refund_amount,
  sum(cart_count) as cart_count,
  sum(cart_num) as cart_num,
  sum(favor_count) as favor_count,
  sum(evaluation_good_count) as evaluation_good_count,
  sum(evaluation_mid_count) as evaluation_mid_count,
  sum(evaluation_bad_count) as evaluation_bad_count
from xxxx.dws_sku_daycount 
group by sku_id ,sku_name ;

计算最近30天 ```sql — 计算总累计值 select sku_id, sku_name, sum(order_count) as order_last_30d_count, sum(order_num) as order_last_30d_num, sum(order_amount) as order_last_30d_amount, sum(payment_count) as payment_last_30d_count, sum(payment_num) as payment_last_30d_num, sum(payment_amount) as payment_last_30d_amount, sum(refund_count) as refund_last_30d_count, sum(refund_num) as refund_last_30d_num, sum(refund_amount) as refund_last_30d_amount, sum(cart_count) as cart_last_30d_count, sum(cart_num) as cart_last_30d_num, sum(favor_count) as favor_last_30d_count, sum(evaluation_good_count) as evaluation_last_30d_good_count, sum(evaluation_mid_count) as evaluation_last_30d_mid_count, sum(evaluation_bad_count) as evaluation_last_30d_bad_count from xxxx.dws_sku_daycount where dt between ‘2021-07-01’ and ‘2021-07-31’ group by sku_id ,sku_name ;

— 如果动态获取最近30天的数据呢? 最近30天范围: 上一天日期 ~ 上一天的前30天 select * from hive.bj59_yp_dws_jiale.dws_sku_daycount where dt between date_format(date_add(‘day’,-30,date_add(‘day’,-1,now())), ‘%Y-%m-%d’) and date_format(date_add(‘day’,-1,now()), ‘%Y-%m-%d’) — 思考: 如何获取当前的时间呢? select NOW();

— 第二个思考: 如果让这个时间 -30天呢? select date_add(‘day’,-30,date_add(‘day’,-1,now())) — 或者 select date_add(‘day’,-31,now())

— 第三个: 如何获取时间的年月日部分 select date_format(date_add(‘day’,-31,now()), ‘%Y-%m-%d’);


- 合并处理
```sql
insert  into xxxx.dm_sku 
with total_cnt as(
    -- 计算总累计值
    select 
        sku_id,
        sku_name,
        sum(order_count) as order_count,
        sum(order_num) as order_num,
        sum(order_amount) as order_amount,
        sum(payment_count) as payment_count,
        sum(payment_num) as payment_num,
        sum(payment_amount) as payment_amount,
        sum(refund_count) as refund_count,
        sum(refund_num) as refund_num,
        sum(refund_amount) as refund_amount,
        sum(cart_count) as cart_count,
        sum(cart_num) as cart_num,
        sum(favor_count) as favor_count,
        sum(evaluation_good_count) as evaluation_good_count,
        sum(evaluation_mid_count) as evaluation_mid_count,
        sum(evaluation_bad_count) as evaluation_bad_count
    from xxxx.dws_sku_daycount 
    group by sku_id ,sku_name 
),
l30_cnt as (
    select 
        sku_id,
        sku_name,
        sum(order_count) as order_last_30d_count,
        sum(order_num) as order_last_30d_num,
        sum(order_amount) as order_last_30d_amount,
        sum(payment_count) as payment_last_30d_count,
        sum(payment_num) as payment_last_30d_num,
        sum(payment_amount) as payment_last_30d_amount,
        sum(refund_count) as refund_last_30d_count,
        sum(refund_num) as refund_last_30d_num,
        sum(refund_amount) as refund_last_30d_amount,
        sum(cart_count) as cart_last_30d_count,
        sum(cart_num) as cart_last_30d_num,
        sum(favor_count) as favor_last_30d_count,
        sum(evaluation_good_count) as evaluation_last_30d_good_count,
        sum(evaluation_mid_count) as evaluation_last_30d_mid_count,
        sum(evaluation_bad_count) as evaluation_last_30d_bad_count
    from xxxx.dws_sku_daycount  where dt between  '2021-07-01'  and '2021-07-31'
    group by sku_id ,sku_name 
)
select 
    total_cnt.sku_id,
    total_cnt.sku_name,
    coalesce(l30_cnt.order_last_30d_count,0) as order_last_30d_count,
    coalesce(l30_cnt.order_last_30d_num,0) as order_last_30d_num,
    coalesce(l30_cnt.order_last_30d_amount,0) as order_last_30d_amount,
    total_cnt.order_count ,
    total_cnt.order_num ,
    total_cnt.order_amount ,
    coalesce(l30_cnt.payment_last_30d_count, 0 ) as payment_last_30d_count,
    coalesce(l30_cnt.payment_last_30d_num , 0 ) as payment_last_30d_num,
    coalesce(l30_cnt.payment_last_30d_amount, 0  ) as payment_last_30d_amount,
    total_cnt.payment_count,
    total_cnt.payment_num ,
    total_cnt.payment_amount ,
    coalesce(l30_cnt.refund_last_30d_count, 0 ) as refund_last_30d_count,
    coalesce(l30_cnt.refund_last_30d_num, 0 ) as refund_last_30d_num,
    coalesce(l30_cnt.refund_last_30d_amount, 0 ) as refund_last_30d_amount,
    total_cnt.refund_count,
    total_cnt.refund_num,
    total_cnt.refund_amount  ,
    coalesce(l30_cnt.cart_last_30d_count, 0 ) as cart_last_30d_count,
    coalesce(l30_cnt.cart_last_30d_num , 0 ) as cart_last_30d_num,
    total_cnt.cart_count,
    total_cnt.cart_num ,
    coalesce(l30_cnt.favor_last_30d_count , 0 ) as favor_last_30d_count,
    total_cnt.favor_count,
    coalesce(l30_cnt.evaluation_last_30d_good_count, 0 ) as evaluation_last_30d_good_count,
    coalesce(l30_cnt.evaluation_last_30d_mid_count, 0  ) as evaluation_last_30d_mid_count,
    coalesce(l30_cnt.evaluation_last_30d_bad_count, 0  ) as evaluation_last_30d_bad_count,
    total_cnt.evaluation_good_count,
    total_cnt.evaluation_mid_count ,
    total_cnt.evaluation_bad_count 
from total_cnt left join l30_cnt on total_cnt.sku_id = l30_cnt.sku_id;

增量实现方案: 比如又过了新的一天

-- 思考1: 在重新计算的时候, 是否需要将之前的总累计值的结果重新计算呢?
      不需要的, 只需要在原有的总累计的结果上, 将新增的这一天的数据结果累计在一起即可

-- 思考2: 在重新统计的时候, 是否需要将之前的最近30天的结果数据重新计算呢?  
      不重新计算: 
          旧的30天的结果 -30天以前的那一天的结果 + 新的这一天的结果

       重新计算: 
           直接计算最近30天的数据, 将我们结果覆盖到原有数据集上


       建议使用覆盖的操作, 比较简单, 而且对性能基本上没有损伤, 因为每次都是30天的数据量, 而且每一天的数据结果在DWS层以及计算过程

增量的SQL实现:

-- 第一步: 重新计算最近30天的数据, 和昨天的结果数据
with t1 as (
  select 
    sku_id,
    sku_name,
    sum(order_count) as order_last_30d_count,
    sum(order_num) as order_last_30d_num,
    sum(order_amount) as order_last_30d_amount,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            order_count,
            0
        )
    ) as order_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            order_num,
            0
        )
    ) as order_num,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            order_amount,
            0
        )
    ) as order_amount,
    sum(payment_count) as payment_last_30d_count,
    sum(payment_num) as payment_last_30d_num,
    sum(payment_amount) as payment_last_30d_amount,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            payment_count,
            0
        )
    ) as payment_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            payment_num,
            0
        )
    ) as payment_num,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            payment_amount,
            0
        )
    ) as payment_amount,
    sum(refund_count) as refund_last_30d_count,
    sum(refund_num) as refund_last_30d_num,
    sum(refund_amount) as refund_last_30d_amount,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            refund_count,
            0
        )
    ) as refund_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            refund_num,
            0
        )
    ) as refund_num,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            refund_amount,
            0
        )
    ) as refund_amount,
    sum(cart_count) as cart_last_30d_count,
    sum(cart_num) as cart_last_30d_num,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            cart_count,
            0
        )
    ) as cart_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            cart_num,
            0
        )
    ) as cart_num,
    sum(favor_count) as favor_last_30d_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            favor_count,
            0
        )
    ) as favor_count,
    sum(evaluation_good_count) as evaluation_last_30d_good_count,
    sum(evaluation_mid_count) as evaluation_last_30d_mid_count,
    sum(evaluation_bad_count) as evaluation_last_30d_bad_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            evaluation_good_count,
            0
        )
    ) as evaluation_good_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            evaluation_mid_count,
            0
        )
    ) as evaluation_mid_count,
    sum(
        if(
            dt = date_format(date_add('day',-1,now()), '%Y-%m-%d') ,
            evaluation_bad_count,
            0
        )
    ) as evaluation_bad_count
  from xxxx.dws_sku_daycount  where dt  between date_format(date_add('day',-30,date_add('day',-1,now())), '%Y-%m-%d')  and date_format(date_add('day',-1,now()), '%Y-%m-%d') 
  group by sku_id ,sku_name 
)
-- 第二步: 直接覆盖为新的最近30天的数据, 将累计的数据和昨日的数据加在一起
select
    coalesce(new.sku_id,old.sku_id) as sku_id,
    coalesce(new.sku_name,old.sku_name) as sku_name,

    coalesce(new.order_last_30d_count,0) as order_last_30d_count,
    coalesce(new.order_last_30d_num,0)  as order_last_30d_num,
    coalesce(new.order_last_30d_amount,0)  as order_last_30d_amount,

    coalesce(new.order_count,0) + coalesce(old.order_count, 0) as order_count,
    coalesce(new.order_num,0) + coalesce(old.order_num, 0) as order_num,
    coalesce(new.order_amount,0) + coalesce(old.order_amount, 0) as order_amount,

    coalesce(new.payment_last_30d_count, 0 ) as payment_last_30d_count,
    coalesce(new.payment_last_30d_num , 0 ) as payment_last_30d_num,
    coalesce(new.payment_last_30d_amount, 0  ) as payment_last_30d_amount,

    coalesce(new.payment_count,0) + coalesce(old.payment_count, 0) as payment_count,
    coalesce(new.payment_num,0) + coalesce(old.payment_num, 0) as payment_num,
    coalesce(new.payment_amount,0) + coalesce(old.payment_amount, 0) as payment_amount,

    coalesce(new.refund_last_30d_count, 0 ) as refund_last_30d_count,
    coalesce(new.refund_last_30d_num, 0 ) as refund_last_30d_num,
    coalesce(new.refund_last_30d_amount, 0 ) as refund_last_30d_amount,

    coalesce(new.refund_count,0) + coalesce(old.refund_count, 0) as refund_count,
    coalesce(new.refund_num,0) + coalesce(old.refund_num, 0) as refund_num,
    coalesce(new.refund_amount,0) + coalesce(old.refund_amount, 0) as refund_amount,

    coalesce(new.cart_last_30d_count, 0 ) as cart_last_30d_count,
    coalesce(new.cart_last_30d_num , 0 ) as cart_last_30d_num,

    coalesce(new.cart_count,0) + coalesce(old.cart_count, 0) as cart_count,
    coalesce(new.cart_num,0) + coalesce(old.cart_num, 0) as cart_num,

    coalesce(new.favor_last_30d_count , 0 ) as favor_last_30d_count,
    coalesce(new.favor_count,0) + coalesce(old.favor_count, 0) as favor_count,

    coalesce(new.evaluation_last_30d_good_count, 0 ) as evaluation_last_30d_good_count,
    coalesce(new.evaluation_last_30d_mid_count, 0  ) as evaluation_last_30d_mid_count,
    coalesce(new.evaluation_last_30d_bad_count, 0  ) as evaluation_last_30d_bad_count,

    coalesce(new.evaluation_good_count,0) + coalesce(old.evaluation_good_count, 0) as evaluation_good_count,
    coalesce(new.evaluation_mid_count,0) + coalesce(old.evaluation_mid_count, 0) as evaluation_mid_count,
    coalesce(new.evaluation_bad_count,0) + coalesce(old.evaluation_bad_count, 0) as evaluation_bad_count

from t1 new  full  join hive.bj59_yp_dm_jiale.dm_sku old  on  new.sku_id  = old.sku_id

如何将最终结果覆盖会到目标表呢?

    persto来说, 不支持 insert overwrite 操作, 无法直接覆盖, 替代操作, 建议使用delete from 的语法. 将数据删除, 删除后, 通过insert into 方式将数据导入即可

    主要: 不能先将旧删除数据, 因为一旦删除了, 上述的SQL无法执行

解决方案:  都需要清楚, 因为在实际中, 可能都会存在
    方案一:  采用临时表方案
        1) 创建一张临时表(需要提前在HIVE中创建好)
        2) 将结果数据导入到临时表
        3) 删除旧表中数据(persto: delete from 表)
        4) 将临时表数据通过 insert into 方式导入到目标表表
        5) 将临时表数据清空掉(persto: delete from 表)

    方案二: 采用分区表方式
        1) 改造DM层商品主题统计宽表, 将其变更为一个分区表, 分区字段为统计数据的时间字段
        2) 在全量导入的时候, 将数据导入到指定的分区中 
            insert into dm.商品统计宽表
            select   *, '2022-05-09' as dt from  .....
        3) 进行增量的数据统计操作, 统计后, 将数据直接导入到DM层商品主题统计宽表, 放置到新的分区中:
            insert into dm.商品统计宽表
            select   *, '2022-05-10' as dt from  .....
        4) 删除掉旧的分区的数据即可: (是否立即删除, 取决于业务是否需要保留前几天的统计结果)
               presto:  delete  from  表 where dt ='2022-05-09'


这两种方案, 我们是以当前我们项目的表情况来说明的. 如果在实际生产中, 大家需要提前想好后续增量使用什么方案, 直接提前对应建好相关方案的表即可

2 RPT层实现

作用: 根据后续对接的业务需求, 从DM层中抽取出相关的数据灌入到PRT层

创建RPT库: hive执行

create database IF NOT EXISTS bj59_yp_rpt_xxxx;

需求一: 按月统计，各个门店的月销售单量。 ```sql — 步骤一: 创建RPT层结果表 (HIVE中执行) CREATE TABLE bj59_yp_rpt_xxxx.rpt_sale_store_cnt_month( year_code string COMMENT ‘年code’, year_month string COMMENT ‘年月’, city_id string COMMENT ‘城市id’, city_name string COMMENT ‘城市name’, trade_area_id string COMMENT ‘商圈id’, trade_area_name string COMMENT ‘商圈名称’, store_id string COMMENT ‘店铺的id’, store_name string COMMENT ‘店铺名称’, order_store_cnt BIGINT COMMENT ‘店铺销售单量’, miniapp_order_store_cnt BIGINT COMMENT ‘店铺小程序销售单量’, android_order_store_cnt BIGINT COMMENT ‘店铺android销售单量’, ios_order_store_cnt BIGINT COMMENT ‘店铺ios销售单量’, pcweb_order_store_cnt BIGINT COMMENT ‘店铺pcweb销售单量’, sale_amt DECIMAL(38,4) COMMENT ‘销售收入’, mini_app_sale_amt DECIMAL(38,4) COMMENT ‘小程序成交额’, android_sale_amt DECIMAL(38,4) COMMENT ‘安卓APP成交额’, ios_sale_amt DECIMAL(38,4) COMMENT ‘苹果APP成交额’, pcweb_sale_amt DECIMAL(38,4) COMMENT ‘PC商城成交额’ ) COMMENT ‘门店月销售单量排行’ — 统计日期,不能用来分组统计 PARTITIONED BY(date_time STRING) ROW format delimited fields terminated BY ‘\t’ stored AS orc tblproperties (‘orc.compress’ = ‘SNAPPY’);

— SQL实现: insert into hive.bj59_yp_rpt_xxx.rpt_sale_store_cnt_month select year_code , year_month , city_id , city_name , trade_area_id , trade_area_name , store_id , store_name , order_cnt as order_store_cnt, miniapp_order_cnt as miniapp_order_store_cnt, android_order_cnt as android_order_store_cnt, ios_order_cnt as ios_order_store_cnt, pcweb_order_cnt as pcweb_order_store_cnt, sale_amt , mini_app_sale_amt , android_sale_amt , ios_sale_amt , pcweb_sale_amt , ‘2022-05-10’ as date_time from hive.bj59_yp_dm_xxxx.dm_sale where time_type =’month’ and group_type =’store’ order by year_month desc,order_cnt desc;


- 需求二: 统计出总退款率最高的Top10商品。
```sql
-- 退款率:  退款的订单数量 / 总的支付的数量 =  退款率

-- 步骤一: 构建目标表 
create table if not exists bj59_yp_rpt_xxxx.rpt_goods_refund_topN(
    sku_id string comment '商品id',
    sku_name string comment '商品名称',
    refund_radio decimal(38,2) comment '最近30天各个商品的退款率'
)
comment '各商品的TOP100退款率'
-- 统计时间
PARTITIONED BY(date_time STRING) 
row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress' = 'SNAPPY');

-- 步骤二: 编写SQL 灌入目标表
insert into hive.bj59_yp_rpt_xxxx.rpt_goods_refund_topn 
select 
    sku_id,
    sku_name ,
    -- 最近30天的累计的退款率
    if( 
        payment_last_30d_count  > 0,
        cast(refund_last_30d_count as decimal(38,2) )  /  cast(payment_last_30d_count as decimal(38,2)) ,
        0
    ) as refund_radio,
    '2022-05-10' as date_time    
from hive.bj59_yp_dm_xxx.dm_sku 
order by refund_radio desc limit 10;

3- 数据导出操作

第一步: 在MySQL中创建一个用于存储结果表的库 (本地需要创建, 云端不需要构建)
```
CREATE DATABASE yp_olap DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

思考: 如何将HIVE中数据导出到MySQL中呢?

方案一:  通过 SQOOP 解决

方案二:  使用presto解决
    presto可以对接多个数据源, 一条SQL语言可以跨越多个数据源进行操作

3.1 Presto整合Mysql (本地Presto集成)

第一步: 在hadoop01的presto的etc/catalog的目录下, 创建一个 mysql.properties, 并添加以下内容:

cd /export/server/presto/etc/catalog/
vim mysql.properties
输入: i
内容如下: 
connector.name=mysql
connection-url=jdbc:mysql://192.168.88.80:3306?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=utf8
connection-user=root
connection-password=123456

第二步: 将hadoop01中mysql.properties发送给其他的presto节点

cd /export/server/presto/etc/catalog/
scp -r mysql.properties hadoop02:$PWD

第三步: 重启presto所有的节点

cd /export/server/presto
./bin/launcher restart

第四步: 通过浏览器, 观察dbeaver所有节点是否全部都启动

第五步: 通过dbeaver连接presto , 在presto中查看是否可以看到mysql相关内容

3.2 使用Presto完成数据导出

1- 需要通过Presto在MySQL中创建目标表 ```sql — 通过presto 创建mysql的表: CREATE TABLE .rpt_sale_store_cnt_month( year_code varchar COMMENT ‘年code’, year_month varchar COMMENT ‘年月’, city_id varchar COMMENT ‘城市id’, city_name varchar COMMENT ‘城市name’, trade_area_id varchar COMMENT ‘商圈id’, trade_area_name varchar COMMENT ‘商圈名称’, store_id varchar COMMENT ‘店铺的id’, store_name varchar COMMENT ‘店铺名称’, order_store_cnt BIGINT COMMENT ‘店铺销售单量’, miniapp_order_store_cnt BIGINT COMMENT ‘店铺小程序销售单量’, android_order_store_cnt BIGINT COMMENT ‘店铺android销售单量’, ios_order_store_cnt BIGINT COMMENT ‘店铺ios销售单量’, pcweb_order_store_cnt BIGINT COMMENT ‘店铺pcweb销售单量’, sale_amt DECIMAL(38,4) COMMENT ‘销售收入’, mini_app_sale_amt DECIMAL(38,4) COMMENT ‘小程序成交额’, android_sale_amt DECIMAL(38,4) COMMENT ‘安卓APP成交额’, ios_sale_amt DECIMAL(38,4) COMMENT ‘苹果APP成交额’, pcweb_sale_amt DECIMAL(38,4) COMMENT ‘PC商城成交额’ , date_time varchar ) COMMENT ‘门店月销售单量排行’ ;


- 2- 执行灌入操作:  
   - 云端, 可能无法灌入, 但是本地是OK
```sql
insert into mysql_class9.class9.rpt_sale_store_cnt_month 
select 
*
from hive.bj59_yp_rpt_jiale.rpt_sale_store_cnt_month;

4.展示:

图表是一个Javaweb的项目,采用 spring cloud, spring boot 和 mybatis 相关后台框架,构建的后台图表系统, 前端是采用 VUE和 node.js 构建前端服务, 前后端分离架构 构建的项目<br />    此项工作, 一般来说 是不需要大数据开发工程师来处理, 主要是由JAVAWEB工程师 或者 BI工程师来实现, 我们仅仅将需要的数据导出对应数据源中<br />当然图表也可以使用fineBI来实现<br />项目成果展示界面:<br />    pc端地址:   [https://yp-bigscreen-dev.itheima.net/](https://yp-bigscreen-dev.itheima.net/)<br />手机端: [https://yp-h5-dev.itheima.net/#/home](https://yp-h5-dev.itheima.net/#/home)

5. 相关的面试题: 写成话术

1- 请简单介绍一下最近做的一个项目 (5分钟左右) ```properties 体现到以下这么几个点:

   项目的基本介绍(项目背景. 主要目的是什么), 项目的架构 , 项目的流转流程 , 我主要负责了那部分内容

项目架构 : 基于cloudera manager构建的大数据分析平台, 在此平台上, 搭建有 zookeeper, HDFS YARN ,HIVE, OOIZE SQOOP 同时还使用presto加快hive数据分析操作

项目的流转流程: 通过sqoop将mysql中数据导入到hive中, 在hive中对数据进行清洗转换处理工作,构建为集团数据中心, 将处理后的数据对接presto, 进行数据分析操作, 将分析后的结果, 通过presto导出到mysql, 最终通过图表进行数据展示操作, 整个统计过程是周而复始, 不断干, 所以加入oozie完成定时化自动调度工作

负责点: 只需要描述出在这一部分做的一些大致事情即可, 不需要详细描述具体流程 (主要将自己熟悉点,说出来, 便于面试官去问这一部分内容) 先说整个项目大致分为两个部分, 一个是构建集团数据中心, 一个基于主题各个主题数据集市

 在集团数据中心部分:  选择起一个业务模块 或者其中两个讲解

 在数据集市部分: 选择一个主题模块或者两个情况


- 2- 面试官会结合回答 挑选一些本人负责点, 来进行深入问答 : 建议写成话术
```properties
 重点关注: 
         DWD层:
         DWB层:
         DWS层: 

     在讲述的时候, 一定要紧密贴合项目, 具体描述出项目整个实施过程 , 万万不可描述非常空, 仅在描述层次作用

     一般会问一到二个层次

3- 在整个项目实施过程, 有没有遇到过问题 (你认为整个项目中最有难度点, 闪光点在哪里 ?)

此部分, 在回答的时候, 一定要往特殊的问题上说, 万万不能将一些语法性错误, 表选择性错误,比较低级错误

   提供可参考问题点: 
           1- DWB 或者 DWS层的 Join的数据倾斜
           2- DWS层 group by  数据倾斜
           3- DWB层 Join 优化方案
           4- DWS层 多表进行full join情况
           5- DWS层  当需要多表进行多次分组情况

4- 项目的基本情况问题:

   目的 考验是否真实做个这个项目