需求-各品牌商品交易統(tǒng)計
| 統(tǒng)計周期 | 統(tǒng)計粒度 | 指標 |
|---|---|---|
| 最近1劣领、7压汪、30日 | 品牌 | 訂單數(shù) |
| 最近1怨喘、7挥吵、30日 | 品牌 | 訂單人數(shù) |
| 最近1匠楚、7九巡、30日 | 品牌 | 退單數(shù) |
| 最近1聚凹、7枉圃、30日 | 品牌 | 退單人數(shù) |
建表語句
CREATE EXTERNAL TABLE ads_trade_stats_by_tm
(
`dt` STRING COMMENT '統(tǒng)計日期',
`recent_days` BIGINT COMMENT '最近天數(shù),1:最近1天,7:最近7天,30:最近30天',
`tm_id` STRING COMMENT '品牌ID',
`tm_name` STRING COMMENT '品牌名稱',
`order_count` BIGINT COMMENT '訂單數(shù)',
`order_user_count` BIGINT COMMENT '訂單人數(shù)',
`order_refund_count` BIGINT COMMENT '退單數(shù)',
`order_refund_user_count` BIGINT COMMENT '退單人數(shù)'
) COMMENT '各品牌商品交易統(tǒng)計'
前提
今天為 2020-06-14
完整sql
with
order_info as (
select
1 recent_days,tm_id,tm_name,
sum(order_count_1d) order_count,
count(distinct user_id) order_user_count
-- 一個用戶在一天下單的一個商品是一行
from dws_trade_user_sku_order_1d
where dt='2020-06-14'
group by tm_id,tm_name
union all
select
recent_days,tm_id,tm_name,
sum(if(recent_days = 7,order_count_7d,order_count_30d)) order_count,
-- 判斷這個人在最近7天或30天是否下過單
count(distinct `if`(if(recent_days = 7,order_count_7d,order_count_30d) >0 ,user_id,null) ) order_user_count
-- 一個用戶在一天下單的一個商品是一行
-- 一個人最近30天下單了芜壁，但是最新7天沒有下單
from dws_trade_user_sku_order_nd
lateral view explode(`array`( 7, 30)) tmp as recent_days
where dt='2020-06-14'
group by recent_days,tm_id,tm_name
-- union all 就是上下拼接
-- union : 在union all的基礎(chǔ)上礁凡，再 group by 上下拼接的所有列
),
refund_info as (
select
1 recent_days,tm_id,tm_name,
sum(order_refund_count_1d) order_refund_count,
count(distinct user_id) order_refund_user_count
-- 一個用戶在一天下單的一個商品是一行
from dws_trade_user_sku_order_refund_1d
where dt='2020-06-14'
group by tm_id,tm_name
union all
select
recent_days,tm_id,tm_name,
sum(if(recent_days = 7,order_refund_count_7d,order_refund_count_30d)) order_refund_count,
-- 判斷這個人在最近7天或30天是否下過單
count(distinct `if`(if(recent_days = 7,order_refund_count_7d,order_refund_count_30d) >0 ,user_id,null) ) order_refund_user_count
-- 一個用戶在一天下單的一個商品是一行
-- 一個人最近30天下單了高氮，但是最新7天沒有下單
from dws_trade_user_sku_order_refund_nd
lateral view explode(`array`( 7, 30)) tmp as recent_days
where dt='2020-06-14'
group by recent_days,tm_id,tm_name
)
insert overwrite table ads_trade_stats_by_tm
select * from ads_trade_stats_by_tm
union
select
'2020-06-14' dt,
nvl(order_info.recent_days,refund_info.recent_days) recent_days,
nvl(order_info.tm_id, refund_info.tm_id) tm_id,
nvl(order_info.tm_name, refund_info.tm_name) tm_name,
nvl(order_count,0) ,
nvl(order_user_count,0) ,
nvl(order_refund_count,0) ,
nvl(order_refund_user_count,0)
--
from
-- 最近30天所有下單的品牌
order_info
full join
-- 所有退單的品牌
refund_info
on order_info.recent_days = refund_info.recent_days and order_info.tm_id= refund_info.tm_id;
思考步驟
訂單 與 退單 思路相同 ，只簡述 訂單步驟
- 算 1日 訂單數(shù) 與 訂單人數(shù)
select
1 recent_days,tm_id,tm_name,
sum(order_count_1d) order_count,
count(distinct user_id) order_user_count
-- 一個用戶在一天下單的一個商品是一行
from dws_trade_user_sku_order_1d
where dt='2020-06-14'
group by tm_id,tm_name
- 計算 7 30 日
（1）表 dws_trade_user_sku_order_nd
| user_id | sku_id | tm_id | tm_name | order_count_7d | order_count_30d |
|---|---|---|---|---|---|
| 33 | 01 | 1001 | 七匹狼 | 20 | 60 |
| 33 | 22 | 1002 | 皮克 | 0 | 30 |
（2）將上表的數(shù)據(jù) 變成兩份
| user_id | tm_id | tm_name | order_count_7d | order_count_30d | recent_days |
|---|---|---|---|---|---|
| 用戶id | 品牌id | 品牌名 | 7日訂單數(shù) | 30日訂單數(shù) | 計算天數(shù) |
| 33 | 1001 | 七匹狼 | 20 | 7 | |
| 33 | 1002 | 皮克 | 0 | 7 | |
| 33 | 1001 | 七匹狼 | 60 | 30 | |
| 33 | 1002 | 皮克 | 30 | 30 |
（3）根據(jù) recent_days,tm_id,tm_name 聚合
（4）選擇有效數(shù)據(jù)
A => if(recent_days = 7,order_count_7d,order_count_30d)
（4）判斷是否 訂單數(shù)>0 ,有效則記錄用戶id顷牌，無效則null
用戶1 訂單1 匹克 籃球鞋
用戶1 訂單2 匹克 拖鞋
計為 兩條記錄
B => `if`( A >0 ,user_id,null)
（5）因為 dws_trade_user_sku_order_nd 的粒度為 sku
防止 有人 在 同一品牌 不同商品 下單
計算下單人數(shù)時：需要對訂單 去重
count(distinct B )
