-- 创建表语句
drop table if exists tb_grouping;
create table tb_grouping
(
    dt         string,
    brand      string,
    city       string,
    sales_sum  int,
    order_num  int,
    group_type string
)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
        WITH SERDEPROPERTIES (
        'serialization.null.format' = '',
        'field.delim' = '\t'
        );
-- 加载数据 注意替换成你自己的数据路径      
load data LOCAL inpath '/root/data/data.txt' into table tb_grouping;

select dt,
       brand,
       city,
       case
           when grouping(dt, brand, city) = 3 and group_type = 'ALL' then sum(sales_sum)
           when grouping(dt, brand, city) = 1 and group_type = 'brand' then sum(sales_sum)
           when grouping(dt, brand, city) = 2 and group_type = 'city' then sum(sales_sum)
           else null
           end as s_total

from tb_grouping
group by dt, brand, city, group_type
    grouping sets (
    ( dt, group_type),
    ( dt, brand, group_type),
    ( dt, city, group_type )
    );