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));--每月 --(使用括号才视为是一个参数)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步