在核心交易分析中完成如下指标的计算
为了计算方便在dws层生产一张每日销售数据
– 创建一张日期维度表,存放日期相关维度
– 在订单宽表上增加日期对应维度信息如。订单日,订单周,订单周开始时间,订单月,订单季度,订单年
DROP TABLE IF EXISTS dws.dws_trade_orders_w;
create table if not exists dws.dws_trade_orders_w(
orderid string, – 订单id
cat_3rd_id string, – 商品三级分类id
thirdname string, – 商品三级分类名称
secondname string, – 商品二级分类名称
firstname string, – 商品一级分类名称
shopid string, – 店铺id
shopname string, – 店铺名
regionname string, – 店铺所在大区
cityname string, – 店铺所在城市
paymethod tinyint, – 支付方式
productsnum bigint, – 商品数量
paymoney double, – 订单明细金额
paytime string, – 订单时间
pay_wek string,-- 订单所在周
pay_mon string,-- 订单所在月
pay_quarter string,-- 订单所在季
pay_year string,-- 订单所在年
is_hol_day int,-- 是否节假日
is_wek_day – 是否休息日
) p
artitioned by (dt string)
STORED AS PARQUET;
统计2020年每个季度的销售订单笔数、订单总额
select
pay_quarter, --季度
count(distinct orderid),-- 订单数
sum(paymoneyproductsnum) – 订单金额
from
dws.dws_trade_orders_w
where dt=‘
d
o
d
a
t
e
′
g
r
o
u
p
b
y
p
a
y
q
u
a
r
t
e
r
统
计
2020
年
每
个
月
的
销
售
订
单
笔
数
、
订
单
总
额
s
e
l
e
c
t
p
a
y
m
o
n
,
−
−
月
度
c
o
u
n
t
(
d
i
s
t
i
n
c
t
o
r
d
e
r
i
d
)
,
−
−
订
单
数
s
u
m
(
p
a
y
m
o
n
e
y
∗
p
r
o
d
u
c
t
s
n
u
m
)
−
−
订
单
金
额
f
r
o
m
d
w
s
.
d
w
s
t
r
a
d
e
o
r
d
e
r
s
w
w
h
e
r
e
d
t
=
′
do_date' group by pay_quarter 统计2020年每个月的销售订单笔数、订单总额 select pay_mon, --月度 count(distinct orderid),-- 订单数 sum(paymoney*productsnum) -- 订单金额 from dws.dws_trade_orders_w where dt='
dodate′groupbypayquarter统计2020年每个月的销售订单笔数、订单总额selectpaymon,−−月度count(distinctorderid),−−订单数sum(paymoney∗productsnum)−−订单金额fromdws.dwstradeorderswwheredt=′do_date’
group by pay_mon
统计2020年每周(周一到周日)的销售订单笔数、订单总额
select
pay_wek, --周
count(distinct orderid),-- 订单数
sum(paymoneyproductsnum) – 订单金额
from
dws.dws_trade_orders_w
where dt=’$do_date’
group by pay_wek
统计2020年国家法定节假日、休息日、工作日的订单笔数、订单总额
select
count(distinct case when is_hol_day =1 then orderid end ), – 国家法定节假日订单笔数
sum(case when is_hol_day =1 then paymoneyproductsnum end),-- 国家法定节假日订单总额
count(distinct case when is_wek_day =1 then orderid end ), – 休息日订单笔数
sum(case when is_hol_day =1 then paymoneyproductsnum end), – 休息日订单总额
–工作日订单笔数
count(distinct case when is_hol_day =0 and is_wek_day=0 then orderid end),
– 工作日订单总额
sum(case when is_hol_day =0 and is_wek_day=0 then paymoney*productsnum end)
from
dws.dws_trade_orders_w
where year(dt)=‘2020’