用SparkSQL构建用户画像

二、前言

大数据时代已经到来，企业迫切希望从已经积累的数据中分析出有价值的东西，而用户行为的分析尤为重要。

利用大数据来分析用户的行为与消费习惯，可以预测商品的发展的趋势，提高产品质量，同时提高用户满意度。

三、初识用户画像

右边是一个人的基本属性，通过一个人的基本属性我们可以了解到这个人的基本信息，左边上图是通过消费购物信息来描述一个人特征，左边下图是通过交际圈信息来描述一个人特征，通过不同的维度，去描述一个人，认识一个人，了解一个人。这就是我们今天所要讲到的用户画像。

用户画像：也叫用户信息标签化、客户标签；根据用户社会属性、生活习惯和消费行为等信息而抽象出的一个标签化的用户模型。从电商的角度看，根据你在电商网站上所填的信息和你的行为，可以用一些标签把你描绘出来，描述你的标签就是用户画像。构建用户画像的核心工作即是给用户贴“标签”，而标签是通过对用户信息分析而来的高度精炼的特征标识。

四、构建电商用户画像的重大意义

罗振宇在《时间的朋友》跨年演讲举了这样一个例子：当一个坏商家掌握了你的购买数据，他就可以根据你平时购买商品的偏好来决定是给你发正品还是假货以此来提高利润，且不说是否存在这种情况，但这也说明了利用用户画像可以做到“精准营销”，当然这是极其错误的用法。

其作用大体不离以下几个方面：

u 1、精准营销，分析产品潜在用户，针对特定群体利用短信邮件等方式进行营销；

u 2、用户统计，比如中国大学购买书籍人数 TOP10；

u 3、数据挖掘，构建智能推荐系统，利用关联规则计算，喜欢红酒的人通常喜欢什么运动品牌，利用聚类算法分析，喜欢红酒的人年龄段分布情况；

u 4、进行效果评估，完善产品运营，提升服务质量，其实这也就相当于市场调研、用户调研，迅速下定位服务群体，提供高水平的服务；

u 5、对服务或产品进行私人定制，即个性化的服务某类群体甚至每一位用户（个人认为这是目前的发展趋势，未来的消费主流）。比如，某公司想推出一款面向5－10岁儿童的玩具，通过用户画像进行分析，发现形象＝“喜羊羊”、价格区间＝“中等”的偏好比重最大，那么就给新产品提供了非常客观有效的决策依据。

u 6、业务经营分析以及竞争分析，影响企业发展战略

五、如何构建电商用户画像

5.1 构建电商用户画像技术和流程

构建一个用户画像，包括数据源端数据收集、数据预处理、行为建模、构建用户画像

有些标签是可以直接获取到的，有些标签需要通过数据挖掘分析到！

5.2 源数据分析

用户数据分为2类：动态信息数据、静态信息数据

静态信息数据来源：

用户填写的个人资料，或者由此通过一定的算法，计算出来的数据
如果有不确定的，可以建立模型来判断，比如用户的性别注册没有填写，可以建立模型，根据用户的行为来判断用户性别是什么，或者它的概率

动态信息数据来源：

用户行为产生的数据：注册、游览、点击、购买、签收、评价、收藏等等。
用户比较重要的行为数据：游览商品，收藏商品、加入购物车、关注商品

根据这些行为特性可以计算出：用户注册时间、首单时间、潮妈族、纠结商品、最大消费、订单数量、退货数量、败家指数、品牌偏好等等。

5.3 目标分析

用户画像的目标是通过分析用户行为，最终为每个用户打上标签，以及该标签的权重。

如，红酒 0.8、李宁 0.6。

标签：表现了内容，用户对该内容有兴趣、偏好、需求等等。

权重：表现了指数，用户的兴趣、偏好指数，也可能表现用户的需求度，可以简单的理解为可信度，概率。

5.4 用户画像建模

5.4.1 用户基本属性表

根据用户所填写的属性标签和推算出来的标签。用于了解用户的人口属性的基本情况和按不同属性维度统计。

作用：按人口属性营销、比如营销80后，对金牛座的优惠，生日营销。

主要数据来源：用户表、用户调查表、孕妇模型表、马甲模型表。

用户表：记录用户最基本的属性特性。

用户调查表：补充用户的其他基本信息。

用户所填写的基本信息：用户ID、用户名、密码、性别、手机号、邮箱、年龄、户籍省份、身份证编号、注册时间、收货地址等

用户所填信息计算得到的指标：

生日、星座、城市等级、手机前几位、手机运营商、邮件运营商

用户调查表得到：学历、收入、职业、婚姻、是否有小孩、是否有车有房、使用手机品牌。

根据算法得到:

身高、体重、性别模型、孩子性别概率、潜在汽车用户概率、是否孕妇、孩子年龄概率、手机品牌、更换手机频率、是否有小孩，是否有车，使用手机档次，疑似马甲标准、疑似马甲账号数、用户忠诚度、用户购物类型。

模型算法---性别模型

用户自己也填写了性别，但仍然要用算法算一次性别

用户性别

1男 0女 -1未识别

1、商品性别得分

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

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

孩子性别

0 仅有男孩

1仅有女孩

2男女都有

3无法识别

1、选择男孩女孩商品

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

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

性别验证方法

随机抽样几千条数据让客户打电话确认。

与用户自己填的性别做对比，确认百分比。

模型算法---用户汽车模型

用户是否有车

1有 0 没有

-1 未识别

根据用户购买车相关产品

判断用户是否有车

潜在汽车用户

1有 0 没有

-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未识别	用户购买服装鞋帽等用户判断

模型算法---用户马甲标志模型

马甲是指一个用户注册多个账号
多次访问地址相同的用户账号是同一个人所有
同一台手机登陆多次的用户是同一个人所有
收货手机号相同的账号同一个人所有

模型算法---手机相关标签模型

对于手机营销参考意义比较大
使用手机品牌: 最常用手机直接得到
使用手机品牌档次：根据档次维表
使用多少种不同的手机：手机登陆情况
更换手机频率（月份):按时间段看手机登陆情况

5.4.2 客户消费订单表

根据客户消费的情况提取的客户标签，用于了解用户的消费总体情况，

最终的目的根据用户消费习惯与消费能力做营销。

主要数据来源：订单表、退货表、用户表、购物车表

订单表可以得到相关标签：

第一次消费时间、

最近一次消费时间、

首单距今时间、

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

最小消费金额、

最大消费金额、

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

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

累计使用代金券金额、

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

客单价（含退拒）、

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

常用收货地址、

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

退货表可以得到相关标签：

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

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

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

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

退货商品数量、

退货商品金额、

拒收商品数量、

拒收商品金额、

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

购物车表可以得到相关标签：

最近30天购物车次数、

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

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

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

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

订单表和用户表可以得到相关标签：

学校下单总数、

单位下单总数、

家里下单总数、

上午下单总数、

下午下单总数、

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

5.4.3 客户购买类目表

根据客户购买类目的情况提取客户标签，用于了解类目的购买人群情况和针对某一类目的营销等。

主要数据来源：订单表、购物车表、类目维表

类目维表可以得到相关标签：

一级分类ID、

一级分类名称、

二级分类ID、

二级分类名称、

三级分类ID、

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

电商的三级类目：

京东商城：

淘宝：

订单表和类目维表可以得到相关标签：

近30天购买类目次数、

近30天购买类目金额、

近90天购买类目次数、

近90天购买类目金额、

近180天购买类目次数、

近180天购买类目金额、

累计购买类目次数、

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

最近一次购买类目时间、

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

购物车表和类目维表可以得到相关标签：

近30天购物车类目次数、

近30天购物车类目金额、

近90天购物车类目次数、

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

5.4.4 用户访问信息表

根据客户访问的情况提取相关客户标签。

用于了解用户的访问总体情况，方便根据客户游览习惯做营销

主要数据来源：点击流日志行为表（PC/APP端）

点击流日志行为表可以得到相关标签:

最近一次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点访问的次数----分析用户喜欢在哪个时间上网访问。

六、电商用户画像环境搭建

众所周知，Hive的执行任务是将hql语句转化为MapReduce来计算的，Hive的整体解决方案很不错，但是从查询提交到结果返回需要相当长的时间，查询耗时太长。这个主要原因就是由于Hive原生是基于MapReduce的，那么如果我们不生成MapReduce Job，而是生成Spark Job，就可以充分利用Spark的快速执行能力来缩短HiveHQL的响应时间。

本项目采用SparkSql与hive进行整合(spark on hive)，通过SparkSql读取hive中表的元数据，把HiveHQL底层采用MapReduce来处理任务,导致性能慢的特点，改为更加强大的Spark引擎来进行相应的分析处理，快速的为用户打上标签构建用户画像。

6.1 环境准备

1、搭建hadoop集群
2、安装hive构建数据仓库
3、安装spark集群
4、sparksql 整合hive

6.2 sparksql整合hive

　 Spark SQL主要目的是使得用户可以在Spark上使用SQL，其数据源既可以是RDD，也可以是外部的数据源（比如文本、Hive、Json等）。Spark SQL的其中一个分支就是Spark on Hive，也就是使用Hive中HQL的解析、逻辑执行计划翻译、执行计划优化等逻辑，可以近似认为仅将物理执行计划从MR作业替换成了Spark作业。SparkSql整合hive就是获取hive表中的元数据信息，然后通过SparkSql来操作数据。

整合步骤：

①　需要将hive-site.xml文件拷贝到Spark的conf目录下，这样就可以通过这个配置文件找到Hive的元数据以及数据存放位置。

②　如果Hive的元数据存放在Mysql中，我们还需要准备好Mysql相关驱动，比如：mysql-connector-java-5.1.35.jar

6.3 测试sparksql整合hive是否成功

先启动hadoop集群，在启动spark集群，确保启动成功之后执行命令：

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

指明master地址、每一个executor的内存大小、一共所需要的核数、

mysql数据库连接驱动。

执行成功后的界面：进入到spark-sql 客户端命令行界面

接下来就可以通过sql语句来操作数据库表：

查看当前有哪些数据库 ---show databases;

看到以上结果，说明sparksql整合hive成功！

日志太多，我们可以修改spark的日志输出级别(conf/log4j.properties)

前方高能：

在spark2.0版本后由于出现了sparkSession，在初始化sqlContext的时候，会设置默认的spark.sql.warehouse.dir=spark-warehouse,

此时将hive与sparksql整合完成之后，在通过spark-sql脚本启动的时候，还是会在哪里启动spark-sql脚本，就会在当前目录下创建一个spark.sql.warehouse.dir为spark-warehouse的目录，存放由spark-sql创建数据库和创建表的数据信息，与之前hive的数据信息不是放在同一个路径下（可以互相访问）。但是此时spark-sql中表的数据在本地，不利于操作，也不安全。

所有在启动的时候需要加上这样一个参数：

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

保证spark-sql启动时不在产生新的存放数据的目录，sparksql与hive最终使用的是hive同一存放数据的目录。

如果使用的是spark2.0之前的版本，由于没有sparkSession，不会有spark.sql.warehouse.dir配置项，不会出现上述问题。

最后的执行脚本；

spark-sql \

--master spark://node1:7077 \

--executor-memory 1g \

--total-executor-cores 2 \

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

七、电商用户画像数据仓库建立

7.1 数据仓库准备工作

为什么要对数据仓库分层？星型模型雪花模型

User----->web界面展示指标表

l 用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；

l 如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大

l 通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。

数据仓库标准上可以分为四层：ODS（临时存储层）、PDW（数据仓库层）、MID（数据集市层）、APP（应用层）

ODS层：

为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。一般来说ODS层的数据和源系统的数据是同构的，主要目的是简化后续数据加工处理的工作。从数据粒度上来说ODS层的数据粒度是最细的。ODS层的表通常包括两类，一个用于存储当前需要加载的数据，一个用于存储处理完后的历史数据。历史数据一般保存3-6个月后需要清除，以节省空间。但不同的项目要区别对待，如果源系统的数据量不大，可以保留更长的时间，甚至全量保存；

PDW层：

为数据仓库层，PDW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。这一层的数据一般是遵循数据库第三范式的，其数据粒度通常和ODS的粒度相同。在PDW层会保存BI系统中所有的历史数据，例如保存10年的数据

MID层：

为数据集市层，这层数据是面向主题来组织数据的，通常是星形或雪花结构的数据。从数据粒度来说，这层的数据是轻度汇总级的数据，已经不存在明细数据了。从数据的时间跨度来说，通常是PDW层的一部分，主要的目的是为了满足用户分析的需求，而从分析的角度来说，用户通常只需要分析近几年（如近三年的数据）的即可。从数据的广度来说，仍然覆盖了所有业务数据。

APP层：

为应用层，这层数据是完全为了满足具体的分析需求而构建的数据，也是星形或雪花结构的数据。从数据粒度来说是高度汇总的数据。从数据的广度来说，则并不一定会覆盖所有业务数据，而是MID层数据的一个真子集，从某种意义上来说是MID层数据的一个重复。从极端情况来说，可以为每一张报表在APP层构建一个模型来支持，达到以空间换时间的目的数据仓库的标准分层只是一个建议性质的标准，实际实施时需要根据实际情况确定数据仓库的分层，不同类型的数据也可能采取不同的分层方法。

这里我们采用的是京东的数据仓库分层模式，是根据标准的模型演化而来。

数据仓库分层：

BDM：缓冲数据，源数据的直接映像

FDM：基础数据层，数据拉链处理、分区处理

GDM：通用聚合

ADM：高度聚合

先把数据从源数据库中抽取加载到BDM层中，

然后FDM层根据BDM层的数据按天分区

7.2 数据仓库基本表介绍

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

八、电商用户画像开发

8.1用户画像--数据开发的步骤

u 数据开发前置依赖

-需求确定 pv uv topn

-建模确定表结构 create table t1(pv int,uv int,topn string)

-实现方案确定

u 数据开发过程

-表落地

-写sql语句实现业务逻辑

-部署代码

-数据测试

-试运行与上线

在接下来的客户基本属性表开发中演示开发的流程。

8.2 用户画像开发--客户基本属性表

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

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

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}"

8.3 用户画像开发--订单表宽表

--订单宽表模型

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 '\n';

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;

8.4 用户画像开发--客户消费订单表

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

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);

8.5 用户画像开发--客户购买类目表

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

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

END

) dat_30,

(

CASE

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

AND order_date <= '2017-01-01'

THEN

END

) dat_90,

(

CASE

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

AND order_date <= '2017-01-01'

THEN

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) ;

8.6 用户画像开发--客户访问信息表

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

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 '\n';

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 '\n';

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'

THEN 1

END

) dat_60,

(

CASE

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

AND log_time <= '2017-01-01'

THEN 1

END

) dat_90,

row_number () over (

distribute BY a.user_id sort BY a.log_time DESC

) rn_desc,

row_number () over (

distribute BY a.user_id sort BY a.log_time ASC

) rn_asc

FROM

fdm.itcast_fdm_user_app_pageview a

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

AND log_time <= '2017-01-01') app

ON (t.user_id = app.user_id)

GROUP BY t.user_id ;

8.7 用户画像开发--用户宽表汇总模型表

--用户画像宽表模型

create database if not exists adm;

create table if not exists adm.itcast_adm_personas(

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 ,--身高

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, --凌晨下单总数

first_category_id BIGINT, --一级分类ID

first_category_name STRING, --一级分类名称

second_category_id BIGINT, --二分类ID

second_catery_name STRING, --二级分类名称

third_category_id BIGINT, --三级分类ID

third_category_name STRING, --三级分类名称

month1_category_cnt BIGINT, --近30天购物类目次数

month1_category_amt STRING, --近30天购物类目金额

month3_category_cnt BIGINT, --近90天购物类目次数

month3_category_amt STRING, --近90天购物类目金额

month6_category_cnt BIGINT, --近180天购物类目次数

month6_category_amt STRING, --近180天购物类目金额

total_category_cnt BIGINT, --累计购物类目次数

total_category_amt STRING, --累计购物类目次数

month1_cart_category_cnt BIGINT, --近30天购物车类目次数

month3_cart_category_cnt BIGINT, --近90天购物车类目次数

month6_cart_category_cnt BIGINT, --近180天购物车类目次数

total_cart_category_cnt BIGINT, --累计购物车类目次数

last_category_time TIMESTAMP, --最后一次购买类目时间

last_category_ago BIGINT, --最后一次购买类目距今天数

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_pv bigint, --近30天PC端访问PV

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

);

-----加载数据

insert overwrite table adm.itcast_adm_personas

select

a.user_id,

a.user_name,

a.user_sex,

a.user_birthday,

a.user_age,

a.constellation,

a.province,

a.city,

a.city_level,

a.hex_mail,

a.op_mail,

a.hex_phone,

a.fore_phone,

a.op_phone,

a.add_time,

a.login_ip,

a.login_source,

a.request_user,

a.total_mark,

a.used_mark,

a.level_name,

a.blacklist,

a.is_married,

a.education,

a.monthly_money,

a.profession,

a.sex_model,

a.is_pregnant_woman,

a.is_have_children,

a.children_sex_rate,

a.children_age_rate,

a.is_have_car,

a.potential_car_user_rate,

a.phone_brand,

a.phone_brand_level,

a.phone_cnt,

a.change_phone_rate,

a.majia_flag,

a.majie_account_cnt,

a.loyal_model,

a.shopping_type_model,

a.figure_model,

a.stature_model,

b.first_order_time,

b.last_order_time,

b.first_order_ago,

b.last_order_ago,

b.month1_hg_order_cnt,

b.month1_hg_order_amt,

b.month2_hg_order_cnt,

b.month2_hg_order_amt,

b.month3_hg_order_cnt,

b.month3_hg_order_amt,

b.month1_order_cnt,

b.month1_order_amt,

b.month2_order_cnt,

b.month2_order_amt,

b.month3_order_cnt,

b.month3_order_amt,

b.max_order_amt,

b.min_order_amt,

b.total_order_cnt,

b.total_order_amt,

b.user_avg_amt,

b.month3_user_avg_amt,

b.common_address,

b.common_paytype,

b.month1_cart_cnt,

b.month1_cart_goods_cnt,

b.month1_cart_submit_cnt,

b.month1_cart_rate,

b.month1_cart_cancle_cnt,

b.return_cnt,

b.return_amt,

b.reject_cnt,

b.reject_amt,

b.last_return_time,

b.school_order_cnt,

b.company_order_cnt,

b.home_order_cnt,

b.forenoon_order_cnt,

b.afternoon_order_cnt,

b.night_order_cnt,

b.morning_order_cnt,

c.first_category_id,

c.first_category_name,

c.second_category_id,

c.second_catery_name,

c.third_category_id,

c.third_category_name,

c.month1_category_cnt,

c.month1_category_amt,

c.month3_category_cnt,

c.month3_category_amt,

c.month6_category_cnt,

c.month6_category_amt,

c.total_category_cnt,

c.total_category_amt,

c.month1_category_cnt,

c.month3_category_cnt,

c.month6_category_cnt,

c.total_category_cnt,

c.last_category_time,

c.last_category_ago,

d.latest_pc_visit_date,

d.latest_app_visit_date,

d.latest_pc_visit_session,

d.latest_pc_cookies,

d.latest_pc_pv,

d.latest_pc_browser_name,

d.latest_pc_visit_os,

d.latest_app_name,

d.latest_app_visit_os,

d.latest_visit_ip,

d.latest_city,

d.latest_province,

d.first_pc_visit_date,

d.first_app_visit_date,

d.first_pc_visit_session,

d.first_pc_cookies,

d.first_pc_pv,

d.first_pc_browser_name,

d.first_pc_visit_os,

d.first_app_name,

d.first_app_visit_os,

d.first_visit_ip,

d.first_city,

d.first_province,

d.day7_app_cnt,

d.day15_app_cnt,

d.month1_app_cnt,

d.month2_app_cnt,

d.month3_app_cnt,

d.day7_pc_cnt,

d.day15_pc_cnt,

d.month1_pc_cnt,

d.month2_pc_cnt,

d.month3_pc_cnt,

d.month1_pc_days,

d.month1_pc_pv,

d.month1_pc_avg_pv,

d.month1_pc_diff_ip_cnt,

d.month1_pc_diff_cookie_cnt,

d.month1_pc_common_ip,

d.month1_pc_common_cookie,

d.month1_pc_common_browser_name,

d.month1_pc_common_os,

d.month1_hour025_cnt,

d.month1_hour627_cnt,

d.month1_hour829_cnt,

d.month1_hour10212_cnt,

d.month1_hour13214_cnt,

d.month1_hour15217_cnt,

d.month1_hour18219_cnt,

d.month1_hour20221_cnt,

d.month1_hour22223_cnt

from gdm.itcast_gdm_user_basic a

left join gdm.itcast_gdm_user_consume_order b on a.user_id=b.user_id

left join gdm.itcast_gdm_user_buy_category c on a.user_id=c.user_id

left join gdm.itcast_gdm_user_visit d on a.user_id=d.user_id;

----------建立hive/hbase关联表（利用hive/hbase之间互相映射的特性，将存在hive数据仓库中的数据导入到hbase中）

CREATE TABLE adm.itcast_adm_personas_hbase (

user_id STRING,

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,

op_phone STRING,

add_time TIMESTAMP,

login_ip STRING,

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,

first_order_time TIMESTAMP,

last_order_time TIMESTAMP,

first_order_ago BIGINT,

last_order_ago BIGINT,

month1_hg_order_cnt BIGINT,

month1_hg_order_amt DOUBLE,

month2_hg_order_cnt BIGINT,

month2_hg_order_amt DOUBLE,

month3_hg_order_cnt BIGINT,

month3_hg_order_amt DOUBLE,

month1_order_cnt BIGINT,

month1_order_amt DOUBLE,

month2_order_cnt BIGINT,

month2_order_amt DOUBLE,

month3_order_cnt BIGINT,

month3_order_amt DOUBLE,

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,

common_address STRING,

common_paytype STRING,

month1_cart_cnt BIGINT,

month1_cart_goods_cnt BIGINT,

month1_cart_submit_cnt BIGINT,

month1_cart_rate DOUBLE,

month1_cart_cancle_cnt DOUBLE,

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,

first_category_id BIGINT,

first_category_name STRING,

second_category_id BIGINT,

second_catery_name STRING,

third_category_id BIGINT,

third_category_name STRING,

month1_category_cnt BIGINT,

month1_category_amt STRING,

month3_category_cnt BIGINT,

month3_category_amt STRING,

month6_category_cnt BIGINT,

month6_category_amt STRING,

total_category_cnt BIGINT,

total_category_amt STRING,

month1_cart_category_cnt BIGINT,

month3_cart_category_cnt BIGINT,

month6_cart_category_cnt BIGINT,

total_cart_category_cnt BIGINT,

last_category_time TIMESTAMP,

last_category_ago BIGINT,

latest_pc_visit_date STRING,

latest_app_visit_date STRING,

latest_pc_visit_session STRING,

latest_pc_cookies STRING,

latest_pc_pv STRING,

latest_pc_browser_name STRING,

latest_pc_visit_os STRING,

latest_app_name STRING,

latest_app_visit_os STRING,

latest_visit_ip STRING,

latest_city STRING,

latest_province STRING,

first_pc_visit_date STRING,

first_app_visit_date STRING,

first_pc_visit_session STRING,

first_pc_cookies STRING,

first_pc_pv STRING,

first_pc_browser_name STRING,

first_pc_visit_os STRING,

first_app_name STRING,

first_app_visit_os STRING,

first_visit_ip STRING,

first_city STRING,

first_province STRING,

day7_app_cnt BIGINT,

day15_app_cnt BIGINT,

month1_app_cnt BIGINT,

month2_app_cnt BIGINT,

month3_app_cnt BIGINT,

day7_pc_cnt BIGINT,

day15_pc_cnt BIGINT,

month1_pc_cnt BIGINT,

month2_pc_cnt BIGINT,

month3_pc_cnt BIGINT,

month1_pc_days BIGINT,

month1_pc_pv BIGINT,

month1_pc_avg_pv BIGINT,

month1_pc_diff_ip_cnt BIGINT,

month1_pc_diff_cookie_cnt BIGINT,

month1_pc_common_ip string,

month1_pc_common_cookie string,

month1_pc_common_browser_name string,

month1_pc_common_os string,

month1_hour025_cnt BIGINT,

month1_hour627_cnt BIGINT,

month1_hour829_cnt BIGINT,

month1_hour10212_cnt BIGINT,

month1_hour13214_cnt BIGINT,

month1_hour15217_cnt BIGINT,

month1_hour18219_cnt BIGINT,

month1_hour20221_cnt BIGINT,

month1_hour22223_cnt BIGINT

) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES (

"hbase.columns.mapping" = ":key,

basicInfo:user_name,

basicInfo:user_sex,

basicInfo:user_birthday,

basicInfo:user_age,

basicInfo:constellation,

basicInfo:province,

basicInfo:city,

basicInfo:city_level,

basicInfo:hex_mail,

basicInfo:op_mail,

basicInfo:hex_phone,

basicInfo:fore_phone,

basicInfo:op_phone,

basicInfo:add_time,

basicInfo:login_ip,

basicInfo:login_source,

basicInfo:request_user,

basicInfo:total_mark,

basicInfo:used_mark,

basicInfo:level_name,

basicInfo:blacklist,

basicInfo:is_married,

basicInfo:education,

basicInfo:monthly_money,

basicInfo:profession,

basicInfo:sex_model,

basicInfo:is_pregnant_woman,

basicInfo:is_have_children,

basicInfo:children_sex_rate,

basicInfo:children_age_rate,

basicInfo:is_have_car,

basicInfo:potential_car_user_rate,

basicInfo:phone_brand,

basicInfo:phone_brand_level,

basicInfo:phone_cnt,

basicInfo:change_phone_rate,

basicInfo:majia_flag,

basicInfo:majie_account_cnt,

basicInfo:loyal_model,

basicInfo:shopping_type_model,

basicInfo:figure_model,

basicInfo:stature_model,

order:first_order_time,

order:last_order_time,

order:first_order_ago,

order:last_order_ago,

order:month1_hg_order_cnt,

order:month1_hg_order_amt,

order:month2_hg_order_cnt,

order:month2_hg_order_amt,

order:month3_hg_order_cnt,

order:month3_hg_order_amt,

order:month1_order_cnt,

order:month1_order_amt,

order:month2_order_cnt,

order:month2_order_amt,

order:month3_order_cnt,

order:month3_order_amt,

order:max_order_amt,

order:min_order_amt,

order:total_order_cnt,

order:total_order_amt,

order:user_avg_amt,

order:month3_user_avg_amt,

order:common_address,

order:common_paytype,

order:month1_cart_cnt,

order:month1_cart_goods_cnt,

order:month1_cart_submit_cnt,

order:month1_cart_rate,

order:month1_cart_cancle_cnt,

order:return_cnt,

order:return_amt,

order:reject_cnt,

order:reject_amt,

order:last_return_time,

order:school_order_cnt,

order:company_order_cnt,

order:home_order_cnt,

order:forenoon_order_cnt,

order:afternoon_order_cnt,

order:night_order_cnt,

order:morning_order_cnt,

category:first_category_id,

category:first_category_name,

category:second_category_id,

category:second_catery_name,

category:third_category_id,

category:third_category_name,

category:month1_category_cnt,

category:month1_category_amt,

category:month3_category_cnt,

category:month3_category_amt,

category:month6_category_cnt,

category:month6_category_amt,

category:total_category_cnt,

category:total_category_amt,

category:month1_cart_category_cnt,

category:month3_cart_category_cnt,

category:month6_cart_category_cnt,

category:total_cart_category_cnt,

category:last_category_time,

category:last_category_ago,

visit:latest_pc_visit_date,

visit:latest_app_visit_date,

visit:latest_pc_visit_session,

visit:latest_pc_cookies,

visit:latest_pc_pv,

visit:latest_pc_browser_name,

visit:latest_pc_visit_os,

visit:latest_app_name,

visit:latest_app_visit_os,

visit:latest_visit_ip,

visit:latest_city,

visit:latest_province,

visit:first_pc_visit_date,

visit:first_app_visit_date,

visit:first_pc_visit_session,

visit:first_pc_cookies,

visit:first_pc_pv,

visit:first_pc_browser_name,

visit:first_pc_visit_os,

visit:first_app_name,

visit:first_app_visit_os,

visit:first_visit_ip,

visit:first_city,

visit:first_province,

visit:day7_app_cnt,

visit:day15_app_cnt,

visit:month1_app_cnt,

visit:month2_app_cnt,

visit:month3_app_cnt,

visit:day7_pc_cnt,

visit:day15_pc_cnt,

visit:month1_pc_cnt,

visit:month2_pc_cnt,

visit:month3_pc_cnt,

visit:month1_pc_days,

visit:month1_pc_pv,

visit:month1_pc_avg_pv,

visit:month1_pc_diff_ip_cnt,

visit:month1_pc_diff_cookie_cnt,

visit:month1_pc_common_ip,

visit:month1_pc_common_cookie,

visit:month1_pc_common_browser_name,

visit:month1_pc_common_os,

visit:month1_hour025_cnt,

visit:month1_hour627_cnt,

visit:month1_hour829_cnt,

visit:month1_hour10212_cnt,

visit:month1_hour13214_cnt,

visit:month1_hour15217_cnt,

visit:month1_hour18219_cnt,

visit:month1_hour20221_cnt,

visit:month1_hour22223_cnt"

) TBLPROPERTIES (

"hbase.table.name" = "itcast_adm_personas_hbase_2017_01_01",

"hbase.mapred.output.outputtable"="itcast_adm_personas_hbase_2017_01_01"

) ;

----向映射表中加载数据

insert overwrite table adm.itcast_adm_personas_hbase select * from adm.itcast_adm_personas;

----加载成功后查看表中是否有数据存在

hive中的表adm.itcast_adm_personas_hbase

hive映射到hbase中的表itcast_adm_personas_hbase_2017_01_01

九、电商用户画像数据可视化

9.1 数据可视化方案

技术框架：maven+spring+phoenix

核心点：采用phoenix与hbase整合，通过我们熟知的sql语句来操作NoSql数据库。

9.2 Phoenix建立hbase表的映射表

CREATE TABLE IF NOT EXISTS "itcast_adm_personas_hbase_2017_01_01"(

rw varchar(100) primary key,

"basicInfo"."user_name" VARCHAR(100),

"basicInfo"."user_sex" VARCHAR(100),

"basicInfo"."user_birthday" VARCHAR(100),

"basicInfo"."user_age" VARCHAR(100),

"basicInfo"."constellation" VARCHAR(100),

"basicInfo"."province" VARCHAR(100),

"basicInfo"."city" VARCHAR(100),

"basicInfo"."city_level" VARCHAR(100),

"basicInfo"."hex_mail" VARCHAR(100),

"basicInfo"."op_mail" VARCHAR(100),

"basicInfo"."hex_phone" VARCHAR(100),

"basicInfo"."fore_phone" VARCHAR(100),

"basicInfo"."op_phone" VARCHAR(100),

"basicInfo"."add_time" VARCHAR(100),

"basicInfo"."login_ip" VARCHAR(100),

"basicInfo"."login_source" VARCHAR(100),

"basicInfo"."request_user" VARCHAR(100),

"basicInfo"."total_mark" VARCHAR(100),

"basicInfo"."used_mark" VARCHAR(100),

"basicInfo"."level_name" VARCHAR(100),

"basicInfo"."blacklist" VARCHAR(100),

"basicInfo"."is_married" VARCHAR(100),

"basicInfo"."education" VARCHAR(100),

"basicInfo"."monthly_money" VARCHAR(100),

"basicInfo"."profession" VARCHAR(100),

"basicInfo"."sex_model" VARCHAR(100),

"basicInfo"."is_pregnant_woman" VARCHAR(100),

"basicInfo"."is_have_children" VARCHAR(100),

"basicInfo"."children_sex_rate" VARCHAR(100),

"basicInfo"."children_age_rate" VARCHAR(100),

"basicInfo"."is_have_car" VARCHAR(100),

"basicInfo"."potential_car_user_rate" VARCHAR(100),

"basicInfo"."phone_brand" VARCHAR(100),

"basicInfo"."phone_brand_level" VARCHAR(100),

"basicInfo"."phone_cnt" VARCHAR(100),

"basicInfo"."change_phone_rate" VARCHAR(100),

"basicInfo"."majia_flag" VARCHAR(100),

"basicInfo"."majie_account_cnt" VARCHAR(100),

"basicInfo"."loyal_model" VARCHAR(100),

"basicInfo"."shopping_type_model" VARCHAR(100),

"basicInfo"."figure_model" VARCHAR(100),

"basicInfo"."stature_model" VARCHAR(100),

"order"."first_order_time" VARCHAR(100),

"order"."last_order_time" VARCHAR(100),

"order"."first_order_ago" VARCHAR(100),

"order"."last_order_ago" VARCHAR(100),

"order"."month1_hg_order_cnt" VARCHAR(100),

"order"."month1_hg_order_amt" VARCHAR(100),

"order"."month2_hg_order_cnt" VARCHAR(100),

"order"."month2_hg_order_amt" VARCHAR(100),

"order"."month3_hg_order_cnt" VARCHAR(100),

"order"."month3_hg_order_amt" VARCHAR(100),

"order"."month1_order_cnt" VARCHAR(100),

"order"."month1_order_amt" VARCHAR(100),

"order"."month2_order_cnt" VARCHAR(100),

"order"."month2_order_amt" VARCHAR(100),

"order"."month3_order_cnt" VARCHAR(100),

"order"."month3_order_amt" VARCHAR(100),

"order"."max_order_amt" VARCHAR(100),

"order"."min_order_amt" VARCHAR(100),

"order"."total_order_cnt" VARCHAR(100),

"order"."total_order_amt" VARCHAR(100),

"order"."user_avg_amt" VARCHAR(100),

"order"."month3_user_avg_amt" VARCHAR(100),

"order"."common_address" VARCHAR(100),

"order"."common_paytype" VARCHAR(100),

"order"."month1_cart_cnt" VARCHAR(100),

"order"."month1_cart_goods_cnt" VARCHAR(100),

"order"."month1_cart_submit_cnt" VARCHAR(100),

"order"."month1_cart_rate" VARCHAR(100),

"order"."month1_cart_cancle_cnt" VARCHAR(100),

"order"."return_cnt" VARCHAR(100),

"order"."return_amt" VARCHAR(100),

"order"."reject_cnt" VARCHAR(100),

"order"."reject_amt" VARCHAR(100),

"order"."last_return_time" VARCHAR(100),

"order"."school_order_cnt" VARCHAR(100),

"order"."company_order_cnt" VARCHAR(100),

"order"."home_order_cnt" VARCHAR(100),

"order"."forenoon_order_cnt" VARCHAR(100),

"order"."afternoon_order_cnt" VARCHAR(100),

"order"."night_order_cnt" VARCHAR(100),

"order"."morning_order_cnt" VARCHAR(100),

"category"."first_category_id" VARCHAR(100),

"category"."first_category_name" VARCHAR(100),

"category"."second_category_id" VARCHAR(100),

"category"."second_catery_name" VARCHAR(100),

"category"."third_category_id" VARCHAR(100),

"category"."third_category_name" VARCHAR(100),

"category"."month1_category_cnt" VARCHAR(100),

"category"."month1_category_amt" VARCHAR(100),

"category"."month3_category_cnt" VARCHAR(100),

"category"."month3_category_amt" VARCHAR(100),

"category"."month6_category_cnt" VARCHAR(100),

"category"."month6_category_amt" VARCHAR(100),

"category"."total_category_cnt" VARCHAR(100),

"category"."total_category_amt" VARCHAR(100),

"category"."month1_cart_category_cnt" VARCHAR(100),

"category"."month3_cart_category_cnt" VARCHAR(100),

"category"."month6_cart_category_cnt" VARCHAR(100),

"category"."total_cart_category_cnt" VARCHAR(100),

"category"."last_category_time" VARCHAR(100),

"category"."last_category_ago" VARCHAR(100),

"visit"."latest_pc_visit_date" VARCHAR(100),

"visit"."latest_app_visit_date" VARCHAR(100),

"visit"."latest_pc_visit_session" VARCHAR(100),

"visit"."latest_pc_cookies" VARCHAR(100),

"visit"."latest_pc_pv" VARCHAR(100),

"visit"."latest_pc_browser_name" VARCHAR(100),

"visit"."latest_pc_visit_os" VARCHAR(100),

"visit"."latest_app_name" VARCHAR(100),

"visit"."latest_app_visit_os" VARCHAR(100),

"visit"."latest_visit_ip" VARCHAR(100),

"visit"."latest_city" VARCHAR(100),

"visit"."latest_province" VARCHAR(100),

"visit"."first_pc_visit_date" VARCHAR(100),

"visit"."first_app_visit_date" VARCHAR(100),

"visit"."first_pc_visit_session" VARCHAR(100),

"visit"."first_pc_cookies" VARCHAR(100),

"visit"."first_pc_pv" VARCHAR(100),

"visit"."first_pc_browser_name" VARCHAR(100),

"visit"."first_pc_visit_os" VARCHAR(100),

"visit"."first_app_name" VARCHAR(100),

"visit"."first_app_visit_os" VARCHAR(100),

"visit"."first_visit_ip" VARCHAR(100),

"visit"."first_city" VARCHAR(100),

"visit"."first_province" VARCHAR(100),

"visit"."day7_app_cnt" VARCHAR(100),

"visit"."day15_app_cnt" VARCHAR(100),

"visit"."month1_app_cnt" VARCHAR(100),

"visit"."month2_app_cnt" VARCHAR(100),

"visit"."month3_app_cnt" VARCHAR(100),

"visit"."day7_pc_cnt" VARCHAR(100),

"visit"."day15_pc_cnt" VARCHAR(100),

"visit"."month1_pc_cnt" VARCHAR(100),

"visit"."month2_pc_cnt" VARCHAR(100),

"visit"."month3_pc_cnt" VARCHAR(100),

"visit"."month1_pc_days" VARCHAR(100),

"visit"."month1_pc_pv" VARCHAR(100),

"visit"."month1_pc_avg_pv" VARCHAR(100),

"visit"."month1_pc_diff_ip_cnt" VARCHAR(100),

"visit"."month1_pc_diff_cookie_cnt" VARCHAR(100),

"visit"."month1_pc_common_ip" VARCHAR(100),

"visit"."month1_pc_common_cookie" VARCHAR(100),

"visit"."month1_pc_common_browser_name" VARCHAR(100),

"visit"."month1_pc_common_os" VARCHAR(100),

"visit"."month1_hour025_cnt" VARCHAR(100),

"visit"."month1_hour627_cnt" VARCHAR(100),

"visit"."month1_hour829_cnt" VARCHAR(100),

"visit"."month1_hour10212_cnt" VARCHAR(100),

"visit"."month1_hour13214_cnt" VARCHAR(100),

"visit"."month1_hour15217_cnt" VARCHAR(100),

"visit"."month1_hour18219_cnt" VARCHAR(100),

"visit"."month1_hour20221_cnt" VARCHAR(100),

"visit"."month1_hour22223_cnt" VARCHAR(100)

);

这个语句有几个注意点

在建立映射表之前要说明的是，Phoenix是大小写敏感的，并且所有命令都是大写，如果你建的表名没有用双引号括起来，那么无论你输入的是大写还是小写，建立出来的表名都是大写的，如果你需要建立出同时包含大写和小写的表名和字段名，请把表名或者字段名用双引号括起来。
你可以建立读写的表或者只读的表，他们的区别如下：

读写表：如果你定义的列簇不存在，会被自动建立出来，并且赋以空值
只读表：你定义的列簇必须事先存在
IF NOT EXISTS可以保证如果已经有建立过这个表，配置不会被覆盖
作为rowkey的字段用 PRIMARY KEY标定
列簇用 columnFamily.columnName 来表示

9.3 构建maven工程

9.4 用户画像查询展现

根据不同的维度进行组合查询，筛选出满足条件的用户。

posted on 2018-11-24 22:27 伪全栈的java工程师阅读(6042) 评论(1) 编辑收藏举报

用SparkSQL构建用户画像

用SparkSQL构建用户画像

二、 前言

三、 初识用户画像

四、 构建电商用户画像的重大意义

五、 如何构建电商用户画像

5.1 构建电商用户画像技术和流程

5.2 源数据分析

5.3 目标分析

5.4 用户画像建模

5.4.1 用户基本属性表

5.4.2 客户消费订单表

5.4.3 客户购买类目表

5.4.4 用户访问信息表

六、 电商用户画像环境搭建

6.1 环境准备

6.2 sparksql整合hive

6.3 测试sparksql整合hive是否成功

七、 电商用户画像数据仓库建立

7.1 数据仓库准备工作

7.2 数据仓库基本表介绍

八、 电商用户画像开发

8.1用户画像--数据开发的步骤

8.2 用户画像开发--客户基本属性表

8.3 用户画像开发--订单表宽表

8.4 用户画像开发--客户消费订单表

8.5 用户画像开发--客户购买类目表

8.6 用户画像开发--客户访问信息表

8.7 用户画像开发--用户宽表汇总模型表

九、 电商用户画像数据可视化

9.1 数据可视化方案

9.2 Phoenix建立hbase表的映射表

9.3 构建maven工程

二、前言

三、初识用户画像

四、构建电商用户画像的重大意义

五、如何构建电商用户画像

六、电商用户画像环境搭建

七、电商用户画像数据仓库建立

八、电商用户画像开发

九、电商用户画像数据可视化