1.阿里云 精准营销实践odpscmd精准营销数据处理

1.创建数据表

--创建用户信息表
CREATE TABLE yhxx (
yhbm STRING COMMENT '用户编码',
xb BIGINT COMMENT '性别',
nl BIGINT COMMENT '年龄',
xfdj BIGINT COMMENT '消费等级（0-3）',
smdr BIGINT COMMENT '数码达人',
llns BIGINT COMMENT '靓丽女士',
xsns BIGINT COMMENT '潇洒男士',
msyz BIGINT COMMENT '美食一族',
zgllcs BIGINT COMMENT '总共浏览次数',
zjllcs BIGINT COMMENT '近6个月浏览次数',
zgtjcs BIGINT COMMENT '总共推荐次数',
zgtjcgcs BIGINT COMMENT '总共推荐成功次数',
zjtjcs BIGINT COMMENT '近6个月推荐次数',
zjtjcgcs BIGINT COMMENT '近6个月推荐成功次数',
zggwcs BIGINT COMMENT '总共购物次数',
zjgwcs BIGINT COMMENT '近6个月购物次数',
zgplcs BIGINT COMMENT '总共评论次数',
zjplcs BIGINT COMMENT '近6个月评论次数'
)
COMMENT '用户信息表'
LIFECYCLE 90;

3.创建推荐分析表

--创建推荐分析表
CREATE TABLE tjfx (
tjbm STRING COMMENT '推荐编码',
tjhdbm STRING COMMENT '推荐活动编码',
fstjsj STRING COMMENT '发送推荐时间',
spbm STRING COMMENT '商品编码',
tjzt BIGINT COMMENT '推荐状态',
czsj STRING COMMENT '操作时间',
yhbm STRING COMMENT '用户编码'
)
COMMENT '推荐分析表'
LIFECYCLE 90;

3.创建用户行为表

--用户行为表
CREATE TABLE yhxw (
czsj STRING COMMENT '操作时间',
yhbm STRING COMMENT '用户编码',
xwbm STRING COMMENT '行为编码',
xwdxbm STRING COMMENT '行为对象编码'
)
COMMENT '用户行为表'
LIFECYCLE 90;

4.创建待预测用户信息表

--待预测用户信息表
CREATE TABLE dycyhxx (
yhbm STRING COMMENT '用户编码',
xb BIGINT COMMENT '性别',
nl BIGINT COMMENT '年龄',
xfdj BIGINT COMMENT '消费等级（0-3）',
smdr BIGINT COMMENT '数码达人',
llns BIGINT COMMENT '靓丽女士',
xsns BIGINT COMMENT '潇洒男士',
msyz BIGINT COMMENT '美食一族',
zgllcs BIGINT COMMENT '总共浏览次数',
zjllcs BIGINT COMMENT '近6个月浏览次数',
zgtjcs BIGINT COMMENT '总共推荐次数',
zgtjcgcs BIGINT COMMENT '总共推荐成功次数',
zjtjcs BIGINT COMMENT '近6个月推荐次数',
zjtjcgcs BIGINT COMMENT '近6个月推荐成功次数',
zggwcs BIGINT COMMENT '总共购物次数',
zjgwcs BIGINT COMMENT '近6个月购物次数',
zgplcs BIGINT COMMENT '总共评论次数',
zjplcs BIGINT COMMENT '近6个月评论次数'
)
COMMENT '待预测用户信息表'
LIFECYCLE 90;

5.创建用户信息更新表

--用户信息更新表
CREATE TABLE yhxxgx (
yhbm STRING COMMENT '用户编码',
xb BIGINT COMMENT '性别',
nl BIGINT COMMENT '年龄',
xfdj BIGINT COMMENT '消费等级（0-3）',
smdr BIGINT COMMENT '数码达人',
llns BIGINT COMMENT '靓丽女士',
xsns BIGINT COMMENT '潇洒男士',
msyz BIGINT COMMENT '美食一族',
zgllcs BIGINT COMMENT '总共浏览次数',
zjllcs BIGINT COMMENT '近6个月浏览次数',
zgtjcs BIGINT COMMENT '总共推荐次数',
zgtjcgcs BIGINT COMMENT '总共推荐成功次数',
zjtjcs BIGINT COMMENT '近6个月推荐次数',
zjtjcgcs BIGINT COMMENT '近6个月推荐成功次数',
zggwcs BIGINT COMMENT '总共购物次数',
zjgwcs BIGINT COMMENT '近6个月购物次数',
zgplcs BIGINT COMMENT '总共评论次数',
zjplcs BIGINT COMMENT '近6个月评论次数'
)
COMMENT '用户信息更新表'
LIFECYCLE 90;

6.向用户信息更新表中插入数据

--向用户信息更新表中插入数据
--更新用户信息
INSERT OVERWRITE TABLE yhxxgx
SELECT yh.yhbm,yh.xb,yh.nl,yh.xfdj,yh.smdr,yh.llns,yh.xsns
,CASE WHEN (yh.zjllcs + zj.llcs) > 10 THEN 1 ELSE 0 END msyz
,(yh.zgllcs + zj.llcs) zgllcs,(yh.zjllcs+zj.llcs)zjllcs
,yh.zgtjcs,(yh.zgtjcgcs+zj.dktjcs)zgtjcgcs
,yh.zjtjcs,(yh.zjtjcgcs+zj.dktjcs)zjtjcgcs
,(yh.zggwcs+zj.gmcs)zggwcs,(yh.zggwcs+zj.gmcs)zggwcs,(yh.zgplcs+
zj.plcs)zgplcs,(yh.zjplcs+zj.plcs)zjplcs
FROM yhxx yh
LEFT OUTER JOIN(
SELECT yh.yhbm
,CASE WHEN ll.llcs IS NULL THEN 0 ELSE ll.llcs END llcs
,CASE WHEN dktj.dktjcs IS NULL THEN 0 ELSE dktj.dktjcs END dktjcs
,CASE WHEN gm.gmcs IS NULL THEN 0 ELSE gm.gmcs END gmcs
,CASE WHEN pl.plcs IS NULL THEN 0 ELSE pl.plcs END plcs 
FROM yhxx yh 
LEFT OUTER JOIN(SELECT yhbm,count(1) llcs FROM yhxw WHERE xwbm='llsp'GROUP BY yhbm) ll ON yh.yhbm=ll.yhbm --浏览次数
LEFT OUTER JOIN(SELECT yhbm,count(1)dktjcs FROM yhxw WHERE xwbm='dktj'GROUP BY yhbm) dktj ON yh.yhbm=dktj.yhbm--打开推荐次数
LEFT OUTER JOIN(SELECT yhbm,count(1)gmcs FROM yhxw WHERE xwbm='zfhk'GROUP BY yhbm) gm ON yh.yhbm=gm.yhbm--支付次数
LEFT OUTER JOIN(SELECT yhbm,count(1)plcs FROM yhxw WHERE xwbm='plgm'GROUP BY yhbm) pl ON yh.yhbm=pl.yhbm--评论次数
)zj ON yh.yhbm=zj.yhbm;

6.查询导入到用户信息更新表的数据

--查询用户信息更新表数据记录条数
SELECT COUNT(*)FROM yhxxgx;

7.创建推荐分析更新表

--创建推荐分析更新表
CREATE TABLE tjfxgx(
tjbm STRING COMMENT'推荐编码',
tjhdbm STRING COMMENT'推荐活动编码',
fstjsj STRING COMMENT'发送推荐时间',
spbm STRING COMMENT'商品编码',
tjzt BIGINT COMMENT'推荐状态',
czsj STRING COMMENT'操作时间',
yhbm STRING COMMENT'用户编码'
)
COMMENT'推荐分析更新表'
LIFECYCLE 30;

8.向推荐分析更新表中插入数据

--向推荐分析更新表中插入数据
INSERT OVERWRITE TABLE tjfxgx 
SELECT tj.tjbm, tj.tjhdbm, tj.fstjsj, tj.spbm,
CASE WHEN xw.xwbm = 'dktj' THEN 1 ELSE 2 END AS tjzt --推荐状态：根据用户行为
, tj.czsj, tj.yhbm
FROM tjfx tj 
LEFT OUTER JOIN yhxw xw
ON tj.yhbm = xw.yhbm 
AND tj.tjbm = xw.xwdxbm;

9.查询导入到推荐分析更新表的数据

--查询导入到推荐分析更新表的数据
SELECT COUNT(*)FROM tjfxgx;

10.创建用户信息推荐分析宽表

--用户信息推荐更新表
CREATE TABLE yhxxtjgx (
tjbm STRING COMMENT '推荐编码',
tjhdbm STRING COMMENT '推荐活动编码',
fstjsj STRING COMMENT '发送推荐时间',
spbm STRING COMMENT '商品编码',
tjzt BIGINT COMMENT '推荐状态',
czsj STRING COMMENT '操作时间',
yhbm STRING COMMENT '用户编码',
xb BIGINT COMMENT '性别',
nl BIGINT COMMENT '年龄',
xfdj BIGINT COMMENT '消费等级（0-3）',
smdr BIGINT COMMENT '数码达人',
llns BIGINT COMMENT '靓丽女士',
xsns BIGINT COMMENT '潇洒男士',
msyz BIGINT COMMENT '美食一族',
zgllcs BIGINT COMMENT '总共浏览次数',
zjllcs BIGINT COMMENT '近6个月浏览次数',
zgtjcs BIGINT COMMENT '总共推荐次数',
zgtjcgcs BIGINT COMMENT '总共推荐成功次数',
zjtjcs BIGINT COMMENT '近6个月推荐次数',
zjtjcgcs BIGINT COMMENT '近6个月推荐成功次数',
zggwcs BIGINT COMMENT '总共购物次数',
zjgwcs BIGINT COMMENT '近6个月购物次数',
zgplcs BIGINT COMMENT '总共评论次数',
zjplcs BIGINT COMMENT '近6个月评论次数'
)
COMMENT '用户信息推荐更新表'
LIFECYCLE 90;

11.向用户信息推荐分析宽表中插入数据

--向用户信息推荐分析宽表中插入数据
INSERT OVERWRITE TABLE yhxxtjgx
SELECT tg.tjbm, tg.tjhdbm, tg.fstjsj, tg.spbm, tg.tjzt, tg.czsj, 
tg.yhbm, yg.xb, yg.nl, yg.xfdj, yg.smdr, yg.llns, yg.xsns, yg.msyz, 
yg.zgllcs, yg.zjllcs, yg.zgtjcs, yg.zgtjcgcs, yg.zjtjcs, yg.zjtjcgcs, 
yg.zggwcs, yg.zjgwcs, yg.zgplcs, yg.zjplcs
FROM tjfxgx tg
LEFT OUTER JOIN yhxxgx yg
ON tg.yhbm = yg.yhbm;

12.查询导入到用户信息推荐分析宽表的数据

--统计推荐状态
SELECT tjzt,COUNT(*) FROM yhxxtjgx GROUP BY tjzt;