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. 查询结果
本文来自博客园,作者:jsqup,转载请注明原文链接:https://www.cnblogs.com/jsqup/p/16298937.html