- 整体开发流程
- 业务数据生成
- ETL数据导入
- 创建ODS层，并完成HDFS数据接入
- 创建DWD层，并完成ODS层数据导入
- 创建DWS层，导入DWD层数据
- 创建ADS层，完成复购率计算
- 编写脚本，将ADS层的数据导出到Mysql中，供业务查询
- 使用Azkaban调度器，实现脚本自动化运行
一、
ETL中三个字母分别代表的是Extract、Transform、Load，即抽取、转化、加载。
1、数据抽取：从源数据系统抽取目的数据系统需求的数据；
2、数据转换：将从源数据源获取的数据按照业务需求，转换成目的数据源要求的形式，并对错误、不一致的数据进行清洗和加工；
3、数据加载：将转换后的数据装载到目的数据源。
1、数据抽取（Extraction）
抽取的数据源可以分为结构化数据、非结构化数据、半结构化数据
结构化数据一般采用JDBC、数据库日志方式，非|半结构化数据会监听文件变动
抽取方式
数据抽取方式有全量同步、增量同步两种方式
全量同步会将全部数据进行抽取，一般用于初始化数据装载
增量同步方式会检测数据的变动，抽取发生变动的数据，一般用于数据更新
2、数据转换（Transformation）
数据转换要经历数据清洗和转换两个阶段
－数据清洗主要是对出现的重复、二义性、不完整、违反业务或逻辑规则等问题的数据进行统一的处理
－数据转换主要是对数据进行标准化处理，进行字段、数据类型、数据定义的转换
结构化数据在转换过程中的逻辑较为简单，非 | 半结构化数据的转换会较为复杂
3、数据加载（ Loading ）
将最后处理完的数据导入到对应的目标源里
左连接 ，右连接，内连接和全外连接的4者区别：
left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。
例如：
A表
id name
1 小王
2 小李
3 小刘
B表
id A_id job
1 2 老师
2 4 程序员
内连接：（只有2张表匹配的行才能显示）
select a.name,b.job from A a inner join B b on a.id=b.A_id
只能得到一条记录
小李 老师
左连接：（左边的表不加限制）
select a.name,b.job from A a left join B b on a.id=b.A_id
三条记录
小王 null
小李 老师
小刘 null
右连接：（右边的表不加限制）
select a.name,b.job from A a right join B b on a.id=b.A_id
两条记录
小李 老师
null 程序员
全外连接：(左右2张表都不加限制）
select a.name,b.job from A a full join B b on a.id=b.A_id
四条数据
小王 null
小李 老师
小刘 null
null 程序员
三范式
1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF
（1NF的定义为：符合1NF的关系中的每个属性都不可再分。）
2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现
3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:
3NF:非主键字段不能相互依赖，这个怎么理解呢，比如student表，班级编号受人员编号的影响，如果在这个表中再插入班级的班主任、数学老师等信息，你们觉得这样合适吗？肯定不合适，因为学生有多个，这样就会造成班级有多个，那么每个班级的班主任、数学老师都会出现多条数据，而我们理想中的效果应该是一条班级信息对应一个班主任和数学老师，这样更易于我们理解，这样就形成class表，那么student表和class表中间靠哪个字段来关联呢，肯定是通过“classNo”，这个字段也叫做两个表的外键，
反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
二、
1、操作数据层（ODS）
数据与原业务数据保持一致，可以增加字段用来进行数据管理
存储的历史数据是只读的，提供业务系统查询使用
业务系统对历史数据完成修改后，将update_type字段更新为UPDATE，追加回ODS中
在离线数仓中，业务数据定期通过ETL流程导入到ODS中，导入方式有全量、增量两种
－全量导入：数据第一次导入时，选择此种方式
－增量导入：数据非第一次导入，每次只需要导入新增、更改的数据，建议使用外连接&全覆盖方式
2、数据明细层（DWD）
数据明细层对ODS层的数据进行清洗、标准化、维度退化（时间、分类、地域）
数据仍然满足3NF模型，为分析运算做准备
3、数据汇总层（DWS）
数据汇总层的数据对数据明细层的数据，按照分析主题进行计算汇总，存放便于分析的宽表
存储模型并非3NF，而是注重数据聚合，复杂查询、处理性能更优的数仓模型，如维度模型
4、数据应用层（ADS）
数据应用层也被称为数据集市
存储数据分析结果，为不同业务场景提供接口，减轻数据仓库的负担
－数据仓库擅长数据分析，直接开放业务查询接口，会加重其负担
三、
1、数据库面向事务设计，属于OLTP（在线事务处理）系统，主要操作是随机读写；在设计时尽 量避免冗余，常采用符合范式规范来设计
2、数据仓库是面向主题设计的，属于OLAP（在线分析处理）系统，主要操作是批量读写；关
注数据整合，以及分析、处理性能；会有意引入冗余，采用反范式方式设计
三、mysql
常用的字符类型是 CHAR、VARCHAR 类型。
CHAR，描述定长的字符串，说明格式为：CHAR(L)，L 为字符串长度，取值范围为 1~255。 比 L 大的值将被截断，比 L 小的值将用空格填补。
VARCHAR，描述变长的字符串，说明格式为：VARCHAR(L)，L 为字符串长度，取值范围 为 1~255。比 L 大的值将被截断，比 L 小的值不会用空格填补，按实际长度存储。 字符串值用单引号或双引号括起来。如'abc'、"女"
常用的日期和时间类型是 DATE、TIME、DATETIME 类型。
DATE，用来保存固定长度的日期数据。说明格式为：DATE。
TIME，用来保存固定长度的时间数据。说明格式为：TIME。
DATETIME，用来保存固定长度的日期时间数据。说明格式为：DATETIME。
日期值格式为'YYYY-MM-DD'；
时间值格式为'HH:MM:SS'；
日期时间值格式为' YYYY-MM-DD HH:MM:SS'
为 dept_c 表增加一个新列 telephone。
ALTER TABLE dept_c
ADD telephon VARCHAR(11);
DESC dept_c
对 dept_c 表中的 telephone 列进行修改，数据类型不变，长度改为 13，默 认值为 0431-86571302。
ALTER TABLE dept_c
MODIFY telephon VARCHAR(13) DEFAULT '0431-86571302';
DESC dept_c
删除 dept_c 表中的 telephon 列。
ALTER TABLE dept_c
DROP telephon
当一个表中的数据不再需要时，可以使用 TRUNCATE TABLE 语句将它们全部删除掉，即 截断。该语句的格式为： TRUNCATE TABLE
注意：使用上面的语句只删除了表中的所有数据行，但表的结构仍然保留
SELECT 语句基本的语法如下： SELECT * | [，]…… FROM [，]…… [ WHERE ] [ GROUP BY ]…… [ HAVING ] ] [ ORDER BY [，
查询结果中有重复的行值出现，要去掉重复的记录，
可将语句改为： SELECT DISTINCT deptno,job FROM emp;
查询 emp 表工资在 2500~3000 之间，1981 年聘用的所有雇员的姓名、工资、 聘用日期信息。
SELECT ename,sal,hiredate FROM emp
WHERE sal BETWEEN 2500 AND 3000
AND hiredate BETWEEN '1981-01-01' AND '1981-12-31';
匹配字符串中使用通配符“%”和“_”，“%”用于表示 0 个或任意多个字符；“_” 表示任意 1 个字符
查询 emp 表中所以姓名以 K 开头或姓名第 2 个字母为 C 的员式的姓名、部 门号及工资的信息。
SELECT ename,deptno,sal FROM emp
WHERE ename LIKE 'K%'
OR ename LIKE '_C%
空值判断 【例 2-19】查询 emp 表中 1981 年聘用没有补助的员工的姓名和职位信息。
SELECT ename,job FROM emp
WHERE hiredate>='1981-01-01' AND hiredate<='1981-12-31'
AND comm IS NULL;
以部门号的降序、姓名升序，查询 emp 表中工资在 2000~3000 员工的部门 号、姓名、工资、补助信息。
SELECT deptno,ename,sal,comm FROM emp
WHERE sal BETWEEN 2000 AND 3000
ORDER BY deptno DESC,ename；
统计 deptno 为 30 的部门的平均工资,总补助款，总人数,补助人数，最高工资和最低工资
SELECT empno,sal,comm FROM emp；
SELECT AVG(sal) AS 平均工资,SUM(comm) 总补助款,
COUNT(*) AS 总人数,COUNT(comm) 补助人数,
MAX(sal) AS 最高工资,MIN(sal) 最低工资
FROM emp WHERE deptno=30;
查询 emp 表中每个部门的平均工资和最高工资，并按部门编号升序排序。
SELECT deptno,AVG(sal) 平均工资,MAX(sal) 最高工资 FROM emp
GROUP BY deptno ORDER BY deptno;
查询 emp 表中每个部门、每种岗位的平均工资和最高工资。
SELECT deptno,job,AVG(sal) 平均工资,MAX(sal) 最高工资 FROM emp
GROUP BY deptno,job ORDER BY deptno;
查询部门编号在 30 以下的各个部门的部门编号、平均工资，要求只显示 平均工资大于等于 2000 的信息。
SELECT deptno,AVG(sal) 平均工资 FROM emp
WHERE deptno<30
GROUP BY deptno
HAVING AVG(sal)>=2000;
查询工资大于等于 3000 的员工的员工编号、姓名、工资、所在部门编号 及部门所在地址，结果按部门编号进行排序。
SELECT empno,ename,sal,e.deptno,loc FROM emp e,dept d
WHERE e.deptno=d.deptno
AND sal>=3000 ORDER BY e.deptno
也可使用 SQL99 标准中的 ON 子句实现内连接。
格式为：FROM 表名 1 INNER JOIN 表 名 2 ON 表名 1.列=表名 2.列。如例 2-27 用 ON 子句写法如下：
SELECT empno,ename,sal,e.deptno,loc
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
WHERE sal>=3000 ORDER BY e.deptno;
查询 emp 表中在部门 20 工作的雇员的姓名及其管理员的姓名。
SELECT e.ename 雇员,m.ename 管理员
FROM emp e,emp m
WHERE m.empno=e.mgr
AND e.deptno=20
salgrade 表中存放着工资等级的信息，查询在部门编号为 20 工作的雇员 的工资及工资等级的信息。
SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND e.deptno=20
左外连接的格式是： FROM 表 1 LEFT OUTER JOIN 表 2 ON 表 1.列=表 2.列
左外连接的结果是：显示表 1 中所有记录和表 2 中与表 1.列相同的记录。
SELECT loc,dept.deptno,emp.deptno,ename,empno
FROM dept
LEFT OUTER JOIN emp ON dept.deptno=emp.deptno
WHERE dept.deptno=10 OR dept.deptno=40;
右外连接的格式是： FROM 表 1 RIGHT OUTER JOIN 表 2 ON 表 1.列=表 2.列
右外连接的结果是：显示表 2 中所有记录和表 1 中与表 2.列 相同的记录。
SELECT empno,ename,emp.deptno,dept.deptno,loc
FROM emp
RIGHT OUTER JOIN dept ON emp.deptno=dept.deptno
WHERE dept.deptno=10 OR dept.deptno=40
查询与 SCOTT 工作岗位相同的员工的员工编号、姓名、工资、岗位信息。
SELECT empno,ename,sal,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT');
查询工资大于平均工资而且与 SCOTT 工作岗位相同员工的信息。
SELECT empno,ename,sal,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT')
AND sal>(SELECT AVG(sal) FROM emp);
查询工资为所任岗位最高的员工的职工编号、姓名、岗位和工资的信息， 不包含岗位为 CLERK 和 PRESIDENT 的员工。
SELECT empno,ename,job,sal FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY job)
AND job<>'CLERK' AND job<>' PRESIDENT ';
MySQL 运行在 SAFE_UPDATES 模式下，该模式会导致非主键条件下无法执行 UPDATEA 或 DELETE 命令。需要执行命令 SET SQL_SAFE_UPDATES=0; 修改数据
UPDATE dept_c SET loc='CHINA' WHERE deptno=10;
创建索引的语句格式如下： CREATE [UNIQUE] INDEX 索引名 ON 表名(列名[,列名]……)
【例 2-49】为 emp_c 表按员工的名字（ename）建立索引，索引名为 emp_ename_idx。 CREATE INDEX emp_ename_idx ON emp_c(ename);
【例 2-50】为 emp_c 表按工作和工资建立索引，索引名为 emp_job_sal_idx。
CREATE INDEX emp_job_sal_idx ON emp_c(job,sal);
索引名的命名一般采用表名_列名_idx 方式，以这种方式命名的索引将来维护起来很 方便
删除 emp_c 表中已建立的索引 emp_job_sal_idx。
DROP INDEX emp_job_sal_idx ON emp_c;
<> ALL
和所有的 都不相等， 也就是说 没有在另一张表里出现啦
<> some
和部分不相等 一般用来看 两个集合 有没有不同元素
= some
和部分相等 一般用来看有没有交集
= ALL
和所有相等 一般用来看两个集合是否相等
5、日期转化为字符串
DATE_FORMAT(date,'%Y-%m-%d %H:%i:%S') //转为字符串
TIME_FORMAT(date,pattern) //只能转化时间，不能转化日期
from_unixtime(timestamp,pattern) //时间戳转为字符串
6、字符串转时间
str_to_date(dateStr,pattern)
日期转字符串
1、函数：date_format(date, format)
2、例：
select date_format(now(),'%Y-%m-%d %H:%i:%S');
结果：2017-10-29 14:02:54
select date_format(now(),'%Y-%m-%d %H:%i:%S');
结果：2017-10-29 14:02:54
二、日期转时间戳
1、函数：unix_timestamp(data)
2、例：
select unix_timestamp(now());
结果：1509257408
select unix_timestamp(now());
结果：1509257408
三、字符串转日期
1、函数：str_to_date(str,format)；注:format格式必须和str的格式相同，否则返回空
2、例：
select str_to_date('2017-10-29', '%Y-%m-%d %H:%i:%S');
结果:2017-10-29 00:00:00
select str_to_date('2017-10-29', '%Y-%m-%d %H:%i:%S');
结果:2017-10-29 00:00:00
四、时间戳转日期
1、函数：from_unixtime(time-stamp)；
2、例：
select from_unixtime(1509257408);
结果：2017-10-29 14:10:08
select from_unixtime(1509257408);
结果：2017-10-29 14:10:08
五、时间戳转字符串
1、函数：from_unixtime(time-stamp,format)；
2、例:
select from_unixtime(1509257408,'%Y~%m~%d %H:%i:%S');
结果:2017~10~29 14:02:08
select from_unixtime(1509257408,'%Y~%m~%d %H:%i:%S');
结果:2017~10~29 14:02:08
使用 DELIMITER 语句将 MySQL 语句的结束标记修改为其它符号。
恢复使用分号作为结束标记，执行 DELIMITER
(1) DECLARE 声明的局部变量，变量名前不能加@。
(2）DEFUALT 子句提供了一个默认值，如果没有给默认值，局部变量初始值默认为 NULL。
创建求任意两个数和的存储函数 sum_fn()。
DELIMITER @@ ##
CREATE FUNCTION sum_fn(a DECIMAL(5,2),b DECIMAL(5,2)) ##创建存储函数 sum_fn
RETURNS DECIMAL
BEGIN
DECLARE x,y DECIMAL(5,2); ##声明两个整型变量,注意变量名前没有@
@ SET x=a,y=b; ##给两个整型变量赋值,注意变量名前没有@
@ RETURN x+y;
[email protected]@
DELIMITER ;
SELECT sum_fn(7,3);
![医院集成平台系统[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)