用户画像客户喜好消费的商品分类模型表

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

• 用户画像  总文章

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

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

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

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

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

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

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

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

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-        

客户消费商品的 每级分类的 类目总表（订单+购物车） 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层（时间分区）

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

订单中客户购买情况 临时表 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,//三级类目ID
      b.third_cat_name  third_category_name,//三级类目名称
      SUM(
        CASE
          WHEN a.dat_30 = 1  //dat_30 = 1表示该订单是在30天之内
          THEN b.goods_amount //满足上述条件之后，返回订单中所有商品的数量，最终SUM操作后便得到了近30天购物类目次数
        END
      ) month1_category_cnt,         --近30天购物类目次数
      SUM(
        CASE
          WHEN 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空的话，则替换为 0
        END
      ) month1_category_amt,         --近30天购物类目金额
      SUM(
        CASE
          WHEN a.dat_90 = 1 
          THEN b.goods_amount 
        END
      ) month3_category_cnt,        --近90天购物类目次数
      SUM(
        CASE
          WHEN a.dat_90 = 1 
          THEN COALESCE(b.curr_price, 0) * COALESCE(b.goods_amount, 0)
        END
      ) month3_category_amt,        --近90天购物类目金额
      SUM(
        CASE
          WHEN a.dat_180 = 1 
          THEN b.goods_amount 
        END
      ) month6_category_cnt,        --近180天购物类目次数
      SUM(
        CASE
          WHEN 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天标识，那么便返回1
            WHEN order_date >= DATE_SUB('2017-01-01', 29) 
            AND order_date <= '2017-01-01' 
            THEN
            1
          END
        ) dat_30,         --近30天标识
        (CASE //如果order_date订单时间 大于等于“2017-01-01减去89天”并且小于等于“2017-01-01”的话，那么表示该订单便是（dat_90）近90天标识，那么便返回1
            WHEN order_date >= DATE_SUB('2017-01-01', 89) 
            AND order_date <= '2017-01-01' 
            THEN 
            1
          END
        ) dat_90,        --近90天标识
        (CASE  //如果order_date订单时间 大于等于“2017-01-01减去179天”并且小于等于“2017-01-01”的话，那么表示该订单便是（dat_180）近180天标识，那么便返回1
            WHEN order_date >= DATE_SUB('2017-01-01', 179) 
            AND order_date <= '2017-01-01' 
            THEN 
            1
          END
        ) 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层（时间分区）

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

购物车中类目表 临时表 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,三级分类ID
    3.版本二：（推荐实际使用的版本）统计“只要加入购物车中后并且成功支付的”商品
        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, //三级类目ID
      sum(
        case //如果加入购物车时间add_time 大于等于“2017-01-01减去29天”并且小于等于“2017-01-01”的话，那么表示该订单便是近30天标识，那么便返回1，否则返回0
          when to_date (add_time) >= date_sub('2017-01-01', 29) 
          and to_date (add_time) <= '2017-01-01' 
          then 1  //满足条件返回1
          else 0  //不满足条件返回0
        end
      ) month1_cart_category_cnt, --近30天的购物车类目次数
      sum(
        case //如果加入购物车时间add_time 大于等于“2017-01-01减去89天”并且小于等于“2017-01-01”的话，那么表示该订单便是近90天标识，那么便返回1，否则返回0
          when to_date (add_time) >= date_sub('2017-01-01', 89) 
          and to_date (add_time) <= '2017-01-01' 
          then 1 
          else 0
        end
      ) month3_cart_category_cnt,  --近90天的购物车类目次数
      sum(
        case //如果加入购物车时间add_time 大于等于“2017-01-01减去179天”并且小于等于“2017-01-01”的话，那么表示该订单便是近180天标识，那么便返回1，否则返回0
          when to_date (add_time) >= date_sub('2017-01-01', 179) 
          and to_date (add_time) <= '2017-01-01' 
          then 1 
          else 0
        end
      ) month6_cart_category_cnt, --近180天的购物车类目次数
      count(1) total_category_cnt  --累计所有购物车类目次数
    from
      (select * from
        fdm.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

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

商品类目码表 
    --------商品类目码表（外部表）--------
    create database if not exists gdm;
    create external table if not exists gdm.itcast_gdm_category_code(
    third_category_id bigint,    -- 三级分类ID
    third_category_name string,-- 三级分类名称
    second_category_id bigint,    -- 二级分类ID
    second_catery_name string,    -- 二级分类名称
    first_category_id bigint,    -- 一级分类ID
    first_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层

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

用户所购买商品的所有类目总表 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,//一级类目ID
      b.first_category_name,//一级类目名称
      b.second_category_id,//二级类目ID
      b.second_catery_name,//二级类目名称
      a.third_category_id,//三级类目ID
      b.third_category_name //三级类目名称
    from
      (select user_id, third_category_id  //三级类目ID
       from gdm.itcast_gdm_user_buy_category_temp //订单中客户购买情况 临时表
       union all //合并
       select user_id, third_cart //三级类目ID
       from 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层（临时表）

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-

客户购买类目表：根据用户所购买的商品分析用户都购买了哪些类目
     
    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,                 --客户ID
      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,       --最后一次购买类目距今天数
      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作为连接条件