日萌社 人工智能AI&＃xff1a;Keras PyTorch MXNet TensorFlow PaddlePaddle 深度学习实战&＃xff08;不定时更新&＃xff09;
订单表1.订单 主要信息表1.订单 主要信息表 BDM层 bdm.itcast_bdm_order&＃xff08;时间分区&＃xff09;2.订单 主要信息表 FDM层 fdm.itcast_fdm_order&＃xff08;时间分区&＃xff09;2.订单 详细信息表1.订单 详细信息表 BDM层 bdm.itcast_bdm_order_desc&＃xff08;时间分区&＃xff09;2.订单 详细信息表 FDM层 fdm.itcast_fdm_order_desc&＃xff08;时间分区&＃xff09;3.订单表 GDM层订单宽表 GDM层 gdm.itcast_gdm_order&＃xff08;时间分区&＃xff09;订单宽表 GDM层 &＃61; 订单 主要信息表 FDM层 &＃43; 订单 详细信息表 FDM层
----------------------------------------
订单中 商品信息表1.订单中 商品信息表 BDM层 bdm.itcast_bdm_order_goods&＃xff08;时间分区&＃xff09;2.订单中 商品信息表 FDM层 fdm.itcast_fdm_order_goods&＃xff08;时间分区&＃xff09;
----------------------------------------
客户订单地址表客户订单地址表 GDM层 gdm.itcast_gdm_user_order_addr_model
----------------------------------------
购物车中 商品信息表1.购物车中 商品信息表 BDM层 bdm.itcast_bdm_order_cart&＃xff08;时间分区&＃xff09;2.购物车中 商品信息表 FDM层 fdm.itcast_fdm_order_cart&＃xff08;时间分区&＃xff09;
----------------------------------------
客户消费模型表&＃xff08;订单&＃43;购物车&＃xff09; GDM层&＃xff08;临时表&＃xff09;没有时间分区&＃xff0c;但是每次都会先 drop 临时表&＃xff0c;再 CREATE 临时表 as select ...1.临时表01&＃xff08;订单&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_01&＃xff08;临时表01&＃xff09;统计订单相关指标客户消费模型表 GDM层 &＃61; 订单表 GDM层&＃xff08;时间分区&＃xff09; &＃43; 订单中商品信息表 FDM层&＃xff08;时间分区&＃xff09; &＃43; 客户订单地址表 GDM层2.临时表02&＃xff08;购物车&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_02&＃xff08;临时表02&＃xff09; 统计购物车相关指标 客户消费模型表 GDM层 &＃61; 购物车中商品信息表 FDM层&＃xff08;时间分区&＃xff09;3.临时表03&＃xff08;订单&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_03&＃xff08;临时表03&＃xff09; 统计订单中常用收货地址和常用支付方式 客户消费模型表 GDM层 &＃61; 订单表 GDM层&＃xff08;时间分区&＃xff09;&＃43; 订单表 GDM层&＃xff08;时间分区&＃xff09;&＃xff08;订单表自身和自身union all&＃xff09;4.临时表04gdm.itcast_gdm_user_consume_order_temp_100&＃xff08;临时表04&＃xff09; &＃61; 客户消费模型表 GDM层&＃xff08;临时表01&＃xff09; &＃43; 客户消费模型表 GDM层&＃xff08;临时表02&＃xff09; 目的&＃xff1a;仅为合并 订单表 和 购物车表 中所相同的的user_id为一个分组&＃xff0c;并且最终合并结果中的每条数据均为不同的user_id
----------------------------------------
客户消费模型表&＃xff08;订单&＃43;购物车&＃xff09; GDM层SQL目的&＃xff1a;统计 订单 和 购物车 中相关消费指标。当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据&＃xff0c;因此还必须根据 where dt >&＃61; date_add(昨天日期时间, -29/-60/-90) 条件进行统计&＃xff0c; 这样才能统计出真正的“近30/60/90天的XX”指标数据。客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order&＃xff08;时间分区&＃xff09;客户消费模型表 GDM层 &＃61; 临时表01 &＃43; 临时表02 &＃43; 临时表03 &＃43; 临时表04
订单表1.订单 主要信息表1.订单 主要信息表 BDM层 bdm.itcast_bdm_order&＃xff08;时间分区&＃xff09;2.订单 主要信息表 FDM层 fdm.itcast_fdm_order&＃xff08;时间分区&＃xff09;2.订单 详细信息表1.订单 详细信息表 BDM层 bdm.itcast_bdm_order_desc&＃xff08;时间分区&＃xff09;2.订单 详细信息表 FDM层 fdm.itcast_fdm_order_desc&＃xff08;时间分区&＃xff09;3.订单表 GDM层订单宽表 GDM层 gdm.itcast_gdm_order&＃xff08;时间分区&＃xff09;订单宽表 GDM层 &＃61; 订单 主要信息表 FDM层 &＃43; 订单 详细信息表 FDM层
----------------------------------------
订单宽表 模型开发 BDM 层&＃xff08;源数据层&＃xff09;--订单主要信息表 BDM 层&＃xff08;源数据层&＃xff09;create database if not exists bdm;create external table if not exists bdm.itcast_bdm_order(order_id string, --订单IDorder_no string, --订单号order_date string, --订单日期user_id string, --用户IDuser_name string, --登录名order_money double, --订单金额order_type string, --订单类型order_status string, --订单状态pay_status string, --支付状态pay_type string, --支付方式 1、在线支付&＃xff0c;2、货到付款order_source string, --订单来源update_time timestamp --订单更新时间) partitioned by (dt string)row format delimited fields terminated by &＃39;,&＃39;lines terminated by &＃39;\n&＃39; //以某字符来分割每行location &＃39;/business/itcast_bdm_order&＃39;;//添加分区alter table bdm.itcast_bdm_order add partition (dt&＃61;&＃39;2017-01-01&＃39;) location &＃39;/business/itcast_bdm_order/dt&＃61;2017-01-01&＃39;;//把 BDM层源数据文件 导入到 BDM层 外部分区表中hdfs dfs -put /root/source_data/itcast_bdm_order.txt /business/itcast_bdm_order/dt&＃61;2017-01-01/
----------------------------------------
订单 主要信息表 FDM层&＃xff08;对 BDM 源数据层的数据进行清洗和预处理&＃xff09;--订单主要信息表FDM层create database if not exists fdm;create table if not exists fdm.itcast_fdm_order(order_id string, --订单IDorder_no string, --订单号order_date string, --订单日期user_id string, --用户IDuser_name string, --登录名order_money double, --订单金额order_type string, --订单类型order_status string, --订单状态pay_status string, --支付状态pay_type string, --支付方式 1、在线支付&＃xff0c;2、货到付款order_source string, --订单来源update_time timestamp, --订单更新时间dw_date timestamp) partitioned by (dt string);
----------------------------------------
--加载 订单主要信息模型表BDM层&＃xff08;源数据层&＃xff09;数据 到 订单主要信息表 FDM层 insert overwrite table fdm.itcast_fdm_order partition(dt&＃61;&＃39;2017-01-01&＃39;)select t.order_id, --订单IDt.order_no, --订单号t.order_date, --订单日期t.user_id, --用户IDt.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 // 作为操作时间&＃xff1a;返回 Unix时间戳(Unix timestamp)&＃xff0c;表示将 Unix 时间戳字符串进行格式化from bdm.itcast_bdm_order t where dt&＃61;&＃39;2017-01-01&＃39;;
----------------------------------------
订单 详细信息表 BDM 层&＃xff08;源数据层&＃xff09;create database if not exists bdm;create external table if not exists bdm.itcast_bdm_order_desc(order_id string, --订单IDorder_no string, --订单号consignee string, --收货人姓名area_id string, --收货人地址IDarea_name string, --地址ID对应的地址段address string, --收货人地址mobile string, --收货人手机号telphone string, --收货人电话coupon_id bigint, --使用代金券IDcoupon_money double, --使用代金券金额carriage_money double, --运费create_time timestamp, --创建时间update_time timestamp, --更新时间dw_date timestamp)partitioned by (dt string)row format delimited fields terminated by &＃39;,&＃39;location &＃39;/business/itcast_bdm_order_desc&＃39;;//添加分区alter table bdm.itcast_bdm_order_desc add partition (dt&＃61;&＃39;2017-01-01&＃39;) location &＃39;/business/itcast_bdm_order_desc/dt&＃61;2017-01-01&＃39;;//把 BDM层源数据文件 导入到 BDM层 外部分区表中hdfs dfs -put /root/source_data/itcast_bdm_order_desc.txt /business/itcast_bdm_order_desc/dt&＃61;2017-01-01
----------------------------------------
订单 详细信息表 FDM层&＃xff08;对 BDM 源数据层的数据进行清洗和预处理&＃xff09;create database if not exists fdm; create table if not exists fdm.itcast_fdm_order_desc(order_id string, --订单IDorder_no string, --订单号consignee string, --收货人姓名area_id string, --收货人地址IDarea_name string, --地址ID对应的地址段address string, --收货人地址mobile string, --收货人手机号telphone string, --收货人电话coupon_id bigint, --使用代金券IDcoupon_money double, --使用代金券金额carriage_money double, --运费create_time timestamp, --创建时间update_time timestamp, --更新时间dw_date timestamp) partitioned by (dt string);
----------------------------------------
--加载 订单详细信息模型表 BDM层&＃xff08;源数据层&＃xff09;数据 到 订单详细信息表 FDM层 insert overwrite table fdm.itcast_fdm_order_desc partition(dt&＃61;&＃39;2017-01-01&＃39;)select t.order_id, --订单IDt.order_no, --订单号t.consignee, --收货人姓名t.area_id, --收货人地址IDt.area_name, --地址ID对应的地址段t.address, --收货人地址t.mobile, --收货人手机号t.telphone, --收货人电话t.coupon_id, --使用代金券IDt.coupon_money, --使用代金券金额t.carriage_money, --运费t.create_time, --创建时间t.update_time, --更新时间from_unixtime(unix_timestamp()) dw_date // 作为操作时间&＃xff1a;返回 Unix时间戳(Unix timestamp)&＃xff0c;表示将 Unix 时间戳字符串进行格式化from bdm.itcast_bdm_order_desc t where dt&＃61;&＃39;2017-01-01&＃39;;
----------------------------------------
订单宽表模型 GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09;--订单宽表模型 create database if not exists gdm;create table if not exists gdm.itcast_gdm_order(order_id string,--订单IDorder_no string,--订单号order_date string,--订单日期user_id string,--用户IDuser_name string,--登录名order_money double,--订单金额order_type string,--订单类型order_status string,--订单状态pay_status string,--支付状态pay_type string,--支付方式 1、在线支付&＃xff0c;2、货到付款order_source string,--订单来源consignee string,--收货人姓名area_id string,--收货人地址IDarea_name string,--地址ID对应的地址段&＃xff08;粒度到县&＃xff09;address string,--收货人地址&＃xff08;手工填写的地址&＃xff09;mobile string,--收货人手机号telphone string,--收货人电话coupon_id bigint,--使用代金券IDcoupon_money double,--使用代金券金额carriage_money double,--运费create_time timestamp,--创建时间update_time timestamp,--更新时间dw_date timestamp --操作时间) partitioned by (dt string);
----------------------------------------
&＃xff08;关联查询&＃xff09;把 订单 主要信息表 FDM层 和 订单 详细信息表 FDM层 对同一订单信息 进行连接查询后返回的数据 加载到 订单宽表 FDM层insert overwrite table gdm.itcast_gdm_order partition(dt&＃61;&＃39;2017-01-01&＃39;) select a.order_id, --订单IDa.order_no, --订单号a.order_date, --订单日期a.user_id, --用户IDa.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, --收货人地址IDb.area_name, --地址ID对应的地址段b.address, --收货人地址b.mobile, --收货人手机号b.telphone, --收货人电话b.coupon_id, --使用代金券IDb.coupon_money, --使用代金券金额b.carriage_money,--运费b.create_time, --创建时间b.update_time, --更新时间from_unixtime(unix_timestamp()) dw_date // 作为操作时间&＃xff1a;返回 Unix时间戳(Unix timestamp)&＃xff0c;表示将 Unix 时间戳字符串进行格式化from (select * from fdm.itcast_fdm_order where dt&＃61;&＃39;2017-01-01&＃39;) a //订单 主要信息表 FDM层join (select * from fdm.itcast_fdm_order_desc where dt&＃61;&＃39;2017-01-01&＃39;) b //订单 详细信息表 FDM层on a.order_id&＃61;b.order_id; //以订单号ID作为两表连接查询的连接条件
订单中 商品信息表1.订单中 商品信息表 BDM层 bdm.itcast_bdm_order_goods&＃xff08;时间分区&＃xff09;2.订单中 商品信息表 FDM层 fdm.itcast_fdm_order_goods&＃xff08;时间分区&＃xff09;
----------------------------------------
订单商品信息表 BDM 层&＃xff08;源数据层&＃xff09;-----订单商品信息表 BDM 层&＃xff08;源数据层&＃xff09;create database if not exists bdm;create external table if not exists bdm.itcast_bdm_order_goods(user_id string,--用户IDorder_id string,--订单IDorder_no string,--订单号sku_id bigint,--SKU编号sku_name string,--SKU名称goods_id bigint,--商品编号goods_no string,--商品货号goods_sn string,--商品条码goods_name string,--商品名称goods_amount bigint,--商品数量size_id bigint,--尺码编号size_name string,--尺码名称colour_id bigint,--颜色IDshop_id string,--店铺编号shop_name string,--店铺名称curr_price double,--售卖价格market_price double,--市场价格discount double,--折扣比例cost_price double,--成本价格cost_type string,--成本类型warehouse string,--所在仓库first_cart bigint,-- 一级分类IDfirst_cart_name string,-- 一级分类名称second_cart bigint,-- 二级分类IDsecond_cart_name string,-- 二级分类名称third_cart bigint,-- 三级分类IDthird_cart_name string,-- 三级分类名称dw_date timestamp) partitioned by (dt string)row format delimited fields terminated by &＃39;,&＃39;lines terminated by &＃39;\n&＃39;location &＃39;/business/itcast_bdm_order_goods&＃39;;//添加分区alter table bdm.itcast_bdm_order_goods add partition (dt&＃61;&＃39;2017-01-01&＃39;) location &＃39;/business/itcast_bdm_order_goods/dt&＃61;2017-01-01&＃39;;//把 BDM层源数据文件 导入到 BDM层 外部分区表中hdfs dfs -put /root/source_data/itcast_bdm_order_goods.txt /business/itcast_bdm_order_goods/dt&＃61;2017-01-01
----------------------------------------
订单商品信息表 FDM 层&＃xff08;对 BDM 源数据层的数据进行清洗和预处理&＃xff09;-----订单商品信息表 FDM 层&＃xff08;对 BDM 源数据层的数据进行清洗和预处理&＃xff09;create database if not exists fdm;create table if not exists fdm.itcast_fdm_order_goods(user_id string,--用户ID order_id string,--订单IDorder_no string,--订单号sku_id bigint,--SKU编号sku_name string,--SKU名称goods_id bigint,--商品编号goods_no string,--商品货号goods_sn string,--商品条码goods_name string,--商品名称goods_amount bigint,--商品数量size_id bigint,--尺码编号size_name string,--尺码名称colour_id bigint,--颜色IDshop_id string,--店铺编号shop_name string,--店铺名称curr_price double,--售卖价格market_price double,--市场价格discount double,--折扣比例cost_price double,--成本价格cost_type string,--成本类型warehouse string,--所在仓库first_cart bigint,-- 一级分类IDfirst_cart_name string,-- 一级分类名称second_cart bigint,-- 二级分类IDsecond_cart_name string,-- 二级分类名称third_cart bigint,-- 三级分类IDthird_cat_name string,-- 三级分类名称dw_date timestamp) partitioned by (dt string);
----------------------------------------
--加载 订单商品信息表 BDM层&＃xff08;源数据层&＃xff09;数据 到 订单商品信息表 FDM层 insert overwrite table fdm.itcast_fdm_order_goods partition(dt&＃61;&＃39;2017-01-01&＃39;)selectt.user_id,--用户ID t.order_id,--订单IDt.order_no,--订单号t.sku_id,--SKU编号t.sku_name,--SKU名称t.goods_id,--商品编号t.goods_no,--商品货号t.goods_sn,--商品条码t.goods_name,--商品名称t.goods_amount,--商品数量t.size_id,--尺码编号t.size_name,--尺码名称t.colour_id,--颜色IDt.shop_id,--店铺编号t.shop_name,--店铺名称t.curr_price,--售卖价格t.market_price,--市场价格t.discount,--折扣比例t.cost_price,--成本价格t.cost_type,--成本类型t.warehouse,--所在仓库t.first_cart,-- 一级分类IDt.first_cart_name,-- 一级分类名称t.second_cart,-- 二级分类IDt.second_cart_name,-- 二级分类名称t.third_cart,-- 三级分类IDt.third_cart_name,-- 三级分类名称from_unixtime(unix_timestamp()) dw_date from bdm.itcast_bdm_order_goods t where dt&＃61;&＃39;2017-01-01&＃39;;
客户订单地址表客户订单地址表 GDM层 gdm.itcast_gdm_user_order_addr_model
----------------------------------------
客户订单地址模型表&＃xff08;用于标识是在哪里下单的&＃xff09;GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09;create database if not exists gdm;create table if not exists gdm.itcast_gdm_user_order_addr_model(user_id string, -- 客户IDorder_addr bigint, -- 1表示学校、2表示单位、3表示家里user_order_flag string -- 关联标识)row format delimited fields terminated by &＃39;,&＃39;lines terminated by &＃39;\n&＃39;;load data local inpath &＃39;/root/source_data/itcast_gdm_user_order_addr_model.txt&＃39; overwrite into table gdm.itcast_gdm_user_order_addr_model;
购物车中 商品信息表1.购物车中 商品信息表 BDM层 bdm.itcast_bdm_order_cart&＃xff08;时间分区&＃xff09;2.购物车中 商品信息表 FDM层 fdm.itcast_fdm_order_cart&＃xff08;时间分区&＃xff09;
----------------------------------------
购物车订单表 BDM 层&＃xff08;源数据层&＃xff09;create database if not exists bdm;create external table if not exists bdm.itcast_bdm_order_cart(id bigint,--IDsession_id string,--sessionIDuser_id string,--用户IDgoods_id string,--商品IDgoods_num bigint,--商品数量add_time string,--商品加入时间cancle_time string,--商品取消时间sumbit_time string,--商品提交时间dw_date timestamp) partitioned by (dt string)row format delimited fields terminated by &＃39;,&＃39;lines terminated by &＃39;\n&＃39;location &＃39;/business/itcast_bdm_order_cart&＃39;;//添加分区 alter table bdm.itcast_bdm_order_cart add partition (dt&＃61;&＃39;2017-01-01&＃39;) location &＃39;/business/itcast_bdm_order_cart/dt&＃61;2017-01-01&＃39;;//把 BDM层源数据文件 导入到 BDM层 外部分区表中hdfs dfs -put /root/source_data/itcast_bdm_order_cart.txt /business/itcast_bdm_order_cart/dt&＃61;2017-01-01
----------------------------------------
购物车订单表 FDM 层&＃xff08;对 BDM 源数据层的数据进行清洗和预处理&＃xff09;create database if not exists fdm;create table if not exists fdm.itcast_fdm_order_cart(id bigint,--IDsession_id string,--sessionIDuser_id string,--用户IDgoods_id string,--商品IDgoods_num bigint,--商品数量add_time string,--商品加入时间cancle_time string,--商品取消时间sumbit_time string,--商品提交时间dw_date timestamp) partitioned by (dt string);
----------------------------------------
--加载 购物车订单表 BDM层&＃xff08;源数据层&＃xff09;数据 到 购物车订单表 FDM层 insert overwrite table fdm.itcast_fdm_order_cart partition(dt&＃61;&＃39;2017-01-01&＃39;)select t.id,--IDt.session_id,--sessionIDt.user_id,--用户IDt.goods_id,--商品IDt.goods_num ,--商品数量t.add_time,--商品加入时间t.cancle_time,--商品取消时间t.sumbit_time,--商品提交时间from_unixtime(unix_timestamp()) dw_date from bdm.itcast_bdm_order_cart t where dt&＃61;&＃39;2017-01-01&＃39;;
客户消费模型表&＃xff08;订单&＃43;购物车&＃xff09; GDM层&＃xff08;临时表&＃xff09;没有时间分区&＃xff0c;但是每次都会先 drop 临时表&＃xff0c;再 CREATE 临时表 as select ...1.临时表01&＃xff08;订单&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_01&＃xff08;临时表01&＃xff09;统计订单相关指标客户消费模型表 GDM层 &＃61; 订单表 GDM层&＃xff08;时间分区&＃xff09; &＃43; 订单中商品信息表 FDM层&＃xff08;时间分区&＃xff09; &＃43; 客户订单地址表 GDM层2.临时表02&＃xff08;购物车&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_02&＃xff08;临时表02&＃xff09; 统计购物车相关指标 客户消费模型表 GDM层 &＃61; 购物车中商品信息表 FDM层&＃xff08;时间分区&＃xff09;3.临时表03&＃xff08;订单&＃xff09;客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_03&＃xff08;临时表03&＃xff09; 统计订单中常用收货地址和常用支付方式 客户消费模型表 GDM层 &＃61; 订单表 GDM层&＃xff08;时间分区&＃xff09;&＃43; 订单表 GDM层&＃xff08;时间分区&＃xff09;&＃xff08;订单表自身和自身union all&＃xff09;4.临时表04gdm.itcast_gdm_user_consume_order_temp_100&＃xff08;临时表04&＃xff09; &＃61; 客户消费模型表 GDM层&＃xff08;临时表01&＃xff09; &＃43; 客户消费模型表 GDM层&＃xff08;临时表02&＃xff09; 目的&＃xff1a;仅为合并 订单表 和 购物车表 中所相同的的user_id为一个分组&＃xff0c;并且最终合并结果中的每条数据均为不同的user_id
----------------------------------------
客户消费订单模型表 GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09;&＃xff1a;--统计订单相关指标&＃xff08;GDM 层的 临时表 itcast_gdm_user_consume_order_temp_01&＃xff09; drop table if exists gdm.itcast_gdm_user_consume_order_temp_01;第一个版本&＃xff1a;&＃xff08;上课时的该版本有所缺失&＃xff0c;订单商品信息表没有WHERE&＃43;时间分区&＃xff09;CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01临时表 AS SELECT ...FROM (SELECT ... FROM gdm.itcast_gdm_order订单宽表 WHERE dt &＃61; &＃39;2017-01-01&＃39;) t LEFT JOIN (SELECT ... FROM fdm.itcast_fdm_order_goods订单商品信息表) t1 ON (t.order_id &＃61; t1.order_id) //order_id 订单IDLEFT JOIN (SELECT ... FROM gdm.itcast_gdm_user_order_addr_model客户订单位置表) t2 ON (t.user_id &＃61; t2.user_id) //user_id 用户IDGROUP BY t.user_id ;第二个版本&＃xff1a;&＃xff08;该版本才为正确&＃xff0c;订单商品信息表的WHERE加上了时间分区&＃xff09;CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01临时表 AS SELECT ...FROM (SELECT ... FROM gdm.itcast_gdm_order订单宽表 WHERE dt &＃61; &＃39;2017-01-01&＃39;) t LEFT JOIN (SELECT ... FROM fdm.itcast_fdm_order_goods订单商品信息表 WHERE dt &＃61; &＃39;2017-01-01&＃39;) t1 ON (t.order_id &＃61; t1.order_id) //order_id 订单IDLEFT JOIN (SELECT ... FROM gdm.itcast_gdm_user_order_addr_model客户订单位置表) t2 ON (t.user_id &＃61; t2.user_id) //user_id 用户IDGROUP BY t.user_id ;//下面很长的SQL语句的结构总结为上面的5行SQL结构CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01 AS SELECT t.user_id,MIN(order_date) first_order_time, --第一次消费时间&＃xff1a;获取所有订单时间然后使用MIN()即能获取第一次消费时间MAX(order_date) last_order_time, --最近一次消费时间&＃xff1a;获取所有订单时间然后使用MAX()即能获取最近一次消费时间DATEDIFF(MIN(order_date), &＃39;2017-01-01&＃39;) first_order_ago, --首单距今时间&＃xff1a;使用“2017-01-01”减去 MIN(订单时间) 即能获取首单距今时间DATEDIFF(MAX(order_date), &＃39;2017-01-01&＃39;) last_order_ago, --尾单距今时间&＃xff1a;使用“2017-01-01”减去 MAX(订单时间) 即能获取尾单距今时间SUM( //SUM()&＃xff1a;累加30天内的所有订单的总次数 CASEWHEN t.dat_30 &＃61; 1 //1 表示订单是近30天内的AND t.order_flag &＃61; 0 //0 表示订单为非退拒的THEN 1 END) month1_hg_order_cnt, --近30天购买次数&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加30天内的所有订单的总金额 CASEWHEN t.dat_30 &＃61; 1 //1 表示订单是近30天内的AND t.order_flag &＃61; 0 //0 表示订单为非退拒的THEN t.order_money //返回订单购买金额END) month1_hg_order_amt, --近30天购买金额&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加60天内的所有订单的总次数CASEWHEN t.dat_60 &＃61; 1 AND t.order_flag &＃61; 0 THEN 1 END) month2_hg_order_cnt, --近60天购买次数&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加60天内的所有订单的总金额CASEWHEN t.dat_60 &＃61; 1 AND t.order_flag &＃61; 0 THEN t.order_money END) month2_hg_order_amt, --近60天购买金额&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加90天内的所有订单的总次数CASEWHEN t.dat_90 &＃61; 1 AND t.order_flag &＃61; 0 THEN 1 END) month3_hg_order_cnt, --近90天购买次数&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加90天内的所有订单的总金额CASEWHEN t.dat_90 &＃61; 1 AND t.order_flag &＃61; 0 THEN t.order_money END) month3_hg_order_amt, --近90天购买金额&＃xff08;不含退拒&＃xff09;SUM(dat_30) month1_order_cnt, --近30天购买次数&＃xff08;含退拒&＃xff09;SUM(CASEWHEN t.dat_30 &＃61; 1 THEN t.order_money END) month1_order_amt, --近30天购买金额&＃xff08;含退拒&＃xff09;SUM(dat_60) month2_order_cnt, --近60天购买次数&＃xff08;含退拒&＃xff09;SUM(CASEWHEN t.dat_60 &＃61; 1 THEN t.order_money END) month2_order_amt, --近60天购买金额&＃xff08;含退拒&＃xff09;SUM(dat_90) month3_order_cnt, --近90天购买次数&＃xff08;含退拒&＃xff09;SUM(CASEWHEN t.dat_90 &＃61; 1 THEN t.order_money END) month3_order_amt, --近90天购买金额&＃xff08;含退拒&＃xff09;MAX(t.order_money) max_order_amt, --最大消费金额 //获取出所有的订单消费金额&＃xff0c;然后使用MAX()获取最大的订单消费金额MIN(t.order_money) min_order_amt, --最小消费金额 //获取出所有的订单消费金额&＃xff0c;然后使用MIX()获取最小的订单消费金额SUM( //SUM()&＃xff1a;累加所有非退拒的订单的总数CASEWHEN t.order_flag &＃61; 0 //0 表示订单为非退拒的THEN 1END) total_order_cnt, --累计消费次数&＃xff08;不含退拒&＃xff09;SUM( //SUM()&＃xff1a;累加所有非退拒的订单的总金额CASEWHEN t.order_flag &＃61; 0 THEN t.order_money //返回订单购买金额END) total_order_amt, --累计消费金额&＃xff08;不含退拒&＃xff09;SUM(coupon_money) total_coupon_amt, --累计使用代金券金额 //SUM()&＃xff1a;累加所有代金券金额SUM(t.order_money) / COUNT(1) user_avg_amt, --客单价&＃xff08;含退拒&＃xff09;&＃xff1a;客单价表示每个订单平均价格&＃xff0c;通过所有商品总价格除以总订单数得出每个订单平均价格 //SUM()&＃xff1a;累加所有订单总金额。COUNT(1)&＃xff1a;统计订单的总数0 month3_user_avg_amt, --近90天的客单价&＃xff08;含退拒&＃xff09;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( //SUM()&＃xff1a;累加所有退货商品的数量CASEWHEN t.order_status &＃61; 3 //订单状态为3表示退货THEN t1.goods_amount //退货商品数量END) return_cnt, --退货商品数量SUM( //SUM()&＃xff1a;累加所有退货商品的金额CASEWHEN t.order_status &＃61; 3 //订单状态为3表示退货THEN t.order_money //退货商品金额END) return_amt, --退货商品金额SUM( //SUM()&＃xff1a;累加所有拒收商品的数量CASEWHEN t.order_status &＃61; 4 //订单状态为4表示拒收THEN t1.goods_amount //拒收商品数量END) reject_cnt, --拒收商品数量SUM( //SUM()&＃xff1a;累加所有拒收商品的金额CASEWHEN t.order_status &＃61; 4 //订单状态为4表示拒收THEN t.order_money //拒收商品金额END) reject_amt, --拒收商品金额MAX( //MAX()获取最大的退货商品时间CASEWHEN t.order_status &＃61; 3 //订单状态为3表示退货THEN t.order_date //退货商品时间END) last_return_time, --最近一次退货时间SUM( //SUM()&＃xff1a;累加所有学校下单的总数CASEWHEN t2.order_addr &＃61; 1 THEN 1 END) school_order_cnt, --学校下单总数SUM( //SUM()&＃xff1a;累加所有单位下单的总数CASEWHEN t2.order_addr &＃61; 2 THEN 1 END) company_order_cnt, --单位下单总数SUM( //SUM()&＃xff1a;累加所有家里下单的总数CASEWHEN t2.order_addr &＃61; 3 THEN 1 END) home_order_cnt, --家里下单总数SUM( //SUM()&＃xff1a;累加所有上午下单的总数CASEWHEN t.order_hour >&＃61; 8 AND t.order_hour <&＃61; 11 THEN 1 END) forenoon_order_cnt, --上午下单总数SUM(//SUM()&＃xff1a;累加所有下午下单的总数CASEWHEN t.order_hour >&＃61; 12 AND t.order_hour <&＃61; 18 THEN 1 END) afternoon_order_cnt, --下午下单总数SUM(//SUM()&＃xff1a;累加所有晚上下单的总数CASEWHEN t.order_hour >&＃61; 19 AND t.order_hour <&＃61; 22 THEN 1 END) night_order_cnt, --晚上下单总数SUM(//SUM()&＃xff1a;累加所有凌晨下单的总数CASEWHEN t.order_hour >&＃61; 23 or t.order_hour <&＃61; 7 THEN 1 END) morning_order_cnt --凌晨下单总数 FROM(SELECT a.*,(CASE //如果order_date订单时间 大于“2017-01-01减去29天”并且小于“2017-01-01”的话&＃xff0c;那么表示该订单便是&＃xff08;dat_30&＃xff09;30天之内的订单&＃xff0c;那么便返回 1 WHEN order_date >&＃61; DATE_SUB(&＃39;2017-01-01&＃39;, 29) AND order_date <&＃61; &＃39;2017-01-01&＃39; THEN 1 END) dat_30,(CASE //如果order_date订单时间 大于“2017-01-01减去59天”并且小于“2017-01-01”的话&＃xff0c;那么表示该订单便是&＃xff08;dat_60&＃xff09;60天之内的订单&＃xff0c;那么便返回 1 WHEN order_date >&＃61; DATE_SUB(&＃39;2017-01-01&＃39;, 59) AND order_date <&＃61; &＃39;2017-01-01&＃39; THEN 1 END) dat_60,(CASE //如果order_date订单时间 大于“2017-01-01减去89天”并且小于“2017-01-01”的话&＃xff0c;那么表示该订单便是&＃xff08;dat_90&＃xff09;90天之内的订单&＃xff0c;那么便返回 1 WHEN order_date >&＃61; DATE_SUB(&＃39;2017-01-01&＃39;, 89) AND order_date <&＃61; &＃39;2017-01-01&＃39; THEN 1 END) dat_90,(CASE //退货标识&＃xff1a;3。拒收标识&＃xff1a;4WHEN a.order_status IN (3, 4) THEN 1 ELSE 0 END) order_flag, --退货与拒收标示HOUR(order_date) order_hour //订单下单时的具体哪个小时&＃xff0c;用于标识是上午、下午、晚上、凌晨下单FROM gdm.itcast_gdm_order a WHERE dt &＃61; &＃39;2017-01-01&＃39;) t LEFT JOIN (SELECT order_id, //订单IDgoods_amount //商品数量FROMfdm.itcast_fdm_order_goods) t1ON (t.order_id &＃61; t1.order_id) LEFT JOIN (SELECT user_id, //用户IDorder_addr //订单地址&＃xff1a;是在哪里下单的FROMgdm.itcast_gdm_user_order_addr_model) t2 ON (t.user_id &＃61; t2.user_id) GROUP BY t.user_id ;
----------------------------------------
客户消费订单模型表-临时表02&＃xff1a;统计购物车相关指标 GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09; 1.MySQL表 购物车表 数据 分为 2种&＃xff1a;1.当前仍在购买车中的商品2.已经不在购买车中的商品(包括 被移除的商品、成功交易的商品)2.MySQL表 购物车表中 每个商品的 重要字段结构&＃xff1a;商品添加进购物车的时间商品移出购物车的时间商品成功支付的时间商品名(SKU名)商品数量3.Hive表 每天定时所统计的购物车表源数据中 分为 3种&＃xff1a;1.当天购买车中 等待支付的商品2.当天购买车中 被移除的商品3.当天购买车中 成功交易的商品4.第一种统计版本&＃xff08;上课时所使用的版本&＃xff09;&＃xff1a;CREATE TABLE 表名 AS SELECT FROM 表名 WHERE dt&＃61;昨天 and (前第30天 <&＃61; add_time <&＃61; 昨天) GROUP BY user_idSQL目的&＃xff1a;仅统计 近30天之内 被添加进购物车的商品&＃xff0c;其中包含等待支付的商品、被移除的商品、成功交易的商品&＃xff0c;但不统计 30天之前 已经被添加进购物车的商品。5.第二种统计版本&＃xff08;推荐实际使用的版本&＃xff09;CREATE TABLE 表名 AS SELECT FROM 表名 WHERE dt&＃61;昨天 GROUP BY user_idSQL目的&＃xff1a;只要是被添加进购物车的商品都进行统计&＃xff0c;而不仅限 近30天之内 被添加进购物车的商品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天购物车次数 //COUNT(1)&＃xff1a;统计购物车中的 不同商品种类数&＃xff08;SKU数&＃xff09;SUM(goods_num) month1_cart_goods_cnt, --最近30天购物车商品件数//SUM()&＃xff1a;累加购物车中所有商品总数SUM( //SUM()&＃xff1a;累加购物车中所提交的成功支付的商品总数CASEWHEN sumbit_time <> &＃39;&＃39; // <> &＃39;&＃39; 表示不等于空字符串&＃xff0c;即商品提交时间不等于THEN goods_num ELSE 0 END) month1_cart_submit_cnt, --最近30天购物车提交的成功支付商品件数SUM(//通过商品提交的数量除以购物车中商品总数得出购物车中商品成交的成功率CASEWHEN sumbit_time <> &＃39;&＃39; THEN goods_num ELSE 0 END)/SUM(goods_num) month1_cart_rate, --最近30天购物车成功率 //商品提交的成功支付的数量除以购物车中商品总数SUM( //SUM()&＃xff1a;累加购物车中所取消商品的总数CASEWHEN cancle_time <> &＃39;&＃39; THEN goods_num ELSE 0 END) month1_cart_cancle_cnt --最近30天购物车放弃件数(所移除的商品总数) FROMfdm.itcast_fdm_order_cart WHERE dt &＃61; &＃39;2017-01-01&＃39; //时间日期转化函数to_date&＃xff1a;可把字符串时间转换为日期类型时间。//添加商品时间 大于“2017-01-01减去29天”并且小于“2017-01-01”的话&＃xff0c;那么表示该商品便是&＃xff08;dat_30&＃xff09;30天之内添加到购物车中的商品AND to_date (add_time) >&＃61; DATE_SUB(&＃39;2017-01-01&＃39;, 29) AND to_date (add_time) <&＃61; &＃39;2017-01-01&＃39; GROUP BY user_id ;
----------------------------------------
客户消费订单模型表-临时表03&＃xff1a;统计常用收货地址和常用支付方式 GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09;drop table if exists gdm.itcast_gdm_user_consume_order_temp_03;create table gdm.itcast_gdm_user_consume_order_temp_03临时表 as select ... from (select ... from (select ... from gdm.itcast_gdm_order订单宽表 where dt &＃61; &＃39;2017-01-01&＃39; group by ...union all select ... from gdm.itcast_gdm_order订单宽表 where dt &＃61; &＃39;2017-01-01&＃39; group by ...) ) ;//下面长长的SQL简化为上面的SQL结构&＃xff0c;下面的SQL目的&＃xff1a;取出每个收货地址分组中使用最多的收货地址&＃xff0c;或每个支付方式分组中使用最多的支付方式create table gdm.itcast_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, //type&＃xff1a;代表收货地址address或支付类型pay_type。别名rn 表示每个分组中的顺序序号&＃xff0c;从1开始。row_number() over(distribute by b.user_id, b.type //把相同用户id和相同收货地址address分为一组&＃xff0c;或把相同用户id和相同支付类型pay_type分为一组sort by b.cnt, b.type desc) rn //先按照使用次数cnt进行降序排序&＃xff0c;如果使用次数cnt相同则按照type收货地址或支付类型进行降序排序from(select //该SQL目的&＃xff1a;统计每个用户对应所使用的每个收货地址的使用次数 //coalesce(字段, &＃39;&＃39;) 如果字段列的值为null空的话&＃xff0c;则替换为空字符串a.user_id, concat(coalesce(area_name, &＃39;&＃39;), coalesce(address, &＃39;&＃39;)) con, //concat 把多个字段列数据 拼接为 一个字符串&＃39;address&＃39; type, //给收货地址字段address取别名为type count(1) cnt //统计每个用户对应所使用的每个收货地址的使用次数from gdm.itcast_gdm_order a where dt &＃61; &＃39;2017-01-01&＃39; //对每个用户进行分组的同时&＃xff0c;还对每个用户所对应使用的每个收货地址进行分组&＃xff0c;意思即把相同用户id和相同收货地址分为一组group by a.user_id, concat(coalesce(area_name, &＃39;&＃39;), coalesce(address, &＃39;&＃39;))union all //合并两个或多个 SELECT 语句的结果集&＃xff0c;并且不会对合并后的结果集进行去重select //该SQL目的&＃xff1a;统计每个用户对应所使用的每个支付方式的使用次数a.user_id, a.pay_type con,&＃39;pay_type&＃39; type, //给支付类型字段pay_type取别名为typecount(1) cnt //统计每个用户对应所使用的每个支付方式的使用次数from gdm.itcast_gdm_order a where dt &＃61; &＃39;2017-01-01&＃39; //对每个用户进行分组的同时&＃xff0c;还对每个用户所对应使用的每个支付方式进行分组&＃xff0c;意思即把相同用户id和相同支付方式分为一组 group by a.user_id, a.pay_type ) b) t where t.rn &＃61; 1 ; //别名rn 表示每个分组中的顺序序号&＃xff0c;从1开始。此处取出每个收货地址分组中使用最多的收货地址&＃xff0c;或每个支付方式分组中使用最多的支付方式注意&＃xff1a;select a.user_id, concat(coalesce(area_name, &＃39;&＃39;), coalesce(address, &＃39;&＃39;)) con, &＃39;address&＃39; type, count(1) cnt group by a.user_id, concat(coalesce(area_name, &＃39;&＃39;), coalesce(address, &＃39;&＃39;)) union all select a.user_id, a.pay_type con, &＃39;pay_type&＃39; type, count(1) cnt group by a.user_id, a.pay_type 上面的SQL语句解析如下&＃xff1a; select 用户id,拼接收货地址,收货地址,收货地址的使用次数 from 订单宽表 group by 用户id,拼接收货地址union all select 用户id,支付方式,支付方式,支付方式的使用次数 from 订单宽表 group by 用户id,支付方式解析&＃xff1a;为的是使用union all把两张表合并时&＃xff0c;把收货地址字段address和支付类型字段pay_type都合并为一列&＃xff0c;取该列名为type&＃xff0c;那么便可以直接对type字段进行分组&＃xff0c;也即是对收货地址字段address和支付类型字段pay_type同时进行各自的分组。
----------------------------------------
订单表 和 购物车表 整合&＃xff08;临时表&＃xff09;目的&＃xff1a;仅为合并两表中所相同的的user_id为一个分组&＃xff0c;并且最终合并结果中的每条数据均为不同的user_iddrop table if exists gdm.itcast_gdm_user_consume_order_temp_100;create table gdm.itcast_gdm_user_consume_order_temp_100 as select a.user_id from(select user_id from gdm.itcast_gdm_user_consume_order_temp_01 //统计 订单 相关指标的 临时表01union all select user_id from gdm.itcast_gdm_user_consume_order_temp_02 //统计 购物车 相关指标的 临时表02) a group by a.user_id ;
客户消费模型表&＃xff08;订单&＃43;购物车&＃xff09; GDM层SQL目的&＃xff1a;统计 订单 和 购物车 中相关消费指标。当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据&＃xff0c;因此还必须根据 where dt >&＃61; date_add(昨天日期时间, -29/-60/-90) 条件进行统计&＃xff0c; 这样才能统计出真正的“近30/60/90天的XX”指标数据。客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order&＃xff08;时间分区&＃xff09;客户消费模型表 GDM层 &＃61; 临时表01 &＃43; 临时表02 &＃43; 临时表03 &＃43; 临时表04
----------------------------------------
客户消费订单表模型 GDM 层&＃xff08;对 FDM 层数据 进行统计一些指标数据&＃xff09;SQL目的&＃xff1a;统计 订单 和 购物车 中相关消费指标。当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据&＃xff0c;因此还必须根据 where dt >&＃61; date_add(昨天日期时间, -29/-60/-90) 条件进行统计&＃xff0c; 这样才能统计出真正的“近30/60/90天的XX”指标数据。 create database if not exists gdm; create table if not exists gdm.itcast_gdm_user_consume_order(user_id string, --客户IDfirst_order_time timestamp, --第一次消费时间last_order_time timestamp, --最近一次消费时间first_order_ago bigint, --首单距今时间last_order_ago bigint, --尾单距今时间month1_hg_order_cnt bigint, --近30天购买次数&＃xff08;不含退拒&＃xff09;month1_hg_order_amt double, --近30天购买金额&＃xff08;不含退拒&＃xff09;month2_hg_order_cnt bigint, --近60天购买次数&＃xff08;不含退拒&＃xff09;month2_hg_order_amt double, --近60天购买金额&＃xff08;不含退拒&＃xff09;month3_hg_order_cnt bigint, --近90天购买次数&＃xff08;不含退拒&＃xff09;month3_hg_order_amt double, --近90天购买金额&＃xff08;不含退拒&＃xff09;month1_order_cnt bigint, --近30天购买次数&＃xff08;含退拒&＃xff09;month1_order_amt double, --近30天购买金额&＃xff08;含退拒&＃xff09;month2_order_cnt bigint, --近60天购买次数&＃xff08;含退拒&＃xff09;month2_order_amt double, --近60天购买金额&＃xff08;含退拒&＃xff09;month3_order_cnt bigint, --近90天购买次数&＃xff08;含退拒&＃xff09;month3_order_amt double, --近90天购买金额&＃xff08;含退拒&＃xff09;max_order_amt double, --最大消费金额min_order_amt double, --最小消费金额total_order_cnt bigint, --累计消费次数&＃xff08;不含退拒&＃xff09;total_order_amt double, --累计消费金额&＃xff08;不含退拒&＃xff09;user_avg_amt double, --客单价&＃xff08;含退拒&＃xff09;//客单价表示每个订单平均价格&＃xff0c;通过所有商品总价格除以总订单数得出每个订单平均价格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天购物车成功率&＃xff0c;通过上面的 购物车提交商品总件数 除以 购物车商品总件数 得出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);
----------------------------------------
//加载数据到 GDM层的 客户消费订单表INSERT overwrite TABLE gdm.itcast_gdm_user_consume_order PARTITION (dt &＃61; &＃39;2017-01-01&＃39;) SELECT t.user_id, --客户IDt1.first_order_time, --第一次消费时间t1.last_order_time, --最近一次消费时间t1.first_order_ago, --首单距今时间t1.last_order_ago, --尾单距今时间t1.month1_hg_order_cnt, --近30天购买次数&＃xff08;不含退拒&＃xff09;t1.month1_hg_order_amt, --近30天购买金额&＃xff08;不含退拒&＃xff09;t1.month2_hg_order_cnt, --近60天购买次数&＃xff08;不含退拒&＃xff09;t1.month2_hg_order_amt, --近60天购买金额&＃xff08;不含退拒&＃xff09;t1.month3_hg_order_cnt, --近90天购买次数&＃xff08;不含退拒&＃xff09;t1.month3_hg_order_amt, --近90天购买金额&＃xff08;不含退拒&＃xff09;t1.month1_order_cnt, --近30天购买次数&＃xff08;含退拒&＃xff09;t1.month1_order_amt, --近30天购买金额&＃xff08;含退拒&＃xff09;t1.month2_order_cnt, --近60天购买次数&＃xff08;含退拒&＃xff09;t1.month2_order_amt, --近60天购买金额&＃xff08;含退拒&＃xff09;t1.month3_order_cnt, --近90天购买次数&＃xff08;含退拒&＃xff09;t1.month3_order_amt, --近90天购买金额&＃xff08;含退拒&＃xff09;t1.max_order_amt, --最大消费金额t1.min_order_amt, --最小消费金额t1.total_order_cnt, --累计消费次数&＃xff08;不含退拒&＃xff09;t1.total_order_amt, --累计消费金额&＃xff08;不含退拒&＃xff09;t1.user_avg_amt, --客单价&＃xff08;含退拒&＃xff09;(CASEWHEN t1.month3_order_cnt <> 0 // <> 0 表示 不等于0THEN t1.month3_order_amt / t1.month3_order_cnt // 近90天购买金额 除以 近90天购买次数 &＃61; 近90天的客单价(含退拒)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天购物车提交商品件数t2.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 // 作为操作时间&＃xff1a;返回 Unix时间戳(Unix timestamp)&＃xff0c;表示将 Unix 时间戳字符串进行格式化FROM gdm.itcast_gdm_user_consume_order_temp_100 t LEFT JOIN gdm.itcast_gdm_user_consume_order_temp_01 t1 //统计 订单 相关指标的 临时表01ON (t.user_id &＃61; t1.user_id) LEFT JOIN gdm.itcast_gdm_user_consume_order_temp_02 t2 //统计 购物车 相关指标的 临时表02ON (t.user_id &＃61; t2.user_id) LEFT JOIN (SELECT user_id,MAX(CASEWHEN type &＃61; &＃39;address&＃39; THEN con END) common_address,MAX(CASEWHEN type &＃61; &＃39;pay_type&＃39; THEN con END) common_paytype FROM gdm.itcast_gdm_user_consume_order_temp_03 //统计 常用收货地址和常用支付方式 的临时表03group by user_id) t3 ON (t.user_id &＃61; t3.user_id);
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有