大数据精准营销数据分析处理（一）

精准营销数据分析处理

1.创建基础数据表

（1）创建用户信息表（yhxx）

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

（2）创建用户行为表（yhxw）
CREATE TABLE if not exists yhxw (
czsj STRING COMMENT ‘操作时间’,
yhbm STRING COMMENT ‘用户编码’,
xwbm STRING COMMENT ‘行为编码’,
xwdxbm STRING COMMENT ‘行为对象编码’)
COMMENT '用户行为表’LIFECYCLE 90;

（3）创建推荐分析表（tjfx）
CREATE TABLE if not exists 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;

（4）创建待预测用户信息表（dycyhxx）
CREATE TABLE if not exists 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;

2.导入基础数据
在数据开发中点击导入通过表名导入数据，

3.更新用户信息

有些表的字段及信息是通过其他表中的数据来确定的如更新用户信息表
setproject odps.sql.type.system.odps2=false;
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.zjgwcs+zj.gmcs)zjgwcs
,(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;

执行后可在该表中查出数据如下