1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
Step1:销售主题统计宽表
需求一：门店月销售单量排行, 按月统计，各个门店的 月销售单量
time_type 的取值有 year, month, week, date,
group_type 的取值有 city_id,tread_area_id,store_id,brand_id,max_class_id,mid_class_id,min_class_id.
create table yp_rpt.rpt_sale_store_cnt_month(
date_time string,
year_code string,
year_month string,
order_store_cnt bigint,miniapp_order_store_cnt,android_order_store_cnt,.......
)row format delimited fields terminated by '\t'
stored as orc tblproperties('orc.compress'='snappy');
insert into
select
'2023-06-05' as date_time,
year_code,
year_month,
city_id,city_name,tread_area_id,tread_area_name,store_id,store_name,
order_cnt,miniapp_order_store_cnt.....
from yp_dm.dm_sale
where group_type='store_id' and time_type='month' and store_id is not null;
Step2:
需求2: 按天统计 总销售额 和 销售单量 rpt_sale_day
CREATE TABLE yp_rpt.rpt_sale_day(
date_time string COMMENT '统计日期,不能用来分组统计',
year_code string COMMENT '年code',
month_code string COMMENT '月份编码',
day_month_num string COMMENT '一月第几天',
dim_date_id string COMMENT '日期',
sale_amt DECIMAL(38,2) COMMENT '销售收入',
order_cnt BIGINT COMMENT '成交单量'
)
COMMENT '日销售曲线'
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
insert into yp_rpt.rpt_sale_day
select
'2023-06-05' as date_time,
year_code,
month_code,
day_month_num,
dim_date_id,
sale_amt,
order_cnt
from
yp_dm.dm_sale
where time_type='date' and group_type='all'
Step3:需求3: 渠道销售占比
create teble yp_rpt.rpt_sale_fromtype_ration(
)comment '渠道销售占比'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');
insert into rpt_sale_fromtype_ratio
select
'2023-06-05' as date_time,
time_type,
year_code,year_month,dim_date_id,order_cnt,miniapp_order_cnt
cast(miniapp_order_cnt as decimal(38,2))/cast(order_cnt as decimal(38,2)) * 100,
cast(miniapp_order_cnt as decimal(38,2))/cast(order_cnt as decimal(38,2)) * 100,
from yp_dm.dm_sale
where group_type='all'
insert into hive.yp_rpt.rpt_goods_sale_topN
with tmp as (
select
dt,
sku_id,payment_num,
dense_rank() over(order by payment_num desc) rn
from yp_dm.dm_sku
)
select dt,sku_id,payment_num where rn<=10;
insert into hive.yp_rpt.rpt_goods_favor_topN
with t1 as (
select
dt,
sku_id,
favor_count,
rank() over(order by favor_count desc) rn
from yp_dws.dws_sku_daycount
)
select dt,sku_id,favor_count where rn<=10;
insert into yp_rpt.rpt_goods_cart_topN
select '2023-06-05',sku_id,cart_num from yp_dws.dws_sku_daycount
order by cart_num desc limit 10;
select
'2023-06-04' as dt,
sku_id,
cast(退款订单个数统计 as decimal(38,2))/cast(支付订单数 as decimal(38,2)) * 100
from yp_dm.dm_sku
where payment_num > 0
order by refund_ratio desc
limit 10;
select
'2023-06-04' as dt,
sum(if(昨日登录时间='2023-06-04', 1, 0)) as 活跃会员数,
sum(if(首次登录时间='2023-06-04', 1, 0)) as 新增会员数
sum(if(首次支付时间='2023-06-04', 1, 0)) as 新增消费会员数
sum(if(支付订单数>0, 1, 0)) as 总付费会员数
count(*) as 总会员数
cast(
if(
sum(if(昨日登录时间='2023-06-04', 1, 0)) =0
null,
sum(if(昨日登录时间='2023-06-04', 1, 0))/count(*) * 100
)
as decimal(38,2)
) as as 会员活跃率
......
from yp_dm.dm_user;
with temp as (
select
'2023-06-04' as dt,
sum(if(昨日登录时间='2023-06-04', 1, 0)) as 活跃会员数,
sum(if(首次登录时间='2023-06-04', 1, 0)) as 新增会员数
sum(if(首次支付时间='2023-06-04', 1, 0)) as 新增消费会员数
sum(if(支付订单数>0, 1, 0)) as 总付费会员数
count(*) as 总会员数
from yp_dm.dm_user
)
select
*,
cast(活跃会员数 as decimal)/cast(总会员数 as decimal) * 100 as 会员活跃率,
总付费会员数 / 总会员数
新增会员数 / 活跃会员数
from temp;
|
微信
支付宝