大数据项目之电商数仓（电商数据仓库系统）

写SQL的规律
第 1 章 数仓分层
1.1 为什么要分层
1.2 数据集市与数据仓库概念
1.3 数仓命名规范
1.3.1 表命名
1.3.2 脚本命名
第 2 章 数仓理论
2.1 范式理论
2.1.1 范式概念
2.1.2 函数依赖
2.1.3 三范式区分
2.2 关系建模与维度建模
2.2.1 关系建模
2.2.2 维度建模
2.3 维度表和事实表（重点）
2.3.1 维度表
2.3.2 事实表
2.4 数据仓库建模（绝对重点）
2.4.1 ODS 层
2.4.2 DWD 层
2.4.3 DWS 层
2.4.4 DWT 层
2.4.5 ADS 层
第 3 章 数仓搭建-ODS 层
3.1 创建数据库
3.2 ODS 层（用户行为数据）
3.2.1 创建启动日志表 ods_start_log
3.2.2 创建事件日志表 ods_event_log
3.2.3 Shell 中单引号和双引号区别
3.2.4 ODS 层加载数据脚本
3.3 ODS 层（业务数据）
3.3.1 订单表（增量及更新）
3.3.2 订单详情表（增量）
3.3.3 SKU 商品表（全量）
3.3.4 用户表（增量及更新）
3.3.5 商品一级分类表（全量）
3.3.6 商品二级分类表（全量）
3.3.7 商品三级分类表（全量）
3.3.8 支付流水表（增量）
3.3.9 省份表（特殊）
3.3.10 地区表（特殊）
3.3.11 品牌表（全量）
3.3.12 订单状态表（增量）
3.3.13 SPU 商品表（全量）
3.3.14 商品评论表（增量）
3.3.15 退单表（增量）
3.3.16 加购表（全量）
3.3.17 商品收藏表（全量）
3.3.18 优惠券领用表（新增及变化）
3.3.19 优惠券表（全量）
3.3.20 活动表（全量）
3.3.21 活动订单关联表（增量）
3.3.22 优惠规则表（全量）
3.3.23 编码字典表（全量）
3.3.24 ODS 层加载数据脚本
总结：
第 4 章 数仓搭建-DWD 层
4.1 DWD 层（用户行为启动表数据解析）
4.1.1 创建启动表
4.1.2 get_json_object 函数使用
4.1.3 向启动表导入数据
4.1.4 DWD 层启动表加载数据脚本
4.2 DWD 层（用户行为事件表数据解析） ****
4.2.1 创建基础明细表
4.2.2 自定义 UDF 函数（解析公共字段）
4.2.3 自定义 UDTF 函数（解析事件字段）
4.2.4 解析事件日志基础明细表
4.2.5 DWD 层数据解析脚本
4.3 DWD 层（用户行为事件表获取）
4.3.1 商品点击表
4.3.2 商品详情页表
4.3.3 商品列表页表
4.3.4 广告表
4.3.5 消息通知表
4.3.6 用户后台活跃表
4.3.7 评论表
4.3.8 收藏表
4.3.9 点赞表
4.3.10 错误日志表
4.3.11 DWD 层事件表加载数据脚本
4.4 DWD 层（业务数据）
4.4.1 商品维度表（全量表）
4.4.2 优惠券信息表（全量）
4.4.3 活动维度表（全量）
4.4.4 地区维度表（特殊）
4.4.5 时间维度表（特殊）（预留）
4.4.6 订单明细事实表（事务型快照事实表）
4.4.7 支付事实表（事务型快照事实表）
4.4.8 退款事实表（事务型快照事实表）
4.4.9 评价事实表（事务型快照事实表）
4.4.10 加购事实表（周期型快照事实表，每日快照）
4.4.11 收藏事实表（周期型快照事实表，每日快照）
4.4.12 优惠券领用事实表（累积型快照事实表）
4.4.13 订单事实表（累积型快照事实表）
4.4.14 用户维度表（拉链表）
步骤 0：初始化拉链表（首次独立执行）
步骤 2：先合并变动信息，再追加新增信息，插入到临时表中
步骤 3：把临时表覆盖给拉链表
4.4.15 DWD 层数据导入脚本
第 5 章 数仓搭建-DWS 层
5.1 业务术语
5.2 系统函数
5.2.1 collect_set 函数
5.2.2 nvl 函数
5.2.3 日期处理函数
5.3 DWS 层（用户行为）
5.3.1 每日设备行为
5.4 DWS 层（业务）
5.4.1 每日会员行为
5.4.2 每日商品行为
5.4.3 每日优惠券统计（预留）
5.4.4 每日活动统计（预留）
5.4.5 每日购买行为
5.5 DWS 层数据导入脚本
第 6 章 数仓搭建-DWT 层
6.1 设备主题宽表
6.2 会员主题宽表
6.3 商品主题宽表
6.4 优惠券主题宽表（预留）
6.5 活动主题宽表（预留）
6.6 DWT 层数据导入脚本
第 7 章 数仓搭建-ADS 层
7.1 设备主题
7.1.1 活跃设备数（日、周、月）
7.1.2 每日新增设备
7.1.3 沉默用户数
7.1.4 本周回流用户数
7.1.5 流失用户数
7.1.6 留存率
7.1.7 最近连续三周活跃用户数
7.1.8 最近七天内连续三天活跃用户数
7.2 会员主题
7.2.1 会员主题信息
7.2.2 漏斗分析(由于事实表不全所以所有数据从dws取)
7.3 商品主题
7.3.1 当天商品个数信息
7.3.2 当天商品销量排名top10
7.3.3 当天商品收藏排名top10
7.3.4 当天商品加入购物车个数排名top10
7.3.5 商品退款率排名(最近 30 天)
7.3.6 当天商品差评率
7.4 营销主题（用户+商品+购买行为）
7.4.1 当天下单数目统计
7.4.2 当天支付信息统计
7.4.3 各一级品类下月品牌复购率
7.5 ADS 层导入脚本
第 8 章 Azkaban 调度
8.1 Azkaban 部署
8.1.1 安装前准备
8.1.2 安装 Azkaban
8.1.3 生成密钥库
8.1.4 时间同步配置
8.1.5 配置文件
8.1.5.1 Web server服务器配置
8.1.5.2 执行服务器配置
8.1.6 启动 executor 服务器
8.1.7 启动 web server服务器
8.2 创建 MySQL 数据库和表
8.3 Sqoop 导出脚本
8.4 会员主题指标获取的全调度流程
写SQL的规律
1、先找到目标表
2、分析一下，需要哪些表能满足目标表的所有字段
即准备所有输入表。
3、写逻辑
3.1 insert overwrite table 目标包名称
观察目标表是否需要考虑分区
3.2 固定格式，如果需要多表join,
先把整体大框写出，再具体去写一个一个子查询

select

from
(

)b1
join
(

)b2
on
3.3 遇到统计什么次数用count
3.4 遇到统计什么金额用sum
3.5 如果是累积表，获取旧表（目标表）数据，
再获取新表（输入表）数据

3.6 遇到统计累积值，旧的 + 新的
3.7 累积表中获取首次时间
旧的时间为null,取当前时间，否则取旧的
3.8 累积表中获取末次时间（最近时间）
新的id不为空，取当前时间，否则取旧的
3.9 天数和次数的转换。
if(new.login_count>0,1,0)
3.10 使用group by时要注意：
查询的字段：1.分组里面有；2.常量；3.聚合函数
3.11 累积30天等指标
在新数据表new中进行累加 where最近30天

3.12 如果涉及的表比较多可以采用with tmp的方法

下载地址:
大数据项目之电商数仓（电商数据仓库系统