DM
名称：数据集市层 Data Market
功能：基于DWS层日统计宽表，上卷出周、月、年等统计宽表，即粗粒度汇总。
解释
从理论层面来说，数据集市是一个小型的部门或工作组级别的数据仓库。
一些公司早期的数据集市后期可能会演变成为数仓系统。
本项目中在数据集市层面主要进行粗粒度汇总，也可以将这些功能下放至DWS层完成。抛弃DM.
使用DataGrip在Hive中创建dm层
注意，对于建库建表操作，需直接使用Hive，因为Presto只是一个数据分析的引擎，其语法不一定支持直接在Hive中建库建表。
知识点03：DM层搭建--销售主题统计宽表--建模
概述
DM层销售主题宽表，基于DWS层销售主题日统计宽表的值，上卷统计出年、月、周的数据。
指标和DWS一致。
指标
销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量
维度
日期:天(已经统计过), 周, 月, 年
日期+城市
日期+商圈
日期+店铺
日期+品牌
日期+大类
日期+中类
日期+小类
建表
整个表和DWS层销售主题统计宽表dws_sale_daycount的区别就在于多了开头的时间粒度字段。
用于标识后面的指标是哪个时间粒度统计出来的指标。
知识点04：DM层搭建--销售主题统计宽表--表关系梳理
销售主题各种指标的数据支撑
dws_sale_daycount
时间粒度的数据支撑
dwd.dim_date 时间维表
企业中，时间维表数据是怎么维护的呢?
1、维护频率：一次性生成1年或者多年的时间数据。
2、使用java、Python代码实现数据的生成。
关联条件
yp_dws.dws_sale_daycount dc
left join yp_dwd.dim_date d on dc.dt = d.date_code
知识点05：DM层搭建--销售主题统计宽表--按年统计
在dws层已经统计出天的指标数据了，现在需要在其之上上卷计算出周、月、年的数据。
这里并不是简单的分组+sum求和即可，需要考虑到分组的类别。
step1：确定分组字段
年
年+城市
年+商圈
年+店铺
年+品牌
年+大类
年+中类
年+小类
（对于销售主题 在DM层 分析的维度不变 时间的粒度改变）
group by
grouping sets (
(d.year_code),
(d.year_code, city_id, city_name),
(d.year_code, city_id, city_name, trade_area_id, trade_area_name),
(d.year_code, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name),
(d.year_code, brand_id, brand_name),
(d.year_code, max_class_id, max_class_name),
(d.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name),
(d.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name))
;
step2：分组聚合
-- 统计值
sum(dc.sale_amt) as sale_amt,
sum(dc.plat_amt) as plat_amt,
sum(dc.deliver_sale_amt) as deliver_sale_amt,
sum(dc.mini_app_sale_amt) as mini_app_sale_amt,
sum(dc.android_sale_amt) as android_sale_amt,
sum(dc.ios_sale_amt) as ios_sale_amt,
sum(dc.pcweb_sale_amt) as pcweb_sale_amt,
sum(dc.order_cnt) as order_cnt,
sum(dc.eva_order_cnt) as eva_order_cnt,
sum(dc.bad_eva_order_cnt) as bad_eva_order_cnt,
sum(dc.deliver_order_cnt) as deliver_order_cnt,
sum(dc.refund_order_cnt) as refund_order_cnt,
sum(dc.miniapp_order_cnt) as miniapp_order_cnt,
sum(dc.android_order_cnt) as android_order_cnt,
sum(dc.ios_order_cnt) as ios_order_cnt,
sum(dc.pcweb_order_cnt) as pcweb_order_cnt
step3：返回字段细节处理
--统计日期，不是分组的日期 所谓统计就是记录你哪天干的这个活
'2022-01-11' date_time,
'year' time_type,
year_code,
-- year_month,
-- month_code,
-- day_month_num,
-- dim_date_id,
-- year_week_name_cn,
-- 产品维度类型：store，trade_area，city，brand，min_class，mid_class，max_class，all
CASE WHEN grouping(dc.city_id, dc.trade_area_id, dc.store_id)=0
THEN 'store'
WHEN grouping(dc.city_id, dc.trade_area_id)=0
THEN 'trade_area'
WHEN grouping(dc.city_id)=0
THEN 'city'
WHEN grouping(dc.brand_id)=0
THEN 'brand'
WHEN grouping(dc.max_class_id, dc.mid_class_id, dc.min_class_id)=0
THEN 'min_class'
WHEN grouping(dc.max_class_id, dc.mid_class_id)=0
THEN 'mid_class'
WHEN grouping(dc.max_class_id)=0
THEN 'max_class'
ELSE 'all'
END as group_type,
step4：最终完整sql
--按年统计，销售主题指标
select
--统计日期，不是分组的日期 所谓统计就是记录你哪天干的这个活
'2022-01-11' date_time,
'year' time_type,
year_code,
-- year_month,
-- month_code,
-- day_month_num,
-- dim_date_id,
-- year_week_name_cn,
-- 产品维度类型：store，trade_area，city，brand，min_class，mid_class，max_class，all
CASE WHEN grouping(dc.city_id, dc.trade_area_id, dc.store_id)=0
THEN 'store'
WHEN grouping(dc.city_id, dc.trade_area_id)=0
THEN 'trade_area'
WHEN grouping(dc.city_id)=0
THEN 'city'
WHEN grouping(dc.brand_id)=0
THEN 'brand'
WHEN grouping(dc.max_class_id, dc.mid_class_id, dc.min_class_id)=0
THEN 'min_class'
WHEN grouping(dc.max_class_id, dc.mid_class_id)=0
THEN 'mid_class'
WHEN grouping(dc.max_class_id)=0
THEN 'max_class'
ELSE 'all'
END as group_type,
city_id,
city_name,
trade_area_id,
trade_area_name,
store_id,
store_name,
brand_id,
brand_name,
max_class_id,
max_class_name,
mid_class_id,
mid_class_name,
min_class_id,
min_class_name,
sum(dc.sale_amt) as sale_amt,
sum(dc.plat_amt) as plat_amt,
sum(dc.deliver_sale_amt) as deliver_sale_amt,
sum(dc.mini_app_sale_amt) as mini_app_sale_amt,
sum(dc.android_sale_amt) as android_sale_amt,
sum(dc.ios_sale_amt) as ios_sale_amt,
sum(dc.pcweb_sale_amt) as pcweb_sale_amt,
sum(dc.order_cnt) as order_cnt,
sum(dc.eva_order_cnt) as eva_order_cnt,
sum(dc.bad_eva_order_cnt) as bad_eva_order_cnt,
sum(dc.deliver_order_cnt) as deliver_order_cnt,
sum(dc.refund_order_cnt) as refund_order_cnt,
sum(dc.miniapp_order_cnt) as miniapp_order_cnt,
sum(dc.android_order_cnt) as android_order_cnt,
sum(dc.ios_order_cnt) as ios_order_cnt,
sum(dc.pcweb_order_cnt) as pcweb_order_cnt
from yp_dws.dws_sale_daycount dc
left join yp_dwd.dim_date d
on dc.dt = d.dim_date_id
group by
grouping sets (
(d.year_code),
(d.year_code, city_id, city_name),
(d.year_code, city_id, city_name, trade_area_id, trade_area_name),
(d.year_code, city_id, city_name, trade_area_id, trade_area_name, store_id, store_name),
(d.year_code, brand_id, brand_name),
(d.year_code, max_class_id, max_class_name),
(d.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name),
(d.year_code, max_class_id, max_class_name,mid_class_id, mid_class_name,min_class_id, min_class_name))
;
知识点06：DM层搭建--销售主题统计宽表--最终合并完整实现
step0：针对时间维表数据进行查询 CTE引导为临时结果集
原因是：并不是时间维表中的每一个字段在本次查询的时候都有用。
step1：编写grouping sets
提示：在grouping sets中养成不管分组字段是一个还是多个，都使用小括号括起来的习惯。
因为grouping sets认为只要是小括号，就是一个分组条件。
step3：指标的聚合
最终完整版sql
知识点07：DM层搭建--销售主题统计宽表--grouping精准识别分组
如何精准的识别该分组中到底有没有包含指定的分组字段，尤其是分组组合很多的时候。
技术：使用强大的grouping方法来精准识别。
难点：多位二进制转换十进制的操作 ，可以借助一些工具实现。
知识点08：DM层搭建--商品主题统计宽表--建模
概述
商品SKU主题宽表，需求指标和dws层一致，但不是每日统计数据，而是总累积值 和 最近30天累积值
建表
思路分析
1、首次计算如何操作？
2、循环计算如何操作？间隔的时间与频率如何？1天计算一次还是一月计算一次？
#在dws层，我们已经计算出每个商品每天的一些指标情况，如下
dt sku_id sku_name order_count order_num order_amount
day 商品ID 商品名称 被下单次数 被下单件数 被下单金额
#首次计算，每件商品的总累积值和近30天累积值 这个简单
总累积值：把全部的数据根据商品分组，每个组内sum求和 也就是说忽略日期计算即可
近30天累积值：控制时间范围后进行分组聚合 (today -30d, today)
#循环计算 本项目采用的是T+1模式
总累积值： 旧的总累积值+新的增值 = 新的总累积值
(当然如果不嫌弃慢，也可以把之前的全部数据再次重新计算一遍也可以得到 "可以没必要")
近30天累积值：控制时间范围后进行分组聚合 (today -30d, today)
#结论
当主题需求计算历史累积值时，不推荐每次都采用全量计算。推荐采用历史累积+新增。
知识点09：DM层搭建--商品主题统计宽表--首次执行
step1：准备好DWS层dws_sku_daycount的统计数据。
step2：计算总累积值
--如果要严谨、成熟一点的话 在处理数字类型字段的时候使用 coalesce()函数 null转为0
step3：计算最近30天累积值
step4：最终完整sql
使用CTE将上述查询合并，插入到目标表中。
知识点10：DM层搭建--商品主题统计宽表--循环操作
step1：建一个临时表
step2：查询出新统计的数据
--我们把2020-05-09向前30天的数据查询出来
--这30天数据之间sum求和的结果 就是新的最近30天累积值
--这30天数据中的最后一天也就是2020-05-09的数据 就是新的一天增加的数据 这个数据要和历史总累积数据进行合并 就是新的总累积值
step3：合并新旧数据
--查询出当前yp_dm.dm_sku中保存的旧数据
--新日期统计数据
--新旧合并 full outer join
--30天数据(永远取新的)
--累积历史数据（旧的+新的）
step4：最终完整sql
step5：把数据从临时表中查询出来覆盖至最终目标中
--Presto中不支持insert overwrite语法，只能先delete，然后insert into。
知识点11：DM层搭建--用户主题统计宽表--建模
概述
用户主题宽表，需求指标和dws层一致，但不是每日统计数据，而是总累积值 和 月份累积值， 可以基于DWS日统计数据计算。
建表
知识点12：DM层搭建--用户主题统计宽表--首次执行、循环执行
step1：准备好DWS层dws_user_daycount的统计数据。
首次执行
循环执行
知识点13：扩展--数据仓库元数据管理
1、背景
问题：任何一个不了解当前数据仓库设计的人，是无法根据当前的数据来合理的管理和使用数 据仓库的（这何尝不是每个刚入职的程序员的心声）
数据使用者：数据分析师、运营、市场
有哪些业务数据，范围？影响？有效性？能否支撑需求？
数据开发者：大数据工程师
新的开发者不能快速的了解整个数据仓库
如何能从数据仓库的N多张表中找到自己想要的那张表
领导决策层：leader、架构师、CTO
快速的了解公司对哪些数据做了处理，做了哪些应用
解决：对数据仓库构建元数据，通过元数据来描述数据仓库
任何一个人，只要知道了数据仓库的元数据，就可以快速的对数据仓库中的设计有一定 的了解，快速上手
2、元数据是什么
用于描述数据的数据、记录数据的数据、解释性数据。
想一想
HDFS中NN管理了文件系统的元数据，这里的元数据是什么？
文件自身属性信息、文件与block位置信息（内存、fsimage edits log）
Hive中启动metastore服务访问metadata，这里的metadata是什么，存储在哪？
表和HDFS上结构化文件映射信息（表和文件位置、字段类型 顺序 分隔符）
Hive的元数据存储在RDBMS（derby MySQL）
数据仓库的元数据：用于描述整个数据仓库中所有数据的来源、转换、关系、应用
比如模型的定义、各层级间的映射关系、监控数据仓库的数据状态及ETL的任务运行状态
管理过程元数据指描述管理领域相关的概念、关系和规则的数据；
3、数仓元数据分类
业务元数据：提供业务性的元数据支持
为管理层和业务分析人员服务，从业务角度描述数据，包括商务术语、数据仓库中有什么数据、数据的位置和数据的可用性等；
帮助业务人员更好地理解数据仓库中哪些数据是可用的以及如何使用。
技术元数据：数仓的设计、规范和数据的定义
为开发和管理数据仓库的 IT 人员使用，它描述了与数据仓库开发、管理和维护相关的数据；
包括数据源信息、数据转换描述、数据仓库模型、数据清洗与更新规则、数据映射和访问权限等。
管理元数据：所有数据来源，数据相关的应用、人员、应用、权限
管理过程元数据指描述管理领域相关的概念、关系和规则的数据；
主要包括管理流程、人员组织、角色职责等信息。
6、功能
血缘分析：向上追溯元数据对象的数据来源
影响分析：向下追溯元数据对象对下游的影响
同步检查：检查源表到目标表的数据结构是否发生变更
指标一致性分析：定期分析指标定义是否和实际情况一致
实体关联查询：事实表与维度表的代理键自动关联
7、应用
ETL自动化管理：使用元数据信息自动生成物理模型，ETL程序脚本，任务依赖关系和调 度程序
数据质量管理：使用数据质量规则元数据进行数据质量测量
数据安全管理：使用元数据信息进行报表权限控制
数据标准管理：使用元数据信息生成标准的维度模型
数据接口管理：使用元数据信息进行接口统一管理
8、工具软件 Apache Atlas
知识点14：扩展--数据质量管理
数据质量分类
数据本身质量：数据的产生的时候，数据包含的内容
数据建设质量：从采集、处理、应用
靠数仓规范、元数据管理、分层设计等来保障
判断数据质量的维度
准确性：数据采集值和真实值之间的接近程度
精确性：数据采集的粒度越细，误差越低，越接近事实
真实性：数据是否能代表客观的事实
完整性：数据中的数据是否有缺失
全面性：多维数据反映事实
及时性：有效的时间内得到事实的结果
关联性：数据之间关系的明确与发掘
自定义规则：手机号异常 身份证号异常 时间异常 用户来源（面向长三角地区）
数据质量处理
缺省值：补全推断【平均值】、给定占位符，标记这条数据【-1，或者flag，做相关统计 的时候，这条数据不参与统计】、丢弃
逻辑删除 物理删除
物理：真实的
逻辑：虚拟的 0无效 1有效 flag :true false T F
通过简单的统计分析，可以得到含有缺失值的属性个数，以及每个属性的未缺失数、缺失数和缺失率。删除含有缺失值的记录、对可能值进行插补和不处理三种情况。
异常值：做标记，或者丢失
关键是如何判断数据异常？
正态分布
标准差
方差
还可以通过数据本身特征判断是否异常？
中国大陆手机号 11位
中国大陆身份证号 18位
如果数据是符合正态分布，在原则下，异常值被定义为一组测定值中与平均值的偏差超过3倍标准差的值；
如果不符合正态分布，也可以用远离、偏离平均值的多少倍标准差来描述。
不一致的值：采集了同一个数据对应的不同时间的状态
注意数据抽取的规则，对于业务系统数据变动的控制应该保证数据仓库中数据抽取最新数据
重复数据或者含有特殊符号的值
在ETL过程中过滤这一部分数据，特殊数据进行数据转换。
Q:如何保证数据处理时的不重复 不遗漏？ 大数据允不允许适当的数据误差？ 不一定 《大数据思维》
已完成：：：：：