|NO.Z.00056|——————————|^^ 实验 ^^|——|Hadoop&PB级数仓.V14|——|PB数仓.v14|核心交易分析|ADS层建表|加载数据|

一、ADS层开发

### --- 需求：计算当天

~~~     全国所有订单信息
~~~     全国、一级商品分类订单信息
~~~     全国、二级商品分类订单信息
~~~     大区所有订单信息
~~~     大区、一级商品分类订单信息
~~~     大区、二级商品分类订单信息
~~~     城市所有订单信息
~~~     城市、一级商品分类订单信息
~~~     城市、二级商品分类订单信息

~~~     # 用到的表：

dws.dws_trade_orders_w

二、ADS层建表

### --- ADS层建表
~~~     # ADS层订单分析表

DROP TABLE IF EXISTS ads.ads_trade_order_analysis;

create table if not exists ads.ads_trade_order_analysis(
areatype string, -- 区域范围：区域类型（全国、大区、城市）
regionname string, -- 区域名称
cityname string, -- 城市名称
categorytype string, -- 商品分类类型（一级、二级）
category1 string, -- 商品一级分类名称
category2 string, -- 商品二级分类名称
totalcount bigint, -- 订单数量
total_productnum bigint, -- 商品数量
totalmoney double -- 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by ',';

三、ADS层加载数据

### --- ADS层加载数据
~~~     1笔订单，有多个商品；多个商品有不同的分类；这会导致一笔订单有多个分类，它们是分别统计的；

[root@hadoop02 ~]# vim /data/yanqidw/script/trade/ads_load_trade_order_analysis.sh

#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
with mid_orders as (
select regionname,
    cityname,
    firstname category1,
    secondname category2,
    count(distinct orderid) as totalcount,
    sum(productsnum) as total_productnum,
    sum(paymoney) as totalmoney
from dws.dws_trade_orders_w
where dt='$do_date'
group by regionname, cityname, firstname, secondname
)
insert overwrite table ads.ads_trade_order_analysis
partition(dt='$do_date')
select '全国' as areatype,
    '' as regionname,
    '' as cityname,
    '' as categorytype,
    '' as category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
union all
select '全国' as areatype,
    '' as regionname,
    '' as cityname,
    '一级' as categorytype,
    category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by category1
union all
select '全国' as areatype,
    '' as regionname,
    '' as cityname,
    '二级' as categorytype,
    '' as category1,
    category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by category2
union all
select '大区' as areatype,
    regionname,
    '' as cityname,
    '' as categorytype,
    '' as category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by regionname
union all
select '大区' as areatype,
    regionname,
    '' as cityname,
    '一级' as categorytype,
    category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by regionname, category1
union all
select '大区' as areatype,
regionname,
    '' as cityname,
    '二级' as categorytype,
    '' as category1,
    category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by regionname, category2
union all
select '城市' as areatype,
    '' as regionname,
    cityname,
    '' as categorytype,
    '' as category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by cityname
union all
select '城市' as areatype,
    '' as regionname,
    cityname,
    '一级' as categorytype,
    category1,
    '' as category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by cityname, category1
union all
select '城市' as areatype,
    '' as regionname,
    cityname,
    '二级' as categorytype,
    '' as category1,
    category2,
    sum(totalcount),
    sum(total_productnum),
    sum(totalmoney)
from mid_orders
group by cityname, category2;
"

hive -e "$sql"

### --- ADS层表数据加载
~~~     加载数据到ADS订单明细表

[root@hadoop02 ~]# sh /data/yanqidw/script/trade/ads_load_trade_order_analysis.sh  2020-07-12

### --- 查看ADS订单明细表
~~~     由于在dws.dws_trade_orders_w中，
~~~     一笔订单可能有多条记录，所以在统计订单数量的时候要用count(distinct orderid)

~~~     # 订单明细表
hive (default)> show partitions ads.ads_trade_order_analysis;
partition
dt=2020-07-12

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---