Oracle基础（十四）：集合操作、高级分组函数

一、集合操作

集合操作（结果集的操作），多个SQL语句查询的结果集并在一起显示。

①UNION：并集，（A∪B，取两个集合中所有的元素，去掉多余的重复的元素）
②UNION ALL：全并集，两个结果集的全并集会出现重复的记录（元素）
③INTERSECT：交集,取结果集共有的记录（A∩B，取两个集合中的公共元素）
④MINUS：差集，获得两个结果集的差集，只有在第一个结果集中存在,在第二个结果集中不存在的数据,才能够被显示出来。也就是结果集一减去结果集二的结果。即：只取我有和你没有的部分。

集合操作的语法：(多条做集合操作的SELECT语句的字段个数和数据类型必须匹配，ORDER BY 只能放在最后一个查询语句当中)

--语法：
SELECT statement1
 [UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2

（1）UNION：并集

--（并集）合并职位是MANAGER的员工和薪水大于2500的员工集合
--(两个SELECT语句的结果集中都存在有名字为BLAKE和JONES两条符合WHERE条件的记录)
select ename,job,sal from emp where job='MANAGER'
union
select ename,job,sal from emp where sal>2500;

（2）UNION ALL：全并集

--（全并集）两个结果集的全并集可能会出现重复的记录（元素）
--(两个SELECT语句的结果集都分别存在有名字为BLAKE和JONES两条符合WHERE条件的记录，
--两个结果集的全并集操作不会过滤掉存在的相同元素)
select ename,job,sal from emp where job='MANAGER'
union all
select ename,job,sal from emp where sal>2500;

（3）INTERSECT：交集

--（交集）使用交集INTESECT对结果集操作，取结果集共有的记录
--INTERSECT,两个结果集共有的部分，即职位是MANAER且工资>2500的记录才会被保留
--两个SELECT语句的结果集当中，都分别存在有名字为BLAKE和JONES这两条符合WHERE条件的记录
select ename,job,sal from emp where job='MANAGER'
INTERSECT
select ename,job,sal from emp where sal>2500;

（4）MINUS：差集

--（差集）使用差集MINUS对结果集操作,只取我有和你没有的部分
--即：job='MANAGER'，但sal不高于2500的记录。
select ename,job,sal from emp where job='MANAGER'
minus
select ename,job,sal from emp where sal>=2500;

二、高级分组函数

ROLLUP、CUBE、和GROUPING SETS 运算符是GROUP BY子句的扩展，可以
生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。

①GROUP BY ROLLUP(a,b,c)
②GROUP BY CUBE(a,b,c)
③GROUP BY GROUPING SETS((a),(b))

在学习高级分组函数前，先准备一个数据表，sales_tab（一间商店每天、笔销售的营业额）

--创建一个测试表sales_tab（一间商店每天、笔销售的营业额）
CREATE TABLE sales_tab(
  year_id NUMBER NOT NULL,
  month_id NUMBER NOT NULL,
  day_id NUMBER NOT NULL,
  sales_value NUMBER(10,2)NOT NULL
);
--给测试表sales_tab插入随机数据，2010-2011年，1-12月，1-31天，
--销售额是小于100的随机浮点数(数据表示该商店连续记账了两年)
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,
  ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level <= 1000;
COMMIT;
SELECT * FROM sales_tab;

场景引入

场景：分别查看sales_tab的每天、每月、每年和总共的营业额。

--查看每天的营业额？
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语句的字段个数和数据类型必须匹配，
若给每个SELECT语句添加一个NULL字段的话能符合要求，但这并不理想。。。

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;
--以上全并集分组GROUP BY后有year_id,month_id,day_id字段每次字段-1的规律

（1）ROLLUP()

ROLLUP()：分组原则（策略）是参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果并且在一个结果集中显示，

如：GROUP BY ROLLUP(a,b,c)三个字段一样的分一次组、GROUP BY ROLLUP(a,b)两个字段一样的分一次组，
GROUP BY ROLLUP(a)一个字段一样的分一次组，直到最后一个字段都不要，全表看成一组。
GROUP BY ROLLUP(字段a,[字段b,字段c...]) 等价于:

SELECT xxx FROM xxx GROUP BY 字段a,字段b,字段c
UNION ALL
SELECT xxx FROM xxx GROUP BY 字段a,字段b
UNION ALL
SELECT xxx FROM xxx GROUP BY 字段a
UNION ALL 全表


--查看每天，每月，每年以及所有营业额？
select year_id,month_id,day_id,sum(sales_value)
from sales_tab 
group by rollup(year_id,month_id,day_id);

（2） CUBE()

CUBE()：CUBE 的分组策略为每个参数组合进行一次分组，

即每一种组合分一次组(分组次数：2的参数个数次方，如3个参数就是2?)

GROUP BY CUBE(a,b,c),（假设abc分别为年月日）等同于：
a,b,c 同年同月同日
a,b 同年同月
b,c 同月同日
a,c 同年同日
a 同年
b 同月
c 同日
全表

--查看每天，每月，每年以及所有营业额？
select year_id,month_id,day_id
from sales_tab 
group by cube(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

（3）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));--每月
--（使用括号才视为是一个参数）

posted @ 2022-06-09 01:45 禾喵阅读(452) 评论(0) 编辑收藏举报

Coder Cat