14.高级查询-查询函数

DECODE函数

• 语法：DECODE(expr,serch1,result1,serch1,result2…,default)

• DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果

• 可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后的default的值

• default参数是可选的，如果没有提供default参数值，且没有匹配到时，将返回NULL

• DECODE函数可以在GROUP BY语句中使用，可以将字段值不同的记录分成一组

• DECODE函数可以在ORDER BY语句中使用，可以按照字段内容排序，但是有局限性

CASE语句

• 和DECODE函数功能相似的有CASE语句，实现类似于if-else

• 语法：CASE expr WHEN search1 THEN result1 WHEN search2 THEN result2…ELSE default END

• 语法理解参照DECODE函数

排序函数

• 它允许对结果集按照指定的字段分组，在组内再按照指定的字段排序，最终生成组内编号

• 语法：排序函数类型 OVER(PARTITION BY 字段1 ORDER BY 字段2)

• 字段1：按照给定字段分组，字段2：按照给定字段排序

排序函数：ROW_NUMBER 函数

• 生成组内连续且唯一的数字

• 语法：ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2)

排序函数：RANK 函数

• 生成组内不连续也不唯一的数字，同样排序字段值一样的记录，生成的数字也一样

• 语法：RANK() OVER(PARTITION BY 字段1 ORDER BY 字段2)

排序函数：DENSE_RANK 函数

• 生成组内连续但不唯一的数字

• 语法：RANK() OVER(PARTITION BY 字段1 ORDER BY 字段2)

集合操作

• 为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并，交，差

• 集合操作符包含以下4种：

  1. UNION、UNION ALL：并集，UNION会去除两个结果集的重复的记录，UNION ALL则不会

  2. INTERSECT：交集，使用INTERSECT操作符后的结果集会以第一列的数据做升序排列

  3. MINUS：差集，只有在第一个结果中存在，在第二个结果集中不存在的数据，才能够被显示出来，也就是结果集一减去结果集二的结果

• 语法：SELECT statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT statement2…

• 多个结果集合并，它们列的个数和数据类型需要匹配，不匹配的时候，可以使用NULL来代替不匹配的字段，那么该字段的值都为NULL

• ORDER BY 子句只能放在最后一个查询语句中

高级分组函数

• 高级分组函数都是GROUP BY子句的扩展，当我们想组合多个分组查询的结果集时，就可以使用高级分组函数

高级分组函数：ROLLUP

• 分组原则：逐次递减，一直到所有参数都不要，每一种分组都统计一次并且在一个结果集显示；

• 用法：GROUP BY ROLLUP(a,b,c…)，a，b，c为要逐个递减的字段

• 等价于：SELECT xxx GROUP BY(a,b,c) UNION ALL SELECT xxx GROUP BY(a,b) UNION ALL SELECT xxx GROUP BY(a) UNION ALL 全表

• 分组次数：参数个数 + 1

高级分组函数：CUBE：

• 分组原则：每种组合分一次组

• 用法：GROUP BY CUBE(a,b…)，a，b为要逐个递减的字段

• 等价于：SELECT xxx GROUP BY(a,b) UNION ALL SELECT xxx GROUP BY(a) UNION ALL SELECT xxx GROUP BY(b) UNION ALL 全表

• 分组次数：2的参数个数次方

高级分组函数：GROUPING SETS

• 分组原则：每种参数是一种分组，然后将这些分组统计后并在一个结果集显示

• 用法：GROUP BY GROUPING SETS((a),(b))

• 等价于：SELECT xxx GROUP BY(a) UNION ALL SELECT xxx GROUP BY(b)

演示：查询函数

• DECODE

  • 根据职位计算奖励金额，当职位分别是‘MANAGER’、‘ANALYST’、‘SALESMAN’时，奖励金额分别是薪水的1.2倍，1.1倍，1.05倍，如果不是这三个职位，则奖励金额取薪水值
    SELECT ename,job,sal,
    DECODE(job,
    ‘MANAGER’,sal1.2,
    ‘ANALYST’,sal1.1,
    ‘SALESMAN’,sal*1.05,
    sal
    ) bonus
    FROM emp;

• 在GROUP BY语句中使用DECODE函数

  • 计算职位人数，将职位是‘MANAGER’、‘ANALYST’看作一组，其余职业看作一组，分别统计人数
    SELECT COUNT(*),DECODE(job,‘MANAGER’,‘VIP’,‘ANALYST’,‘VIP’,‘OTHER’)
    FROM emp GROUP BY DECODE(job,‘MANAGER’,‘VIP’,‘ANALYST’,‘VIP’,‘OTHER’);

• 在ORDER BY语句中使用DECODE函数

  • Dept表中按“OPERATIONS”，“ACCOUNTING”，“SALES”排序，无法按照字面数据排序
    SELECT deptno,dname,loc FROM dept
    ORDER BY DECODE(dname,‘OPERATIONS’,1,‘ACCOUNTING’,2,‘SALES’,3);

• CASE语句

  • 执行“根据职位计算奖励金额”的查询
    SELECT ename,job,sal,
    (CASE job WHEN ‘MANAGER’ THEN sal1.2
    WHEN ‘ANALYST’ THEN sal1.1
    WHEN ‘SALESMAN’ THEN sal*1.05
    ELSE sal END
    ) bonus
    FROM emp;

• 排序函数：ROW_NUMBER

  • 查看每个部门的工资排名（注意WAD,MARTIN,JAMES员工记录的RANK值）
    SELECT ename,sal,deptno,
    ROW_NUMBER() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
    ) rank
    FROM emp;

• 排序函数：RANK

  • 注意WAD,MARTIN,JAMES员工记录的RANK值
    SELECT ename,sal,deptno,
    RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
    ) rank
    FROM emp;

• 排序函数：DENSE_RANK

  • 注意WAD,MARTIN,JAMES员工记录的RANK值
    SELECT ename,sal,deptno,
    DENSE_RANK() OVER(
    PARTITION BY deptno
    ORDER BY sal DESC
    ) rank
    FROM emp;

• 集合操作：UNION

  • 合并职位是’MANAGER’的员工和薪水大于2500的员工集合
    SELECT ename,job,sal FROM emp WHERE job=‘MANAGER’
    UNION
    SELECT ename,job,sal FROM emp WHERE sal>2500;

• 集合操作：UNION ALL

  SELECT ename,job,sal FROM emp WHERE job=‘MANAGER’
  UNION ALL
  SELECT ename,job,sal FROM emp WHERE sal>2500;

• 集合操作：INTERSECT

  • 查看工资高于2500且工作是’MANAGER’的员工
    SELECT ename,sal,job FROM emp WHERE sal>2500
    INTERSECT
    SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’;

• 集合操作：MINUS

  • 列出职位是MANAGER但是薪水低于2500的员工记录
    SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’
    MINUS
    SELECT ename,sal,job FROM emp WHERE sal>=2500;

• 素材，该表是存储某个商店在某年某月某日的销售额

  CREATE TABLE sales_tab(
  year_id NUMBER NOT NULL,
  month_id NUMBER NOT NULL,
  day_id NUMBER NOT NULL,
  sales_value NUMBER(10,2) NULL
  );
  INSERT INTO sales_tab
  SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
  TRUNC(DBMS_RANDOM.value(1,13)) AS month_id,
  TRUNC(DBMS_RANDOM.value(1,32)) AS day_id,
  TRUNC(DBMS_RANDOM.value(1,100),2) AS sales_value
  FROM dual
  CONNECT BY level <=1000;

• 查看每天总营业额

  SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id,day_id ORDER BY year_id,month_id,day_id;

• 查看每个月总营业额

  SELECT year_id,month_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id ORDER BY year_id,month_id;

• 查看每年总营业额

  SELECT year_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id ORDER BY year_id;

• 查看总营业额

  SELECT SUM(sales_value) FROM sales_tab;

• 当想把上面的结果显示在一张表中，可以使用UNION ALL

  SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id,day_id
  UNION ALL
  SELECT year_id,month_id,NULL,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id
  UNION ALL
  SELECT year_id,NULL,NULL,SUM(sales_value) FROM sales_tab
  GROUP BY year_id
  UNION ALL
  SELECT NULL,NULL,NULL,SUM(sales_value) FROM sales_tab;

• 高级分组函数：ROLLUP

  • 查看每天，每月，每年，以及总营业额
    SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
    GROUP BY ROLLUP(year_id,month_id,day_id);

• 高级分组函数：CUBE

  SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
  GROUP BY CUBE(year_id,month_id,day_id)
  ORDER BY year_id,month_id,day_id;

• 高级分组函数：GROUPING SETS

  • 仅查看每天与每月的营业额
    SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
    GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id)