大数据技术暑期实习八___构建用户画像（SQL语句打标签）

一、用户画像的介绍

用户画像的核心工作是为用户打标签，打标签的重要目的之一是为了让人能 够理解并且方便计算机处理，如，可以做分类统计：喜欢 iphone 的用户有多少？ 喜欢 iphone 的人群中，男、女比例是多少？也可以做数据挖掘工作：利用聚类 算法分析，喜欢 iphone 的人年龄段分布情况。

二、构建用户画像

　　2.1 标签的命名　　　

　　　　标签主题：用于刻画属于那种类型的标签，如用户属性、用户行为、用户消 费、风险控 制等多种类型，可用 A、B、C、D 等字母表示各标签主题；　　　　

　　　　标签类型：标签类型可划为分类型和统计型这两种类型，其中分类型用于刻画用户属于哪种类型，如是男是女、是否是会员、是否已流失等标签，统计型 标签用于刻画统计用 户的某些行为次数，如收藏次数、近 30 日购买次数等标签，这类标签都需要对应一个 用户相应行为的权重次数；　　　　

　　　　开发方式：开发方式可分为统计型开发和算法型开发两大开发方式。其中统计型开发可 直接从数据仓库中各主题表建模加工而成，算法型开发需要对数据做机器学习的算法处 理得到相应的标签；

　　　　是否互斥标签：对应同一级类目下（如一级标签、二级标签），各标签之间的关系是否 为互斥，可将标签划分为互斥关系和非互斥关系。例如对于男、女标签就是互斥关系， 同一个用户不是被打上男性标签就是女性标签，对于高活跃、中活跃、低活跃标签也是 互斥关系；

　　　　用户维度：用于刻画该标签是打在用户唯一标识（userid）上，还是打在用 户使用的设 备（cookieid）上或其他的唯一标识。可用 U、 C 等字母分别标识 userid 和 cookieid 维度。

　　　　示例：对于用户是男是女这个标签，标签主题是用户属性，标签类型属于分 类型，开发 方式为统计型，为互斥关系，用户维度为 userid。这样给男性用户打 上“A111U001_001”，女性用户打上标签“A111U001_002”，其中“A111U” 为上面介绍的命名 方式，“001”为一 级标签的 id，后面对于用户属性维度的 其他一级标签可用“002”、“003” 等方式追加 命名，“_”后面的“001” 和“002”为该一级标签下的标签明细，如果是划分高、中、低活 跃用户的， 对应一级标签下的明细可划分为“001”、“002”、“003”。

　　　注：本案例中标签主题以用户属性和用户行为；开发方式以统计性开发为主；用户维度使用userid为唯一标识。

　　2.2 sql语句

create table profile_tag_user_gender
(
user_id string comment '用户编码',
tag_id string comment '标签 id',
tag_name string comment '用户性别',
tag_type string comment '用户属性'
)
comment '用户性别标签表';

insert into profile_tag_user_gender (user_id,tag_id,tag_name,tag_type)
select  user_id,
case when gender='男' then 'A111U001_001'
else 'A111U001_002'
end,
gender,'用户性别'
from user_info;
----------------
create table profile_tag_user_age_region
(
user_id string comment '用户编码',
tag_id string comment '标签 id',
tag_name string comment '用户年龄段',
tag_type string comment '用户属性'
)
comment '用户年龄段标签表';

insert into profile_tag_user_age_region (user_id,tag_id,tag_name,tag_type)
select  user_id,
case   when age_region=1 then 'A111U002_001'
when age_region=2 then 'A111U002_002'
when age_region=3 then 'A111U002_003'
when age_region=4 then 'A111U002_004'
when age_region=5 then 'A111U002_005'
when age_region=6 then 'A111U002_006'
else 'A111U002_008'
end,
age_region_alias,'用户年龄段'
from user_info;
------------------
create table profile_tag_user_grade
(
user_id string comment '用户编码',
tag_id string comment '标签 id',
tag_name string comment '用户等级',
tag_type string comment '用户属性'
)
comment '用户会员标签标签表';

insert into profile_tag_user_grade (user_id,tag_id,tag_name,tag_type)
select  user_id,
case   when user_grade='铜牌会员' then 'A111U003_001'
when user_grade='银牌会员' then 'A111U003_002'
when user_grade='金牌会员' then 'A111U003_003'
when user_grade='钻石会员' then 'A111U003_004'
when user_grade='PLUS会员' then 'A111U003_005'
end,
 age_region_alias,'用户等级'
from user_info;
----------------
create table person_user_tag_action
(
user_id string comment '用户编码',
tag_id string comment '标签 id',
tag_name string comment '标签名称',
tag_type string comment '用户行为',
action_count int comment '行为次数'
)
comment '用户行为标签表';

insert into person_user_tag_action (user_id,tag_id,tag_name,tag_type,action_count)
select  user_id,
case   when user_action='0' then 'A111U004_001'
when user_action='1' then 'A111U004_002'
when user_action='2' then 'A111U004_003'
when user_action='3' then 'A111U004_004'
end,
case   when user_action='0' then '加入购物车'
when user_action='1' then '点击'
when user_action='2' then '购买'
when user_action='3' then '删除'
end,
'用户行为',
count(*) from action group by user_id,user_action;
---------------

　　2.3 效果