用户性别

1男 0女 -1未识别

1、商品性别得分

2、用户购买上述商品计算用户性别等得分

3、最优化算法训练阀值，根据阀值判断

孩子性别

0 仅有男孩

1仅有女孩

2男女都有

3无法识别

1、选择男孩女孩商品

2、确定用户购买商品的男女性别比例

3、训练阀值，判断孩子性别,同用户性别类似

用户是否有车

1有 0 没有

-1 未识别

根据用户购买车相关产品

判断用户是否有车

潜在汽车用户

1有

-1 未识别

用户游览或者搜索汽车

用户数据判断

用户忠诚度

1忠诚型用户

2偶尔型用户

3投资型用户

4游览型用户

-1未识别

总体规则是判断+聚类算法

1、游览用户型：只游览不购买的

2、购买天数大于一定天数的为忠诚用户

3、购买天数小于一定天数，大部分是有优惠才购买的

4、其他类型根据购买天数，购买最后一次距今时间，购买金额进行聚类

男性用户身高尺码

xxx-xxx身高段，-1未识别

用户购买服装鞋帽等用户判断

男性身材

1偏瘦、2标准、3偏胖4肥胖、-1未识别

用户购买服装鞋帽等用户判断

女性用户身高尺码

xxx-xxx身高段，-1未识别

用户购买服装鞋帽等用户判断

女性身材

1偏瘦、2标准、3偏胖4肥胖、-1未识别

用户购买服装鞋帽等用户判断

第一次消费时间、

最近一次消费时间、

首单距今时间、

尾单距今时间------分析用户什么时候来购买商品以及多久没有购买了。

最小消费金额、

最大消费金额、

累计消费次数(不含退拒)、

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

累计使用代金券金额、

累计使用代金券次数。-----分析用户总体消费情况。

     客单价（含退拒）、

     近60天客单价（含退拒）-----分析用户消费水平。

     常用收货地址、

     常用支付方式----分析用户常用的消费属性，方便做定向营销。

近30天购买次数(不含退拒)、

近30天购买金额（不含退拒）

近30天购买次数(含退拒)、

近30天购买金额（含退拒）----分析用户最近的消费能力。

退货商品数量、

退货商品金额、

拒收商品数量、

拒收商品金额、

最近一次退货时间-----分析用户拒收和退货习惯。 

最近30天购物车次数、

最近30天购物车商品件数、

最近30天购物车提交商品件数、

最近30天购物车放弃件数、

最近30天购物车成功率------分析用户购物车使用习惯

学校下单总数、

单位下单总数、

家里下单总数、

上午下单总数、

下午下单总数、

晚上下单总数----分析用户购物时间与地点习惯。

一级分类ID、

一级分类名称、

二级分类ID、

二级分类名称、

三级分类ID、

三级分类名称-----分析用户都购买了哪些类目。

近30天购买类目次数、

近30天购买类目金额、

近90天购买类目次数、

近90天购买类目金额、

近180天购买类目次数、

近180天购买类目金额、

累计购买类目次数、

累计购买类目金额----分析用户最近都购买了哪些类目。

最近一次购买类目时间、

最后一次购买类目距今天数----分析用户多久没有购买这个类目。

近30天购物车类目次数、

近30天购物车类目金额、

近90天购物车类目次数、

近90天购物车类目金额----分析用户最近都挑中哪些类目。

最近一次APP/PC端访问日期、

最近一次APP/PC端访问使用操作系统、

最近一次APP/PC端访问使用游览器、

最近一次访问IP地址、

最近一次访问城市、

最近一次访问的省份-----分析用户最近一次访问情况。

第一次APP/PC端访问日期、

第一次APP/PC端访问使用操作系统、

第一次APP/PC端访问使用游览器、

第一次访问IP地址、

第一次访问城市、

第一次访问的省份-----分析用户第一次访问情况。

近7天APP/PC端访问次数、

近30天APP/PC访问次数、

近60天APP/PC端访问次数、

近90天APP/PC端访问次数、

近180天APP/PC端访问次数、

近365天APP/PC端访问次数----分析用户APP/PC端访问次数。

近30天PC/APP端访问天数、

近30天PC/APP端访问并购买次数、

近30天PC/APP端访问PV、

近30天PC/APP端访问平均PV、

近30天PC/APP端最常用的游览器、

近30天PC/APP端不同IP数、

近30天PC/APP端最常用IP-----分析用户访问详情。

近30天0-5点访问的次数、

近30天6-7点访问的次数、

近30天8-9点访问的次数、

近30天10-12点访问的次数、

近30天13-14点访问的次数、

近30天15-17点访问的次数、

近30天18-19点访问的次数、

近30天20-21点访问的次数、

近30天22-23点访问的次数----分析用户喜欢在哪个时间上网访问。

/var/local/spark/bin/spark-sql  --master spark://itcast01:7077  --executor-memory 1g  --total-executor-cores 4

spark-sql

--master spark://node1:7077

--executor-memory 1g

--total-executor-cores 2

--conf  spark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse

BDM层数据表

(贴源缓存层)

订单表

itcast_bdm_order

订单明细表

itcast_bdm_order_desc

订单商品表

itcast_bdm_order_goods

 用户表

itcast_bdm_user

 购物车表

itcast_bdm_order_cart

用户上网记录表

itcast_bdm_user_pc_click_log

itcast_bdm_user_app_click_log

FDM层数据表

(拉链表、分区表)

用户宽表

itcast_fdm_user_wide

购物车表

itcast_fdm_order_cart

订单表

itcast_fdm_order

订单表明细表

itcast_fdm_order_desc

用户app端view表

itcast_fdm_user_app_pageview

用户pc端view表

itcast_fdm_user_pc_pageview

GDM层数据表

(通用数据模型层)

客户基本属性表

itcast_gdm_user_basic

客户消费订单表

itcast_gdm_user_consume_order

订单模型表

itcast_gdm_order

客户购买类目表

itcast_gdm_user_buy_category

 客户访问信息表

itcast_gdm_user_visit

--用户画像-客户基本属性模型表

create database if not exists gdm;

create table if not exists gdm.itcast_gdm_user_basic(

user_id string       ,--用户ID

user_name string ,--用户登陆名

user_sex  string ,--用户性别

user_birthday string       ,--用户生日

user_age  bigint ,--用户年龄

constellation string       ,--用户星座

province string ,--省份

city string             ,--城市

city_level string ,--城市等级

hex_mail string ,--邮箱

op_mail string ,--邮箱运营商

hex_phone string ,--手机号

fore_phone string ,--手机前3位

op_phone string ,--手机运营商

add_time timestamp ,--注册时间

login_ip string ,--登陆ip地址

login_source string ,--登陆来源

request_user string ,--邀请人

total_mark bigint ,--会员积分

used_mark bigint ,--已使用积分

level_name string ,--会员等级名称

blacklist bigint       ,--用户黑名单

is_married bigint ,--婚姻状况

education string ,--学历

monthly_money double ,--收入

profession string ,--职业

sex_model bigint ,--性别模型

is_pregnant_woman bigint       ,--是否孕妇

is_have_children bigint ,--是否有小孩

children_sex_rate double       ,--孩子性别概率

children_age_rate double       ,--孩子年龄概率

is_have_car bigint ,--是否有车

potential_car_user_rate double     ,--潜在汽车用户概率

phone_brand string ,--使用手机品牌

phone_brand_level string       ,--使用手机品牌档次

phone_cnt bigint ,--使用多少种不同的手机

change_phone_rate bigint       ,--更换手机频率

majia_flag string ,--马甲标志

majie_account_cnt bigint       ,--马甲账号数量

loyal_model bigint ,--用户忠诚度

shopping_type_model bigint       ,--用户购物类型

figure_model bigint ,--身材

stature_model bigint       ,--身高

dw_date timestamp

) partitioned by (dt string);

#***************************

--客户基本属性模型表BDM层

create database if not exists bdm;

create external table if not exists bdm.itcast_bdm_user(

user_id string     ,--用户ID

user_name string     ,--用户登陆名

user_sex  string     ,--用户性别

user_birthday string     ,--用户生日

user_age  bigint     ,--用户年龄

constellation string     ,--用户星座

province string     ,--省份

city string     ,--城市

city_level string     ,--城市等级

hex_mail string     ,--邮箱

op_mail string     ,--邮箱运营商

hex_phone string     ,--手机号

fore_phone string     ,--手机前3位

op_phone string     ,--手机运营商

add_time string     ,--注册时间

login_ip string     ,--登陆ip地址

login_source string     ,--登陆来源

request_user string     ,--邀请人

total_mark bigint     ,--会员积分

used_mark bigint     ,--已使用积分

level_name string     ,--会员等级名称

blacklist bigint     ,--用户黑名单

is_married bigint     ,--婚姻状况

education string     ,--学历

monthly_money double     ,--收入

profession string           --职业

) partitioned by (dt string)

row format delimited fields terminated by ',';

alter table itcast_bdm_user add partition (dt='2017-01-01') location '/business/itcast_bdm_user/2017-01-01';

--客户基本属性表FDM层

create database if not exists fdm;

create table if not exists fdm.itcast_fdm_user_wide(

user_id string     ,--用户ID

user_name string     ,--用户登陆名

user_sex  string     ,--用户性别

user_birthday string     ,--用户生日

user_age  bigint     ,--用户年龄

constellation string     ,--用户星座

province string     ,--省份

city string     ,--城市

city_level string     ,--城市等级

hex_mail string     ,--邮箱

op_mail string     ,--邮箱运营商

hex_phone string     ,--手机号

fore_phone string     ,--手机前3位

op_phone string     ,--手机运营商

add_time string     ,--注册时间

login_ip string     ,--登陆ip地址

login_source string     ,--登陆来源

request_user string     ,--邀请人

total_mark bigint     ,--会员积分

used_mark bigint     ,--已使用积分

level_name string     ,--会员等级名称

blacklist bigint     ,--用户黑名单

is_married bigint     ,--婚姻状况

education string     ,--学历

monthly_money double     ,--收入

profession string     ,--职业

dw_date  timestamp

) partitioned by (dt string);

--加载数据

insert overwrite table fdm.itcast_fdm_user_wide partition(dt='2017-01-01')

select

t.user_id,

t.user_name,

t.user_sex,

t.user_birthday,

t.user_age,

t.constellation,

t.province,

t.city,

t.city_level,

t.hex_mail,

t.op_mail,

t.hex_phone,

t.fore_phone,

t.op_phone,

t.add_time,

t.login_ip,

t.login_source,

t.request_user,

t.total_mark,

t.used_mark,

t.level_name,

t.blacklist,

t.is_married,

t.education,

t.monthly_money,

t.profession,

from_unixtime(unix_timestamp())  dw_date

from bdm.itcast_bdm_user t where dt='2017-01-01';

--用户画像-客户基本属性模型表GDM层

create database if not exists gdm;

create  table if not exists gdm.itcast_gdm_user_basic(

user_id string       ,--用户ID

user_name string ,--用户登陆名

user_sex  string ,--用户性别

user_birthday string       ,--用户生日

user_age  bigint ,--用户年龄

constellation string       ,--用户星座

province string ,--省份

city string       ,--城市

city_level string ,--城市等级

hex_mail string ,--邮箱

op_mail string ,--邮箱运营商

hex_phone string ,--手机号

fore_phone string ,--手机前3位

op_phone string ,--手机运营商

add_time string ,--注册时间

login_ip string ,--登陆ip地址

login_source string ,--登陆来源

request_user string ,--邀请人

total_mark bigint ,--会员积分

used_mark bigint ,--已使用积分

level_name string ,--会员等级名称

blacklist bigint       ,--用户黑名单

is_married bigint ,--婚姻状况

education string ,--学历

monthly_money double ,--收入

profession string ,--职业

sex_model bigint ,--性别模型

is_pregnant_woman bigint       ,--是否孕妇

is_have_children bigint ,--是否有小孩

children_sex_rate double       ,--孩子性别概率

children_age_rate double       ,--孩子年龄概率

is_have_car bigint ,--是否有车

potential_car_user_rate double     ,--潜在汽车用户概率

phone_brand string ,--使用手机品牌

phone_brand_level string       ,--使用手机品牌档次

phone_cnt bigint ,--使用多少种不同的手机

change_phone_rate bigint       ,--更换手机频率

majia_flag string ,--马甲标志

majie_account_cnt bigint       ,--马甲账号数量

loyal_model bigint ,--用户忠诚度

shopping_type_model bigint       ,--用户购物类型

figure_model bigint ,--身材

stature_model bigint       ,--身高

dw_date timestamp

) partitioned by (dt string);

--加载数据

insert overwrite table  gdm.itcast_gdm_user_basic partition(dt='2017-01-01')

select

t.user_id,

t.user_name,

t.user_sex,

t.user_birthday,

t.user_age,

t.constellation,

t.province,

t.city,

t.city_level,

t.hex_mail,

t.op_mail,

t.hex_phone,

t.fore_phone,

t.op_phone,

t.add_time,

t.login_ip,

t.login_source,

t.request_user,

t.total_mark,

t.used_mark,

t.level_name,

t.blacklist,

t.is_married,

t.education,

t.monthly_money,

t.profession,

null sex_model,--数据挖掘模型-开始

null is_pregnant_woman,

null is_have_children,

null children_sex_rate,

null children_age_rate,

null is_have_car,

null potential_car_user_rate,

null phone_brand,

null phone_brand_level,

null phone_cnt,

null change_phone_rate,

null majia_flag,

null majie_account_cnt,

null loyal_model,

null shopping_type_model,

null figure_model,

null stature_model,--数据挖掘模型-结束

from_unixtime(unix_timestamp())  dw_date

from (select * from fdm.itcast_fdm_user_wide where dt='2017-01-01') t;

演示模型表开发脚本：

######################

#名称：客户基本属性模型表

#itcast_gdm_user_basic.sh

######################

#!/bin/sh

yesterday=`date -d '-1 day' "+%Y-%m-%d"`

if [ $1 ];then

yesterday=$1

fi

SPARK_SUBMIT_INFO="/export/servers/spark/bin/spark-sql --master spark://node1:7077 --executor-memory 1g --total-executor-cores 2 --conf spark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse"

SOURCE_DATA="/root/source_data"

SQL_BDM="create database if not exists bdm;

create external table if not exists bdm.itcast_bdm_user(

user_id string    ,--用户ID

user_name string,--用户登陆名

user_sex  string,--用户性别

user_birthday string,--用户生日

user_age  bigint,--用户年龄

constellation string,--用户星座

province string    ,--省份

city string,--城市

city_level string,--城市等级

hex_mail string,--邮箱

op_mail string,--邮箱运营商

hex_phone string,--手机号

fore_phone string,--手机前3位

op_phone string,--手机运营商

add_time string,--注册时间

login_ip string,--登陆ip地址

login_source string,--登陆来源

request_user string,--邀请人

total_mark bigint,--会员积分

used_mark bigint,--已使用积分

level_name string,--会员等级名称

blacklist bigint,--用户黑名单

is_married bigint,--婚姻状况

education string,--学历

monthly_money double,--收入

profession string--职业

) partitioned by (dt string)

row format delimited fields terminated by ','

location '/business/bdm/itcast_bdm_user' ;

alter table bdm.itcast_bdm_user add partition (dt='$yesterday');"

SQL_FDM="create database if not exists fdm;

create table if not exists fdm.itcast_fdm_user_wide(

user_id string    ,--用户ID

user_name string,--用户登陆名

user_sex  string,--用户性别

user_birthday string,--用户生日

user_age  bigint,--用户年龄

constellation string,--用户星座

province string    ,--省份

city string,--城市

city_level string,--城市等级

hex_mail string,--邮箱

op_mail string,--邮箱运营商

hex_phone string,--手机号

fore_phone string,--手机前3位

op_phone string,--手机运营商

add_time string,--注册时间

login_ip string,--登陆ip地址

login_source string,--登陆来源

request_user string,--邀请人

total_mark bigint,--会员积分

used_mark bigint,--已使用积分

level_name string,--会员等级名称

blacklist bigint,--用户黑名单

is_married bigint,--婚姻状况

education string,--学历

monthly_money double,--收入

profession string,--职业

dw_date  timestamp

) partitioned by (dt string);"

##加载数据

LOAD_FDM="

insert overwrite table fdm.itcast_fdm_user_wide partition(dt='$yesterday')

select

t.user_id,

t.user_name,

t.user_sex,

t.user_birthday,

t.user_age,

t.constellation,

t.province,

t.city,

t.city_level,

t.hex_mail,

t.op_mail,

t.hex_phone,

t.fore_phone,

t.op_phone,

t.add_time,

t.login_ip,

t.login_source,

t.request_user,

t.total_mark,

t.used_mark,

t.level_name,

t.blacklist,

t.is_married,

t.education,

t.monthly_money,

t.profession,

from_unixtime(unix_timestamp())  dw_date

from bdm.itcast_bdm_user t where dt='$yesterday';"

SQL_GDM="create database if not exists gdm;

create  table if not exists gdm.itcast_gdm_user_basic(

user_id string    ,--用户ID

user_name string,--用户登陆名

user_sex  string,--用户性别

user_birthday string,--用户生日

user_age  bigint,--用户年龄

constellation string,--用户星座

province string    ,--省份

city string,--城市

city_level string,--城市等级

hex_mail string,--邮箱

op_mail string,--邮箱运营商

hex_phone string,--手机号

fore_phone string,--手机前3位

op_phone string,--手机运营商

add_time string,--注册时间

login_ip string,--登陆ip地址

login_source string,--登陆来源

request_user string,--邀请人

total_mark bigint,--会员积分

used_mark bigint,--已使用积分

level_name string,--会员等级名称

blacklist bigint,--用户黑名单

is_married bigint,--婚姻状况

education string,--学历

monthly_money double,--收入

profession string,--职业

sex_model bigint,--性别模型

is_pregnant_woman bigint,--是否孕妇

is_have_children bigint,--是否有小孩

children_sex_rate double,--孩子性别概率

children_age_rate double,--孩子年龄概率

is_have_car bigint,--是否有车

potential_car_user_rate double,--潜在汽车用户概率

phone_brand string,--使用手机品牌

phone_brand_level string,--使用手机品牌档次

phone_cnt bigint,--使用多少种不同的手机

change_phone_rate bigint,--更换手机频率

majia_flag string,--马甲标志

majie_account_cnt bigint,--马甲账号数量

loyal_model bigint,--用户忠诚度

shopping_type_model bigint,--用户购物类型

figure_model bigint,--身材

stature_model bigint,--身高

dw_date timestamp

) partitioned by (dt string);"

##加载数据到GDM

LOAD_GDM="insert overwrite table  gdm.itcast_gdm_user_basic partition(dt='$yesterday')

select

t.user_id,

t.user_name,

t.user_sex,

t.user_birthday,

t.user_age,

t.constellation,

t.province,

t.city,

t.city_level,

t.hex_mail,

t.op_mail,

t.hex_phone,

t.fore_phone,

t.op_phone,

t.add_time,

t.login_ip,

t.login_source,

t.request_user,

t.total_mark,

t.used_mark,

t.level_name,

t.blacklist,

t.is_married,

t.education,

t.monthly_money,

t.profession,

null sex_model,--数据挖掘模型-开始

null is_pregnant_woman,

null is_have_children,

null children_sex_rate,

null children_age_rate,

null is_have_car,

null potential_car_user_rate,

null phone_brand,

null phone_brand_level,

null phone_cnt,

null change_phone_rate,

null majia_flag,

null majie_account_cnt,

null loyal_model,

null shopping_type_model,

null figure_model,

null stature_model,--数据挖掘模型-结束

from_unixtime(unix_timestamp())  dw_date

from (select * from fdm.itcast_fdm_user_wide where dt='$yesterday') t;"

##创建BDM层表

echo "${SQL_BDM}"

$SPARK_SUBMIT_INFO -e "${SQL_BDM}"

##添加数据到BDM

hdfs dfs -put $SOURCE_DATA/itcast_bdm_user.txt /business/bdm/itcast_bdm_user/"dt=$yesterday"

##创建FDM层表

echo "${SQL_FDM}"

$SPARK_SUBMIT_INFO -e "${SQL_FDM}"

##导入数据到FDM

echo "${LOAD_FDM}"

$SPARK_SUBMIT_INFO -e "${LOAD_FDM}"

##创建GDM层表

echo "${SQL_GDM}"

$SPARK_SUBMIT_INFO -e "${SQL_GDM}"

##导入GDM数据

echo "${LOAD_GDM}"

$SPARK_SUBMIT_INFO -e "${LOAD_GDM}"

--订单宽表模型

create database if not exists gdm;

create  table if not exists gdm.itcast_gdm_order(

order_id   string,--订单ID

order_no   string,--订单号

order_date string,--订单日期

user_id string,--用户ID

user_name string,--登录名

order_money double,--订单金额

order_type string,--订单类型

order_status       string,--订单状态

pay_status string,--支付状态

pay_type string,--支付方式  1、在线支付，2、货到付款

order_source       string,--订单来源

consignee string,--收货人姓名

area_id string,--收货人地址ID

area_name string,--地址ID对应的地址段（粒度到县）

address string,--收货人地址（手工填写的地址）

mobile string,--收货人手机号

telphone string,--收货人电话

coupon_id bigint,--使用代金券ID

coupon_money double,--使用代金券金额

carriage_money double,--运费

create_time timestamp,--创建时间

update_time timestamp,--更新时间

dw_date timestamp

) partitioned by (dt string);

--订单主要信息表BDM层

create database if not exists bdm;

create external table if not exists bdm.itcast_bdm_order(

order_id string, --订单ID

order_no string, --订单号

order_date string, --订单日期

user_id  string, --用户ID

user_name string, --登录名

order_money double, --订单金额

order_type string, --订单类型

order_status string,       --订单状态

pay_status string, --支付状态

pay_type string, --支付方式  1、在线支付，2、货到付款

order_source string,       --订单来源

update_time timestamp, --订单更新时间

dw_date timestamp

) partitioned by (dt string)

row format delimited fields terminated by ','

lines terminated by ' ';

alter table bdm.itcast_bdm_order add partition (dt='2017-01-01') location '/business/itcast_bdm_order/2017-01-01';

hdfs dfs -put fdm_order.txt /business/itcast_bdm_order/2017-01-01

--订单主要信息表FDM层

create database if not exists fdm;

create  table if not exists fdm.itcast_fdm_order(

order_id string, --订单ID

order_no string, --订单号

order_date string, --订单日期

user_id  string, --用户ID

user_name string, --登录名

order_money double, --订单金额

order_type string, --订单类型

order_status string,       --订单状态

pay_status string, --支付状态

pay_type string, --支付方式  1、在线支付，2、货到付款

order_source string,       --订单来源

update_time timestamp, --订单更新时间

dw_date timestamp

) partitioned by (dt string);

--加载数据

insert overwrite table fdm.itcast_fdm_order partition(dt='2017-01-01')

select

t.order_id, --订单ID

t.order_no, --订单号

t.order_date,       --订单日期

t.user_id, --用户ID

t.user_name,       --登录名

t.order_money, --订单金额

t.order_type,       --订单类型

t.order_status,       --订单状态

t.pay_status,       --支付状态

t.pay_type, --支付方式

t.order_source,       --订单来源

t.update_time timestamp,--订单更新时间

from_unixtime(unix_timestamp())  dw_date

from bdm.itcast_bdm_order t where dt='2017-01-01';

-------订单详细信息表BDM层----------------

create database if not exists bdm;

create external table if not exists bdm.itcast_bdm_order_desc(

order_id string, --订单ID

order_no string, --订单号

consignee string, --收货人姓名

area_id string, --收货人地址ID

area_name string, --地址ID对应的地址段

address string, --收货人地址

mobile string, --收货人手机号

telphone string, --收货人电话

coupon_id bigint, --使用代金券ID

coupon_money double, --使用代金券金额

carriage_money double, --运费

create_time timestamp, --创建时间

update_time timestamp, --更新时间

dw_date timestamp

)partitioned by (dt string)

row format delimited fields terminated by ',';

alter table bdm.itcast_bdm_order_desc add partition (dt='2017-01-01') location '/business/itcast_bdm_order_desc/2017-01-01';

hdfs dfs -put itcast_bdm_order_desc.txt /business/itcast_bdm_order_desc/2017-01-01

-----订单主要信息表FDM层

create database if not exists fdm;

create table if not exists fdm.itcast_fdm_order_desc(

order_id string, --订单ID

order_no string, --订单号

consignee string, --收货人姓名

area_id string, --收货人地址ID

area_name string, --地址ID对应的地址段

address string, --收货人地址

mobile string, --收货人手机号

telphone string, --收货人电话

coupon_id bigint, --使用代金券ID

coupon_money double, --使用代金券金额

carriage_money double, --运费

create_time timestamp, --创建时间

update_time timestamp, --更新时间

dw_date timestamp

) partitioned by (dt string);

------加载数据

insert overwrite table fdm.itcast_fdm_order_desc partition(dt='2017-01-01')

select

t.order_id, --订单ID

t.order_no, --订单号

t.consignee,       --收货人姓名

t.area_id, --收货人地址ID

t.area_name,       --地址ID对应的地址段

t.address, --收货人地址

t.mobile, --收货人手机号

t.telphone, --收货人电话

t.coupon_id,       --使用代金券ID

t.coupon_money, --使用代金券金额

t.carriage_money,       --运费

t.create_time,       --创建时间

t.update_time,       --更新时间

from_unixtime(unix_timestamp())  dw_date

from bdm.itcast_bdm_order_desc t where dt='2017-01-01';

--------订单宽表模型表GDM

create database if not exists gdm;

create external table if not exists gdm.itcast_gdm_order(

order_id string, --订单ID

order_no string, --订单号

order_date string, --订单日期

user_id string, --用户ID

user_name string, --登录名

order_money double, --订单金额

order_type string, --订单类型

order_status string,--订单状态

pay_status string, --支付状态

pay_type string, --支付方式  1、在线支付，2、货到付款

order_source string,--订单来源

consignee string, --收货人姓名

area_id string, --收货人地址ID

area_name string, --地址ID对应的地址段（粒度到县）

address string, --收货人地址（手工填写的地址）

mobile string, --收货人手机号

telphone string, --收货人电话

coupon_id bigint, --使用代金券ID

coupon_money double,--使用代金券金额

carriage_money double,--运费

create_time timestamp,--创建时间

update_time timestamp,--更新时间

dw_date timestamp

) partitioned by (dt string);

---加载数据生成订单宽表

insert overwrite table gdm.itcast_gdm_order partition(dt='2017-01-01')

select

a.order_id, --订单ID

a.order_no, --订单号

a.order_date,       --订单日期

a.user_id, --用户ID

a.user_name,       --用户名

a.order_money, --订单金额

a.order_type,       --订单类型

a.order_status,    --订单状态

a.pay_status,       --支付类型

a.pay_type, --支付方式

a.order_source, --订单来源

b.consignee,       --收货人姓名

b.area_id, --收货人地址ID

b.area_name,       --地址ID对应的地址段

b.address, --收货人地址

b.mobile,          --收货人手机号

b.telphone,         --收货人电话

b.coupon_id,       --使用代金券ID

b.coupon_money,   --使用代金券金额

b.carriage_money,  --运费

b.create_time,     --创建时间

b.update_time,     --更新时间

from_unixtime(unix_timestamp()) dw_date

from (select *  from fdm.itcast_fdm_order where dt='2017-01-01') a

join (select * from fdm.itcast_fdm_order_desc where dt='2017-01-01') b on a.order_id=b.order_id;

--用户画像 客户消费订单表

create database if not exists gdm;

create  table if not exists gdm.itcast_gdm_user_consume_order(

user_id string, --客户ID

first_order_time timestamp, --第一次消费时间

last_order_time timestamp, --最近一次消费时间

first_order_ago bigint, --首单距今时间

last_order_ago bigint, --尾单距今时间

month1_hg_order_cnt bigint, --近30天购买次数（不含退拒）

month1_hg_order_amt double, --近30天购买金额（不含退拒）

month2_hg_order_cnt bigint, --近60天购买次数（不含退拒）

month2_hg_order_amt double, --近60天购买金额（不含退拒）

month3_hg_order_cnt bigint, --近90天购买次数（不含退拒）

month3_hg_order_amt double, --近90天购买金额（不含退拒）

month1_order_cnt bigint, --近30天购买次数（含退拒）

month1_order_amt double, --近30天购买金额（含退拒）

month2_order_cnt bigint, --近60天购买次数（含退拒）

month2_order_amt double, --近60天购买金额（含退拒）

month3_order_cnt bigint, --近90天购买次数（含退拒）

month3_order_amt double, --近90天购买金额（含退拒）

max_order_amt double, --最大消费金额

min_order_amt double, --最小消费金额

total_order_cnt bigint, --累计消费次数（不含退拒）

total_order_amt double, --累计消费金额（不含退拒）

user_avg_amt double, --客单价（含退拒）

month3_user_avg_amt double, --近90天的客单价

common_address string, --常用收货地址

common_paytype string, --常用支付方式

month1_cart_cnt bigint, --近30天购物车的次数

month1_cart_goods_cnt bigint, --近30天购物车商品件数

month1_cart_submit_cnt bigint, --近30天购物车提交商品件数

month1_cart_rate double, --近30天购物车成功率

month1_cart_cancle_cnt double, --近30天购物车放弃件数

return_cnt bigint, --退货商品数量

return_amt double, --退货商品金额

reject_cnt bigint, --拒收商品数量

reject_amt double, --拒收商品金额

last_return_time timestamp, --最近一次退货时间

school_order_cnt bigint, --学校下单总数

company_order_cnt bigint, --单位下单总数

home_order_cnt bigint, --家里下单总数

forenoon_order_cnt bigint, --上午下单总数

afternoon_order_cnt bigint, --下午下单总数

night_order_cnt bigint, --晚上下单总数

morning_order_cnt bigint, --凌晨下单总数

dw_date timestamp

) partitioned by (dt string);

---客户消费订单模型表-临时表01

drop table if exists gdm.itcast_gdm_user_consume_order_temp_01;

CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01 AS

SELECT

  t.user_id,

  MIN(order_date) first_order_time,--第一次消费时间

  MAX(order_date) last_order_time,--最近一次消费时间

  DATEDIFF(MIN(order_date), '2017-01-01') first_order_ago,--首单距今时间

  DATEDIFF(MAX(order_date), '2017-01-01') last_order_ago,--尾单距今时间

  SUM(

    CASE

      WHEN t.dat_30 = 1

      AND t.order_flag = 0

      THEN 1

    END

  ) month1_hg_order_cnt,--近30天购买次数（不含退拒）

  SUM(

    CASE

      WHEN t.dat_30 = 1

      AND t.order_flag = 0

      THEN t.order_money

    END

  ) month1_hg_order_amt,--近30天购买金额（不含退拒）

  SUM(

    CASE

      WHEN t.dat_60 = 1

      AND t.order_flag = 0

      THEN 1

    END

  ) month2_hg_order_cnt,--近60天购买次数（不含退拒）

  SUM(

    CASE

      WHEN t.dat_60 = 1

      AND t.order_flag = 0

      THEN t.order_money

    END

  ) month2_hg_order_amt,--近60天购买金额（不含退拒）

  SUM(

    CASE

      WHEN t.dat_90 = 1

      AND t.order_flag = 0

      THEN 1

    END

  ) month3_hg_order_cnt,--近90天购买次数（不含退拒）

  SUM(

    CASE

      WHEN t.dat_90 = 1

      AND t.order_flag = 0

      THEN t.order_money

    END

  ) month3_hg_order_amt,--近90天购买金额（不含退拒）

  SUM(dat_30) month1_order_cnt,--近30天购买次数（含退拒）

  SUM(

    CASE

      WHEN t.dat_30 = 1

      THEN t.order_money

    END

  ) month1_order_amt,--近30天购买金额（含退拒）

  SUM(dat_60) month2_order_cnt,--近60天购买次数（含退拒）

  SUM(

    CASE

      WHEN t.dat_60 = 1

      THEN t.order_money

    END

  ) month2_order_amt,--近60天购买金额（含退拒）

  SUM(dat_90) month3_order_cnt,--近90天购买次数（含退拒）

 SUM(

    CASE

      WHEN t.dat_90 = 1

      THEN t.order_money

    END

  ) month3_order_amt,--近90天购买金额（含退拒）

  MAX(t.order_money) max_order_amt,--最大消费金额

  MIN(t.order_money) min_order_amt,--最小消费金额

    SUM(

    CASE

      WHEN t.order_flag = 0

      THEN 1

    END

  ) total_order_cnt,--累计消费次数（不含退拒）

  SUM(

    CASE

      WHEN t.order_flag = 0

      THEN t.order_money

    END

  ) total_order_amt,--累计消费金额（不含退拒）

  SUM(coupon_money) total_coupon_amt,--累计使用代金券金额

  SUM(t.order_money) / COUNT(1) user_avg_amt,--客单价（含退拒）

  0 month3_user_avg_amt,--近90天的客单价（含退拒）

  0 common_address,--常用收获地址

  0 common_paytype,--常用支付方式

  0 month1_cart_cnt,--最近30天购物车次数

  0 month1_cart_goods_cnt,--最近30天购物车商品件数

  0 month1_cart_submit_cnt,--最近30天购物车提交商品件数

  0 month1_order_rate,--最近30天购物车成功率

  0 month1_cart_cancle_cnt,--最近30天购物车放弃件数

 SUM(

    CASE

      WHEN t.order_status = 3

      THEN t1.goods_amount

    END

  ) return_cnt,--退货商品数量

  SUM(

    CASE

      WHEN t.order_status = 3

      THEN t.order_money

    END

  ) return_amt,--退货商品金额

  SUM(

    CASE

      WHEN t.order_status = 4

      THEN t1.goods_amount

    END

  ) reject_cnt,--拒收商品数量

  SUM(

    CASE

      WHEN t.order_status = 4

      THEN t.order_money

    END

  ) reject_amt,--拒收商品金额

  MAX(

    CASE

      WHEN t.order_status = 3

      THEN t.order_date

    END

  ) last_return_time,--最近一次退货时间

  SUM(

    CASE

      WHEN t2.order_addr = 1

      THEN 1

    END

  ) school_order_cnt,--学校下单总数

  SUM(

    CASE

      WHEN t2.order_addr = 2

      THEN 1

    END

  ) company_order_cnt,--单位下单总数

  SUM(

    CASE

      WHEN t2.order_addr = 3

      THEN 1

    END

  ) home_order_cnt,--家里下单总数

  SUM(

    CASE

      WHEN t.order_hour >= 8

      AND t.order_hour <= 11

      THEN 1

    END

  ) forenoon_order_cnt,--上午下单总数

  SUM(

    CASE

      WHEN t.order_hour >= 12

      AND t.order_hour <= 18

      THEN 1

    END

  ) afternoon_order_cnt,--下午下单总数

  SUM(

    CASE

      WHEN t.order_hour >= 19

      AND t.order_hour <= 22

      THEN 1

    END

  ) night_order_cnt,--晚上下单总数

  SUM(

    CASE

      WHEN t.order_hour >= 23

      AND t.order_hour <= 7

      THEN 1

    END

  ) morning_order_cnt --凌晨下单总数

FROM

  (SELECT

    a.*,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 29)

        AND order_date <= '2017-01-01'

        THEN 1

      END

    ) dat_30,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 59)

        AND order_date <= '2017-01-01'

        THEN 1

      END

    ) dat_60,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 89)

        AND order_date <= '2017-01-01'

        THEN 1

      END

    ) dat_90,

    (

      CASE

        WHEN a.order_status IN (3, 4)

        THEN 1

        ELSE 0

      END

    ) order_flag,--退货与拒收标示

    HOUR(order_date) order_hour

  FROM

    gdm.itcast_gdm_order a

  WHERE dt = '2017-01-01') t

  LEFT JOIN

    (SELECT

      order_id,

      goods_amount

    FROM

      fdm.itcast_fdm_order_goods) t1

    ON (t.order_id = t1.order_id)

  LEFT JOIN

    (SELECT

      user_id,

      order_addr

    FROM

      gdm.itcast_user_order_addr_model) t2

    ON (t.user_id = t2.user_id)

GROUP BY t.user_id ;

---购物车临时模型表--临时表02

DROP TABLE IF EXISTS gdm.itcast_gdm_user_consume_order_temp_02;

CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_02 AS

SELECT

  user_id,

  COUNT(1) month1_cart_cnt,--最近30天购物车次数

  SUM(goods_num) month1_cart_goods_cnt,--最近30天购物车商品件数

  SUM(

    CASE

      WHEN sumbit_time <> ''

      THEN goods_num

      ELSE 0

    END

  ) month1_cart_submit_cnt,--最近30天购物车提交商品件数

 '' month1_cart_rate,--最近30天购物车成功率

 SUM(

    CASE

      WHEN cancle_time <> ''

      THEN goods_num

      ELSE 0

    END

  ) month1_cart_cancle_cnt  --最近30天购物车放弃件数

FROM

  fdm.itcast_fdm_order_cart

WHERE dt = '2017-01-01'

  AND to_date (add_time) >= DATE_SUB('2017-01-01', 29)

  AND to_date (add_time) <= '2017-01-01'

GROUP BY user_id ;

---购物车临时模型表---常用地址和常用支付方式-临时表03

drop table if exists gdm.itcast_gdm_user_consume_order_temp_03;

create table gdm.gdm_user_consume_order_temp_03 as

select

  t.user_id,

  t.con,

  t.type,

  t.cnt

from

  (select

    b.user_id,

    b.con,

    b.type,

    b.cnt,

    row_number() over(distribute by b.user_id,

    b.type sort by b.cnt,

    b.type desc) rn

from

  (select

    a.user_id,concat(

      coalesce(area_name, ''),

      coalesce(address, '')) con,

    'address' type,

    count(1) cnt

  from

    gdm.itcast_gdm_order a where dt = '2017-01-01'

  group by a.user_id,

    concat(

      coalesce(area_name, ''),

      coalesce(address, '')

    )

    union

    all

    select

      a.user_id,

      a.pay_type con,

      'pay_type' type,

      count(1) cnt

    from

      gdm.itcast_gdm_order a

    where dt = '2017-01-01'

    group by a.user_id,

      a.pay_type) b) t

  where t.rn = 1 ;

--购物车表和订单表整合

drop table if exists gdm.itcast_gdm_user_consume_order_temp_100;

create table gdm.gdm_user_consume_order_temp_100 as

select

  a.user_id

from

  (select

    user_id

  from

    gdm.itcast_gdm_user_consume_order_temp_01

  union

  all

  select

    user_id

  from

    gdm.itcast_gdm_user_consume_order_temp_02) a

group by a.user_id ;

--生成最终的客户消费订单表

INSERT overwrite TABLE gdm.itcast_gdm_user_consume_order PARTITION (dt = '2017-01-01')

SELECT

  t.user_id,  --客户ID

  t1.first_order_time,  --常用地址和常用支付方式次消费时间

  t1.last_order_time,  --最近一次消费时间

  t1.first_order_ago,  --首单距今时间

  t1.last_order_ago,  --尾单距今时间

  t1.month1_hg_order_cnt,--近30天购买次数（不含退拒）

  t1.month1_hg_order_amt,--近30天购买金额（不含退拒）

  t1.month2_hg_order_cnt,--近60天购买次数（不含退拒）

  t1.month2_hg_order_amt,--近60天购买金额（不含退拒）

  t1.month3_hg_order_cnt,--近90天购买次数（不含退拒）

  t1.month3_hg_order_amt,--近90天购买金额（不含退拒）

  t1.month1_order_cnt,    --近30天购买次数（含退拒）

  t1.month1_order_amt,    --近30天购买金额（含退拒）

  t1.month2_order_cnt,    --近60天购买次数（含退拒）

  t1.month2_order_amt,    --近60天购买金额（含退拒）

  t1.month3_order_cnt,    --近90天购买次数（含退拒）

  t1.month3_order_amt,    --近90天购买金额（含退拒）

  t1.max_order_amt,       --最大消费金额

  t1.min_order_amt,       --最小消费金额

  t1.total_order_cnt,       --累计消费次数（不含退拒）

  t1.total_order_amt,      --累计消费金额（不含退拒）

  t1.user_avg_amt,        --客单价（含退拒）

  (

    CASE

      WHEN t1.month3_order_cnt <> 0

      THEN t1.month3_order_amt / t1.month3_order_cnt

      ELSE 0

    END

  ) month3_user_avg_amt,    --近90天的客单价(含退拒)

  t3.common_address,        --常用收货地址

  t3.common_paytype,        --常用支付方式

  t2.month1_cart_cnt,         --近30天购物车的次数

  t2.month1_cart_goods_cnt,   --近30天购物车商品件数

  t2.month1_cart_submit_cnt,  --近30天购物车提交商品件数

  (

    CASE

      WHEN t1.month1_order_cnt <> 0

      THEN t2.month1_cart_submit_cnt / t2.month1_cart_goods_cnt

      ELSE 0

    END

  ) month1_cart_rate,  --近30天购物车成功率

  t2.month1_cart_cancle_cnt,  --近30天购物车放弃件数

  t1.return_cnt,                  --退货商品数量

  t1.return_amt,                  --退货商品金额

  t1.reject_cnt,                 --拒收商品数量

  t1.reject_amt,                 --拒收商品金额

  t1.last_return_time,            --最近一次退货时间

  t1.school_order_cnt,           --学校下单总数

  t1.company_order_cnt,         --单位下单总数

  t1.home_order_cnt,            --家里下单总数

  t1.forenoon_order_cnt,         --上午下单总数

  t1.afternoon_order_cnt,        --下午下单总数

  t1.night_order_cnt,            --晚上下单总数

  t1.morning_order_cnt,          --凌晨下单总数

  FROM_UNIXTIME(UNIX_TIMESTAMP())  dw_date

FROM                             

  gdm.itcast_gdm_user_consume_order_temp_100 t

  LEFT JOIN gdm.itcast_gdm_user_consume_order_temp_01 t1

    ON (t.user_id = t1.user_id)

  LEFT JOIN gdm.itcast_gdm_user_consume_order_temp_02 t2

    ON (t.user_id = t2.user_id)

  LEFT JOIN

    (SELECT

      user_id,

      MAX(

        CASE

          WHEN type = 'address'

          THEN con

        END

      ) common_address,

      MAX(

        CASE

          WHEN type = 'pay_type'

          THEN con

        END

      ) common_paytype

    FROM

      gdm.itcast_gdm_user_consume_order_temp_03

    GROUP BY user_id) t3

    ON (t.user_id = t3.user_id);

--用户画像 客户购买类目表

create database if not exists gdm;

create external table if not exists gdm.itcast_gdm_user_buy_category(

user_id bigint , --客户ID

first_cat bigint , --一级分类ID

first_cat_name       string , --一级分类名称

second_cat bigint , --二分类ID

second_cat_name string , --二级分类名称

third_cat bigint , --三级分类ID

third_cat_name string , --三级分类名称

month1_cat_cnt bigint , --近30天购物类目次数

month1_cat_amt string , --近30天购物类目金额

month3_cat_cnt bigint , --近90天购物类目次数

month3_cat_amt string , --近90天购物类目金额

month6_cat_cnt bigint , --近180天购物类目次数

month6_cat_amt string , --近180天购物类目金额

total_cat_cnt       bigint ，--累计购物类目次数

total_cat_amt       string , --累计购物类目次数

month1_cart_cat_cnt       bigint , --近30天购物车类目次数

month3_cart_cat_cnt       bigint , --近90天购物车类目次数

month6_cart_cat_cnt       bigint , --近180天购物车类目次数

total_cart_cat_cnt          bigint , --累计购物车类目次数

last_cat_time       timestamp , --最后一次购买类目时间

last_cat_ago       bigint , --最后一次购买类目距今天数

dw_date             timestamp

)partitioned by (dt string);

--计算订单中客户购买情况

drop table if exists gdm.itcast_gdm_user_buy_category_temp;

CREATE TABLE gdm.itcast_gdm_user_buy_category_temp AS

SELECT

  a.user_id,

  '' first_category_id,

  '' first_category_name,

  '' second_category_id,

  '' second_catery_name,

  b.third_cart  third_category_id,

  b.third_cat_name  third_category_name,

  SUM(

    CASE

      WHEN a.dat_30 = 1

      THEN b.goods_amount

    END

  ) month1_category_cnt,

  SUM(

    CASE

      WHEN a.dat_30 = 1

      THEN COALESCE(b.curr_price,0)* COALESCE(b.goods_amount,0)

    END

  ) month1_category_amt,

  SUM(

    CASE

      WHEN a.dat_90 = 1

      THEN b.goods_amount

    END

  ) month3_category_cnt,

  SUM(

    CASE

      WHEN a.dat_90 = 1

      THEN COALESCE(b.curr_price, 0)* COALESCE(b.goods_amount, 0)

    END

  ) month3_category_amt,

  SUM(

    CASE

      WHEN a.dat_180 = 1

      THEN b.goods_amount

    END

  ) month6_category_cnt,

  SUM(

    CASE

      WHEN a.dat_180 = 1

      THEN COALESCE(b.curr_price, 0) * COALESCE(b.goods_amount, 0)

    END

  ) month6_category_amt,

  SUM(b.goods_amount) total_category_cnt,

  SUM(

    COALESCE(b.curr_price, 0) * COALESCE(b.goods_amount, 0)

  ) total_category_amt,

  MAX(a.order_date) last_category_time,

  DATEDIFF(MIN(a.order_date), '2017-01-01') last_category_ago,

  FROM_UNIXTIME(UNIX_TIMESTAMP()) dw_date

FROM

  (SELECT

    a.*,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 29)

        AND order_date <= '2017-01-01'

        THEN

        1

      END

    ) dat_30,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 89)

        AND order_date <= '2017-01-01'

        THEN

        1

      END

    ) dat_90,

    (

      CASE

        WHEN order_date >= DATE_SUB('2017-01-01', 179)

        AND order_date <= '2017-01-01'

        THEN

        1

      END

    ) dat_180

  FROM

    fdm.itcast_fdm_order a

  WHERE dt = '2017-01-01') a

  JOIN

    (SELECT

      *

    FROM

      fdm.itcast_fdm_order_goods

    WHERE dt = '2017-01-01') b

    ON (a.user_id = b.user_id)

GROUP BY a.user_id,b.third_cart,b.third_cat_name;

--购物车中类目情况

--购物车中类目情况

drop table if exists gdm.itcast_gdm_user_cart_category_temp;

create table gdm.itcast_gdm_user_cart_category_temp as

select

  a.user_id,

  b.third_cart,

  sum(

    case

      when to_date (add_time) >= date_sub('2017-01-01', 29)

      and to_date (add_time) <= '2017-01-01'

      then 1

    end

  ) month1_category_cnt,

  sum(

    case

      when to_date (add_time) >= date_sub('2017-01-01', 59)

      and to_date (add_time) <= '2017-01-01'

      then 1

    end

  ) month3_category_cnt,

  sum(

    case

      when to_date (add_time) >= date_sub('2017-01-01', 179)

      and to_date (add_time) <= '2017-01-01'

      then 1

    end

  ) month6_category_cnt,

  count(1) total_category_cnt

from

  (select

    *

  from

    fdm.itcast_fdm_order_cart

  where dt = '2017-01-01'

    and to_date (add_time) >= date_sub('2017-01-01', 179)

    and to_date (add_time) <= '2017-01-01') a

  left join

    (select

      goods_id,

      third_cart

    from

      fdm.itcast_fdm_order_goods

    where dt = '2017-01-01'

    group by goods_id,

      third_cart) b

    on (a.goods_id = b.goods_id)

group by user_id,

  b.third_cart ;

--整合

drop table if exists gdm.itcast_gdm_user_category_total;

create table gdm.itcast_gdm_user_category_total as

select

  a.user_id,

  b.first_category_id,

  b.first_category_name,

  b.second_category_id,

  b.second_catery_name,

  a.third_category_id,

  b.third_category_name

from

  (select

    user_id,

    third_category_id

  from

    gdm.itcast_gdm_user_buy_category_temp

  union

  all

  select

    user_id,

    third_cart

  from

    gdm.itcast_gdm_user_cart_category_temp) a

  left join gdm.gdm_category_code b

    on (

      a.third_category_id = b.third_category_id

    )

group by a.user_id,

  b.first_category_id,

  b.first_category_name,

  b.second_category_id,

  b.second_catery_name,

  a.third_category_id,

  b.third_category_name ;

--生成最终客户购买类目表

INSERT overwrite TABLE gdm.itcast_gdm_user_buy_category PARTITION (dt = '2017-01-01')

SELECT

  t.user_id,

  t.first_category_id,

  t.first_category_name,

  t.second_category_id,

  t.second_catery_name,

  t.third_category_id,

  t.third_category_name,

  t1.month1_category_cnt,

  t1.month1_category_amt,

  t1.month3_category_cnt,

  t1.month3_category_amt,

  t1.month6_category_cnt,

  t1.month6_category_amt,

  t1.total_category_cnt,

  t1.total_category_amt,

  t2.month1_category_cnt,

  t2.month3_category_cnt,

  t2.month6_category_cnt,

  t2.total_category_cnt,

  t1.last_category_time,

  t1.last_category_ago,

  FROM_UNIXTIME(UNIX_TIMESTAMP()) dw_date

FROM

  gdm.itcast_gdm_user_category_total t

  LEFT JOIN gdm.itcast_gdm_user_buy_category_temp t1

    ON (

      t.user_id = t1.user_id

      AND t.third_category_id = t1.third_category_id

    )

  LEFT JOIN gdm.itcast_gdm_user_cart_category_temp t2

    ON (t.user_id = t2.user_id

    AND t.third_category_id = t2.third_cart) ;

--用户画像-客户访问信息表

drop table if exists gdm.itcast_gdm_user_visit;

create external table gdm.itcast_gdm_user_visit(

user_id string ,--客户ID

latest_pc_visit_date string ,--最近一次PC端访问日期

latest_app_visit_date string ,--最近一次APP端访问日期

latest_pc_visit_session string ,--最近一次PC端访问的session

latest_pc_cookies string ,--最近一次PC端访问的cookies

latest_pc_pv string ,--最近一次PC端访问的PV

latest_pc_browser_name string ,--最近一次PC端访问使用的游览器

latest_pc_visit_os string ,--最近一次PC端访问使用的操作系统

latest_app_name string ,--最近一次APP端访问app名称

latest_app_visit_os string ,--最近一次APP端访问使用的操作系统

latest_visit_ip string ,--最近一次访问IP(不分APP与PC)

latest_city string ,--最近一次访问城市(不分APP与PC)

latest_province string ,--最近一次访问省份(不分APP与PC)

first_pc_visit_date string ,--第一次PC端访问日期

first_app_visit_date string ,--第一次APP端访问日期

first_pc_visit_session string ,--第一次PC端访问的session

first_pc_cookies string ,--第一次PC端访问的cookies

first_pc_pv string ,--第一次PC端访问的PV

first_pc_browser_name string ,--第一次PC端访问使用的游览器

first_pc_visit_os string ,--第一次PC端访问使用的操作系统

first_app_name string ,--第一次APP端访问app名称

first_app_visit_os string ,--第一次APP端访问使用的操作系统

first_visit_ip string ,--第一次访问IP(不分APP与PC)

first_city string ,--第一次访问城市(不分APP与PC)

first_province string ,--第一次访问省份(不分APP与PC)

day7_app_cnt bigint ,--近7天APP端访问次数

day15_app_cnt bigint ,--近15天APP端访问次数

month1_app_cnt bigint ,--近30天APP端访问次数

month2_app_cnt bigint ,--近60天APP端访问次数

month3_app_cnt bigint ,--近90天APP端访问次数

day7_pc_cnt bigint ,--近7天PC端访问次数

day15_pc_cnt bigint ,--近15天PC端访问次数

month1_pc_cnt bigint ,--近30天PC端访问次数

month2_pc_cnt bigint ,--近60天PC端访问次数

month3_pc_cnt bigint ,--近90天PC端访问次数

month1_pc_days bigint ,--近30天PC端访问天数

month1_pc_buy_cnt bigint ,--近30天PC端访问购买次数

month1_pc_pv bigint ,--近30天PC端访问PV

month1_pc_avg_pv bigint ,--近30天PC端访问平均PV

month1_pc_diff_ip_cnt bigint ,--近30天PC端访问不同ip数

month1_pc_common_ip bigint ,--近30天PC端访问最常用ip

month1_pc_diff_cookie_cnt bigint ,--近30天PC端访问不同的cookie数

month1_pc_common_cookie bigint ,--近30天PC端访问最常用的cookie

month1_pc_common_browser_name     bigint ,--近30天PC端访问最常用游览器

month1_pc_common_os bigint ,--近30天PC端访问最常用的操作系统

month1_hour025_cnt        bigint ,--近30天0-5点访问次数(不分PC与APP)

month1_hour627_cnt bigint ,--近30天6-7点访问次数(不分PC与APP)

month1_hour829_cnt bigint ,--近30天8-9点访问次数(不分PC与APP)

month1_hour10212_cnt bigint ,--近30天10-12点访问次数(不分PC与APP)

month1_hour13214_cnt bigint ,--近30天13-14点访问次数(不分PC与APP)

month1_hour15217_cnt bigint ,--近30天15-17点访问次数(不分PC与APP)

month1_hour18219_cnt bigint ,--近30天18-19点访问次数(不分PC与APP)

month1_hour20221_cnt bigint ,--近30天20-21点访问次数(不分PC与APP)

month1_hour22223_cnt bigint ,--近30天22-23点访问次数(不分PC与APP)

dw_date        timestamp

) partitioned by (dt string);

------用户上网轨迹表BDM层--PC端

DROP TABLE IF EXISTS bdm.itcast_bdm_user_pc_click_log ;

CREATE external TABLE bdm.itcast_bdm_user_pc_click_log (

  session_id STRING,  --sessionID

  cookie_id STRING,   --cookieID

  visit_time STRING,  --访问时间

  user_id STRING,     --用户ID

  visit_url STRING,   --访问的URL

  visit_os STRING,    --操作系统

  browser_name STRING,--游览器名称

  visit_ip STRING,    --访问ip

  province STRING,    --省份

  city STRING,        --城市

  page_id STRING,     --页面ID

  goods_id STRING,    --商品ID

  shop_id STRING      --商店ID

) partitioned by (dt string)

row format delimited fields terminated by ','

lines terminated by ' ';

alter table bdm.itcast_bdm_user_pc_click_log add partition (dt='2017-01-01') location '/business/itcast_bdm_user_pc_click_log/2017-01-01';

hdfs dfs -put itcast_bdm_user_pc_click_log.txt /business/itcast_bdm_user_pc_click_log/2017-01-01

------用户上网轨迹表FDM层--PC端

DROP TABLE IF EXISTS fdm.itcast_fdm_user_pc_pageview ;

CREATE external TABLE fdm.itcast_fdm_user_pc_pageview (

  session_id STRING,  --sessionID

  cookie_id STRING,   --cookieID

  user_id STRING,     --用户ID

  in_time STRING,     --访问进入时间

  out_time STRING,    --访问离开时间

  stay_time STRING,   --访问停留时间

  pv BIGINT,          --PV

  visit_os STRING,    --操作系统

  browser_name STRING,--游览器名称

  visit_ip STRING,     --访问ip

  province STRING,    --省份

  city STRING         --城市

) partitioned BY (dt STRING);

-------------加载数据---

INSERT overwrite TABLE fdm.itcast_fdm_user_pc_pageview PARTITION (dt = '2017-01-01')

SELECT

  t.session_id,

  t.cookie_id,

  t.user_id,

  MIN(visit_time) in_time,

  MAX(visit_time) out_time,

  (

    case

      WHEN MIN(visit_time) = MAX(visit_time)

      then 5

      else unix_timestamp(MAX(visit_time)) - unix_timestamp(MIN(visit_time))

    end

  ) stay_time,

  COUNT(1) pv,

  t.visit_os,

  t.browser_name,

  t.visit_ip,

  t.province,

  t.city

FROM

  bdm.itcast_bdm_user_pc_click_log t

WHERE dt = '2017-01-01'

GROUP BY t.session_id,

  t.cookie_id,

  t.user_id,

  t.visit_os,

  t.browser_name,

  t.visit_ip,

  t.province,

  t.city ;

------用户上网轨迹表BDM层--APP端

drop table if exists bdm.itcast_bdm_user_app_click_log;

create external table bdm.itcast_bdm_user_app_click_log(

  user_id string,        --用户ID

  log_time string,       --访问时间

  phone_id string,       --手机ID，唯一标识一台设备

  visit_os string,        --操作系统 android、ios、wp

  os_version string,     --操作系统版本

  app_name string,     --APP的名称

  app_version string,    --APP的版本

  device_token string,   --PUSH码，消息推送的

  visit_ip string,        --访问ip

  province string,       --省份

  city string            --城市

) partitioned by (dt string)

row format delimited fields terminated by ','

lines terminated by ' ';

alter table bdm.itcast_bdm_user_app_click_log add partition (dt='2017-01-01') location '/business/itcast_bdm_user_app_click_log/2017-01-01';

hdfs dfs -put itcast_bdm_user_app_click_log.txt /business/itcast_bdm_user_app_click_log/2017-01-01

--Push指运营人员通过自己的产品或第三方工具对用户移动设备进行的主动消息推送。用户可以在移动设备锁定屏幕和通知栏看到push消息通知，通知栏点击可唤起APP并去往相应页面。我们平时在锁屏上看到的微信消息等等都属于APP消息推送行列。

------用户上网轨迹表FDM层--app端

DROP TABLE IF EXISTS fdm.itcast_fdm_user_app_pageview ;

CREATE external TABLE fdm.itcast_fdm_user_app_pageview (

  user_id string,        --用户ID

  log_time string,       --访问时间

  log_hour string,     --访问时间的小时数

  phone_id string,       --手机ID，唯一标识一台设备

  visit_os string,       --操作系统 android、ios、wp

  os_version string,     --操作系统版本

  app_name string,      --APP的名称

  app_version string,    --APP的版本

  device_token string,   --PUSH码，消息推送的

  visit_ip string,       --访问ip

  province string,       --省份

  city string            --城市

) partitioned BY (dt STRING);

-------------加载数据---

INSERT overwrite TABLE fdm.itcast_fdm_user_app_pageview PARTITION (dt = '2017-01-01')

SELECT

  t.user_id,

  t.log_time,

  HOUR(t.log_time) log_hour,

  t.phone_id,

  t.visit_os,

  t.os_version,

  t.app_name,

  t.app_version,

  t.device_token,

  t.visit_ip,

  t.province,

  t.city

FROM

  bdm.itcast_bdm_user_app_click_log t

WHERE dt = '2017-01-01' ;

--生成临时表-统计出用户近30天访问的ip/cookie/游览器信息

drop table if exists gdm.itcast_gdm_user_visit_temp_01;

create table gdm.itcast_gdm_user_visit_temp_01 as

select

  t.user_id,

  t.type,

  t.con,

  t.cnt,

  t.rn

from

  (select

    b.user_id,

    b.con,

    b.type,

    b.cnt,

    row_number () over (

      distribute by b.user_id,

      b.type sort by b.cnt desc

    ) rn

  from

    (select

      a.user_id,

      a.visit_ip con,

      'visit_ip' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.visit_ip

    union

    all

    select

      a.user_id,

      a.cookie_id con,

      'cookie_id' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.cookie_id

    union

    all

    select

      a.user_id,

      a.browser_name con,

      'browser_name' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.browser_name

    union

    all

    select

      a.user_id,

      a.visit_os con,

      'visit_os' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.visit_os) b) t ;

-------生成客户访问模型表

-------------加载数据---

INSERT overwrite TABLE fdm.itcast_fdm_user_app_pageview PARTITION (dt = '2017-01-01')

SELECT

  t.user_id,

  t.log_time,

  HOUR(t.log_time) log_hour,

  t.phone_id,

  t.visit_os,

  t.os_version,

  t.app_name,

  t.app_version,

  t.device_token,

  t.visit_ip,

  t.province,

  t.city

FROM

  bdm.itcast_bdm_user_app_click_log t

WHERE dt = '2017-01-01' ;

------近30天PC端访问最常用的指标

drop table if exists gdm.itcast_gdm_user_visit_temp_01;

create table gdm.itcast_gdm_user_visit_temp_01 as

select

  t.user_id,

  t.type,

  t.con,

  t.cnt,

  t.rn

from

  (select

    b.user_id,

    b.con,

    b.type,

    b.cnt,

    row_number () over (

      distribute by b.user_id,

      b.type sort by b.cnt desc

    ) rn

  from

    (select

      a.user_id,

      a.visit_ip con,

      'visit_ip' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.visit_ip

    union

    all

    select

      a.user_id,

      a.cookie_id con,

      'cookie_id' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.cookie_id

    union

    all

    select

      a.user_id,

      a.browser_name con,

      'browser_name' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.browser_name

    union

    all

    select

      a.user_id,

      a.visit_os con,

      'visit_os' type,

      count(1) cnt

    from

      fdm.itcast_fdm_user_pc_pageview a

    where dt >= date_add('2017-01-01', -29)

    group by a.user_id,

      a.visit_os) b) t ;

-------生成客户访问模型表

INSERT overwrite TABLE gdm.itcast_gdm_user_visit PARTITION (dt = '2017-01-01')

SELECT

  t.user_id,--客户ID

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.in_time

    END

  ) latest_pc_visit_date,--最近一次PC端访问日期

  MAX(

    CASE

      WHEN app.rn_desc = 1

      THEN app.log_time

    END

  ) latest_app_visit_date,--最近一次APP端访问日期

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.session_id

    END

  ) latest_pc_visit_session,--最近一次PC端访问的session

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.cookie_id

    END

  ) latest_pc_cookies,--最近一次PC端访问的cookies

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.pv

    END) latest_pc_pv,--最近一次PC端访问的PV

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.browser_name

    END

  ) latest_pc_browser_name,--最近一次PC端访问使用的游览器

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      THEN pc.visit_os

    END

  ) latest_pc_visit_os,--最近一次PC端访问使用的操作系统

  MAX(

    CASE

      WHEN app.rn_desc = 1

      THEN app.app_name

    END

  ) latest_app_name,--最近一次APP端访问app名称

  MAX(

    CASE

      WHEN app.rn_desc = 1

      THEN app.visit_os

    END

  ) latest_app_visit_os,--最近一次APP端访问使用的操作系统

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time >= app.log_time

      THEN pc.visit_ip

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time < app.log_time

      THEN app.visit_ip  

    END

  ) latest_visit_ip,--最近一次访问IP(不分APP与PC)

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time >= app.log_time

      THEN pc.city

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time < app.log_time

      THEN app.city

    END

  ) latest_city,--最近一次访问城市(不分APP与PC)

  MAX(

    CASE

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time >= app.log_time

      THEN pc.province

      WHEN pc.rn_desc = 1

      AND app.rn_desc = 1

      AND pc.in_time < app.log_time

      THEN app.province

    END

  ) latest_province,--最近一次访问省份(不分APP与PC)

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.in_time

    END

  ) first_pc_visit_date,--第一次PC端访问日期

  MAX(

    CASE

      WHEN app.rn_asc = 1

      THEN app.log_time

    END

  ) first_app_visit_date,--第一次APP端访问日期

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.session_id

    END

  ) first_pc_visit_session,--第一次PC端访问的session

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.cookie_id

    END

  ) first_pc_cookies,--第一次PC端访问的cookies

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.pv

    END) first_pc_pv,--第一次PC端访问的PV

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.browser_name

    END

  ) first_pc_browser_name,--第一次PC端访问使用的游览器

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      THEN pc.visit_os

    END

  ) first_pc_visit_os,--第一次PC端访问使用的操作系统

  MAX(

    CASE

      WHEN app.rn_asc = 1

      THEN app.app_name

    END

  ) first_app_name,--第一次APP端访问app名称

  MAX(

    CASE

      WHEN app.rn_asc = 1

      THEN app.visit_os

    END

  ) first_app_visit_os,--第一次APP端访问使用的操作系统

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time <= app.log_time

      THEN pc.visit_ip

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time > app.log_time

      THEN app.visit_ip

    END

  ) first_visit_ip,--第一次访问IP(不分APP与PC)

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time <= app.log_time

      THEN pc.city

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time > app.log_time

      THEN app.city

    END

  ) first_city,--第一次访问城市(不分APP与PC)

  MAX(

    CASE

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time <= app.log_time

      THEN pc.province

      WHEN pc.rn_asc = 1

      AND app.rn_asc = 1

      AND pc.in_time > app.log_time

      THEN app.province

    END

  ) first_province,--第一次访问省份(不分APP与PC)

  SUM(

    CASE

      WHEN app.dat_7 = 1

      THEN 1

    END) day7_app_cnt,--近7天APP端访问次数

  SUM(

    CASE

      WHEN app.dat_15 = 1

      THEN 1

    END) day15_app_cnt,--近15天APP端访问次数

  SUM(

    CASE

      WHEN app.dat_30 = 1

      THEN 1

    END) month1_app_cnt,--近30天APP端访问次数

  SUM(

    CASE

      WHEN app.dat_60 = 1

      THEN 1

    END) month2_app_cnt,--近60天APP端访问次数

  SUM(

    CASE

      WHEN app.dat_90 = 1

      THEN 1

    END) month3_app_cnt,--近90天APP端访问次数

  COUNT(

    CASE

      WHEN pc.dat_7 = 1

      THEN pc.session_id

    END

  ) day7_pc_cnt,--近7天PC端访问次数

  COUNT(

    CASE

      WHEN pc.dat_15 = 1

      THEN pc.session_id

    END

  ) day15_pc_cnt,--近15天PC端访问次数

  COUNT(

    CASE

      WHEN pc.dat_30 = 1

      THEN pc.session_id

    END

  ) month1_pc_cnt,--近30天PC端访问次数

  COUNT(

    CASE

      WHEN pc.dat_60 = 1

      THEN pc.session_id

    END

  ) month2_pc_cnt,--近60天PC端访问次数

  COUNT(

    CASE

      WHEN pc.dat_90 = 1

      THEN pc.session_id

    END

  ) month3_pc_cnt,--近90天PC端访问次数

  COUNT(DISTINCT substr(pc.in_time,0,10)) month1_pc_days,--近30天PC端访问天数

  SUM(

    CASE

      WHEN pc.dat_30 = 1

      THEN pc.pv

    END) month1_pc_pv,--近30天PC端访问PV

  SUM(

    CASE

      WHEN pc.dat_30 = 1

      THEN pc.pv

    END) / COUNT(DISTINCT substr(pc.in_time,0,10)) month1_pc_avg_pv,--近30天PC端访问平均PV

  MAX(b.month1_pc_diff_ip_cnt),--近30天PC端访问不同ip数

  MAX(b.month1_pc_diff_cookie_cnt),--近30天PC端访问不同的cookie数

  MAX(b.month1_pc_common_ip),--近30天PC端访问最常用ip

  MAX(b.month1_pc_common_cookie),--近30天PC端访问最常用的cookie

  MAX(b.month1_pc_common_browser_name),--近30天PC端访问最常用游览器

  MAX(b.month1_pc_common_os),--近30天PC端访问最常用的操作系统

  COUNT(

    CASE

      WHEN pc.visit_hour >= 0

      AND pc.visit_hour <= 5

      THEN pc.session_id

    END

  ) month1_hour025_cnt,--近30天PC端0-5点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 6

      AND pc.visit_hour <= 7

      THEN pc.session_id

    END

  ) month1_hour627_cnt,--近30天PC端6-7点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 8

      AND pc.visit_hour <= 9

      THEN pc.session_id

    END

  ) month1_hour829_cnt,--近30天PC端8-9点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 10

      AND pc.visit_hour <= 12

      THEN pc.session_id

    END

  ) month1_hour10212_cnt,--近30天PC端10-12点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 13

      AND pc.visit_hour <= 14

      THEN pc.session_id

    END

  ) month1_hour13214_cnt,--近30天PC端13-14点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 15

      AND pc.visit_hour <= 17

      THEN pc.session_id

    END

  ) month1_hour15217_cnt,--近30天PC端15-17点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 18

      AND pc.visit_hour <= 19

      THEN pc.session_id

    END

  ) month1_hour18219_cnt,--近30天PC端18-19点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 20

      AND pc.visit_hour <= 21

      THEN pc.session_id

    END

  ) month1_hour20221_cnt,--近30天PC端20-21点访问次数

  COUNT(

    CASE

      WHEN pc.visit_hour >= 22

      AND pc.visit_hour <= 23

      THEN pc.session_id

    END

  ) month1_hour22223_cnt,--近30天PC端22-23点访问次数

  FROM_UNIXTIME(UNIX_TIMESTAMP()) dw_date

FROM

  (SELECT

    user_id

  FROM

    fdm.itcast_fdm_user_wide

  WHERE dt = '2017-01-01') t

  LEFT JOIN

    (SELECT

      a.*,

      (

        CASE

          WHEN in_time >= DATE_SUB('2017-01-01', 6)

          AND in_time <= '2017-01-01'

          THEN 1

        END

      ) dat_7,

      (

        CASE

          WHEN in_time >= DATE_SUB('2017-01-01', 14)

          AND in_time <= '2017-01-01'

          THEN 1

        END

      ) dat_15,

      (

        CASE

          WHEN in_time >= DATE_SUB('2017-01-01', 29)

          AND in_time <= '2017-01-01'

          THEN 1

        END

      ) dat_30,

      (

        CASE

          WHEN in_time >= DATE_SUB('2017-01-01', 59)

          AND in_time <= '2017-01-01'

          THEN 1

        END

      ) dat_60,

      (

        CASE

          WHEN in_time >= DATE_SUB('2017-01-01', 89)

          AND in_time <= '2017-01-01'

          THEN 1

        END

      ) dat_90,

      hour(in_time) visit_hour,

      row_number () over (

        distribute BY a.user_id sort BY a.in_time DESC

      ) rn_desc,

      row_number () over (

        distribute BY a.user_id sort BY a.in_time ASC

      ) rn_asc

    FROM

      fdm.itcast_fdm_user_pc_pageview a

    WHERE in_time >= DATE_ADD('2017-01-01', -89)

      AND in_time <= '2017-01-01') pc

    ON (t.user_id = pc.user_id)

  LEFT JOIN

    (SELECT

      user_id,

      sum(

        CASE

          WHEN TYPE = 'visit_ip'

          THEN cnt

        END

      ) month1_pc_diff_ip_cnt,

      MAX(

        CASE

          WHEN TYPE = 'visit_ip' and rn= 1

          THEN con

        END

      ) month1_pc_common_ip,

      sum(

        CASE

          WHEN TYPE = 'cookie_id'

          THEN cnt

        END

      ) month1_pc_diff_cookie_cnt,

      MAX(

        CASE

          WHEN TYPE = 'cookie_id' and rn = 1

          THEN con

        END

      ) month1_pc_common_cookie,

      MAX(

        CASE

          WHEN TYPE = 'browser_name' and rn = 1

          THEN con

        END

      ) month1_pc_common_browser_name,

      MAX(

        CASE

          WHEN TYPE = 'visit_os' and rn = 1

          THEN con

        END

      ) month1_pc_common_os

    FROM

      gdm.itcast_gdm_user_visit_temp_01

    GROUP BY user_id) b

    ON (t.user_id = b.user_id)

  LEFT JOIN

    (SELECT

      a.*,

      (

        CASE

          WHEN log_time >= DATE_SUB('2017-01-01', 6)

          AND log_time <= '2017-01-01'

          THEN 1

        END

      ) dat_7,

      (

        CASE

          WHEN log_time >= DATE_SUB('2017-01-01', 14)

          AND log_time <= '2017-01-01'

          THEN 1

        END

      ) dat_15,

      (

        CASE

          WHEN log_time >= DATE_SUB('2017-01-01', 29)

          AND log_time <= '2017-01-01'

          THEN 1

        END

      ) dat_30,

      (

        CASE

          WHEN log_time >= DATE_SUB('2017-01-01', 59)

          AND log_time <= '2017-01-01'