数仓项目 - 订单分析时间维度分析业务开发
总结需求1：ads数据都来源于dw, DW层数据都来源于ods。 ads一张表存储了四个需求的所有数据
准备日期数据

 drop table if exists `itcast_dw`.`dim_date`;CREATE TABLE `itcast_dw`.`dim_date`(`date_key` string, `date_value` string, `day_in_year` string, `day_in_month` string, `is_first_day_in_month` string, `is_last_day_in_month` string, `weekday` string, `week_in_month` string, `is_first_day_in_week` string, `is_dayoff` string, `is_workday` string, `is_holiday` string, `date_type` string, `month_number` string, `year` string, `year_month_number` string, `quarter_name` string, `quarter_number` string, `year_quarter` string)STORED AS PARQUET;

1.1 需求分析
统计2019年期间每个季度的销售订单笔数、订单总额
统计2019年期间每个月的销售订单笔数、订单总额
统计2019年期间每周一到周日的销售订单笔数、订单总额
统计2019年期间国家法定节假日、休息日、工作日的订单笔数、订单总额
1.2 创建ads层数据表

drop table if exists itcast_ads.ads_order_date;
create table itcast_ads.ads_order_date(
date_type string, – 时间维度类型
date_val string, – 时间维度值
order_count bigint, – 订单笔数
order_money double – 订单交易额
)
partitioned by (dt string)
STORED AS PARQUET;

1.3 创建dw层数据表

DROP TABLE IF EXISTS itcast_dw.fact_order_goods;
CREATE TABLE itcast_dw.fact_order_goods(
ogid bigint,
orderid bigint,
goodsid bigint,
goodsnum bigint,
goodsprice double,
goodsspecid bigint,
goodsspecnames string,
goodsname string,
goodsimg string,
extrajson string,
goodstype bigint,
commissionrate double,
goodscode string,
createtime string)
partitioned by (dt string)
STORED AS PARQUET;

1.4 数据装载
1 加载订单明细数据到dw层
加载从1月1号，到9月9日的所有数据到dw层

insert overwrite table itcast_dw.fact_order_goods partition(dt=‘20190908’)
select
ogid,
orderid,
goodsid,
goodsnum,
goodsprice,
goodsspecid,
goodsspecnames,
goodsname,
goodsimg,
extrajson,
goodstype,
commissionrate,
goodscode,
createtime
from
itcast_ods.itcast_order_goods
where dt=‘20190908’;
​
– 测试代码
select * from itcast_dw.fact_order_goods where dt = ‘20190908’ limit 10;

2 统计半年期间每个季度的销售订单笔数、订单总额
时间维度类型：
1 → 季度
2 → 月度
3 → 星期
4 → 日期类型（节假日、休息日、工作日）
统计2019年期间每个季度的销售订单笔数、订单总额

insert overwrite table itcast_ads.ads_order_date partition(dt=‘20190908’)
select
‘1’ as date_type, – 时间维度类型，1表示季度
t1.year_quarter as date_val, – 季度名称
count(distinct t2.orderid), – 订单总数
sum(t2.goodsprice*t2.goodsnum) – 订单总额
from
itcast_dw.dim_date t1
left join
(select * from itcast_dw.fact_order_goods where dt=‘20190908’) t2
on t1.date_value = substr(createtime, 1, 10)
group by t1. year_quarter
order by t1.year_quarter;
​
– 测试
select * from itcast_ads.ads_order_date where dt=‘20190908’ and date_type = ‘1’;

3 统计2019年期间每个月的销售订单笔数、订单总额

insert into table itcast_ads.ads_order_date partition(dt=‘20190908’)
select
‘2’ as date_type, --2表示月类型
t1.year_month_number as date_val,
count(distinct t2.orderid), – 订单总数
sum(t2.goodsprice*t2.goodsnum) – 订单总额
from
itcast_dw.dim_date t1
left join
(select * from itcast_dw.fact_order_goods where dt <=‘20190908’ and dt >= ‘20190101’) t2
on t1.date_value = substr(createtime, 1, 10)
group by
t1.year_month_number
order by t1.year_month_number;
​
– 测试
select * from itcast_ads.ads_order_date where dt=‘20190908’ and date_type = ‘2’;

4 统计2019年期间每周一到周日的销售订单笔数、订单总额

insert into table itcast_ads.ads_order_date partition(dt=‘20190908’)
select
‘3’ as date_type,
t1.weekday as date_val,
count(distinct t2.orderid), – 订单总数
sum(t2.goodsprice*t2.goodsnum) – 订单总额
from
itcast_dw.dim_date t1
left join
(select * from itcast_dw.fact_order_goods where dt =‘20190908’) t2
on t1.date_value = substr(createtime, 1, 10)
group by
t1.weekday
order by t1.weekday;
​
– 测试
select * from itcast_ads.ads_order_date where dt=‘20190908’ and date_type = ‘3’;

5 统计2019年期间国家法定节假日、休息日、工作日的订单笔数、订单总额

insert into table itcast_ads.ads_order_date partition(dt=‘20190908’)
select
‘4’ as date_type,
t1.date_type as date_val,
count(distinct t2.orderid) as order_count, – 订单总数
sum(t2.goodsprice*t2.goodsnum) as order_money – 订单总额
from
itcast_dw.dim_date t1
left join
(select * from itcast_dw.fact_order_goods where dt <=‘20190908’ and dt >= ‘20190101’) t2
on t1.date_value = substr(createtime, 1, 10)
group by
t1.date_type
order by t1.date_type;
​
– 测试
select * from itcast_ads.ads_order_date where dt=‘20190908’ and date_type = ‘4’;

实现流程图: