零售数仓项目（一）

项目数据下载：

链接: https://pan.baidu.com/s/1IU6EaRiXoP7ibBxo_N1ttA 提取码: 35x4

1.项目介绍

1.1 项目功能需求

这个数据仓库是某电商运营支撑平台的一部分，用户使用浏览器(PC 端)和
app 对该平台进行访问，记录用户的注册信息，通过调研平台和数据挖掘记录用
户的扩展信息，业务系统对用户的每次点击和下单信息记录到数据库中，并提供
一台从库将主库的业务数据实时同步到从库中，同时分配对从库的只读访问权限，
运营分析平台通过 Sqoop 将数据抽取到运营分析平台，运营分析平台进行指标
的计算，将结果存入到 MySQL 中，将结果展示到报表。

通过对业务数据的分析，获得用户主题和订单主题的相关分析情况，生成用
户的访问模型和用户的订单行为模型，对访问时段和下单区域分布进行分析和报
表展示。

1.2 目架构设计图

**数据源：**本项目主要数据来源为用户在网站和 APP 的访问行为数据，业务系
统将数据存储到 MySQL 中，供多个部门使用。（该部分未在提供代码中实现）

**数据导入部分：**根据数据特性的不同，处理方式也不一样，对于一些已经对
历史数据进行更改的我们采取全量导入，例如 user 表；对于一些不会对历史数
据进行更改的我们采取增量导入，例如 order 表。（该部分为项目提供的代码数
据加载部分）

**数据仓库分析部分：**我们需要根据业务需求建立相应的主题，并将对数据进
行建模（即建表），通过对建立的模型进行分析，分析出用户的访问时间以及用
户的订单分布两个需求

**可视化展示：**最终将分析的结果用于前端展示或后端留存（该部分未在项目
代码中实现，属于其他项目组负责）。

1.2.1 架构所涉及技术分析

部分技术栈：MySQL+Sqoop+HDFS+Hive

 MySQL：存储业务数据以及分析结果数据，给前端进行展示

 Sqoop：导入 MySQL 中数据到 HDFS

 HDFS：数据存储

 Hive：用于数据仓库构建以及相关业务分析

2. 数据导入

用户通过访问 APP 或者网站，通过业务系统对用户的数据进行存储，存储到
MySQL 中。

本例子直接通过sql语句将数据导入到MySQL当中。

mysql -uroot -pok snbap_ods < /root/snbap_ods.sql

利用sqoop工具将数据从MySQL导入到Hive进行离线数据分析。

这里以user_pc_click_log为例，其他数据表类同：

sqoop-import \
--connect jdbc:mysql://hadoop100:3306/snbap_ods \
--username root \
--password ok \
--table user_pc_click_log  \
--m 3 \
--hive-import \
--hive-overwrite \
--create-hive-table \
--hive-table snbap_ods.user_pc_click_log 

3. 业务表模型

当我们对用户访问时间进行分析的时候需要用到用户表，用户的扩展表，用
户的 app 端点击日志表，用户的 pc 端点击日志表，在这个需求当中，我们
的事实表为用户的点击日志，而我们的维度表只有用户维度包括用户信息表
和用户扩展信息，所以需要列出这四张表

当我们对用户订单分布进行分析的时候需要用到用户表，用户扩展表，用户
订单表，用户收货地址表，订单收货地址表，交易表，订单商品表。在这个
需求当中我们的事实表是用户订单表，维度表是用户维度，商品维度，收货
地址维度，购物车维度等等，所以需要列出这 8 张表

在上图中还有一些和其他的表没有连线的表意味着在我们实现的部分当中
没有用到这些表，但是在实现其他的指标时候可能会用到，所以在这里选择
保留

4. 用户访问时间分析

按照用户 id 进行分组，统计用户 PC 端和 APP 端不同时间粒度的访问频
率

4.1 user_basic

生成用户基本宽表 user_basic

将用户的基本信息表和用户的扩展表进行合并形成用户宽表.
包括信息如下:

用户 id，登录名，用户性别，出生日期，年龄，星座，省份，城市，城
市等级，邮箱，邮箱运营商，手机号，手机号段（前三位），手机运营商，
注册时间，登录 ip，登录来源，邀请人，会员积分，已使用积分，会员等级
名称，是否黑名单，是否结婚，学历，月收入，职业，是否孕妇，是否有小
孩，是否有车，使用手机品牌，使用手机等级，使用手机种类数量，更换手
机数量 ，是否马甲用户，马甲账户数量，用户忠诚度，用户购物类型，体
重，身高。

create table user_bastic as
select 
u.user_id,
u.user_name, 
u.user_gender, 
user_birthday, 
user_age, 
constellation, 
province,
city,
city_level, 
e_mail, 
op_mail, 
mobile, 
num_seg_mobile,
op_Mobile, 
register_time, 
login_ip, 
login_source, 
request_user, 
total_score,
used_score,
is_blacklist, 
is_married, 
education, 
monthly_income, 
profession, 
is_pregnant_woman,
is_have_children,
is_have_car, 
phone_brand, 
phone_brand_level,
phone_cnt, 
change_phone_cnt, 
is_maja, 
majia_account_cnt, 
loyal_model, 
shopping_type_model, 
weight, 
height
from user u join user_extend e on u.user_id=e.user_id;

4.2 user_visit

根据用户的访问日志信息，统计以下指标，结果存入用户访问模型
表 user_visit

4.2.1 PC 端指标

 最近一次访问时间

 最近一次访问使用的 session

 最近一次使用的 cookie

 最近一次的 pc 端的 pv 量

 最近一次访问使用的浏览器

 最近一次访问使用的操作系统

create table user_last_pc_click as
select distinct * from 
(select user_id,session_id,cookie_id,visit_os,browser_name,visit_time
rank() over(partition by user_id order by visit_time desc) rn,
count(page_id) over(partition by user_id,visit_time order by visit_time) cnt
from snbap_ods.user_pc_click_log) a where rn=1;

 第一次 pc 端访问的日期

 第一次 pc 端访问的 session

 第一次 pc 端访问的 cookie

 第一次访问的 pv

 第一次访问使用的浏览器

 第一次访问的 os

create table user_first_pc_click as
select distinct * from 
(select user_id,session_id,cookie_id,visit_os,browser_name,visit_time,
rank() over(partition by user_id order by visit_time ) r,
count(page_id) over(partition by user_id,visit_time order by visit_time desc ) cnt
from snbap_ods.user_pc_click_log) a
where r=1;

 PC 连续 7 天访问次数(跑任务的日期的前 7 天，以下相同不再赘

述)

 连续 15 天访问次数

 连续 30 天访问次数

 连续 60 天访问的次数

 连续 90 天访问的次数

create table snbap_dw.user_pc_visit_total as
with d1 as
(select count(visit_time) week,user_id from snbap_ods.user_pc_click_log 
where visit_time between date_sub(current_date(),7) 
and current_date() group by user_id),
d2 as
(select count(visit_time) half,user_id  from snbap_ods.user_pc_click_log 
where visit_time between date_sub(current_date(),15) 
and current_date() group by user_id),
d3 as
(select count(visit_time) mon,user_id  from snbap_ods.user_pc_click_log 
where visit_time between date_sub(current_date(),30) 
and current_date() group by user_id),
d4 as
(select count(visit_time) mon2,user_id  from snbap_ods.user_pc_click_log 
where visit_time between date_sub(current_date(),60) 
and current_date() group by user_id),
d5 as
(select count(visit_time) season,user_id  from snbap_ods.user_pc_click_log 
where visit_time between date_sub(current_date(),90) 
and current_date() group by user_id)
select coalesce(d5.user_id,d4.user_id,d3.user_id,d2.user_id,d1.user_id) user_id,
nvl(season,0) season,nvl(mon2,0) mon2,nvl(mon,0) mon,nvl(half,0) half,nvl(week,0) week
from d1 full join d2 on d1.user_id=d2.user_id
full join d3 on d1.user_id=d3.user_id
full join d4 on d1.user_id=d4.user_id
full join d5 on d1.user_id=d5.user_id;

 近 30 天 pc 端访问的次数

 近 30 天 pc 端的 pv

 近 30 天 pc 端每天的平均 pv

与连续30天相似

 近 30 天的 0 到 5 点的 pv 数量

 近 30 天的 6 到 7 点的 pv 数量

 近 30 天的 8 到 9 的 pv 数量

 近 30 天的 10 到 11 的 pv 数量

 近 30 天的 12 到 13 的 pv 数量

 近 30 天的 14 到 15 点的 pv 数量

 近 30 天的 16 到 17 点的 pv 数量

 近 30 天的 18 到 19 点的 pv 数量

 近 30 天的 20 到 21 点的 pv 数量

 近 30 天的 22 到 23 点的 pv 数量

 近 30 天访问使用的不同 ip 数量

create table snbap_dw.user_pv_30_total as
select user_id,count(pv) pv_30_total,
sum(case when hour(visit_time) between 0 and 5 then 1 else 0 end) pv_30_0_5,
sum(case when hour(visit_time) between 6 and 7 then 1 else 0 end) pv_30_6_7,
sum(case when hour(visit_time) between 8 and 9 then 1 else 0 end) pv_30_8_9,
sum(case when hour(visit_time) between 10 and 11 then 1 else 0 end) pv_30_10_11,
sum(case when hour(visit_time) between 12 and 13 then 1 else 0 end) pv_30_12_13,
sum(case when hour(visit_time) between 14 and 15 then 1 else 0 end) pv_30_14_15,
sum(case when hour(visit_time) between 16 and 17 then 1 else 0 end) pv_30_16_17,
sum(case when hour(visit_time) between 18 and 19 then 1 else 0 end) pv_30_18_19,
sum(case when hour(visit_time) between 20 and 21 then 1 else 0 end) pv_30_20_21,
sum(case when hour(visit_time) between 22 and 23 then 1 else 0 end) pv_30_22_23,
count(distinct visit_ip) visit_ip_cnt,
count(distinct cookie_id) cookie_30_cnt
from snbap_dw.user_pc_click_partition
where visit_date between date_sub(current_date(),30) and current_date()
group by user_id;

 近 30 天最常用的 ip

create table snbap_dw.user_visit_30_popular_ip as
select user_id,visit_ip,from
(select user_id,visit_ip,dense_rank() over(partition by user_id order by ip_cnt desc)  rn from
(select user_id,visit_ip,count(visit_ip) ip_cnt from snbap_dw.user_pc_click_partition 
 where visit_date between date_sub(current_date(),30) and current_date() group by user_id,visit_ip) a)b
where rn=1;

 近 30 天使用的 cookie 的数量

 近 30 使用最常用的 cookie_id

create table snbap_dw.user_cookie_30_popular_id as
select user_id,cookie_id from
(select user_id,cookie_id,dense_rank() over(partition by user_id order by ip_cnt desc)  rn from
(select user_id,cookie_id,count(cookie_id) ip_cnt from snbap_dw.user_pc_click_partition 
 where visit_date between date_sub(current_date(),30) and current_date() group by user_id,cookie_id) a)b
where rn=1;

 近 30pc 最常用浏览器

create table snbap_dw.user_30_popular_browser as
select user_id,browser_name from
(select user_id,browser_name,dense_rank() over(partition by user_id order by ip_cnt desc)  rn from
(select user_id,browser_name,count(browser_name) ip_cnt from snbap_dw.user_pc_click_partition 
 where visit_date between date_sub(current_date(),30) and current_date() group by user_id,browser_name) a)b
where rn=1;

 近 30 天使用最常用系统

create table snbap_dw.user_visit_30_popular_os as
select user_id,visit_os from
(select user_id,visit_os,dense_rank() over(partition by user_id order by ip_cnt desc)  rn from
(select user_id,visit_os,count(visit_os) ip_cnt from snbap_dw.user_pc_click_partition 
 where visit_date between date_sub(current_date(),30) and current_date() group by user_id,visit_os) a)b
where rn=1;

pc端宽表

create table snbap_dm.user_pc_visit as
select 
lc.user_id,
lc.visit_time last_time,
lc.session_id last_session,
lc.cookie_id last_cookie,
lc.cnt last_pv,
lc.browser_name last_browser,
lc.visit_os last_os,
fc.visit_time first_time,
fc.session_id first_session,
fc.cookie_id first_cookie,
fc.cnt first_pv,
fc.browser_name first_browser,
fc.visit_os first_os,
week visit_week_cnt,
half visit_2week_cnt,
mon visit_month_cnt,
mon2 visit_mon2_cnt,
season visit_season_cnt,
mon pc_30_cnt,
pv_30_total,
round(pv_30_total/30,2) avg_30_total,
pv_30_0_5,
pv_30_6_7,
pv_30_8_9,
pv_30_10_11,
pv_30_12_13,
pv_30_14_15,
pv_30_16_17,
pv_30_18_19,
pv_30_20_21,
pv_30_22_23,
visit_ip_cnt,
vi.visit_ip popular_ip,
cookie_30_cnt,
ci.cookie_id popular_cokkie,
b.browser_name popular_browser,
vo.visit_os popular_os
from
snbap_dw.user_last_pc_click lc join
snbap_dw.user_first_pc_click fc on lc.user_id=fc.user_id left join
snbap_dw.user_pc_visit_total vt on lc.user_id=vt.user_id left join
snbap_dw.user_pv_30_total pv on lc.user_id=pv.user_id left join
snbap_dw.user_visit_30_popular_ip vi on lc.user_id=vi.user_id left join
snbap_dw.user_visit_30_popular_os vo on lc.user_id=vo.user_id left join
snbap_dw.user_30_popular_browser b on lc.user_id=b.user_id left join
snbap_dw.user_cookie_30_popular_id ci on lc.user_id=ci.user_id;

4.2.2 APP 端指标

 最近一次 app 访问的日期

 最近一次访问 app 的名称

 最近一次 app 访问的操作系统

create table snbap_dw.user_last_app_click as
select distinct * from 
(select user_id,log_time,app_name,visit_os,
rank() over(partition by user_id order by log_time desc) rn
from snbap_ods.user_app_click_log) a where rn=1;

 第一次 app 访问日期

 第一次 app 访问 app 的名称

 第一次 app 访问 os

 app 第一次访问 ip

create table snbap_dw.user_first_app_click as
select distinct * from 
(select user_id,log_time,app_name,visit_os,visit_ip,
rank() over(partition by user_id order by log_time ) r
from snbap_ods.user_app_click_log) a
where r=1;

 app 近 7 天访问 pv 数

 app 近 15 天访问 pv 数

 app 近 30 天的访问 pv 数

 app 近 60 天的访问 pv 数

 app 近 90 天的访问 pv 数

create table snbap_dw.user_app_visit_total as
with d1 as
(select count(log_id) week,user_id from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),7) 
and current_date() group by user_id),
d2 as
(select count(log_id) half,user_id from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),15) 
and current_date() group by user_id),
d3 as
(select count(log_id) mon,user_id from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),30) 
and current_date() group by user_id),
d4 as
(select count(log_id) mon2,user_id from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),60) 
and current_date() group by user_id),
d5 as
(select count(log_id) season,user_id from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),90) 
and current_date() group by user_id)
select coalesce(d5.user_id,d4.user_id,d3.user_id,d2.user_id,d1.user_id) user_id,
nvl(season,0) season,nvl(mon2,0) mon2,nvl(mon,0) mon,nvl(half,0) half,nvl(week,0) week
from d1 full join d2 on d1.user_id=d2.user_id
full join d3 on d1.user_id=d3.user_id
full join d4 on d1.user_id=d4.user_id
full join d5 on d1.user_id=d5.user_id;

 app 近 30 天 0 到 5 点的访问 pv 数

 app 近 30 天的 6 到 7 点的访问 pv 数

 app 近 30 天 8 到 9 的访问 pv 数

 app 近 30 天 10 到 11 访问 pv 数

 app 近 30 天 12 到 13 点的访问 pv 数

 app 近 30 天 14 到 15 点的访问 pv 数

 app 近 30 天 16 到 17 点的访问 pv 数

 app 近 30 天 18 到 19 点的访问 pv 数

 app 近 30 天 20 到 21 点的访问 pv 数

 app 近 30 天 22 到 23 点的访问 pv 数

create table snbap_dw.user_pv_30_total_app as
select user_id,
sum(case when hour(log_time)  between 0 and 5 then 1 else 0 end) pv_30_0_5,
sum(case when hour(log_time)  between 6 and 7 then 1 else 0 end) pv_30_6_7,
sum(case when hour(log_time)  between 8 and 9 then 1 else 0 end) pv_30_8_9,
sum(case when hour(log_time)  between 10 and 11 then 1 else 0 end) pv_30_10_11,
sum(case when hour(log_time)  between 12 and 13 then 1 else 0 end) pv_30_12_13,
sum(case when hour(log_time)  between 14 and 15 then 1 else 0 end) pv_30_14_15,
sum(case when hour(log_time)  between 16 and 17 then 1 else 0 end) pv_30_16_17,
sum(case when hour(log_time)  between 18 and 19 then 1 else 0 end) pv_30_18_19,
sum(case when hour(log_time)  between 20 and 21 then 1 else 0 end) pv_30_20_21,
sum(case when hour(log_time)  between 22 and 23 then 1 else 0 end) pv_30_22_23 
from snbap_ods.user_app_click_log
where log_time between date_sub(current_date(),30) 
and current_date() group by user_id;

APP端宽表

create table snbap_dm.user_app_visit as
select
lc.user_id,
lc.log_time last_time,
lc.app_name last_app,
lc.visit_os last_os,
fc.log_time first_time,
fc.app_name first_app,
fc.visit_os first_os,
fc.visit_ip first_ip,
week visit_week_cnt,
half visit_2week_cnt,
mon visit_month_cnt,
mon2 visit_mon2_cnt,
season visit_season_cnt,
pv_30_0_5,
pv_30_6_7,
pv_30_8_9,
pv_30_10_11,
pv_30_12_13,
pv_30_14_15,
pv_30_16_17,
pv_30_18_19,
pv_30_20_21,
pv_30_22_23
from 
snbap_dw.user_last_app_click lc join
snbap_dw.user_first_app_click fc on lc.user_id=fc.user_id left join
snbap_dw.user_app_visit_total pv on lc.user_id=pv.user_id left join
snbap_dw.user_pv_30_total_app pv_30 on lc.user_id=pv_30.user_id;

4.2.3 综合指标

 最近一次访问的 ip

 最近一次访问的城市

 最近一次访问的省份

create table snbap_dw.user_last_complex_click as
select distinct * from 
(select user_id,time,visit_ip,province,city,
rank() over(partition by user_id order by time desc) rn
from (select user_id,log_time as time,visit_ip,province,city from snbap_ods.user_app_click_log
union all
select user_id,visit_time as time,visit_ip,province,city from snbap_ods.user_pc_click_log) a)b where rn=1;

 第一次访问的 ip

 第一次访问的城市

 第一次访问的省份

create table snbap_dw.user_first_complex_click as
select distinct * from 
(select user_id,time,visit_ip,province,city,
rank() over(partition by user_id order by time) rn
from (select user_id,log_time as time,visit_ip,province,city from snbap_ods.user_app_click_log
union all
select user_id,visit_time as time,visit_ip,province,city from snbap_ods.user_pc_click_log) a)b where rn=1;

综合指标宽表

create table snbap_dm.user_complex_click as
select
lc.user_id,
lc.visit_ip last_ip,
lc.city last_city,
lc.province last_province,
fc.visit_ip first_ip,
fc.city first_city,
fc.province first_province
from
snbap_dw.user_last_complex_click lc join
snbap_dw.user_last_complex_click fc on lc.user_id=fc.user_id;

4.2.4用户订单分布

INSERT INTO us_order(order_no,user_id,user_name,order_money,order_type,
order_status,pay_status,pay_type,order_date)
SELECT order_no,user_id,user_name,ROUND(RAND()*100,2),ROUND(RAND()),
ROUND(RAND()*2),ROUND(RAND()),ROUND(RAND()*2),CURRENT_TIMESTAMP() FROM us_order;

order_type 0线下订单 1线上订单

order_status 0已成交 1已收货 2已取消

pay_type 0现金支付 2银行卡/网银支付 1第三方支付

pay_status 0未支付 1已支付

 第一次下单时间，

 最近一次下单时间，

 首单距今时间，

 尾单距今时间，

select user_id,min(order_date) first_order_date,max(order_date) last_order_date,
datediff(current_date(),min(order_date)) first_order_days,
datediff(current_date(),max(order_date)) last_order_days
from snbap_ods.us_order group by user_id;

 近 30 天订单数量（不含退拒），

 近 30 天订单金额（不含退拒），

 近 60 天订单数量（不含退拒），

 近 60 天订单金额（不含退拒），

 近 90 天订单数量（不含退拒），

 近 90 天订单金额（不含退拒），

 近 30 天订单数量（含退拒），

 近 30 天订单金额（含退拒），

 近 60 天购买次数（含退拒），

 近 60 天购买金额（含退拒），

 近 90 天购买次数（含退拒），

 近 90 天购买金额（含退拒），

 近 90 天的客单价（含退拒），

 最大消费金额 ，

 最小消费金额，

 累计消费次数（不含退拒），

 累计消费金额（不含退拒），

create table snbap_dw.user_order_info as
select user_id,min(order_date) first_order_date,max(order_date) last_order_date,
datediff(current_date(),min(order_date)) first_order_days,
datediff(current_date(),max(order_date)) last_order_days,
sum(case when order_status<>2 and order_date between date_sub(current_date(),30) 
and current_date() then 1 else 0 end) order_cnt_30,
sum(case when order_status<>2 and order_date between date_sub(current_date(),30) 
and current_date() then order_money else 0 end) order_money_30,
sum(case when order_status<>2 and order_date between date_sub(current_date(),60) 
and current_date() then 1 else 0 end) order_cnt_60,
sum(case when order_status<>2 and order_date between date_sub(current_date(),60) 
and current_date() then order_money else 0 end) order_money_60,
sum(case when order_status<>2 and order_date between date_sub(current_date(),90) 
and current_date() then 1 else 0 end) order_cnt_90,
sum(case when order_status<>2 and order_date between date_sub(current_date(),90) 
and current_date() then order_money else 0 end) order_money_90,
sum(case when order_date between date_sub(current_date(),30) 
and current_date() then 1 else 0 end) order_cnt_30_all,
sum(case when order_date between date_sub(current_date(),30) 
and current_date() then order_money else 0 end) order_money_30_all,
sum(case when order_date between date_sub(current_date(),60) 
and current_date() then 1 else 0 end) order_cnt_60_all,
sum(case when order_date between date_sub(current_date(),60) 
and current_date() then order_money else 0 end) order_money_60_all,
sum(case when order_date between date_sub(current_date(),90) 
and current_date() then 1 else 0 end) order_cnt_90_all,
sum(case when order_date between date_sub(current_date(),90) 
and current_date() then order_money else 0 end) order_money_90_all,
max(order_money) max_order_money,
min(order_money) min_order_money,
sum(case when order_status<>2 then 1 else 0 end) total_order_cnt,
sum(case when order_status<>2 then order_money else 0 end) total_order_money,
sum(order_money) total_order_money_all,
max(case when order_status=2 then update_time else null end) last_retreate_time
from snbap_ods.us_order group by user_id;

 客单价（含退拒），

 最常用收货地址，

create table snbap_dw.dwd_user_address as
select user_id,address from
(select user_id,address,rank() over(partition by user_id order by addr_cnt) rn from
(select user_id,address,count(1) addr_cnt from order_delivery d join us_order u on d.order_id=u.order_id group by user_id,address) a)b where rn=1;

 最常用支付方式

create table snbap_dw.dwd_user_popular_pay as
select user_id,pay_type from
(select user_id,pay_type,rank() over(partition by user_id order by pay_cnt desc) rn from
(select user_id,pay_type,count(1) pay_cnt
from us_order group by user_id,pay_type) a)b where rn=1;

 退货商品数量，

 退货商品金额，

create table snbap_dw.dwd_user_retreat as
select i.user_id,
sum(case when order_status=2 then goods_amount else 0 end) retreat_goods_num,
sum(case when order_status=2 then cost_price else 0 end) retreat_goods_money
from snbap_ods.order_item i join snbap_ods.us_order o 
on i.user_id=o.user_id group by i.user_id;

商品宽表

create table snbap_dm.user_order_info_final as
select u.*,address,pay_type,retreat_goods_num,retreat_goods_money
from snbap_dw.user_order_info u join
snbap_dw.dwd_user_address a on u.user_id=a.user_id
join snbap_dw.dwd_user_popular_pay p on u.user_id=p.user_id
join snbap_dw.dwd_user_retreat r on u.user_id=r.user_id;

 拒收商品数量，

 拒收商品金额，

 最近一次退货时间

下单分布：

 学校下单总数，

 公司下单总数，

 家里下单总数，

 凌晨下单总数，

 上午下单总数，

 中午下单总数，

 下午下单总数，

 晚上下单总数，

 指标口径:凌晨:0‐05 上午:06‐12 中午:13‐15 下午 16‐20 晚上：

21‐24

create table snbap_dw.user_order_distribution as
select o.user_id,
sum(case when user_order_flag=1 then 1 else 0 end) school_order,
sum(case when user_order_flag=2 then 1 else 0 end) company_order,
sum(case when user_order_flag=3 then 1 else 0 end) home_order,
sum(case when hour(order_date) between 0 and 5 then 1 else 0 end) early_order,
sum(case when hour(order_date) between 6 and 12 then 1 else 0 end) morning_order,
sum(case when hour(order_date) between 13 and 15 then 1 else 0 end) noon_order,
sum(case when hour(order_date) between 16 and 20 then 1 else 0 end) afternoon_order,
sum(case when hour(order_date) between 21 and 24 then 1 else 0 end) night_order
from snbap_ods.order_delivery d 
join snbap_ods.user_addr a on d.addr_id=a.addr_id
join snbap_ods.us_order o on d.order_id=o.order_id
group by a.user_id;