9. ADS层及业务调度

1. 需求

对用户各个年龄段统计、地区销售统计、热门商品排行

2. 建表语句

DROP table ads_trade_age_d;
CREATE TABLE `ads_trade_age_d` (
`age` double COMMENT '年龄',
`sku_num` double COMMENT '购买商品个数',
`order_count` double COMMENT '订单个数',
`order_amount` double COMMENT '销售额',
`avg_amount` double COMMENT '平均客单价'
) 
COMMENT '年龄销售统计'
PARTITIONED BY (ds string);
 
drop table ads_trade_province_d;
CREATE TABLE `ads_trade_province_d` (
`province` string COMMENT '省份 id',
`province_name` string COMMENT '省市名称',
`region_id` string COMMENT '地区 ID',
`region_name` string COMMENT '地区名称',
`sku_num` double COMMENT '购买商品个数',
`order_count` double COMMENT '订单个数',
`order_amount` double COMMENT '销售额',
`avg_amount` double COMMENT '平均客单价'
) 
COMMENT '地区销售统计'
PARTITIONED BY (ds string);
 
drop table ads_abc;
CREATE TABLE `ads_abc` (
`sku_id` string COMMENT '商品 id',
`sku_name` string COMMENT '商品名称',
`sku_num` double COMMENT '购买商品个数',
`category3_id` string COMMENT '三级分类 id',
`category2_id` string COMMENT '二级分类 id',
`category1_id` string COMMENT '一级分类 id',
`category3_name` string COMMENT '三级分类名称',
`category2_name` string COMMENT '二级分类名称',
`category1_name` string COMMENT '一级分类名称',
`order_count` double COMMENT '订单个数',
`order_amount` double COMMENT '销售额',
`avg_amount` double COMMENT '平均客单价'
) 
COMMENT '商品销售统计'
PARTITIONED BY (ds string);

3. 手动加入表中数据

insert OVERWRITE table ads_trade_age_d PARTITION (ds = '20220521')
select 
 round(td.user_age) age,
 sum(sku_num) sku_num,
 sum(order_count) order_count,
 sum(order_amount) order_amount,
 round(avg(order_amount),2) avg_amount
from dws_trade_detail td
where ds = '20220521'
group by round(td.user_age);
select * from ads_trade_age_d where ds='20220521';
 
 
 
 
insert OVERWRITE table ads_trade_province_d PARTITION (ds = '20220521')
select 
 td.province_id,td.province_name,td.region_id,td.region_name,
 sum(sku_num) sku_num,
 sum(order_count) order_count,
 sum(order_amount) order_amount,
 round(avg(order_amount),2) avg_amount
from dws_trade_detail td
where ds='20220521'
group by td.province_id,td.province_name,td.region_id,td.region_name;
select * from ads_trade_province_d where ds='20220521';
 
 
 
insert OVERWRITE table ads_abc PARTITION (ds = '20220521')
select 
 td.sku_id,td.sku_name,
 td.sku_category3_id,
 td.sku_category2_id,
 td.sku_category1_id,
 td.sku_category3_name,
 td.sku_category2_name,
 td.sku_category1_name,
 sum(sku_num) sku_num,
 sum(order_count) order_count,
 sum(order_amount) order_amount,
 round(avg(order_amount),2) avg_amount
from dws_trade_detail td
where ds = '20220521'
group by 
 td.sku_id, td.sku_name, td.sku_category3_id,
 td.sku_category2_id, td.sku_category1_id,
 td.sku_category3_name, td.sku_category2_name,
td.sku_category1_name;
select * from ads_abc where ds='20220521';

4. 开发脚本

1. 表ads_trade_age_d

2. 表ads_trade_province_d

3. 表ads_trade_sku_d（由于无法导入的原因，改名为ads_abc）

5. 业务调度

1. 查询结果