1
2
3
4
5
6
端口号9870  HDFS端口号
端口号7180  clouder manager 集群管理软件
端口号8889  Hue的web界面
端口号8090  Presto的webUI界面
端口号19888 Yarn历史服务
端口号8088  ResourceManager的web界面

维度之间的关系

维度之间存在并列和递进包含的关系。

比如列出以下维度组合：（日期，城市，商圈，店铺，品牌，大类，中类，小类)

总共可以得出2的8次方总共256种组合。这里分为

日期，

日期，城市

日期，城市，商圈

日期，城市，商圈，店铺

品牌

品牌，大类

品牌，大类，中类

品牌，大类，中类，小类

  • 比如:日期，城市，商圈，日期和后面的两个属于并列关系。

  • 但是城市和商圈属于递进包含关系。因为属于一个商圈的那必然属于一个城市。

  • 从而日期，城市，商圈可以简写为日期，商圈

例如: 日期+城市 去掉城市, 影响分组结果, 所以: 日期和城市是并列关系.
例如: 日期+城市+商圈 去掉城市, 不影响结果, 属于同一个商圈的一定属于同一个城市.递进包含关系

主题需求

  • 指标

    1
    2
    3
    销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量

    --共计: 16个指标

  • 维度

    1
    2
    3
    4
    5
    6
    7
    8
    日期、城市、商圈、店铺、品牌、商品大类、商品中类、商品小类

    --共计:  8个维度
    --cube所有组合:  2^8=256个

    注意，其中日期这个维度很特殊，特殊在我们的表就是根据日期分区的，分区的字段是day天。
    而dws这一层我们需要统计的也是按day统计，日统计宽表嘛
    这也就意味着一个分区就是一天。

聚合函数增强

增强聚合函数有：

grouping sets cube、rollup grouping
功能:针对分组聚合操作进行优化。

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--在hive中建表操作
use test;

create table test.t_cookie(
    month string, 
    day string, 
    cookieid string) 
row format delimited fields terminated by ',';


--数据样例
2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
  • 分别按照月（month）、天（day）、月和天（month,day）统计来访用户cookieid个数(相当于按这三个分组），并获取三者的结果集（一起插入到目标宽表中）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--注意union all合并结果集需要各个查询返回字段个数、类型一致，因此需要合理的使用null来填充返回结果。
select month,
       null,
       count(cookieid)
from test.t_cookie
group by month

union all

select null,
       day,
       count(cookieid)
from test.t_cookie
group by day

union all

select month,
       day,
       count(cookieid)
from test.t_cookie
group by month,day;
  • 上述可以用group sets函数来实现，但是注意Hive和Presto中的写法略有不同。
  • hive中group by后面要加分组字段，Presto中group by不加分组字段。
  • 根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- grouping sets 函数演示
select
    "month", "day", count (cookieid)
from t_cookie
group by
grouping sets(month ,day,(month ,day));

-- grouping 函数 (用来显示是通过什么来进行分组的
select
    month ,
    day,
    count (cookieid) as cnt,
    case grouping (month,day)
        when 0 then '月和天'
        when 1 then '月'
        when 2 then '天'
        when 3 then '无'
    end as dimension
from t_cookie
group by
grouping sets((),month ,day,(month ,day));

-- cube，rollup函数
select
    month,
    day,
    count (cookieid)
from t_cookie
group by
cube(month ,day);  -- 注意cube函数里面不能写括号-组合

select
    month,
    day,
    count(cookieid)
from t_cookie
group by
rollup (month,day);  -- rollup是从右到左依次递减相当于grouping sets((month,day),month,())

-- 只以订单oid去重(根据什么分组就根据什么去重）
with t1 as (
    select *,
           row_number() over(partition by oid) rn
    from t_order_detail
)
select * from t1 where rn=1;

-- 以订单oid+品牌brand_id去重
with t1 as (
    select *,
           row_number() over(partition by oid,brand_id) rn
    from t_order_detail
)
select * from t1 where rn=1;
-- 以订单oid+品牌brand_id+商品goods_id去重
with t1 as (
    select *,
           row_number() over(partition by oid,brand_id,goods_id) rn
    from t_order_detail
)
select * from t1 where rn=1;

DWS层搭建

销售主题统计宽表的实现

Step1字段抽取

主题需求

  • 指标

    1
    2
    3
    销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量

    --共计: 16个指标

  • 维度

    1
    2
    3
    4
    5
    6
    7
    8
    日期、城市、商圈、店铺、品牌、商品大类、商品中类、商品小类

    --共计:  8个维度
    --cube所有组合:  2^8=256个

    注意，其中日期这个维度很特殊，特殊在我们的表就是根据日期分区的，分区的字段是day天。
    而dws这一层我们需要统计的也是按day统计，日统计宽表嘛
    这也就意味着一个分区就是一天。

先写数据来源(dwb_order_detail，dwb_goods_detail，dwb_shop_detail)然后将 * 替换成需要的字段

1
2
3
4
5
--以订单为准，以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;

抽取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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分组去重的时候需要注意：

1、对于城市、商圈、店铺等维度的成交额计算，根据订单order_amount汇总求和即可；

2、而对于品牌、大类、中类、小类等维度成交额计算，需要根据goods_id计算。

举个例子：001号订单总共1w元，小米手机4k，联想电脑6k，在算城市、商圈、店铺等维度的成交额要以订单总价进行计算.

而对于品牌，假如说联想品牌计算成交额，能以订单总价进行计算吗，肯定不能，因为订单还包括小米品牌，此时需要根据商品总价进行计算了。

  • 订单总价order_amount: 日期, 城市, 商圈, 店铺
  • 商品总价total_price: 品牌, 商品大类, 商品中类, 商品小类

去重实现(根据谁分组就根据谁去重)

注意(这九个去重的操作并不是每一个都用，而是根据需要进行去重，一般最后写，遇到问题了改代码即可。)

1
2
3
4
5
6
7
8
9
10
11
row_number() over(partition by order_id) as order_rn,  -- 根据订单id去重
row_number() over(partition by order_id,g.brand_id) as brand_rn,  -- 根据订单id，品牌id去重
row_number() over(partition by order_id,g.max_class_name) as maxclass_rn, -- 根据订单id，大类名称去重
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多维度分组

思想：用CTE表达式将刚才抽取的字段包裹，然后进行多维度分组查询（用到grouping sets函数）。

相当于使用grouping sets 后面的字段进行分组，然后在将数据进行join on。

根据业务需求进行维度组合，使用grouping sets进行分组。

1
2
3
4
5
6
7
8
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺
日期+品牌
日期+大类
日期+大类+中类
日期+大类+中类+小类

加上前两步的内容，分组后结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with temp as (
select 抽取字段、row_number去重  -- 上面的两堆
)
select * 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库中的日统计销售数据宽表yp_dws.dws_sale_daycount中插入数据。

查询出来的字段个数、顺序、类型要和待插入表的一致

(直白来讲就是判断当前维度组合是否包含该维度字段，包含则显示，不包含则为null)

让我们回到建表语句中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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');

是按照建表语句中的字段一一对应写入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
case when grouping(city_id) = 0   --如果分组中包含city_id 则grouping为0 那么就返回city
	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 ,

明确分组类型-即属于哪种维度组合

这个意思就是说我想知道该字段是根据哪个维度分的组。通过grouping实现。涉及到二进制与十进制的转换，对考过计算机组成原理的哥们来说不算难。(grouping为0表示有，1表示没有)

  • grouping(字段1，字段2) = 0 即0，0两个字段都有(是根据这两个字段-(可以说维度)分的组)

  • grouping(字段1，字段2) = 1 即0，1只有字段一(是根据第一个字段-(可以说维度)分的组)

  • grouping(字段1，字段2) = 2 即1，0只有字段二(是根据第两个字段-(可以说维度)分的组)

  • grouping(字段1，字段2) = 0 即1，1两个字段都没有(是根据空字段分的组)-没分组

跟上面建表字段group_type string对应。

分组类型有：

store，trade_area，city，brand，min_class，mid_class，max_class，all’,

版本一：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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,

版本二(个人倾向)-详见Step4

Step4精准判断维度类型

这个步骤就是判断表中的数据是根据什么进行分组的，理解grouping函数是什么意思基本就知道这步操作的目的。

1
2
3
4
5
6
case 
		grouping(create_date, city_id, trade_area_id, store_id, brand_id, max_class_id, mid_class_id, min_class_id) = 15 then 'store'  -- 这里8个维度，15=00001111即为日期，城市，商圈，店铺(以四个维度分组)
		grouping(create_date, city_id, trade_area_id, store_id, brand_id, max_class_id, mid_class_id, min_class_id) = 31 then 'trade_area_id'  -- 8个维度，31=00011111即为日期，城市，商圈
		grouping(create_date, city_id, trade_area_id, store_id, brand_id, max_class_id, mid_class_id, min_class_id) = 63 then 'city'  -- 63=00111111 即为日期，城市
		grouping(create_date, city_id, trade_area_id, store_id, brand_id, max_class_id, mid_class_id, min_class_id) = 119 then 'brand'
    end as group_type

Step5指标计算金额相关

指标计算 注意每个指标都对应着8个分组维度的计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
日期

日期，城市

日期，城市，商圈

日期，城市，商圈，店铺

品牌

品牌，大类

品牌，大类，中类

品牌，大类，中类，小类

指标1：销售收入

主要就是写case when 条件判断语句，使用sum函数来从八个维度分别对销售收入分别求和（且对订单进行去重order_rn = 1 ），并判断store_id 不为空订单才有效，如果订单无效就用0来充当订单销售收入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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))
	
-- 这里注意上面（日期+城市+商圈+店铺）算的是order_amount订单总价上面，举过例子了，根据品牌维度不能是订单总价，而是商品总价(联想和小米是一个订单共1w，联想7k，小米3k)
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))
se null end  as sale_amt,

指标2：平台收入

跟上面差不多，都要按八个维度来分析成交额，且对订单进行去重，店铺id不为空才有效，否则就用0填充，继而用sum函数对成交额求和。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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：配送成交额

跟上面差不多，都要按八个维度来分析成交额，且对订单进行去重，店铺id不为空才有效，否则就用0填充，继而用sum函数对成交额求和。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 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 ,

其他指标(各种平台)

跟上面差不多，都要按八个维度来分析成交额，且对订单进行去重，店铺id不为空才有效，否则就用0填充，继而用sum函数对成交额求和。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

-- 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 ,

Step6指标计算订单量相关

和上面的不同在于使用count函数对订单进行求和，计算订单量。参评单量要加上评价id不为空才会被计算进去。

订单来源于不同的平台要对来源进行过滤，比如order_from = ‘miniapp’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
   -- 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 ,

Step7完整实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
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)--日期+大类+中类+小类
    );

总结

DWS(DataWare House Service)

  • 名称：数据服务层 service
  • 功能：按主题划分，形成日统计的宽表，轻度汇总==提前聚合操作==。
  • 解释：轻度提前聚合说的是先聚合出日的指标，后续可以上卷出周、月、年的指标。

使用DataGrip在Hive中创建dws层,不要使用Presto

注意，对于建库建表操作，需直接使用Hive，因为Presto只是一个数据分析的引擎，其语法不一定支持直接在Hive中建库建表。

对于DWS层一个统计宽表400多行一下子干完还是有些难度的，希望下次再写这个主题的时候能在3天之内完成吧。Respect！