日萌社

人工智能AI：Keras PyTorch MXNet TensorFlow PaddlePaddle 深度学习实战（不定时更新）

用户画像总文章

================= 客户喜好消费的商品分类模型表 =================

订单中 商品类目表 GDM层（临时表）SQL目的：统计用户订单中所消费的商品分类情况，即可得知用户偏向于消费何种分类的商品没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...订单中 商品类目表 GDM层 gdm.itcast_gdm_user_buy_category_temp（临时表）订单中 商品类目表 GDM层 = 订单 主要信息表 FDM层（时间分区） + 订单中商品信息表 FDM层（时间分区）

-------------------------------

购物车中 商品类目表 GDM层（临时表）SQL目的：统计用户购物车中成功支付消费的商品分类情况，即可得知用户偏向于消费何种分类的商品没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...购物车中 商品类目表 GDM层 gdm.itcast_gdm_user_cart_category_temp（临时表）购物车中 商品类目表 GDM层 = 购物车中商品信息表 FDM层（时间分区） + 订单中商品信息表 FDM层（时间分区）

-------------------------------

商品类目码表 GDM层（1级/2级/3级分类详细）商品类目码表 GDM层 gdm.itcast_gdm_category_code

-------------------------------

客户消费商品的 每级分类的 类目详细表（订单+购物车） GDM层（临时表）（客户喜好消费的商品分类）SQL目的：根据三级分类ID得出二级分类和一级分类的ID和名称没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...客户消费商品的 每级分类的 类目详细表 GDM层 gdm.itcast_gdm_user_category_total（临时表）客户消费商品的 每级分类的 类目详细表 GDM层 = 订单中 商品类目表 GDM层（临时表）+ 购物车中 商品类目表 GDM层（临时表）+ 商品类目码表 GDM层

-------------------------------

客户消费商品的 每级分类的 类目总表（订单+购物车） GDM层（客户喜好消费的商品分类）SQL目的：汇总 用户订单中所消费的商品分类情况 和 用户购物车中成功支付消费的商品分类情况，最终得知用于总体偏向于消费何种分类的商品。当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据，因此还必须根据 where dt >= date_add(昨天日期时间, -29/-60/-90) 条件进行统计， 这样才能统计出真正的“近30/60/90天的XX”指标数据。客户消费商品的 每级分类的 类目总表 GDM层 gdm.itcast_gdm_user_buy_category（时间分区）客户消费商品的 每级分类的 类目总表 GDM层 = 客户消费商品的 每级分类的 类目详细表 GDM层（临时表） + 订单中 商品类目表 GDM层（临时表） + 购物车中 商品类目表 GDM层（临时表）

================= 订单中商品类目表 =================

订单中 商品类目表 GDM层（临时表）SQL目的：统计用户订单中所消费的商品分类情况，即可得知用户偏向于消费何种分类的商品没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...订单中 商品类目表 GDM层 gdm.itcast_gdm_user_buy_category_temp（临时表）订单中 商品类目表 GDM层 = 订单 主要信息表 FDM层（时间分区） + 订单中商品信息表 FDM层（时间分区）

-------------------------------

订单中客户购买情况 临时表 GDM 层（对 FDM 层数据 进行统计一些指标数据）--计算订单中客户购买情况drop table if exists gdm.itcast_gdm_user_buy_category_temp;SQL目的：统计用户订单中所消费的商品分类情况，即可得知用户偏向于消费何种分类的商品CREATE TABLE gdm.itcast_gdm_user_buy_category_temp AS SELECT a.user_id,  '' first_category_id,  //一级类目ID （一开始初始化为空字符串，可根据三级类目ID/名称可分别获取到一级和二级类目ID/名称）'' first_category_name,//一级类目名称'' second_category_id,//二级类目ID'' second_catery_name,//二级类目名称b.third_cart  third_category_id,//三级类目IDb.third_cat_name  third_category_name,//三级类目名称SUM(CASEWHEN a.dat_30 = 1  //dat_30 = 1表示该订单是在30天之内THEN b.goods_amount //满足上述条件之后，返回订单中所有商品的数量，最终SUM操作后便得到了近30天购物类目次数END) month1_category_cnt,         --近30天购物类目次数SUM(CASEWHEN a.dat_30 = 1 //dat_30 = 1表示该订单是在30天之内//curr_price表示每个商品的单价，不存在则返回0；goods_amount获取到当前商品的数量；//然后curr_price*goods_amount得到该商品的购买总金额，最终SUM操作后便得到了近30天购物类目金额THEN COALESCE(b.curr_price,0) * COALESCE(b.goods_amount,0) //coalesce(字段, '') 如果字段列的值为null空的话，则替换为 0END) month1_category_amt,         --近30天购物类目金额SUM(CASEWHEN a.dat_90 = 1 THEN b.goods_amount END) month3_category_cnt,        --近90天购物类目次数SUM(CASEWHEN a.dat_90 = 1 THEN COALESCE(b.curr_price, 0) * COALESCE(b.goods_amount, 0)END) month3_category_amt,        --近90天购物类目金额SUM(CASEWHEN a.dat_180 = 1 THEN b.goods_amount END) month6_category_cnt,        --近180天购物类目次数SUM(CASEWHEN a.dat_180 = 1 THEN COALESCE(b.curr_price, 0) * COALESCE(b.goods_amount, 0)END) month6_category_amt,                --近180天购物类目金额//下面的累计购物类目次数和累计购物类目金额都不做时间判断，直接累计所购买商品类目的数量和累计所购买商品的总金额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(MAX(a.order_date), '2017-01-01') last_category_ago,                --最后一次购买类目距今天数 //前面的日期参数减去后面的日期参数FROM_UNIXTIME(UNIX_TIMESTAMP()) dw_date    // 作为操作时间：返回 Unix时间戳(Unix timestamp)，表示将 Unix 时间戳字符串进行格式化FROM(SELECT a.*,(CASE   //如果order_date订单时间 大于等于“2017-01-01减去29天”并且小于等于“2017-01-01”的话，那么表示该订单便是（dat_30）近30天标识，那么便返回1WHEN order_date >= DATE_SUB('2017-01-01', 29) AND order_date <= '2017-01-01' THEN1END) dat_30,         --近30天标识(CASE //如果order_date订单时间 大于等于“2017-01-01减去89天”并且小于等于“2017-01-01”的话，那么表示该订单便是（dat_90）近90天标识，那么便返回1WHEN order_date >= DATE_SUB('2017-01-01', 89) AND order_date <= '2017-01-01' THEN 1END) dat_90,        --近90天标识(CASE  //如果order_date订单时间 大于等于“2017-01-01减去179天”并且小于等于“2017-01-01”的话，那么表示该订单便是（dat_180）近180天标识，那么便返回1WHEN order_date >= DATE_SUB('2017-01-01', 179) AND order_date <= '2017-01-01' THEN 1END) dat_180          --近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; //以用户ID、三级分类ID、三级分类名称一起作为分组条件

========================== 购物车中商品类目表 ====================

购物车中 商品类目表 GDM层（临时表）SQL目的：统计用户购物车中成功支付消费的商品分类情况，即可得知用户偏向于消费何种分类的商品没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...购物车中 商品类目表 GDM层 gdm.itcast_gdm_user_cart_category_temp（临时表）购物车中 商品类目表 GDM层 = 购物车中商品信息表 FDM层（时间分区） + 订单中商品信息表 FDM层（时间分区）

-------------------------------

购物车中类目表 临时表 GDM 层（对 FDM 层数据 进行统计一些指标数据）1.SQL目的：统计用户购物车中成功支付消费的商品分类情况，即可得知用户偏向于消费何种分类的商品2.版本一：（上课时使用的版本）只统计近180天之内加入购物车中的商品，并且要求该购物车中的商品最终是成功支付的create table 表名 as select ... from (select * from 购物车中商品信息表 where dt=昨天 and 前第30天 <= add_time <= 昨天) left join (select * from 订单中商品信息表 where dt=昨天 group by 商品ID,三级类目ID) on 商品ID group by 用户ID,三级分类ID3.版本二：（推荐实际使用的版本）统计“只要加入购物车中后并且成功支付的”商品create table 表名 as select ... from (select * from 购物车中商品信息表 where dt=昨天) left join (select * from 订单中商品信息表 where dt=昨天 group by 商品ID,三级类目ID) on 商品ID group by 用户ID,三级分类ID--购物车中类目情况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, //用户ID b.third_cart, //三级类目IDsum(case //如果加入购物车时间add_time 大于等于“2017-01-01减去29天”并且小于等于“2017-01-01”的话，那么表示该订单便是近30天标识，那么便返回1，否则返回0when to_date (add_time) >= date_sub('2017-01-01', 29) and to_date (add_time) <= '2017-01-01' then 1  //满足条件返回1else 0  //不满足条件返回0end) month1_cart_category_cnt, --近30天的购物车类目次数sum(case //如果加入购物车时间add_time 大于等于“2017-01-01减去89天”并且小于等于“2017-01-01”的话，那么表示该订单便是近90天标识，那么便返回1，否则返回0when to_date (add_time) >= date_sub('2017-01-01', 89) and to_date (add_time) <= '2017-01-01' then 1 else 0end) month3_cart_category_cnt,  --近90天的购物车类目次数sum(case //如果加入购物车时间add_time 大于等于“2017-01-01减去179天”并且小于等于“2017-01-01”的话，那么表示该订单便是近180天标识，那么便返回1，否则返回0when to_date (add_time) >= date_sub('2017-01-01', 179) and to_date (add_time) <= '2017-01-01' then 1 else 0end) month6_cart_category_cnt, --近180天的购物车类目次数count(1) total_category_cnt  --累计所有购物车类目次数from(select * fromfdm.itcast_fdm_order_cart   //购物车订单表where dt = '2017-01-01'   //add_time 表示加入购物车时间；to_date表示转换为日期时间然后用于时间比用途and to_date (add_time) >= date_sub('2017-01-01', 179)  //表示当前时间2017-01-01减去197天，然后比较加入购物车时间add_time大于“2017-01-01减去197天” and to_date (add_time) <= '2017-01-01'//表示购物车时间add_time小于当前时间2017-01-01，从而计算出加入加入购物车时间是否在当前时间的180天之内) 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 //以商品ID 和 三级类目ID 一起来分组) b on (a.goods_id = b.goods_id)  //以商品ID作为连接条件group by user_id, b.third_cart ;  //以用户ID 和 三级类目ID 一起来分组

====================== 商品类目码表 =====================

商品类目码表 GDM层（1级/2级/3级分类详细）商品类目码表 GDM层 gdm.itcast_gdm_category_code

-------------------------------

商品类目码表 --------商品类目码表（外部表）--------create database if not exists gdm;create external table if not exists gdm.itcast_gdm_category_code(third_category_id bigint,    -- 三级分类IDthird_category_name string,-- 三级分类名称second_category_id bigint,    -- 二级分类IDsecond_catery_name string,    -- 二级分类名称first_category_id bigint,    -- 一级分类IDfirst_category_name string -- 一级分类名称)row format delimited fields terminated by ','lines terminated by '\n'location  '/business/itcast_gdm_category_code';//把数据添加到商品类目码表（外部表）中load data local inpath '/root/source_data/itcast_gdm_category_code.txt' overwrite into table gdm.itcast_gdm_category_code;

=============== 客户消费商品的每级分类的类目详细表 ==============

客户消费商品的 每级分类的 类目详细表（订单+购物车） GDM层（临时表）（客户喜好消费的商品分类）SQL目的：根据三级分类ID得出二级分类和一级分类的ID和名称没有时间分区，但是每次都会先 drop 临时表，再 CREATE 临时表 as select ...客户消费商品的 每级分类的 类目详细表 GDM层 gdm.itcast_gdm_user_category_total（临时表）客户消费商品的 每级分类的 类目详细表 GDM层 = 订单中 商品类目表 GDM层（临时表）+ 购物车中 商品类目表 GDM层（临时表）+ 商品类目码表 GDM层

-------------------------------

用户所购买商品的所有类目总表 GDM层（临时表）：购物车中类目表 临时表 + 订单中客户购买情况 临时表 + 类目码表drop table if exists gdm.itcast_gdm_user_category_total;SQL目的：根据三级分类ID得出二级分类和一级分类的ID和名称create table gdm.itcast_gdm_user_category_total as select a.user_id,b.first_category_id,//一级类目IDb.first_category_name,//一级类目名称b.second_category_id,//二级类目IDb.second_catery_name,//二级类目名称a.third_category_id,//三级类目IDb.third_category_name //三级类目名称from(select user_id, third_category_id  //三级类目IDfrom gdm.itcast_gdm_user_buy_category_temp //订单中客户购买情况 临时表union all //合并select user_id, third_cart //三级类目IDfrom gdm.itcast_gdm_user_cart_category_temp //购物车中类目表 临时表) a left join gdm.itcast_gdm_category_code b //类目码表：可根据三级类目ID得到对应的一级、二级的类目ID/类目名称on ( a.third_category_id = b.third_category_id ) //以三级类目ID作为连接条件//以用户ID、一级的类目ID和类目名称、二级的类目ID和类目名称、三级的类目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 ;

====================== 客户喜好消费的商品分类模型表 =====================

客户消费商品的 每级分类的 类目总表（订单+购物车） GDM层（客户喜好消费的商品分类）SQL目的：汇总 用户订单中所消费的商品分类情况 和 用户购物车中成功支付消费的商品分类情况，最终得知用于总体偏向于消费何种分类的商品。当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据，因此还必须根据 where dt >= date_add(昨天日期时间, -29/-60/-90) 条件进行统计， 这样才能统计出真正的“近30/60/90天的XX”指标数据。客户消费商品的 每级分类的 类目总表 GDM层 gdm.itcast_gdm_user_buy_category（时间分区）客户消费商品的 每级分类的 类目总表 GDM层 = 客户消费商品的 每级分类的 类目详细表 GDM层（临时表） + 订单中 商品类目表 GDM层（临时表） + 购物车中 商品类目表 GDM层（临时表）

-------------------------------

客户购买类目表：根据用户所购买的商品分析用户都购买了哪些类目create database if not exists gdm;SQL目的：汇总 用户订单中所消费的商品分类情况 和 用户购物车中成功支付消费的商品分类情况，最终得知用于总体偏向于消费何种分类的商品当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据，因此还必须根据 where dt >= date_add(昨天日期时间, -29/-60/-90) 条件进行统计， 这样才能统计出真正的“近30/60/90天的XX”指标数据。 CREATE TABLE if not exists gdm.itcast_gdm_user_buy_category (user_id STRING,                 --客户IDfirst_category_id BIGINT,       --一级分类IDfirst_category_name STRING,     --一级分类名称second_category_id BIGINT,      --二分类IDsecond_catery_name STRING,      --二级分类名称third_category_id BIGINT,       --三级分类IDthird_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,       --最后一次购买类目距今天数dw_date TIMESTAMP) partitioned BY (dt STRING) ;//加载数据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_cart_category_cnt,t2.month3_cart_category_cnt,t2.month6_cart_category_cnt,t2.total_category_cnt,t1.last_category_time,t1.last_category_ago,FROM_UNIXTIME(UNIX_TIMESTAMP()) dw_date // 作为操作时间：返回 Unix时间戳(Unix timestamp)，表示将 Unix 时间戳字符串进行格式化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) //三级类目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) ;//三级类目ID作为连接条件