DWS层建设实战-1
Day07_DWS层建设实战-1
资料文档
课程内容大纲与学习目标
#课程内容大纲
1、聚合函数增强
grouping sets
cube、rollup
grouping
功能:针对分组聚合操作进行优化。
2、DWS层搭建
目标与需求
销售主题统计宽表--简易模型分析 知识点：grouping sets
销售主题统计宽表--复杂模型分析 知识点：row_number去重
新零售项目销售主题统计宽表的实现
#学习目标
掌握grouping sets等函数的功能、应用
掌握使用row_number函数进行去重操作
掌握数仓DWS层功能
理解销售主题宽表的实现
聚合函数增强--grouping sets介绍与使用
- 背景
- 数据环境准备
--在hive中建表操作
use test;
create table test.t_user(
month string,
day string,
userid string)
row format delimited fields terminated by ',';
--数据样例
2015-03,2015-03-10,user1
2015-03,2015-03-10,user5
2015-03,2015-03-12,user7
2015-04,2015-04-12,user3
2015-04,2015-04-13,user2
2015-04,2015-04-13,user4
2015-04,2015-04-16,user4
2015-03,2015-03-10,user2
2015-03,2015-03-10,user3
2015-04,2015-04-12,user5
2015-04,2015-04-13,user6
2015-04,2015-04-15,user3
2015-04,2015-04-15,user2
2015-04,2015-04-16,user1
- 需求
分别按照月（month）、天（day）、月和天（month,day）统计来访用户userid个数，并获取三者的结果集（一起插入到目标宽表中）。
目标表：month day cnt_nums
维度：
天
月
天 + 月
指标：
访问用户数
----------------------------
维度组合:
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺(自己写)
日期+品牌
日期+大类
日期+大类+中类
日期+大类+类+小类
--3个分组统计而已，简单。统计完再使用union all合并结果集。
--注意union all合并结果集需要各个查询返回字段个数、类型一致，因此需要合理的使用null来填充返回结果。
select
month,
day,
count(userid) as user_cnt
from test.t_user
group by month, day
union all
select
null as month,
day,
count(userid) as user_cnt
from test.t_user
group by day
union all
select
month,
null as day,
count(userid) as user_cnt
from test.t_user
group by month;
- 执行结果如下
思考一下，这样有什么不妥的地方吗？
首先感受就是执行时间很长，很长；
另外从sql层面分析，会对test.t_user查询扫描3次，因为是3个查询的结果集合并。
假如这是10个维度的所有组合计算同一个指标呢？？
2^10 * 1 条select查询 union all？？？？？
- grouping sets功能
根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL。
select
month,day,count(userid)
from test.t_user
group by month,day
grouping sets (month,day,(month,day));
1、使用grouping sets，执行结果与使用多个分组查询union合并结果集一样；
2、grouping sets查询速度吊打分组查询结果union all。大家可以使用explain执行计划查看两条sql的执行逻辑差异。
3、使用grouping sets只会对表进行一次扫描。
- hive中grouping sets语法
特别注意：Presto的grouping sets语法和hive略有差异。
---下面这个是Hive SQL语法支持
select
month,day,count(userid)
from test.t_user
group by month,day
grouping sets (month,day,(month,day));
----下面这个是Presto SQL语法支持
select
month,day,count(userid)
from test.t_user
group by
grouping sets (month,day,(month,day));
--区别就是在presto的语法中，group by后面不要再加上字段了。
聚合函数增强--cube、rollup介绍与使用
- cube
- cube翻译过来叫做立方体，data cubes就是数据立方体。
- cube的功能：实现多个任意维度的查询。也可以理解为所有维度组合。
公式：假如说有==N个维度，那么所有维度的组合的个数：2^N==
下面这个图，就显示了4个维度所有组合构成的数据立方体。
- 语法
-- cube的语法在hive和presto中是通用的
select month,day,count(userid)
from test.t_user
group by
cube (month, day); -- (month, day) ,month,day,() 如果有n个字段，则就是分组个数是2的n次方
--上述sql等价于
select month,day,count(userid)
from test.t_user
group by
grouping sets ((month,day), month, day, ());
- rollup
- 语法功能：实现从右到左递减多级的统计,显示统计某一层次结构的聚合。
即：rollup(a,b,c)等价于grouping sets((a,b,c),(a,b),(a),())。
rollup(日期，城市，商圈)
grouping sets((日期，城市，商圈),(日期，城市),(日期),())。
grouping sets((),(日期),(日期，城市),(日期，城市，商圈))。 !!!!!!!!!!!!!
-- rollup的语法在hive和presto中是通用的
select month,day,count(userid)
from test.t_user
group by
rollup (month,day);
--等价于
select month,day,count(userid)
from test.t_user
group by
grouping sets ((month,day), (month), ());
聚合函数增强--grouping介绍与使用
- 功能：使用grouping操作来判断当前数据是按照哪个字段来分组的。
对于给定的分组，如果分组中包含相应的列，则将位设置为0，否则将其设置为1。
- 例子
--为了计算高效 切换至Presto引擎中进行计算
select month,
day,
count(userid),
grouping(month) as m,
grouping(day) as d,
grouping(month, day) as m_d
from test.t_user
group by
grouping sets (month, day, (month, day));
- 解释说明
grouping(month)列为0时，可以看到month列都是有值的，为1时则相反，证明当前行是按照month来进行分组统计的；
grouping(day)同理，为0时day列有值，为1时day为空，证明当前行时按照day来进行分组统计的；
grouping(month, day)是grouping(month)、grouping(day)二进制数值组合后转换得到的数字：
a. 按照month分组，则month=0，day=1，组合后为01，二进制转换为十进制得到数字1；
b. 按照day分组，则month=1，day=0，组合后为10，二进制转换为十进制得到数字2；
c. 同时按照month和day分组，则month=0，day=0，组合后为00，二进制转换为十进制得到数字0。
因此可以使用grouping操作来判断当前数据是按照哪个字段来分组的。
目标与需求
- 新零售数仓分层图
- DWS
- 名称：数据服务层 service
- 功能：按主题划分，形成日统计的宽表，轻度汇总提前聚合操作。
- 解释：轻度提前聚合说的是先聚合出日的指标，后续可以上卷出周、月、年的指标。
dws这里，主题终于出现了~~~
一个主题所需要的指标、维度，可能往往需要多个DWB层的宽表共同参与计算才能得出。甚至还需要之前层如dwd等参与计算。
- 使用DataGrip在Hive中创建dws层
注意，对于建库建表操作，需直接使用Hive，因为Presto只是一个数据分析的引擎，其语法不一定支持直接在Hive中建库建表。
drop database if exists yp_dws cascade ;
create database if not exists yp_dws;
销售主题宽表--需求与建表
- 主题需求
- 指标
销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量
--共计: 16个指标
- 维度
日期、城市、商圈、店铺、品牌、商品大类、商品中类、商品小类
--共计: 8个维度
--cube所有组合: 2^8=256个
注意，其中日期这个维度很特殊，特殊在我们的表就是根据日期分区的，分区的字段是day天。
而dws这一层我们需要统计的也是按day统计，日统计宽表嘛
这也就意味着一个分区就是一天。
- 本主题需要维度组合
提示：256个组合都计算还是计算当中的一部分，主动权在于业务、需求，我们做的是技术实现。
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺
日期+品牌
日期+大类
日期+大类+中类
日期+大类+中类+小类
- 总计需要计算的指标
16*8=128
1、如果不加任何限制，只是求出每个指标，那么很简单，128个sql语句进行分组聚合操作。
2、实际中肯定不能那么做，需要一条sql完成128个指标的计算，并且把结果插入主题统计宽表中。
- Hive中建表
既然是把一个主题相关的数据统计数据都存储在一张表中，那么意味着这张统计宽表应该要清晰的记录出维度、指标，有的计算，没有的null补上。
CREATE TABLE yp_dws.dws_sale_daycount(
--维度
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 '小类名称',
group_type string COMMENT '分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all',
-- =======日统计=======
-- 销售收入
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 '销售主题日统计宽表'
PARTITIONED BY(dt STRING)
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
销售主题宽表--简易模型分析（分组聚合union）
为了更好的实现一条sql计算出所有的指标，这里我们先对数据模型进行简化操作。
这个模型理解了，销售主题宽表的计算就可以实现了。
- 需求
- 订单宽表t_order_detail
--建表（在hive中创建）
create table test.t_order_detail(
oid string comment '订单ID',
goods_id string comment '商品ID',
o_price int comment '订单总金额',
g_num int comment '商品数量',
g_price int comment '商品单价',
brand_id string comment '品牌ID',
dt string comment '日期'
) comment '订单详情宽表_简易模型'
row format delimited fields terminated by ',';
--加载数据
o01,g01,100,1,80,b01,2021-08-29
o01,g02,100,1,20,b02,2021-08-29
o02,g03,180,1,80,b01,2021-08-29
o02,g04,180,2,40,b02,2021-08-29
o02,g07,180,3,60,b01,2021-08-29
o03,g02,80,1,80,b02,2021-08-30
o04,g01,300,2,160,b01,2021-08-30
o04,g02,300,3,60,b02,2021-08-30
o04,g03,300,4,80,b01,2021-08-30
- 需求
- 指标：订单量、销售额
- 维度：日期、日期+品牌
- 计算出结果
- 实现思路（在presto中计算）
立马想到：多条sql分组聚合+union合并结果集，没有的字段使用null进行填补。
- step1:统计每天的订单量、销售额
select
dt,
count(distinct oid) as "订单量",
sum(g_price) as "销售额"
from test.t_order_detail
group by dt;
- step2:统计每天每个品牌的订单量、销售额
select
dt,
brand_id,
count(distinct oid) as "各品牌订单量",
sum(g_price) as "各品牌销售额"
from test.t_order_detail
group by dt,brand_id;
- step3:union all合并两个查询结果集
select
dt as "日期",
null as "品牌id",
count(distinct oid) as "订单量",
null as "各品牌订单量",
sum(g_price) as "销售额",
null as "各品牌销售额",
1 as group_id
from test.t_order_detail
group by dt
union all
select
dt as "日期",
brand_id as "品牌id",
null as "订单量",
count(distinct oid) as "各品牌订单量",
null as "销售额",
sum(g_price) as "各品牌销售额",
2 as group_id
from test.t_order_detail
group by dt,brand_id;
销售主题宽表--简易模型分析（grouping sets增强聚合）
思路：采用grouping sets增强聚合计算，并且使用grouping函数判断分组中是否包含字段。
0表示有，1表示没有。
- sql实现
select
count(distinct oid),
sum(g_price)
from test.t_order_detail
group by grouping sets(dt,(dt,brand_id));
--直接这样写，结果如下显示，不友好，无法区分是哪一个分组聚合的结果
- sql最终实现
可以考虑使用grouping函数判断分组中是否包含指定的字段，并且配合case when进行转换。
注意：grouping函数有为0，没有是1。
select
dt as "日期",
case when grouping(brand_id) =0
then brand_id
else null end as "品牌id", -- 第二列，如果分组中有品牌id,就显示，没有就null
count(distinct oid) as "订单量",
sum(g_price)
from test.t_order_detail
group by grouping sets(dt,(dt,brand_id));
--执行上面sql，看看效果。
--下面是最终完整版
select
dt as "日期",
case when grouping(brand_id) =0 --判断是否包含brand_id
then brand_id
else null end as "品牌id",
case when grouping(dt,brand_id) =1 --只包含日期 就是订单量
then count(distinct oid)
else null end as "订单量",
case when grouping(brand_id) =0 --包含品牌 就是各品牌订单量
then count(distinct oid)
else null end as "各品牌订单量",
case when grouping(brand_id) =1 --没有品牌，就是销售额
then sum(g_price)
else null end as "销售额",
case when grouping(brand_id) =0 --包含品牌，就是各个品牌销售额
then sum(g_price)
else null end as "各品牌销售额",
case when grouping(brand_id) = 1 --没有品牌 就是分组1 否则就是2
then 1
else 2 end as group_id
from test.t_order_detail
group by grouping sets(dt,(dt,brand_id));
销售主题宽表--复杂模型分析（去重）
- 项目订单宽表梳理
根据上述的简易模型我们去梳理一下项目中的yp_dwb.dwb_order_detail订单明细宽表。
把属于同一笔订单的所有商品信息提取出来，验证一下数据是否匹配模型。
--根据订单id分组，找出订单商品数最多的
select
order_id,
count (order_id) as nums
from yp_dwb.dwb_order_detail
group by order_id
order by nums desc limit 10;
--查看订单ID为dd190227318021f41f的信息
select * from yp_dwb.dwb_order_detail where order_id = 'dd190227318021f41f';
- 问题
上述简易模型中，数据是没有重复的，直接grouping sets 统计没有问题；
假如数据是重复的又该如何处理呢？如何进行去重？
或者说不管数据有没有重复，会不会重复，能不能设计一种解决方案，不管重复如何，先过滤重复，保证计算一定是正确的？？
--建表（在hive中创建）
create table test.t_order_detail_dup(
oid string comment '订单ID',
goods_id string comment '商品ID',
o_price int comment '订单总金额',
g_num int comment '商品数量',
g_price int comment '商品单价',
brand_id string comment '品牌ID',
dt string comment '日期'
) comment '订单详情宽表_复杂模型'
row format delimited fields terminated by ',';
--加载数据
o01,g01,100,1,80,b01,2021-08-29
o01,g02,100,1,20,b02,2021-08-29
o01,g01,100,1,80,b01,2021-08-29
o02,g03,180,1,80,b01,2021-08-29
o02,g04,180,2,40,b02,2021-08-29
o02,g04,180,2,40,b02,2021-08-29
o02,g07,180,3,60,b01,2021-08-29
o03,g02,80,1,80,b02,2021-08-30
o04,g01,300,2,160,b01,2021-08-30
o04,g02,300,3,60,b02,2021-08-30
o04,g03,300,4,80,b01,2021-08-30
- 实现思路
1、ROW_NUMBER() OVER(PARTITION BY 需要去重字段 ) ，这样相同的就会分到一组；
2、为分组中指定的去重字段标上行号,如果有重复的,选中行号为1的就可以。
- 比如只以订单oid去重
select
oid,
row_number() over(partition by oid) as rn1
from test.t_order_detail_dup;
--去重过程
with tmp as (select
oid,
row_number() over(partition by oid) as rn1
from test.t_order_detail_dup)
select * from tmp where rn1 = 1;
- 以订单oid+品牌brand_id去重
select
oid,
brand_id,
row_number() over(partition by oid,brand_id) as rn2
from test.t_order_detail_dup;
with tmp1 as (select
oid,
brand_id,
row_number() over(partition by oid,brand_id) as rn2
from test.t_order_detail_dup)
select * from tmp1 where rn2 = 1;
- 再比如以订单oid+品牌brand_id+商品goods_id去重
select
oid,
brand_id,
goods_id,
row_number() over(partition by oid,brand_id,goods_id) as rn3
from test.t_order_detail_dup;
with tmp2 as (select
oid,
brand_id,
goods_id,
row_number() over(partition by oid,brand_id,goods_id) as rn3
from test.t_order_detail_dup)
select * from tmp2 where rn3 = 1;
- 整合一起
select
oid,
brand_id,
goods_id,
row_number() over(partition by oid) as rn1,
row_number() over(partition by oid,brand_id) as rn2,
row_number() over(partition by oid,brand_id,goods_id) as rn3
from test.t_order_detail_dup;
- 结论
当我们以不同维度进行组合统计的时候，为了避免重复数据对最终结果的影响，可以考虑配合使用row_number去重。
销售主题宽表--step1--字段抽取
- 表关系
一切的前提是，先了解原始数据的结构和关系。
对于销售主题宽表来说，其当中的指标和维度字段分别来源于DWB层：订单明细宽表、店铺明细宽表、商品明细宽表。
比如商圈、店铺等维度来自于店铺明细宽表；大中小分类来自于商品明细宽表；而成交额等指标需要依赖订单明细宽表。
--以订单为准，以goods_id关联商品，以store_id关联店铺
select *
from dwb_order_detail o
left join dwb_goods_detail g on o.goods_id = g.id
left join dwb_shop_detail s on o.store_id = s.id;
- 字段抽取
关联之后，字段非常多，但是并不意味着每一个字段都是销售主题宽表统计需要的；
因此需要根据销售主题宽表的计算指标和维度，把相关的字段抽取出来
select
--维度
o.dt as create_date,--日期(注意，分区表的粒度就是按天分区)
s.city_id,
s.city_name, --城市
s.trade_area_id,
s.trade_area_name, --商圈
s.id as store_id,
s.store_name, --店铺
g.brand_id,
g.brand_name, --品牌
g.max_class_id,
g.max_class_name, --商品大类
g.mid_class_id,
g.mid_class_name,-- 商品中类
g.min_class_id,
g.min_class_name,--商品小类
--订单量指标
o.order_id, --订单id
o.goods_id, --商品id
--金额指标
o.order_amount, --订单金额
o.total_price, --商品金额(商品数量*商品单价)
o.plat_fee, --平台分润
o.dispatcher_money, --配送员的运费
--判断条件
o.order_from, --订单来源渠道：安卓、苹果....
o.evaluation_id, --评价单id,不为空表示有评价
o.geval_scores, --综合评分，差评的计算
o.delievery_id, --配送单ID(如果不为null，表示是配送单，其他还有可能是自提、商家配送)
o.refund_id --退款单id,不为空表示有退款
from dwb_order_detail o
left join dwb_goods_detail g on o.goods_id = g.id
left join dwb_shop_detail s on o.store_id = s.id;
销售主题宽表--step2--row_number去重
使用row_number分组去重的时候需要注意：
1、对于城市、商圈、店铺等维度的成交额计算，根据订单order_amount汇总求和即可；
2、而对于品牌、大类、中类、小类等维度成交额计算，需要根据goods_id计算。
- 以品牌为例
--上述表的数据中，如果计算不同品牌的成交额，就不能再根据订单金额相加了
--而是必须根据每个订单中，这个品牌的金额进行计算
--因为订单中可以有不同的商品品牌。
- 分组去重
row_number() over(partition by order_id) as order_rn,
row_number() over(partition by order_id,g.brand_id) as brand_rn,
row_number() over(partition by order_id,g.max_class_name) as maxclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name) as midclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name) as minclass_rn,
--下面分组加入goods_id
row_number() over(partition by order_id,g.brand_id,o.goods_id) as brand_goods_rn,
row_number() over(partition by order_id,g.max_class_name,o.goods_id) as maxclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,o.goods_id) as midclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name,o.goods_id) as minclass_goods_rn
销售主题宽表--step3--grouping sets分组
- 使用CTE表达式针对上面抽取字段、分组去重的结果进行引导
with temp as (
select 抽取字段、row_number去重)
注意，到这一步为止，temp表的数据已经和之前的我们创建的简易模型、复杂模型差不多了。
后面的技术主要就是case when+grouoing判断。
- 根据业务需求进行维度组合，使用grouping sets进行分组。
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺
日期+品牌
日期+大类
日期+大类+中类
日期+大类+中类+小类
with temp as (
select 抽取字段、row_number去重)
select
xxxxx
from temp
group by
grouping sets(
create_date, --日期
(create_date,city_id,city_name),--日期+城市
(create_date,city_id,city_name,trade_area_id,trade_area_name),--日期+城市+商圈
(create_date,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name), --日期+城市+商圈+店铺
(create_date,brand_id,brand_name),--日期+品牌
(create_date,max_class_id,max_class_name),--日期+大类
(create_date,max_class_id,max_class_name,mid_class_id,mid_class_name),--日期+大类+中类
(create_date,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)--日期+大类+中类+小类
);
销售主题宽表--step4--维度字段判断
提示：可以根据待插入的目标表yp_dws.dws_sale_daycount的字段顺序，把结果返回。
case when grouping(city_id) = 0 --如果分组中包含city_id 则grouping为0 那么就返回city_id
then city_id
else null end as city_id ,
case when grouping(city_id) = 0
then city_name
else null end as city_name ,
case when grouping(trade_area_id) = 0--商圈
then trade_area_id
else null end as trade_area_id ,
case when grouping(trade_area_id) = 0
then trade_area_name
else null end as trade_area_name ,
case when grouping(store_id) = 0 --店铺
then store_id
else null end as store_id ,
case when grouping(store_id) = 0
then store_name
else null end as store_name ,
case when grouping(brand_id) = 0 --品牌
then brand_id
else null end as brand_id ,
case when grouping(brand_id) = 0
then brand_name
else null end as brand_name ,
case when grouping(max_class_id) = 0 --大类
then max_class_id
else null end as max_class_id ,
case when grouping(max_class_id) = 0
then max_class_name
else null end as max_class_name ,
case when grouping(mid_class_id) = 0 --中类
then mid_class_id
else null end as mid_class_id ,
case when grouping(mid_class_id) = 0
then mid_class_name
else null end as mid_class_name ,
case when grouping(min_class_id) = 0--小类
then min_class_id
else null end as min_class_id ,
case when grouping(min_class_id) = 0
then min_class_name
else null end as min_class_name ,
case when grouping(store_id,store_name) = 0 --分组类型
then 'store'
when grouping(trade_area_id ,trade_area_name) = 0
then 'trade_area'
when grouping (city_id,city_name) = 0
then 'city'
when grouping (brand_id,brand_name) = 0
then 'brand'
when grouping (min_class_id,min_class_name) = 0
then 'min_class'
when grouping (mid_class_id,mid_class_name) = 0
then 'mid_class'
when grouping (max_class_id,max_class_name) = 0
then 'max_class'
when grouping (create_date) = 0
then 'all'
else 'other' end as group_type,
销售主题宽表--step5--销售收入统计
--指标计算 注意每个指标都对应着8个分组维度的计算
--1、销售收入指标 sale_amt
case when grouping(store_id,store_name) =0 --如果分组中包含店铺,则分组为：日期+城市+商圈+店铺
then sum(if( order_rn = 1 and store_id is not null ,order_amount,0)) --只有分组中标号为1的(去重)，店铺不为空的才参与计算
--then sum(if( order_rn = 1 and store_id is not null ,coalesce(order_amount,0),0)) --使用coalesce函数更加成熟
when grouping (trade_area_id ,trade_area_name) = 0 --日期+城市+商圈
then sum(if( order_rn = 1 and trade_area_id is not null ,order_amount,0))
when grouping (city_id,city_name) = 0 --日期+城市
then sum(if( order_rn = 1 and city_id is not null,order_amount,0))
when grouping (brand_id,brand_name) = 0 --日期+品牌
then sum(if(brand_goods_rn = 1 and brand_id is not null,total_price,0))
when grouping (min_class_id,min_class_name) = 0 --日期+大类+中类+小类
then sum(if(minclass_goods_rn = 1 and min_class_id is not null ,total_price,0))
when grouping (mid_class_id,mid_class_name) = 0 --日期+大类+中类
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null,total_price,0))
when grouping (max_class_id,max_class_name) = 0 ----日期+大类
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null ,total_price,0))
when grouping (create_date) = 0 --日期
then sum(if(order_rn=1 and create_date is not null,order_amount,0))
else null end as sale_amt,
销售主题宽表--step6--金额指标统计
--2、平台收入 plat_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null ,plat_fee,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null ,plat_fee,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null,plat_fee,0))
when grouping (brand_id,brand_name) = 0
then null
when grouping (min_class_id,min_class_name) = 0
then null
when grouping (mid_class_id,mid_class_name) = 0
then null
when grouping (max_class_id,max_class_name) = 0
then null
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null,plat_fee,0))
else null end as plat_amt ,
-- 3、配送成交额 deliver_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and delievery_id is not null ,dispatcher_money,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and delievery_id is not null,dispatcher_money,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and delievery_id is not null,dispatcher_money,0))
when grouping (brand_id,brand_name) = 0
then null
when grouping (min_class_id,min_class_name) = 0
then null
when grouping (mid_class_id,mid_class_name) = 0
then null
when grouping (max_class_id,max_class_name) = 0
then null
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and delievery_id is not null ,dispatcher_money,0))
else null end as deliver_sale_amt ,
-- 4、小程序成交额 mini_app_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='miniapp' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='miniapp',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='miniapp',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='miniapp',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='miniapp',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='miniapp',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='miniapp',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='miniapp',order_amount ,0))
else null end as mini_app_sale_amt ,
-- 5、安卓成交额 android_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='android' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='android',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='android',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='android',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='android',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='android',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='android',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='android',order_amount ,0))
else null end as android_sale_amt ,
-- 6、苹果成交额 ios_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='ios' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='ios',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='ios',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='ios',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='ios',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='ios',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='ios',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='ios',order_amount ,0))
else null end as ios_sale_amt ,
-- 7、pc成交额 pcweb_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='pcweb' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='pcweb',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='pcweb',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='pcweb',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='pcweb',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='pcweb',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='pcweb',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='pcweb',order_amount ,0))
else null end as pcweb_sale_amt ,
销售主题宽表--step7--订单量指标统计
-- 8、订单量 order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null , order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null , order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null , order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null , order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null , order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null , order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 , order_id,null))
else null end as order_cnt ,
--9、 参评单量 eva_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and evaluation_id is not null , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and evaluation_id is not null , order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and evaluation_id is not null , order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and evaluation_id is not null , order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and evaluation_id is not null , order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and evaluation_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and evaluation_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and evaluation_id is not null, order_id,null))
else null end as eva_order_cnt ,
--10、差评单量 bad_eva_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6 , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
else null end as bad_eva_order_cnt ,
--11、配送单量 deliver_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and delievery_id is not null, order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and delievery_id is not null, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and delievery_id is not null, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and delievery_id is not null, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and delievery_id is not null, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and delievery_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and delievery_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and delievery_id is not null, order_id,null))
else null end as deliver_order_cnt ,
--12、退款单量 refund_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and refund_id is not null, order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and refund_id is not null, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and refund_id is not null, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and refund_id is not null, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and refund_id is not null, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and refund_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and refund_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and refund_id is not null, order_id,null))
else null end as refund_order_cnt ,
-- 13、小程序订单量 miniapp_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'miniapp', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'miniapp', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'miniapp', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'miniapp', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'miniapp', order_id,null))
else null end as miniapp_order_cnt ,
-- 14、android订单量 android_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'android', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'android', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'android', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'android', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'android', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'android', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'android', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'android', order_id,null))
else null end as android_order_cnt ,
-- 15、ios订单量 ios_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'ios', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'ios', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'ios', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'ios', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'ios', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'ios', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'ios', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'ios', order_id,null))
else null end as ios_order_cnt ,
--16、pcweb订单量 pcweb_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'pcweb', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'pcweb', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'pcweb', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'pcweb', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'pcweb', order_id,null))
else null end as pcweb_order_cnt ,
销售主题宽表--最终完整版sql实现
-------------------------------老师课堂代码----------------------------------------
insert into yp_dws.dws_sale_daycount
select * from(
with temp as (
select
o.dt as create_time , --o.dt,
sd.province_id,
sd.province_name,
sd.city_id,
sd.city_name,
sd.trade_area_id,
sd.trade_area_name,
sd.store_name,
o.store_id,
gd.brand_id,
gd.brand_name,
gd.max_class_id,
gd.max_class_name,
gd.mid_class_id,
gd.mid_class_name,
gd.min_class_id,
gd.min_class_name,
-- 指标字段:
o.order_id, -- 订单id 计算订单量
o.order_amount, -- 订单销售收入
o.goods_amount, -- 商品销售金额
o.plat_fee, -- 平台利润
o.delivery_fee, -- 配送运费
-- 用于判断的字段
o.order_from,
o.evaluation_id, -- 评价表 id 如果不为null, 说明订单有评价信息的
o.delievery_id, -- 配送表id
o.geval_scores, -- 评分信息
o.refund_id,
row_number() over (partition by order_id) as rk1, -- 去重
row_number() over (partition by order_id,goods_id) as rk2 -- 去重
from (select * from yp_dwb.dwb_order_detail where is_pay = 1 and order_state not in (1, 7)) as o
left join yp_dwb.dwb_goods_detail gd on o.goods_id = gd.id
left join yp_dwb.dwb_shop_detail sd on o.store_id = sd.id
)
select
-- 维度
city_id,
city_name,
trade_area_id,
trade_area_name,
store_id,
store_name,
brand_id,
brand_name,
max_class_id,
max_class_name,
mid_class_id,
mid_class_name,
min_class_id,
min_class_name,
case when grouping(store_id) = 0
then 'store'
when grouping(trade_area_id) = 0
then 'trade_area'
when grouping(city_id) = 0
then 'city'
when grouping(min_class_id) = 0
then 'min_class'
when grouping(mid_class_id) = 0
then 'mid_class'
when grouping(max_class_id) = 0
then 'max_class'
when grouping(brand_id) = 0
then 'brand'
when grouping(create_time) = 0
then 'all'
else
'other'
end as group_type,
-- 指标
case when grouping(store_id) = 0 -- 总成交额
then sum(if(rk1 = 1 and store_id is not null, coalesce(order_amount,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null, coalesce(order_amount,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null, coalesce(order_amount,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null,coalesce(goods_amount,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null,coalesce(goods_amount,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null,coalesce(goods_amount,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null,coalesce(goods_amount,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null,coalesce(order_amount,0),0)) -- 日期
end as sale_amt,
case when grouping(store_id) = 0 -- 平台收入
then sum(if(rk1 = 1 and store_id is not null, coalesce(plat_fee,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null, coalesce(plat_fee,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null, coalesce(plat_fee,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null,coalesce(plat_fee,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null,coalesce(plat_fee,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null,coalesce(plat_fee,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null,coalesce(plat_fee,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null,coalesce(plat_fee,0),0)) -- 日期
end as plat_amt,
case when grouping(store_id) = 0 -- 配送成交额
then sum(if(rk1 = 1 and store_id is not null, coalesce(delivery_fee,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null, coalesce(delivery_fee,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null, coalesce(delivery_fee,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null,coalesce(delivery_fee,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null,coalesce(delivery_fee,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null,coalesce(delivery_fee,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null,coalesce(delivery_fee,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null,coalesce(delivery_fee,0),0)) -- 日期
end as deliver_sale_amt,
case when grouping(store_id) = 0 -- 小程序miniapp成交额
then sum(if(rk1 = 1 and store_id is not null and order_from = 'miniapp', coalesce(order_amount,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null and order_from = 'miniapp', coalesce(order_amount,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null and order_from = 'miniapp', coalesce(order_amount,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null and order_from = 'miniapp',coalesce(goods_amount,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null and order_from = 'miniapp',coalesce(goods_amount,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null and order_from = 'miniapp',coalesce(goods_amount,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null and order_from = 'miniapp',coalesce(goods_amount,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null and order_from = 'miniapp',coalesce(order_amount,0),0)) -- 日期
end as mini_app_sale_amt,
case when grouping(store_id) = 0 -- android成交额
then sum(if(rk1 = 1 and store_id is not null and order_from = 'android', coalesce(order_amount,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null and order_from = 'android', coalesce(order_amount,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null and order_from = 'android', coalesce(order_amount,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null and order_from = 'android',coalesce(goods_amount,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null and order_from = 'android',coalesce(goods_amount,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null and order_from = 'android',coalesce(goods_amount,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null and order_from = 'android',coalesce(goods_amount,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null and order_from = 'android',coalesce(order_amount,0),0)) -- 日期
end as android_sale_amt,
case when grouping(store_id) = 0 -- ios成交额
then sum(if(rk1 = 1 and store_id is not null and order_from = 'ios', coalesce(order_amount,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null and order_from = 'ios', coalesce(order_amount,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null and order_from = 'ios', coalesce(order_amount,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null and order_from = 'ios',coalesce(goods_amount,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null and order_from = 'ios',coalesce(goods_amount,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null and order_from = 'ios',coalesce(goods_amount,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null and order_from = 'ios',coalesce(goods_amount,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null and order_from = 'ios',coalesce(order_amount,0),0)) -- 日期
end as ios_sale_amt,
case when grouping(store_id) = 0 -- pcweb成交额
then sum(if(rk1 = 1 and store_id is not null and order_from = 'pcweb', coalesce(order_amount,0),0))
when grouping(trade_area_id) = 0
then sum(if(rk1 = 1 and trade_area_id is not null and order_from = 'pcweb', coalesce(order_amount,0),0))
when grouping(city_id) = 0
then sum(if(rk1 = 1 and city_id is not null and order_from = 'pcweb', coalesce(order_amount,0),0))
when grouping(min_class_id) = 0
then sum(if(rk2 = 1 and min_class_id is not null and order_from = 'pcweb',coalesce(goods_amount,0),0))
when grouping(mid_class_id) = 0
then sum(if(rk2 = 1 and mid_class_id is not null and order_from = 'pcweb',coalesce(goods_amount,0),0))
when grouping(max_class_id) = 0
then sum(if(rk2 = 1 and max_class_id is not null and order_from = 'pcweb',coalesce(goods_amount,0),0))
when grouping(brand_id) = 0
then sum(if(rk2 = 1 and brand_id is not null and order_from = 'pcweb',coalesce(goods_amount,0),0))
when grouping(create_time) = 0
then sum(if(rk1 = 1 and create_time is not null and order_from = 'pcweb',coalesce(order_amount,0),0)) -- 日期
end as pcweb_sale_amt,
case when grouping(store_id) = 0 -- 成交单量
then count(if(rk1 = 1 and store_id is not null , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null , order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null , order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null , order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null , order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null , order_id,null)) -- 日期
end as order_cnt,
case when grouping(store_id) = 0 -- 参评单量
then count(if(rk1 = 1 and store_id is not null and evaluation_id is not null , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and evaluation_id is not null , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and evaluation_id is not null, order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and evaluation_id is not null, order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and evaluation_id is not null , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and evaluation_id is not null , order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and evaluation_id is not null , order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and evaluation_id is not null , order_id,null)) -- 日期
end as eva_order_cnt,
case when grouping(store_id) = 0 -- 差评单量
then count(if(rk1 = 1 and store_id is not null and evaluation_id is not null and geval_scores < 6 , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and evaluation_id is not null and geval_scores < 6 , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and evaluation_id is not null and geval_scores < 6, order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and evaluation_id is not null and geval_scores < 6, order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and evaluation_id is not null and geval_scores < 6 , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and evaluation_id is not null and geval_scores < 6, order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and evaluation_id is not null and geval_scores < 6 , order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and evaluation_id is not null and geval_scores < 6 , order_id,null)) -- 日期
end as bad_eva_order_cnt,
case when grouping(store_id) = 0 -- 配送单量
then count(if(rk1 = 1 and store_id is not null and delievery_id is not null , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and delievery_id is not null , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and delievery_id is not null, order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and delievery_id is not null, order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and delievery_id is not null , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and delievery_id is not null, order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and delievery_id is not null, order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and delievery_id is not null , order_id,null)) -- 日期
end as deliver_order_cnt,
case when grouping(store_id) = 0 -- 退款单量
then count(if(rk1 = 1 and store_id is not null and refund_id is not null , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and refund_id is not null , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and refund_id is not null, order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and refund_id is not null, order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and refund_id is not null , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and refund_id is not null, order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and refund_id is not null, order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and refund_id is not null , order_id,null)) -- 日期
end as refund_order_cnt, -- 退款单量
case when grouping(store_id) = 0 -- 小程序成交单量
then count(if(rk1 = 1 and store_id is not null and order_from = 'miniapp' , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and order_from = 'miniapp' , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and order_from = 'miniapp', order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and order_from = 'miniapp' , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and order_from = 'miniapp', order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and order_from = 'miniapp' , order_id,null)) -- 日期
end as miniapp_order_cnt,
case when grouping(store_id) = 0 -- 安卓APP订单量
then count(if(rk1 = 1 and store_id is not null and order_from = 'android' , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and order_from = 'android' , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and order_from = 'android', order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and order_from = 'android', order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and order_from = 'android' , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and order_from = 'android', order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and order_from = 'android', order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and order_from = 'android' , order_id,null)) -- 日期
end as android_order_cnt,
case when grouping(store_id) = 0 -- ios订单量
then count(if(rk1 = 1 and store_id is not null and order_from = 'ios' , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and order_from = 'ios' , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and order_from = 'ios', order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and order_from = 'ios', order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and order_from = 'ios' , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and order_from = 'ios', order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and order_from = 'ios', order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and order_from = 'ios' , order_id,null)) -- 日期
end as ios_order_cnt,
case when grouping(store_id) = 0 -- ios订单量
then count(if(rk1 = 1 and store_id is not null and order_from = 'pcweb' , order_id,null))
when grouping(trade_area_id) = 0
then count(if(rk1 = 1 and trade_area_id is not null and order_from = 'pcweb' , order_id,null))
when grouping(city_id) = 0
then count(if(rk1 = 1 and city_id is not null and order_from = 'pcweb', order_id,null))
when grouping(min_class_id) = 0
then count(distinct if(rk2 = 1 and min_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping(mid_class_id) = 0
then count(distinct if(rk2 = 1 and mid_class_id is not null and order_from = 'pcweb' , order_id,null))
when grouping(max_class_id) = 0
then count(distinct if(rk2 = 1 and max_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping(brand_id) = 0
then count(distinct if(rk2 = 1 and brand_id is not null and order_from = 'pcweb', order_id,null))
when grouping(create_time) = 0
then count(if(rk1 = 1 and create_time is not null and order_from = 'pcweb' , order_id,null)) -- 日期
end as pcweb_order_cnt, -- PC商城成交单量
create_time as dt
from temp
group by
grouping sets(
create_time,
(create_time,city_id,city_name),
(create_time,city_id,city_name,trade_area_id,trade_area_name),
(create_time,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name),
(create_time,brand_id,brand_name),
(create_time,max_class_id,max_class_name),
(create_time,max_class_id,max_class_name,mid_class_id,mid_class_name),
(create_time,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)
)
)
-------------------------------参考代码----------------------------------------
insert into hive.yp_dws.dws_sale_daycount
with temp as (select
--维度抽取
o.dt as create_date, --日期
s.city_id,
s.city_name,--城市
s.trade_area_id,
s.trade_area_name,--商圈
s.id as store_id,
s.store_name,--店铺
g.brand_id,
g.brand_name, --品牌
g.max_class_id,
g.max_class_name,--商品大类
g.mid_class_id,
g.mid_class_name,--商品中类
g.min_class_id,
g.min_class_name, --商品小类
--订单量指标
o.order_id, --订单ID
o.goods_id, --商品ID
--金额指标
o.order_amount,--订单金额
o.total_price,--商品金额
o.plat_fee, --平台分润
o.dispatcher_money,--配送员运费
--判断条件
o.order_from,--订单来源：安卓，苹果啥的...
o.evaluation_id,--评论单ID（如果不为null,表示该订单有评价）
o.geval_scores, --订单评分（用于计算差评）
o.delievery_id, --配送单ID(如果不为null，表示是配送单，其他还有可能是自提、商家配送)
o.refund_id, --退款单ID(如果不为null,表示有退款)
--分组去重
row_number() over(partition by order_id) as order_rn,
row_number() over(partition by order_id,g.brand_id) as brand_rn,
row_number() over(partition by order_id,g.max_class_name) as maxclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name) as midclass_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name) as minclass_rn,
--下面分组加入goods_id
row_number() over(partition by order_id,g.brand_id,o.goods_id) as brand_goods_rn,
row_number() over(partition by order_id,g.max_class_name,o.goods_id) as maxclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,o.goods_id) as midclass_goods_rn,
row_number() over(partition by order_id,g.max_class_name,g.mid_class_name,g.min_class_name,o.goods_id) as minclass_goods_rn
from dwb_order_detail o
left join dwb_goods_detail g on o.goods_id = g.id
left join dwb_shop_detail s on o.store_id = s.id)
select
--查询出来的字段个数、顺序、类型要和待插入表的一致 dws_sale_daycount
case when grouping(city_id) = 0 --如果分组中包含city_id 则grouping为0 那么就返回city_id
then city_id
else null end as city_id ,
case when grouping(city_id) = 0
then city_name
else null end as city_name ,
case when grouping(trade_area_id) = 0--商圈
then trade_area_id
else null end as trade_area_id ,
case when grouping(trade_area_id) = 0
then trade_area_name
else null end as trade_area_name ,
case when grouping(store_id) = 0 --店铺
then store_id
else null end as store_id ,
case when grouping(store_id) = 0
then store_name
else null end as store_name ,
case when grouping(brand_id) = 0 --品牌
then brand_id
else null end as brand_id ,
case when grouping(brand_id) = 0
then brand_name
else null end as brand_name ,
case when grouping(max_class_id) = 0 --大类
then max_class_id
else null end as max_class_id ,
case when grouping(max_class_id) = 0
then max_class_name
else null end as max_class_name ,
case when grouping(mid_class_id) = 0 --中类
then mid_class_id
else null end as mid_class_id ,
case when grouping(mid_class_id) = 0
then mid_class_name
else null end as mid_class_name ,
case when grouping(min_class_id) = 0--小类
then min_class_id
else null end as min_class_id ,
case when grouping(min_class_id) = 0
then min_class_name
else null end as min_class_name ,
case when grouping(store_id,store_name) = 0 --分组类型
then 'store'
when grouping(trade_area_id ,trade_area_name) = 0
then 'trade_area'
when grouping (city_id,city_name) = 0
then 'city'
when grouping (brand_id,brand_name) = 0
then 'brand'
when grouping (min_class_id,min_class_name) = 0
then 'min_class'
when grouping (mid_class_id,mid_class_name) = 0
then 'mid_class'
when grouping (max_class_id,max_class_name) = 0
then 'max_class'
when grouping (create_date) = 0
then 'all'
else 'other' end as group_type,
--指标计算 注意每个指标都对应着8个分组维度的计算
--1、销售收入指标 sale_amt
case when grouping(store_id,store_name) =0 --如果分组中包含店铺,则分组为：日期+城市+商圈+店铺
then sum(if( order_rn = 1 and store_id is not null ,order_amount,0)) --只有分组中标号为1的(去重)，店铺不为空的才参与计算
--then sum(if( order_rn = 1 and store_id is not null ,coalesce(order_amount,0),0)) --使用coalesce函数更加成熟
when grouping (trade_area_id ,trade_area_name) = 0 --日期+城市+商圈
then sum(if( order_rn = 1 and trade_area_id is not null ,order_amount,0))
when grouping (city_id,city_name) = 0 --日期+城市
then sum(if( order_rn = 1 and city_id is not null,order_amount,0))
when grouping (brand_id,brand_name) = 0 --日期+品牌
then sum(if(brand_goods_rn = 1 and brand_id is not null,total_price,0))
when grouping (min_class_id,min_class_name) = 0 --日期+大类+中类+小类
then sum(if(minclass_goods_rn = 1 and min_class_id is not null ,total_price,0))
when grouping (mid_class_id,mid_class_name) = 0 --日期+大类+中类
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null,total_price,0))
when grouping (max_class_id,max_class_name) = 0 ----日期+大类
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null ,total_price,0))
when grouping (create_date) = 0 --日期
then sum(if(order_rn=1 and create_date is not null,order_amount,0))
else null end as sale_amt,
--2、平台收入 plat_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null ,plat_fee,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null ,plat_fee,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null,plat_fee,0))
when grouping (brand_id,brand_name) = 0
then null
when grouping (min_class_id,min_class_name) = 0
then null
when grouping (mid_class_id,mid_class_name) = 0
then null
when grouping (max_class_id,max_class_name) = 0
then null
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null,plat_fee,0))
else null end as plat_amt ,
-- 3、配送成交额 deliver_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and delievery_id is not null ,dispatcher_money,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and delievery_id is not null,dispatcher_money,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and delievery_id is not null,dispatcher_money,0))
when grouping (brand_id,brand_name) = 0
then null
when grouping (min_class_id,min_class_name) = 0
then null
when grouping (mid_class_id,mid_class_name) = 0
then null
when grouping (max_class_id,max_class_name) = 0
then null
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and delievery_id is not null ,dispatcher_money,0))
else null end as deliver_sale_amt ,
-- 4、小程序成交额 mini_app_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='miniapp' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='miniapp',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='miniapp',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='miniapp',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='miniapp',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='miniapp',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='miniapp',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='miniapp',order_amount ,0))
else null end as mini_app_sale_amt ,
-- 5、安卓成交额 android_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='android' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='android',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='android',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='android',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='android',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='android',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='android',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='android',order_amount ,0))
else null end as android_sale_amt ,
-- 6、苹果成交额 ios_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='ios' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='ios',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='ios',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='ios',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='ios',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='ios',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='ios',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='ios',order_amount ,0))
else null end as ios_sale_amt ,
-- 7、pc成交额 pcweb_sale_amt
case when grouping(store_id,store_name) =0
then sum(if( order_rn = 1 and store_id is not null and order_from='pcweb' ,order_amount,0))
when grouping (trade_area_id ,trade_area_name) = 0
then sum(if( order_rn = 1 and trade_area_id is not null and order_from='pcweb',order_amount,0))
when grouping (city_id,city_name) = 0
then sum(if( order_rn = 1 and city_id is not null and order_from='pcweb',order_amount,0))
when grouping (brand_id,brand_name) = 0
then sum(if(brand_goods_rn = 1 and brand_id is not null and order_from='pcweb',total_price,0))
when grouping (min_class_id,min_class_name) = 0
then sum(if(minclass_goods_rn = 1 and min_class_id is not null and order_from='pcweb',total_price,0))
when grouping (mid_class_id,mid_class_name) = 0
then sum(if(midclass_goods_rn = 1 and mid_class_id is not null and order_from='pcweb',total_price,0))
when grouping (max_class_id,max_class_name) = 0
then sum(if(maxclass_goods_rn = 1 and max_class_id is not null and order_from='pcweb',total_price,0))
when grouping (create_date) = 0
then sum(if(order_rn=1 and create_date is not null and order_from='pcweb',order_amount ,0))
else null end as pcweb_sale_amt ,
-- 8、订单量 order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null , order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null , order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null , order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null , order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null , order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null , order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 , order_id,null))
else null end as order_cnt ,
--9、 参评单量 eva_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and evaluation_id is not null , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and evaluation_id is not null , order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and evaluation_id is not null , order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and evaluation_id is not null , order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and evaluation_id is not null , order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and evaluation_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and evaluation_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and evaluation_id is not null, order_id,null))
else null end as eva_order_cnt ,
--10、差评单量 bad_eva_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6 , order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and evaluation_id is not null and coalesce(geval_scores,0) <6, order_id,null))
else null end as bad_eva_order_cnt ,
--11、配送单量 deliver_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and delievery_id is not null, order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and delievery_id is not null, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and delievery_id is not null, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and delievery_id is not null, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and delievery_id is not null, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and delievery_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and delievery_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and delievery_id is not null, order_id,null))
else null end as deliver_order_cnt ,
--12、退款单量 refund_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and refund_id is not null, order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and refund_id is not null, order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and refund_id is not null, order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and refund_id is not null, order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and refund_id is not null, order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and refund_id is not null, order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and refund_id is not null, order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and refund_id is not null, order_id,null))
else null end as refund_order_cnt ,
-- 13、小程序订单量 miniapp_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'miniapp', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'miniapp', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'miniapp', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'miniapp', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'miniapp', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'miniapp', order_id,null))
else null end as miniapp_order_cnt ,
-- 14、android订单量 android_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'android', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'android', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'android', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'android', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'android', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'android', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'android', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'android', order_id,null))
else null end as android_order_cnt ,
-- 15、ios订单量 ios_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'ios', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'ios', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'ios', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'ios', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'ios', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'ios', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'ios', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'ios', order_id,null))
else null end as ios_order_cnt ,
--16、pcweb订单量 pcweb_order_cnt
case when grouping(store_id,store_name) =0
then count(if(order_rn=1 and store_id is not null and order_from = 'pcweb', order_id,null))
when grouping (trade_area_id ,trade_area_name) = 0
then count(if(order_rn=1 and trade_area_id is not null and order_from = 'pcweb', order_id,null))
when grouping (city_id,city_name) = 0
then count(if(order_rn=1 and city_id is not null and order_from = 'pcweb', order_id,null))
when grouping (brand_id,brand_name) = 0
then count(if(brand_rn=1 and brand_id is not null and order_from = 'pcweb', order_id,null))
when grouping (min_class_id,min_class_name) = 0
then count(if(minclass_rn=1 and min_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (mid_class_id,mid_class_name) = 0
then count(if(midclass_rn=1 and mid_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (max_class_id,max_class_name) = 0
then count(if(maxclass_rn=1 and max_class_id is not null and order_from = 'pcweb', order_id,null))
when grouping (create_date) = 0
then count(if(order_rn=1 and order_from = 'pcweb', order_id,null))
else null end as pcweb_order_cnt ,
create_date as dt --日期
from temp
group by
grouping sets(
create_date, --日期
(create_date,city_id,city_name),--日期+城市
(create_date,city_id,city_name,trade_area_id,trade_area_name),--日期+城市+商圈
(create_date,city_id,city_name,trade_area_id,trade_area_name,store_id,store_name), --日期+城市+商圈+店铺
(create_date,brand_id,brand_name),--日期+品牌
(create_date,max_class_id,max_class_name),--日期+大类
(create_date,max_class_id,max_class_name,mid_class_id,mid_class_name),--日期+大类+中类
(create_date,max_class_id,max_class_name,mid_class_id,mid_class_name,min_class_id,min_class_name)--日期+大类+中类+小类
);