========================================
数据仓库分层:
BDM 层(源数据层):缓冲数据,源数据的直接映射
FDM 层(对 BDM 源数据层的数据进行清洗和预处理):基础数据层,数据拉链处理、分区处理
GDM 层(对 FDM 层数据 进行统计一些指标数据):通用聚合
ADM 层(对 GDM 层指标数据 进行指标数据的业务汇总分析):高度聚合
---------------------------------------------------------------------
1.客户基本属性表
SQL目的:时间分区中每天的数据应为每天新增用户和每天访问用户
1.客户基本属性表 BDM层 bdm.itcast_bdm_user(时间分区)
2.客户基本属性表 FDM层 fdm.itcast_fdm_user_wide(时间分区)
3.客户基本属性表 GDM层 gdm.itcast_gdm_user_basic(时间分区)
2.订单表
1.订单 主要信息表
1.订单 主要信息表 BDM层 bdm.itcast_bdm_order(时间分区)
2.订单 主要信息表 FDM层 fdm.itcast_fdm_order(时间分区)
2.订单 详细信息表
1.订单 详细信息表 BDM层 bdm.itcast_bdm_order_desc(时间分区)
2.订单 详细信息表 FDM层 fdm.itcast_fdm_order_desc(时间分区)
3.订单表 GDM层
订单宽表 GDM层 gdm.itcast_gdm_order(时间分区)
订单宽表 GDM层 = 订单 主要信息表 FDM层 + 订单 详细信息表 FDM层
3.订单中 商品信息表
1.订单中 商品信息表 BDM层 bdm.itcast_bdm_order_goods(时间分区)
2.订单中 商品信息表 FDM层 fdm.itcast_fdm_order_goods(时间分区)
4.客户订单地址表
客户订单地址表 GDM层 gdm.itcast_gdm_user_order_addr_model
5.购物车中 商品信息表
1.购物车中 商品信息表 BDM层 bdm.itcast_bdm_order_cart(时间分区)
2.购物车中 商品信息表 FDM层 fdm.itcast_fdm_order_cart(时间分区)
6.客户消费模型表(订单+购物车) GDM层(临时表)
没有时间分区,但是每次都会先 drop 临时表,再 CREATE 临时表 as select ...
1.临时表01(订单)
客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_01(临时表01)统计订单相关指标
客户消费模型表 GDM层 = 订单表 GDM层(时间分区) + 订单中商品信息表 FDM层(时间分区) + 客户订单地址表 GDM层
2.临时表02(购物车)
客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_02(临时表02) 统计购物车相关指标
客户消费模型表 GDM层 = 购物车中商品信息表 FDM层(时间分区)
3.临时表03(订单)
客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order_temp_03(临时表03) 统计订单中常用收货地址和常用支付方式
客户消费模型表 GDM层 = 订单表 GDM层(时间分区)+ 订单表 GDM层(时间分区)(订单表自身和自身union all)
4.临时表04
gdm.itcast_gdm_user_consume_order_temp_100(临时表04) = 客户消费模型表 GDM层(临时表01) + 客户消费模型表 GDM层(临时表02)
目的:仅为合并 订单表 和 购物车表 中所相同的的user_id为一个分组,并且最终合并结果中的每条数据均为不同的user_id
7.客户消费模型表(订单+购物车) GDM层
SQL目的:统计 订单 和 购物车 中相关消费指标。
当前该表中的“近30/60/90天的XX”等指标 仅为时间分区中每天的指标数据,因此还必须根据 where dt >= date_add(昨天日期时间, -29/-60/-90) 条件进行统计,
这样才能统计出真正的“近30/60/90天的XX”指标数据。
客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order(时间分区)
客户消费模型表 GDM层 = 临时表01 + 临时表02 + 临时表03 + 临时表04
8.订单中 商品类目表 GDM层(临时表)
SQL目的:统计用户订单中所消费的商品分类情况,即可得知用户偏向于消费何种分类的商品
没有时间分区,但是每次都会先 drop 临时表,再 CREATE 临时表 as select ...
订单中 商品类目表 GDM层 gdm.itcast_gdm_user_buy_category_temp(临时表)
订单中 商品类目表 GDM层 = 订单 主要信息表 FDM层(时间分区) + 订单中商品信息表 FDM层(时间分区)
9.购物车中 商品类目表 GDM层(临时表)
SQL目的:统计用户购物车中成功支付消费的商品分类情况,即可得知用户偏向于消费何种分类的商品
没有时间分区,但是每次都会先 drop 临时表,再 CREATE 临时表 as select ...
购物车中 商品类目表 GDM层 gdm.itcast_gdm_user_cart_category_temp(临时表)
购物车中 商品类目表 GDM层 = 购物车中商品信息表 FDM层(时间分区) + 订单中商品信息表 FDM层(时间分区)
10.商品类目码表 GDM层(1级/2级/3级分类详细)
商品类目码表 GDM层 gdm.itcast_gdm_category_code
11.客户消费商品的 每级分类的 类目详细表(订单+购物车) GDM层(临时表)(客户喜好消费的商品分类)
SQL目的:根据三级分类ID得出二级分类和一级分类的ID和名称
没有时间分区,但是每次都会先 drop 临时表,再 CREATE 临时表 as select ...
客户消费商品的 每级分类的 类目详细表 GDM层 gdm.itcast_gdm_user_category_total(临时表)
客户消费商品的 每级分类的 类目详细表 GDM层 = 订单中 商品类目表 GDM层(临时表)+ 购物车中 商品类目表 GDM层(临时表)+ 商品类目码表 GDM层
12.客户消费商品的 每级分类的 类目总表(订单+购物车) 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层(临时表)
13.用户上网轨迹表
SQL目的:以同一个用户ID作为同一个分组的情况下,同时可根据session_id和cookie_id的不同,计算出该同一用户当天的访问次数和每次访问的停留时长
1.用户上网轨迹表 BDM层(PC端/网页端)
用户上网轨迹表 BDM层 bdm.itcast_bdm_user_pc_click_log(时间分区)
用户上网轨迹表 FDM层 fdm.itcast_fdm_user_pc_pageview(时间分区)
2.用户上网轨迹表 BDM层(APP端)
用户上网轨迹表 BDM层 bdm.itcast_bdm_user_app_click_log(时间分区)
用户上网轨迹表 FDM层 fdm.itcast_fdm_user_app_pageview(时间分区)
14.近30天 PC端/网页端 访问最常用的指标(临时表)
SQL目的:在同一个用户ID分组下,可对应多个不同 ip/cookie/浏览器名/系统名 的分组的情况下,统计出近30天之内的使用次数情况
统计近30天之内指标的方式:where dt >= date_add(昨天日期时间, -29)
没有时间分区,但是每次都会先 drop 临时表,再 CREATE 临时表 as select ...
近30天 PC端 访问最常用的指标 GDM层 gdm.itcast_gdm_user_visit_temp_01(临时表)
近30天 PC端 访问最常用的指标 GDM层 = 用户上网轨迹表 FDM层(时间分区)
15.用户访问模型表 GDM层
用户访问模型表 GDM层 gdm.itcast_gdm_user_visit(时间分区)
用户访问模型表 GDM层 = 客户基本属性表 FDM层(时间分区)
+ 用户上网轨迹表(PC端/网页端) FDM层(无需时间分区,因为进行了时间日期比较)
+ 近30天 PC端 访问最常用的指标 GDM层(临时表)
+ 用户上网轨迹表(APP端) FDM层(无需时间分区,因为进行了时间日期比较)
16.用户画像宽表 ADM层
每天汇总出的用户画像表仅包含当天的数据,因此还需要和之前汇总好的用户画像表进行新的合并操作后,才算真正的最新数据的用户画像表
用户画像宽表 ADM层 adm.itcast_adm_personas(时间分区)
用户画像宽表 ADM层 = 用户基本属性表 GDM层 gdm.itcast_gdm_user_basic(时间分区)(第 1 张表)
+ 客户消费模型表 GDM层 gdm.itcast_gdm_user_consume_order(时间分区)(第 7 张表 = 2 + 3 + 4 + 5 + 6)
+ 客户喜好消费的商品分类模型表 GDM层 gdm.itcast_gdm_user_buy_category(时间分区)(第 12 张表 = 8 + 9 + 10 + 11)
+ 用户访问模型表 GDM层 gdm.itcast_gdm_user_visit(时间分区)(第 15 张表 = 13 + 14)
17.用户标签表
可根据 客户消费模型表 + 客户喜好消费的商品分类模型表 + 用户访问模型表 分析出 该人的购买喜好、购买习惯、购买实力,从而推荐相似的商品给该用户