大数据

大数据：递归：从顶层到下一层级,一层一层递归去找。递归里面有一个很重要的关键字, LEVEL -- 级别。

ORCL BI大数据(续） 2022年

• 4.1
  • 存在 & 不存在
    • EXISTS
      • 找出 哪些部门 存在员工(在EMP表 在DEPT表也有) -- 10、20、30 SELECT E.* FROM EMP E WHERE EXISTS -- 当存在....的场景 (SELECT * -- 存在的场景判断 不关注 SELECT 输出值 FROM DEPT D WHERE E.DEPTNO = D.DEPTNO) -- E表的部门编号在D表也存在
        • 其查出来的表相当于： SELECT E.* FROM EMP E --用*的出来的结果就不一样了 INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
      • SELECT * FROM DEPT D WHERE EXISTS (SELECT 'ABC' --随便什么值都可以 FROM EMP E WHERE D.DEPTNO = E.DEPTNO );
    • NOT EXISTS
      • SELECT * FROM DEPT D WHERE NOT EXISTS -- 不存在 (SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO ) AND D.DEPTNO > 10 --后面可以接其它条件
      • SELECT * FROM DEPT D WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE E.JOB <> 'MANAGER' AND E.DEPTNO = D.DEPTNO );
    • 总结： 1.（NOT）EXISTS 基本都是放在 WHERE 后面作为过滤条件使用; 2.（NOT）EXISTS 括号里面 SELECT 对应的值无意义,SELECT 任何值都可以; 3.（NOT）EXISTS 后面可以接其它条件;
  • 临时表
    • 物理临时表 --相当于正常的表
    • 会话临时表 CREATE TEMP TABLE TEMP_ODS_XUESBIAO_A
    • WITH AS
      • WITH -- 定义一段临时表 T1 -- 第一个临时表的别名 AS -- 定义别名对应的 sql语句 跟CREATE TABLE XXX AS 一样 ( ), -- 子查询 T2 AS ( ), T3 ( ) SELECT FROM T1,T2,T3 WHERE 过滤条件;
        • 取平均工资高于1000的部门 SELECT * FROM (SELECT DEPTNO,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) T1 WHERE AVG_SAL > 1000;
          • 相当于： WITH T1 AS (SELECT DEPTNO,AVG(SAL)平均工资 FROM EMP GROUP BY DEPTNO) SELECT * FROM T1 WHERE 平均工资 > 1000;
        • EMP按照薪资从高到低,5条/页,看第2页的薪资(6~10) SELECT * FROM (SELECT ROWNUM RN,E.* FROM --给E表添加序号成为T表 (SELECT * FROM EMP ORDER BY SAL DESC) E) T --给EMP表的薪资降序排列成为E表 WHERE T.RN BETWEEN 6 AND 10;
          • 用临时表表示： WITH T1 AS (SELECT * FROM EMP ORDER BY SAL DESC),--第一步排序 T2 AS (SELECT ROWNUM RN,T1.* FROM T1), --给ROWNUM别名 T3 AS (SELECT * FROM T2 WHERE RN BETWEEN 6 AND 10) -- 分页筛选 SELECT T2.* FROM T3,T2 WHERE T2.EMPNO = T3.EMPNO;
  • 递归查询
    • 递归：从顶层到下一层级,一层一层递归去找。 递归里面有一个很重要的关键字, LEVEL -- 级别
    • 我要找KING这个员工的所有下属(跟自关联很像) SELECT LEVEL,E.* FROM EMP E START WITH ENAME = 'KING' -- 从 ENAME 等于 KING这一行开始递归去找 CONNECT BY PRIOR EMPNO = MGR -- 定义连接条件 下级等于上级 PRIOR优先级 ORDER BY LEVEL;
    • 一段 SELECT 输出 1~1000 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000; -- (LEVEL 可以理解成 1到 +∞ ) LEVEL 是必须从1开始的
    • 输出最近500天的日期 SELECT TRUNC(SYSDATE,'DD') - (LEVEL - 1) DATE_TIME FROM DUAL CONNECT BY LEVEL <= 500;
    • 怎么 SELECT 输出从2020-01-01到当前的所有日期 LEVEL[1,+∞) SELECT TO_DATE('2020-01-01','YYYY-MM-DD') + LEVEL - 1 --[0,+∞) FROM DUAL CONNECT BY LEVEL <= TRUNC(SYSDATE,'DD') - TO_DATE('2020-01-01','YYYY-MM-DD') -- 时间差 + 1; --所有日期 = 时间差 + 1
      • 2020-01-01 到 2020-01-02 中间一共有多少个日期？ 日期差 = 大的时间 - 小的时间 所有日期 = 大的时间 - 小的时间 + 1。
  • 连续登陆
    • CREATE TABLE T_LOGIN_DATE (USER_NAME VARCHAR2(20), LOGIN_DATE DATE);
    • INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200102','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200103','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200104','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200105','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200106','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200110','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200111','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200112','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200113','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200117','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200118','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200119','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200120','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200121','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200122','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200123','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200124','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200125','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200128','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200129','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200130','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200131','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200201','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200202','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200203','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200215','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200216','YYYYMMDD')); INSERT INTO T_LOGIN_DATE VALUES ('zhangsan',TO_DATE('20200217','YYYYMMDD')); --COMMIT;
    • SELECT * FROM T_LOGIN_DATE;
    • 预期结果： user_name 最长连续登陆天数 zhangsan 9
      • SELECT USER_NAME, MAX(CNT) 最长连续登陆天数 FROM (SELECT USER_NAME, RR, COUNT(1) CNT --用差值分组计数 就是连续的次数 FROM (SELECT USER_NAME, LOGIN_DATE, ROW_NUMBER() OVER (PARTITION BY USER_NAME ORDER BY LOGIN_DATE) RN, --按用户名分类，以登录时间升序，添加序号 LOGIN_DATE - ROW_NUMBER() OVER (PARTITION BY USER_NAME ORDER BY LOGIN_DATE) RR --把这个字段 跟 另外一个连续字段相减 差值相同,则连续 FROM T_LOGIN_DATE) GROUP BY USER_NAME, RR) --用差值分组 GROUP BY USER_NAME;
    • -- 判断某个字段连续,我们需要 把这个字段 跟 另外一个连续字段相减 -- 差值相同,则连续,然后 用差值分组 计数 就是连续的次数 -- 另外一个连续字段 基本上都是 用 ROW_NUMBER ROW_NUMBER() OVER (PARTITION BY USER_NAME ORDER BY LOGIN_DATE)
  • Oracle的集合运算
    • 语法： SELECT 字段A FROM 表A 哪一种集合(集合运算) SELECT 字段B FROM 表B
      • 交集 INTERSECT
        • 找出EMP表跟DEPTNO共有的部门编号 SELECT DEPTNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT;
      • 全集去重 UNION
        • 输出EMP表跟DEPT所有的部门编号(去重) SELECT DEPTNO FROM DEPT UNION SELECT DEPTNO FROM EMP;
      • 全集不去重是 UNION ALL
        • 输出EMP表跟DEPT表所有的部门编号(不去重) SELECT DEPTNO FROM DEPT UNION ALL SELECT DEPTNO FROM EMP;
        • 表A多少条数据 + 表B多少条数据
      • 补集 MINUS
        • 取DEPT表存在,EMP表不存在的部门编号 SELECT DEPTNO FROM DEPT MINUS -- 减法 SELECT DEPTNO FROM EMP;
    • 注意：集合运算,上下字段个数,字段类型一定要一致
      • 示例： SELECT 1 NUM FROM DUAL INTERSECT SELECT 2,SYSDATE,'ABC' FROM DUAL; -- 会报错 字段个数不一致 字段类型也不一致
    • 总结：（有点相当于两表内/外关联） INTERSECT :交集 UNION :全集去重 UNION ALL :全集不去重 MINUS :集合相减,取补集