导图社区 大数据分析
- 60
- 1
- 0
- 举报
大数据分析
这是一篇关于大数据分析的思维导图，主要内容包括：操作系统，存储调用，分析计算。展示了从基础到高级的数据处理技术和工具的演进过程。大数据分析作为数据处理的高级阶段，不仅要求掌握基本的数据操作和分析技能，还需要具备分布式计算、数据仓库管理等方面的专业知识。
编辑于2024-08-04 00:59:16- 大数据分析
- 相似推荐
- 大纲
大数据分析
分析计算
sql语言基础（以Oracle为例）
增删改查
数据定义（DDL）
表操作
创建表
CREATE TABLE 表名(字段名 数据类型,字段名 数据类型......)
修改表
ALTER TABLE 表名 MODIFY(字段名 数据类型(长度));
添加列
增加列 AMOUNT NUMBER(10,2) ALTER TABLE EMP_BAK ADD AMOUNT NUMBER(10,2);
修改列
更新 comm 字段 属性为VARCHAR2 (100) ALTER TABLE EMP_BAK MODIFY(COMM VARCHAR2(100));
删除列
删除列 AMOUNT ALTER TABLE EMP_BAK DROP COLUMN AMOUNT;
删除表
DROP TABLE 表; (删除所有数据以及表) TRUNCATE TABLE 表;（清空表的所有数据，保留表字段/表结构。可以重置表的水位线）
视图操作
索引操作
约束操作
主键约束 唯一 且 非空
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(字段名);
唯一约束 唯一可为空
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(字段名);
非空约束 不能为空，可以重复
ALTER TABLE 表名 MODIFY 字段名 NOT NULL;
数据操纵（DML）
插入 INSERT
INSERT INTO 表名 VALUES(值1,值2......)
更新 UPDATE
UPDATE 表名 SET 字段名 = 值 WHERE 筛选条件
删除 DELETE
DELETE FROM 表 WHERE 筛选条件; 条件筛选 --可通过筛选去删除部分数据，没有where条件会全部删除，可以提交和回滚
事务控制（TCL）
提交事务 COMMIT
回滚事务 ROLLBACK
数据查询（DQL）
基本查询结构
SELECT 7
DISTINCT 8
FROM 1
JOIN 2
ON 3
WHERE 4
Q:WHERE & HAVING 的区别？ A:WHERE 是过滤表里面所有的字段，包括分组之前的字段，不能过滤分组之后的字段和 聚合函数 HAVING 是过滤聚合函数 和 分组之后的字段，不能过滤未分组的字段
子主题
空值
空值：NULL 对空值判断 IS /NOT IS NULL 去判断 不能用比较运算符
空值运算 空值和任何值进行 加减乘除的运算时，返回的结果都是空值
空值转换
1. NVL NVL(X,Y) 判断X是否为空，为空则输出 Y,如果不为空，则输出X --只能转换成数值
2. NVL2 NVL2(X,Y,Z) 如果X是空值，则返回Z 如果X不是空值，则返回Y
3. COALESCE COALESCE(A,B,C,D,E......,Z) 从A开始 从左往右去判断，知道出现第一个非空值，则返回该值
子主题
GROUP BY 5
结果集排序 关键字： ORDER BY 字段名 升序(ASC) 降序(DESC)
1多字段排序，用逗号去分隔，按照第一个字段先排序，再排第二个字段空值排序 2空值永远都是最大的 3ORDER BY 后面 一般是可以直接用列别名去进行排序的
HAVING 6
聚合函数的过滤 HAVING
ORDER BY 9
前提是：出现分组情况 --ORDER BY 后面只能跟分组之后的字段 和 聚合函数，未分组的字段不能进行过滤
关联查询
内关联（inner join）
内关联 取两张表关联上的数据，不能关联上的数据则过滤 --取两张表的 交集
--ORACLE 写内关联的时候，如果没有写关联条件，会产生 笛卡尔 乘积 --等于 A表的所有行数 * B表的所有行数 --SELECT * FROM EMP E JOIN DEPT D ON 1 = 1; 条件恒成立，也会产生 笛卡尔乘积
左外关联（left join）
--主表，FROM 后面的是主表，主表的数据就算不满足关联条件，也会被查询出来 --从表，JOIN 后面的是从表，不满足关联条件，则用null补充
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO = 20; --WHERE 才能起到过滤的作用 SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND E.DEPTNO = 20; --AND 在外关联的时候，只能过滤从表的数据
右外关联（right join）
语法： SELECT 要查询的列/字段 FROM 表1 --从表 RIGHT JOIN 表2 --主表 ON 关联条件 WHERE 过滤条件
全关联（full join）
1不区分主从表,取的是两张表的全量结果集，关联不上的互相补 NULL 2.两张表的共同字段，可以用NVL的空值转换去处理 （防止有的数据在A表有 B表没有，有的数据在B表有 A表没有）
自关联
自己和自己关联（当一张表 存在 上下级关系的时候）
FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO; -- 用领导编号 去找 员工是谁， --找领导
FROM EMP E1 LEFT JOIN EMP E2 ON E1.EMPNO = E2.MGR; -- 用员工编号 去找 领导是谁，--找下级 -- 去匹配 第二张表 的领导编号
查询所有员工的下级，并输出 员工姓名，下级员工姓名，下下级员工姓名，下下下级员工姓名。 SELECT E1.ENAME 员工姓名, E2.ENAME 下级员工姓名, E3.ENAME 下下级员工姓名, E4.ENAME 下下下级员工姓名 FROM EMP E1 --领导表 LEFT JOIN EMP E2 --下级员工 ON E1.EMPNO = E2.MGR LEFT JOIN EMP E3 --下下级员工 ON E2.EMPNO = E3.MGR LEFT JOIN EMP E4 --下下下级员工 ON E3.EMPNO = E4.MGR;
不等值关联 --数据发散
示例：查询每个员工的薪资等级; SELECT E.ENAME,E.SAL,S.GRADE,S.LOSAL,S.HISAL FROM EMP E LEFT JOIN SALGRADE S ON E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO > D.DEPTNO;
子查询
WHERE 后面去接子查询
在做比较运算符去判断的时候，只能一行一列值 SELECT ENAME FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE DEPTNO = 20); --报错：单行子查询返回多个值 出现多个值做判断的时候，我们需要用到范围查询 IN 或者 NOT IN 去判断 SELECT ENAME FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 20);
FROM 后面去接子查询
--当某个结果集 行成一张表的时候，如果我们要去筛选某一个字段的数据再处理， --在SELECT 后面 必须用跟结果集里面的字段名一样，当FROM 后面的子查询作为一张临时表 去查询 --当使用聚合函数去做FROM 后面的子查询时候，需要将该聚合函数先命名
EXISTS 和 WITH AS 的应用
函数
聚合函数
可以同时把多行进行操作，返回一个结果
计数
求和
平均值
最大值
最小值
标量函数
字符串
正则表达式
字符串相关函数
数学函数
ABS 取绝对值
TRUNC 截取
FLOOR 向下取整
CEIL 向上取整
ROUND 四舍五入
MOD 取余数
TRUNC 取商
POWER 取幂
时间函数
转换函数
窗口函数/分析函数
聚合类
排名类
偏移
拼接
占比
切片
某一字段值的第一条数据
集合运算
UNION ALL 合并结果集 不去重
UNION 合并结果集 去重
INTERSECT 合并结果集 交集
MINUS 合并结果集 补集
数据控制（DCL）
权限与安全
授予权限 GRANT
撤销权限 REVOKE
操作系统
Windows
Linux
文件系统
lrwxrwxrwx. 1 root root 7 9月 23 2021 bin -> usr/bin dr-xr-xr-x. 5 root root 4096 9月 23 2021 boot
根目录 /
在linux系统里面，每一次敲完一个关键字，都要用空格，一定要知道自己所在的目录位置（必须是小写）
主要目录结构
/bin,/sbin,/etc,/dev,/var,/home等
文件权限
rwx 读写执行
文件属性
所有者，群组，时间戳
权限位后面的数字：代表的该目录下面的目录个数
用户后面的数字： 文件大小（字节单位：byte）
Linux命令
Linux的命令组成：关键字 参数 文件名或者目录的路径
cd + 路径 → 切换路径
cd .. 返回上一级目录
ll 查看当前路径下的所有文件和文件夹 详细信息
ls 查看当前路径下的所有文件名和文件夹名
ll/ls -a 查看当前目录所有文件和文件夹 包括 隐藏文件
mkdir -p 路径 创建目录 -p 递归
touch 文件名 → 创建文件
rm -rf 文件名或者目录名 文件夹名 删除文件或者删除目录
clear --清屏
vi / vim
vi / vim 编辑器 有三种模型
1.浏览模式
在浏览模式下 按 dd 删除行数据
2.编辑模式(在浏览模式下，按I/O进入编辑模式)
3.命令行模式(在编辑完成之后，按ESC键退出编辑模式，然后按住 shift+：进入命令行模式)
退出
wq 保存并退出 q 退出 wq! 强制保存并退出 q! 强制退出不保存
vi / vim 文件名 → 进入编辑模式 文件
cat 文件名 → 查看文件内容
head -n 文件名 → 查看文件前N行的内容
tail -n 文件名 → 查看文件后N行的内容 tail -f 文件名 → 查看文件的实时内容 -- 用crtl + z 退出
echo 文件内容 文件名 → 写入相关内容 到 这个文件里面
> → 全量写入 >> → 增量写入
cp 源文件名 复制之后的文件名 → 复制文件
示例：将task复制文件 0717 cp task 0717
子主题cp 源文件名 根目录 → 移动文件并保存原文件
示例：将0717这个文件复制到 /home/student/aaa 改名为aaa cp /home/hadoop/student/name/0717 /home/student/aaa
MV → 剪切文件/移动文件不保存原文件
示例：将aaa这个文件 转移到 /home/hadoop/test 目录下，并命名为ccc mv /home/student/aaa /home/hadoop/test/ccc
mv ccc 0717 --重命名
pwd → 查看当前的路径
find 路径 -name "匹配项" → 查看文件位置（*代表所有）
示例：查找 /opt 目录下面的所有日志文件(.log 结尾的文件) find /opt -name "*.log" 示例：查找 /home/hadoop/test 目录下面的含C的文件 并删除 find /home/hadoop/test -name "*c*" | xargs rm -rf
tar -zcvf 包名 文件名/目录 → 将指定文件/目录压缩到指定包名下
示例：将 /home/hadoop/student/name 目录下面的所有文件及文件夹压缩成 0717.tar.gz tar -zcvf 0717.tar.gz /home/hadoop/student/name/
解压：tar -zxvf 包名
ps -ef 查看所有服务进程
ps -ef | grep 进程名 → 查看该进程是否启动
top → 查看当前所有正在运行的进程
kill -9 PID : PID 进程号
-------强制终止某一个进程----------- 先找该进程号， 然后再去kill 该进程
chmod -R 权限位 文件名/目录名 去递归修改权限
权限位修改涉及数字： 4：代表可读 2：代表可写 1：代表可执行
示例：将0717文件修改属主可读可写可执行 属组可读可写 其他用户可读可执行 chmod -R 765 0717
chown -R --修改文件的 属主 属组
示例：修改文件0717 将这个文件属主和属组给 mysql chown -R mysql:mysql 0717
用户管理
切换用户： su - hadoop → 切换至hadoop用户 su hadoop → 切换至hadoop用户 区别 加了 - 会执行用户下的环境变量
useradd 用户名 → 创建用户
passwd 用户名(密码需要输入两次) → 设置或者修改密码
userdel 用户名 → 删除用户
定时编辑器
1.crontab -e 进入编辑器 2.输入内容 * * * * * echo hello yunzhidao >> /home/hadoop/aaa
（第一个 * ：每分钟 第二个 * ：每小时 第三个 * ：每天 第四个 * ：每月 第五个 * ：每周）
Shell 脚本
脚本基础
脚本编辑器
#!/bin/sh --指向shell脚本的编辑器
示例：开发一个脚本 t_0723 ,要查看home下文件1下面的前1行内容 （VI/VIM） 进入VI编辑器 #!/bin/sh --指向shell脚本的编辑器 head -1 /home/1 调用shell 脚本 sh 文件名
shell脚本的特殊表达式
1.$2 用于接受2个入参的值 2.$0 返回调用脚本的文件名 3.$* 返回shell脚本参数的集合 4.$# 返回shell脚本参数的个数 5.$? 返回上一个命令的返回值类型(真：0 假：1)
控制结构
条件判断
1.语法： if [ 条件表达1 ] then 程序 elif [ 条件表达2 ] then 程序 else 程序 fi --fi 表示结束if
循环
语法： for (( 初始值;循环的判断条件;变量变化 )) do 程序 done
示例：输出1加到100 5050 #!/bin/bash s=0 #给s一个初始值 for (( i=0;i<=100;i++ )) #变量i的初始值是 0 do s=$[$s+$i] done echo $s
比较运算符
less then -lt 小于 great then -gt 大于 equel -eq 等于 less equel -le 小于等于 great equel -ge 大于等于
文件操作
文件读取
cat
文件写入
echo
文件权限和所有权
chmod，chown
文件查找和操作
linux 常用的三剑客
-- sed sed 删除/替换
awk 文本内容的截取
grep
hadoop
核心组件
HDFS :分布式的文件存储系统(硬盘)
数据块存储
NameNode
DataNode
SecondaryNameNode
Mapreduce :计算引擎(CPU) / --spark
Map 映射阶段
Reduce 归约阶段
Yarn (Yet Another Resource Negotiator) :资源调度(任务管理器的资源分配)
ResourceManager
NodeManager
Hadoop生态系统
数据仓库工具
Hive
基于Hadoop的数据仓库工具，可以将结构化数据映射到HDFS中，并提供SQL查询和数据分析功能。
SQL查询
Matastore
HBase
分布式非关系型数据库，可用于存储大量结构化和半结构化数据。
数据流处理
Apache Flume
分布式日志采集和聚合系统，可用于将日志数据收集到Hadoop集群中进行处理。
Apache Kafka
分布式流处理平台，可用于高吞吐量、低延迟的消息传递和数据处理。
数据处理工具
Pig
数据流语言和执行环境，可用于Hadoop的数据分析和数据处理。 --批数据 离线 流数据 实时
Apache Spark
基于内存计算的大数据分析框架，可以用于替代MapReduce进行数据处理和机器学习等计算。
分布式协调服务
ZooKeeper
分布式应用程序协调服务，用于管理分布式应用程序的配置信息、命名服务、分布式同步和状态同步等。
实时数据处理
Apache Flink
Flink是一个框架和分布式处理引擎，用于对无边界和有边界的数据流进行有状态的计算。 Flink是原生的流处理系统，但也提供了批处理API，用于基于流式计算引擎处理批量数据的计算能力，真正实现了批流统一。
数据迁移
------------------------上传-------------------- hadoop fs -put 本地文件名(linux的本地) HDFS文件路径 示例：将linux的本地文件 /home/1 上传到HDFS的路径下面student 下 命名为2 hadoop fs -put /home/1 /student/2
------------------------读取-------------------- hadoop fs -get HDFS文件路径 本地路径 示例：将HDFS上的文件/student/2 复制到 linux 本地/home 下命名为 6 hadoop fs -get /student/2 /home/6
权限修改：
hadoop fs -chmod g+w /tmp --增加属组可写 hadoop fs -chmod g+w /user/hive/warehouse hadoop fs -chmod g+w /student hadoop fs -chmod g=r /student -- =表示属组只读
存储调用
关系型数据库管理系统
ORACLE
数据类型
VARCHAR2(X) 字符串类型 X表示 字符串的可存储的长度 可变长度 --Oracle 11g 一个中文表示3个字符长度
CHAR(X) 字符串类型 X表示 字符串的可存储的长度 固定长度
NUMBER(X) 数字类型 X表示 数字的可存储长度
NUMBER(X,Y) 数字类型 X表示 数字的总存储长度为X，Y表示保留几位小数
DATE 日期类型 年/月/日 时：分：秒 --时间戳
Oracle的系统时间 SYSDATE --SYSTEM DATE 的时间 --YYYY/MM/DD H:mm:SS
在数据库查询里面，加了单引号表示该查询是字符串，双引号一般是给字段命名 --字符串里面的字符在引号下是严格区分大小写
oracle中的sql应用
查询
基本查询
多条件判断
AND 并且 同时满足两个或者以上的条件
AND 的优先级 高于 OR
OR 或者 只需要满足其中一个条件即可
条件查询
关键字：WHERE 在..情况下，当满足..的条件下 --满足这个条件是则输出结果集，不满足则过滤
比较运算符
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> / != 不等于
范围查询
1.BETWEEN X AND Y 在X和Y之间， 相当于 >= AND <=
2. IN (在..里面) 相当于 = OR = 字段A IN(X,Y,Z) 相当于 字段A = X OR 字段A = Y OR 字段A = Z
模糊查询
LIKE %%（两个通配符） 1.% 表示 0位或者以上的任意字符长度 2._ 表示 固定一个字符
ORACLE常用函数
聚合函数
可以同时把多行进行操作，返回一个结果
count()
sum()
avg()
max()
min()
标量函数
字符串
正则表达式
REGEXP_LIKE : 正则模糊匹配
REGEXP_INSTR : 正则查找索引位置
REGEXP_SUBSTR : 正则截取
REGEXP_REPLACE : 正则替换
字符相关函数
REPLACE --替换
REPLACE(字段串/字段名,'待替换的值','替换之后的值')
SELECT ENAME 替换前 ,REPLACE(ENAME,'A','B') 替换后 FROM EMP ------------------------------------ SELECT ENAME 替换前 ,REPLACE(ENAME,'A') 替换后 FROM EMP --默认则替换为空
LENGTH --长度 LENGTHB --长度
SELECT LENGTH(CAST(ENAME AS CHAR(10))) FROM EMP;
管道符 ||
结果集输出 "SCOTT的薪资是3000" 拼接符 || --管道符 拼接两个或者多个字符串以及字段 SELECT ENAME || '的薪资是' || SAL AS 拼接后 FROM EMP WHERE ENAME = 'SCOTT'; SELECT ENAME || SAL || ' ' ||DEPTNO FROM EMP;
拼接
CONCAT(X,Y)
SELECT CONCAT(EMPNO,ENAME) FROM EMP
WM_CONCAT(字段名)
将部门的所有名字拼接到一起 SELECT DEPTNO,wm_concat(ENAME) FROM EMP GROUP BY DEPTNO
INSTR 查找位置 返回值是数字
INSTR (字段名/字符串,要找的值,X,Y) 从X位开始，第Y次出现的索引位置
找出EMP中 ename 从第2位开始，第一次出现O的位子 SELECT ENAME,INSTR(ENAME,'O') FROM EMP; --省略不写(X,Y) 默认表示从第一位开始，第一次出现的位置
大小写转换
UPPER 转换成大写
LOWER 转换成小写
SELECT UPPER('AbCDe') FROM DUAL; SELECT LOWER('AbCDe') FROM DUAL; SELECT LOWER(ENAME) FROM EMP;
TRIM 去左右空格 LTRIM / RTRIM
去掉字符串 ' A B C D E ' 的左右空格 SELECT TRIM(' A B C D E ') FROM DUAL; SELECT LTRIM(' A B C D E ') FROM DUAL; SELECT RTRIM(' A B C D E ') FROM DUAL; SELECT REPLACE(' A B C D E ',' ') FROM DUAL;
SUBSTR
SUBSTR(字段名/字符串,X,Y) --截取字段，从X位开始，Y位长度，如果X为负数，则从倒数开始X往前推
截取字符串中'ABCDEFG' 中的 'CDEF' SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL; SELECT SUBSTR('ABCDEFG',3) FROM DUAL; --不要Y 说明从第3位开始 截取，保留后面所有数 SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;
左右填充
LPAD 左填充 RPAD 右填充
SELECT RPAD(1,6,0) FROM DUAL; --对数字1进行左填充，设定长度为6，不满足长度补0
身份证号码 SELECT RPAD(身份证字段,18,X) FROM DUAL;
数学函数
ABS 取绝对值
示例：SELECT ABS(-3.14) FROM DUAL; 返回 3.14
ROUND 四舍五入
示例：SELECT ROUND(3.1455,2) FROM DUAL; 3.15 示例：SELECT ROUND(3.7455) FROM DUAL;4 --没有指定小数位四舍五入至整数
TRUNC 截取
示例：SELECT TRUNC(3.1495926,2) FROM DUAL;3.14 示例：SELECT TRUNC(3.1495926) FROM DUAL; 3 --没有直接截取位，舍弃小数位
FLOOR 向下取整
示例：SELECT FLOOR(3.84) FROM DUAL;3 示例：SELECT FLOOR(-3.84) FROM DUAL;-4
CEIL 向上取整
示例：SELECT CEIL(3.84) FROM DUAL;4 示例：SELECT CEIL(-3.84) FROM DUAL;-3
MOD 取余数
示例：SELECT MOD(8,5) FROM DUAL;3
TRUNC 取商
示例：SELECT TRUNC(8/3) FROM DUAL;2
POWER 取幂
示例：SELECT POWER(2,10) FROM DUAL; 1024 -- 2的十次方
ADD_MONTHS -- ADD 增加 MONTHS 月
返回上个月的当天
SELECT TRUNC(ADD_MONTHS(SYSDATE,-1),'DD') FROM DUAL;
返回下下个月的当天
SELECT TRUNC(ADD_MONTHS(SYSDATE,2),'DD') FROM DUAL;
转换函数
空值
空值：NULL 对空值判断 IS /NOT IS NULL 去判断 不能用比较运算符
空值运算 空值和任何值进行 加减乘除的运算时，返回的结果都是空值
空值转换
1. NVL NVL(X,Y) 判断X是否为空，为空则输出 Y,如果不为空，则输出X --只能转换成数值
2. NVL2 NVL2(X,Y,Z) 如果X是空值，则返回Z 如果X不是空值，则返回Y
3. COALESCE COALESCE(A,B,C,D,E......,Z) 从A开始 从左往右去判断，知道出现第一个非空值，则返回该值
数据类型的转换
TO_CHAR 将日期类型转换为字符串类型,结果是字符串类型 TO_CHAR(日期类型,'YYYY')
TO_DATE 将字符串类型转换为日期类型,结果是日期类型 TO_DATE(字符串类型,'YYYY-MM-DD')
TO_NUMBER 将字符串类型转换为数字类型,结果是数字类型 TO_NUMBER(字符串类型) --隐式转换
SELECT '2024/06/28', SYSDATE, TO_DATE('2024/06/28','YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY'), TO_CHAR(SYSDATE,'MM'), TO_CHAR(SYSDATE,'DD'), TO_CHAR(SYSDATE,'W'), --返回第几周 TO_CHAR(SYSDATE,'Q'), --返回第几季度 TO_CHAR(SYSDATE,'DAY') --返回星期几 FROM DUAL;
时间函数
日期 - 日期 = 天数 --日期+日期是不允许的 SELECT SYSDATE,SYSDATE - TO_DATE(20240101,'YYYY-MM-DD') FROM DUAL; 日期 +— 天数 = 日期 SELECT SYSDATE + 20 FROM DUAL;
trunc
获取当前时间的年月日
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL; SELECT TRUNC(SYSDATE,'DD') FROM DUAL; --截取到日期的DD，返回值为当前时间的年月日
截取当月的1号
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回当年的第一天
截取 1995/09/10 的一月一号
SELECT TRUNC(TO_DATE('1995/09/10','YYYY-MM-DD'),'YYYY') FROM DUAL;
当前季度的第一天
SELECT TRUNC(SYSDATE,'Q') FROM DUAL; --季度的第一天 SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; --返回的是当前第几季度
SELECT TRUNC(SYSDATE,'W') FROM DUAL; SELECT TRUNC(SYSDATE,'DAY') FROM DUAL; SELECT TRUNC(SYSDATE,'D') FROM DUAL;
ADD_MONTHS -- ADD 增加 MONTHS 月
返回上个月的当天
SELECT TRUNC(ADD_MONTHS(SYSDATE,-1),'DD') FROM DUAL;
在某一个时间点 去增加X几个月 示例：往前推10年 SELECT ADD_MONTHS(SYSDATE,-12*10) FROM DUAL; 示例：社保缴纳 15号是一个分割期 SELECT TRUNC(SYSDATE + 15,'MM') FROM DUAL; SELECT ROUND(SYSDATE,'MM') FROM DUAL;
返回下下个月的当天
SELECT TRUNC(ADD_MONTHS(SYSDATE,2),'DD') FROM DUAL;
当前时间 ROUND
ROUND 对日期按月进行截取，日期天数大于15号则返回下个月的1号，小于等于15号则返回当月1号
SELECT ROUND(TO_DATE('20240416 161032','YYYY-MM-DD HH24MISS'),'DD') FROM DUAL;
对时间进行 ROUND 24小时制 12点之前的返回当前时间，12点之后返回下一天
SELECT TO_DATE('20240416 111032','YYYY-MM-DD HH24MISS') FROM DUAL;
四舍五入到年
SELECT ROUND(SYSDATE,'YYYY') FROM DUAL; SELECT ROUND(SYSDATE+180,'YYYY') FROM DUAL;
返回当月的最后一天
SELECT TRUNC(ADD_MONTHS(SYSDATE,1),'MM') -1 FROM DUAL; SELECT ROUND(SYSDATE + 15,'MM') -1 FROM DUAL;
last_day --最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
MONTHS_BETWEEN 求两个日期之间的月份
计算当前时间与 1995/09/10 相差多个月 SELECT TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE,'DD'),TO_DATE('1995/09/10','YYYY-MM-DD'))) FROM DUAL; SELECT ABS(TRUNC(MONTHS_BETWEEN(TO_DATE('1995/09/10','YYYY-MM-DD'),TRUNC(SYSDATE,'DD')))) FROM DUAL;
窗口函数/分析函数
聚合类
SUM/MAX/MIN/AVG/COUNT() OVER(PARTITION BY ... ORDER BY ...)
在 WHERE 子句条件后面的时候，不能直接对开窗函数和聚合函数去进行判断
排名类
ROW_NUMBER（） OVER (PARTITION BY 字段1 ORDER BY 字段A) 含义：按照字段1去分组，按照字段A去排序，取对应的排名号
RANK（） OVER (PARTITION BY 字段1 ORDER BY 字段A)
DENSE_RANK （） OVER (PARTITION BY 字段1 ORDER BY 字段A)
三者都是排名函数，语法都是一样的，关键字不一样，ORDER BY 是不能省略 区别： 当存在并列的时候，是非空出排名 ROW_NUMBER 排名：1，2，3，4 RANK 排名：1，2，2，4 DENSE_RANKE 排名：1，2，2，3
偏移
LAG/LEAD()OVER(PARTITION BY 字段 ORDER BY 字段) 向上/向下偏移，必须要 ORDER BY LAG 和 LEAD 可以在一次查询中，取同一个字段的前N行数据和后N行数据
LAG/LEAD(X,Y,Z) OVER(PARTITION BY 字段1 ORDER BY 字段A) X：取偏移的列名/字段名 Y：偏移的行数 Z：当偏移之后找不到数据的时候，就会给一个Z值，不写Z值默认为NULL
拼接
WM_CONCAT()OVER()
占比
RATIO_TO_REPORT () OVER()
示例：取每个部门下每个员工的薪资占比
SELECT ENAME,DEPTNO,SAL, SUM(SAL) OVER(PARTITION BY DEPTNO) 部门总薪资, ROUND(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO) * 100,2) || '%' 薪资占比 FROM EMP;
SELECT ENAME,DEPTNO,SAL,COMM, RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO) 薪资占比, RATIO_TO_REPORT(COMM) OVER(PARTITION BY DEPTNO) FROM EMP;
空值求占比，是不纳入比例计算
切片
切片函数 NTILE()OVER() --必须要 ORDER BY
取EMP表，每个部门薪资前30% 的高薪，中间30%是中薪，后30%是低薪 SELECT E.*, NTILE(3) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) NT FROM EMP E;
某一字段值的第一条数据
FIRST_VALUE() OVER()
SELECT E.*, FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 部门最高薪资的人 FROM EMP E;
ORACLE伪列
ORACLE 伪表 DUAL → 只有一行数据
ORACLE 伪列 ROWNUM & ROWID
1. ROWNUM -- 返回查询结果集的行号，一般用来做分页查询的 插入数据的顺序
--查询薪资前五的人 SELECT * FROM EMP WHERE ROWNUM <=5 ORDER BY SAL DESC ; SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= 5;
ROWNUM 不能 > 或者 >=
2.ROWID --返回每一行数据的物理地址，唯一，一般用来删除重复数据
删除重复数据的常用方法
DELETE FROM EMP_BAK WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_BAK E GROUP BY E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO); --先按字段分组，一般是ID 找出最大 最小的 ROWID --删除重复数据，一般是用不在最大 最小 ROWID的范围查询 去删除
Q:如何找出某一个JOB ，下面重复数据有几条 SELECT JOB,COUNT(1) FROM EMP GROUP BY JOB HAVING COUNT(1) > 1;
ORACLE 的场景判断
CASE WHEN 的判断
CASE WHEN 场景1(判断条件) THEN 返回值1 WHEN 场景2 THEN 返回值2 ........... ELSE 返回值N --ELSE不写，默认为NULL END AS 列名(AS 可省略)
case when 行转列应用
SELECT SNO, SUM(CASE WHEN SUBJECT = '语文' THEN SCORE ELSE 0 END) AS 语文, SUM(CASE WHEN SUBJECT = '数学' THEN SCORE ELSE 0 END) AS 数学, SUM(CASE WHEN SUBJECT = '英语' THEN SCORE ELSE 0 END) AS 英语 FROM T_SCORE GROUP BY SNO ORDER BY SNO ASC;
DECODE 的判断
DECODE(字段名,值1,返回值1,值2,返回值2,.....,返回值N) 值N不写 则 返回 NULL 值N表示其他条件
DECODE 只能用于等值判断，CASE WHEN 可以用多条件以及不等值的判断；
decode 行转列应用
DECODE(字段名,'判断值1','输出值A'.......,'返回值N') SELECT SNO, SUM(DECODE(SUBJECT,'语文',SCORE,0)) 语文, SUM(DECODE(SUBJECT,'数学',SCORE,0)) 数学, SUM(DECODE(SUBJECT,'英语',SCORE,0)) 英语 FROM T_SCORE GROUP BY SNO ORDER BY SNO ASC;
行列转换
行转列 pivot
SELECT * FROM T_SCORE PIVOT(SUM(SCORE) FOR SUBJECT IN('语文' 语文,'数学' 数学,'英语' 英语)) ORDER BY SNO ASC; PIVOT(SUM(聚合字段) FOR 待转换的字段 IN (待转换的值 转换之后的字段名,...........))
列转行 unpivot
UNPIVOT UNPIVOT(指标值 FOR 合并之后的字段名 IN(待合并的字段名1,待合并的字段2,.........)) SELECT * FROM T_SCORE_LIEBIAO UNPIVOT(SCORE FOR SUBJECT IN (语文,数学,英语)) ORDER BY SNO;
递归查询和连续登录
PL/ SQL
SQL 结构化查询语言 PL/SQL 过程化语言，面向过程的一个编程 PROCEDURE LANGUAGE(PL)
---PL/SQL程序块---- 语法： DECLARE ---声明 --声明变量部分，可以不写 BEGIN --要执行SQL语句； --必须要写 EXCEPTION --异常处理模块，可以不写 END;
DECLARE --声明变量， 可以赋予 缺省值/初始值
入参
IF 条件判断
语法： IF 条件1 THEN 执行的事项1 ELSIF 条件2 THEN 执行的事项2 ELSIF 条件3 THEN 执行的事项3 ELSE 执行的事项4 END IF;
循环
游标
PL/SQL 的具体应用
存储过程
存储过程
动态SQL
函数
自定义函数
触发器
包
数据库对象
表 Tables
普通表
拉链表
维度缓慢变化 维护历史状态，以及最新状态数据的一种表； 所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。 例如一个快递订单，从分拣配货——>配送运输——>用户收货， 每一个步骤都有一条对应的数据保存到数据库；
拉链表的原理： 拉链表对比源表,新增开始时间,结束时间,以及FLAG标志位(标识是否是历史数据) 然后,对比源表,有则更新,无则插入.
日志表
视图 Views
索引 Indexes
触发器 Triggers
存储过程 Procedures
函数 Functions
序列 Sequences
数据库管理
用户和权限管理
数据迁移
全量同步
增量同步
事务管理
锁管理
设计优化
数据库分区
范围分区
列表分区
哈希分区
优化
MYSQL
数据仓库工具（用于大数据处理）
HIVE
中心主题
列分隔符是 逗号：
row format delimited fields terminated by ',';
列分隔符是 tab键：
row format delimited fields terminated by '\t';
元数据存储在MYSQL的metastore数据库下面，TBLS表中可看到hive表信息
LMySQL