数据仓库 品牌复购率
每月品牌复购率 = 某品牌本月被购买的次数 / 所有品牌本月被购买的次数。
分析：
完成品牌复购率需要建立一个宽表，宽表里面包括用户信息，商品信息，以及此商品购买的个数，即一个订单，根据订单里面的商品id不同，可能有多条这样的宽表记录。
有了宽表后，再做相应的统计工作。
一 用户购买商品明细表（宽表）
建表语句：
订单详情表数据量较大，采用分区表。
drop table if exists dws_sale_detail_daycount; create external table dws_sale_detail_daycount ( user_id string comment '用户 id', sku_id string comment '商品 Id', user_gender string comment '用户性别', user_age string comment '用户年龄', user_level string comment '用户等级', order_price decimal(10,2) comment '商品价格', sku_name string comment '商品名称', sku_tm_id string comment '品牌id', sku_category3_id string comment '商品三级品类id', sku_category2_id string comment '商品二级品类id', sku_category1_id string comment '商品一级品类id', sku_category3_name string comment '商品三级品类名称', sku_category2_name string comment '商品二级品类名称', sku_category1_name string comment '商品一级品类名称', spu_id string comment '商品 spu', sku_num int comment '购买个数', order_count string comment '当日下单单数', order_amount string comment '当日下单金额' ) COMMENT '用户购买商品明细表' PARTITIONED BY (`dt` string) stored as parquet location '/warehouse/gmall/dws/dws_user_sale_detail_daycount/' tblproperties ("parquet.compression"="snappy");
数据导入脚本：
①如上分析，这张宽表数据分为3部分，一是用户信息，来自DWD层用户表；二是商品信息表，来自DWD层降维后的商品表；三是此商品的用户购买信息，包括购买的几个，总计多少钱，来自DWD层订单详情表。
②在订单详情表加限制条件where od.dt='$do_date'是因为只统计昨天的数据。
③在商品和用户表的连接加限制条件u.dt='$do_date'是因为商品和用户是采取每日增量导入，昨天的才是最新的数据。
④每个用户，根据商品id，可能有多条记录，在sql上的体现为group by user_id, sku_id。
⑤临时表中count(*) order_count, 表示昨天这个用户购买这个商品的订单有几个，即购买了几次。
#!/bin/bash # 定义变量方便修改 APP=gmall hive=/opt/module/hive/bin/hive # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$1" ] ;then do_date=$1 else do_date=`date -d "-1 day" +%F` fi sql=" set hive.exec.dynamic.partition.mode=nonstrict; with tmp_detail as ( select user_id, sku_id, sum(sku_num) sku_num, count(*) order_count, sum(od.order_price*sku_num) order_amount from "$APP".dwd_order_detail od where od.dt='$do_date' group by user_id, sku_id ) insert overwrite table "$APP".dws_sale_detail_daycount partition(dt='$do_date') select tmp_detail.user_id, tmp_detail.sku_id, u.gender, months_between('$do_date', u.birthday)/12 age, u.user_level, price, sku_name, tm_id, category3_id, category2_id, category1_id, category3_name, category2_name, category1_name, spu_id, tmp_detail.sku_num, tmp_detail.order_count, tmp_detail.order_amount from tmp_detail left join "$APP".dwd_user_info u on tmp_detail.user_id=u.id and u.dt='$do_date' left join "$APP".dwd_sku_info s on tmp_detail.sku_id =s.id and s.dt='$do_date'; " $hive -e "$sql"
二 ADS层品牌复购率
建表语句：
drop table ads_sale_tm_category1_stat_mn; create external table ads_sale_tm_category1_stat_mn ( tm_id string comment '品牌id', category1_id string comment '1级品类id ', category1_name string comment '1级品类名称 ', buycount bigint comment '购买人数', buy_twice_last bigint comment '两次以上购买人数', buy_twice_last_ratio decimal(10,2) comment '单次复购率', buy_3times_last bigint comment '三次以上购买人数', buy_3times_last_ratio decimal(10,2) comment '多次复购率', stat_mn string comment '统计月份', stat_date string comment '统计日期' ) COMMENT '复购率统计' row format delimited fields terminated by '\t' location '/warehouse/gmall/ads/ads_sale_tm_category1_stat_mn/' ;
数据导入脚本：
①内层分组条件group by user_id, od.sku_tm_id, od.sku_category1_id, od.sku_category1_name
a）要求的是品牌复购率，根据用户id分组似乎并没有意义。
b）如果只是根据品牌进行分组，一个品牌可能有多种类型的产品，如果不加分类分组条件，这样的数据不具有参考价值，至于精确到几级分类，视情况而定。
②上面的宽表还是某个用户购买某个商品的宽表，而这里品牌复购率的核心逻辑，就是对上面的宽表里的order_count做统计，order_count表示某个商品被购买了多少次（出现在不同的订单里有多少次）。
③where date_format(dt,'yyyy-MM')=date_format('$do_date' ,'yyyy-MM')，求的是每个月的品牌复购率。
#!/bin/bash # 定义变量方便修改 APP=gmall hive=/opt/module/hive/bin/hive # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天 if [ -n "$1" ] ;then do_date=$1 else do_date=`date -d "-1 day" +%F` fi sql=" set hive.exec.dynamic.partition.mode=nonstrict; insert into table "$APP".ads_sale_tm_category1_stat_mn select mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name, sum(if(mn.order_count>=1,1,0)) buycount, sum(if(mn.order_count>=2,1,0)) buyTwiceLast, sum(if(mn.order_count>=2,1,0))/sum( if(mn.order_count>=1,1,0)) buyTwiceLastRatio, sum(if(mn.order_count>=3,1,0)) buy3timeLast, sum(if(mn.order_count>=3,1,0))/sum( if(mn.order_count>=1,1,0)) buy3timeLastRatio , date_format('$do_date' ,'yyyy-MM') stat_mn, '$do_date' stat_date from ( select user_id, od.sku_tm_id, od.sku_category1_id, od.sku_category1_name, sum(order_count) order_count from "$APP".dws_sale_detail_daycount od where date_format(dt,'yyyy-MM')=date_format('$do_date' ,'yyyy-MM') group by user_id, od.sku_tm_id, od.sku_category1_id, od.sku_category1_name ) mn group by mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name; " $hive -e "$sql"
