7 集合运算

7.1 表的加减法

集合在数学领域表示"（各种各样）事物的总和"，在数据库领域表示记录(行)的集合，集合运算是对满足同一规则的记录的进行加减等四则运算。用来进行集合运算的运算符称为集合运算符。

表的加法--union

select product_id,sale_price from Product
union
select product_id,sale_price from Product2;

注意事项：作为运算对象的记录的列数必须相同作为运算对象的记录中列的类型必须一致，如果要使用不同的类型，可以使用类型转换函数cast可以使用select语句，但是order by子句只能在最后使用一次
包含重复行的集合运算--all选项包含重复行的只需只需在union后加all即可

select product_id,sale_price from Product
union all
select product_id,sale_price from Product2;

选取表中公共部分--intersect--intersect应用于两张表中，选取出公共记录

select product_id,sale_price from Product
intersect
select product_id,sale_price from Product2
order by product_id;

记录的减法--except

select product_id,sale_price from Product
except
select product_id,sale_price from Product2
order by product_id;

--减法运算的减数和被减数位置的不同，得到的结果也不同

7.2 联结（以列为单位进行联结）

联结(join)就是将其他表中的列添加过来。联结大致分为内连接和外联结。

之前的union和intersect都是以行方向为单位进行操作。union增加行，intersect减少行。

内联结(inner join)from 子句之前from都是一个表，而这次同时使用两张表，使用inner join将两张表联结过来on子句注意on后边的联结条件，指定两张表联结使用的列（联结键），使用内连接必须使用on子句，书写在from和where之间

外联结(outer join)外联结包括做联结和右联结，外联结使用left和right来指定主表，使用left时，from子句中left左边的表是主表，使用right时，from子句中right右边的表是主表。

3张表以上的联结-- 联结的from子句中再次使用使用inner join(outer join) 将其他表也添加进来

交叉联结--cross join(笛卡尔积)-- 进行交叉联结时无法使用内联结和外联结中所使用的on,交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录通常是两张表行数的集合。

8 SQL高级处理

8.1 窗口函数

窗口函数可以进行排序、生成序列号等一系列的聚合函数无法实现的高级操作

窗口函数也称为OLAP函数(online analytical processing 实时分析处理)，例如进行市场分析、创建财务报表、创建计划等日常商务工作。

<窗口函数> over ([partition by <列清单>]
order by <排序用列清单>）
--能够作为窗口函数的函数
--聚合函数（sum,avg,count,max,min）
--rank,dense_rank,row_number等专用窗口函数

语法的基本使用方法--使用rank函数

select product_name,product_type,sale_price,
rank() over (partition by product_type
             order by sale_price) as ranking
from Product;

-- partition by 能够设定排序的对象范围，本例中设定了商品的种类进行排序--order by指定了按照哪一列，何种顺序进行排序，与select语句中末尾的order by一样，可以通过关键字asc/desc来指定升序或者降序partition by在横向上对表进行分组，而order by决定了纵向的排序规则。通过partition by分组后的记录合称为窗口。省略了partition by相当于一个大的窗口。窗口函数兼具group by子句分组和排序的功能，但是不具有group by子句汇总的功能

专用创建函数的种类

rank函数计算排序时，如果存相同位次的记录，则会跳过之后的位次

dense_rnak函数同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次

row_number函数赋予唯一的连续位次(同价也是不同位次)

窗口函数的适用范围窗口函数只能在select中使用，能在order by中使用是因为order by在select之后执行的

作为窗口函数使用的聚合函数所有的聚合函数都能用作窗口函数

计算移动平均值窗口函数就是以窗口为单位进行分割，并在其中进行排序的函数，其实还有包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架

select product_id,product_name,sale_price,
avg(sale_price) over (order by product_id
    rows 2 preceding) as moving_avg
from Product;

-- 这里使用row("行")和preceding("之前")两个关键字，将框架指定为"截止~行"，因此rows 2 preceding这就是截止2之前两行，响应的“之后”是following-- 由于框架是根据当前记录来确定，因此和固定的窗口不同，其范围会随着当前的记录的变化而变化

--如果是当前记录的前后作为汇总对象rows between 1 preceding and 1 following --前中后三行
两个order by在窗口函数中也有一个order by，但是这个order by只是决定窗口函数按照什么样的顺序进行计算的，对结果的排序没有影响。要想最后按照顺序排序，需要在select语句最后，使用order by子句进行指定；

8.2 GROUPING运算符

只使用group by子句和聚合函数是无法同时得到小计和合计的，可以用grouping实现

理解grouping运算符中的cube关键在于形成“积木搭建出的立方体”的印象

grouping 运算符包含以下是三种：rollup(卷起的意思)、cube、grouping sets

同时得到合计行rollup--同时得到合计和小计

select product_type,sum(sale_price) as sum_price
from Product
group by rollup(product_type);
--使用了rollup时多出了最上方的合计和个分类的小计

grouping--让null更加容易分辨该函数在其参数列的值为超级分组记录所产生的null时返回1，其他情况返回0

select grouping(product_type) as product_type,
grouping(regist_date) as regist_date,
sum(sale_price) as sum_price
from Product
group by rollup(product_type,regist_date);

--结果product_type  regist_date  sum_price1                      1                   167800                      0                   111800                      0                   880
cube--用数据来搭积木(立方体的意思)cube语法和rollup相同，只需将rollup换成cube即可，rollup结果一定包含在cube中所谓cube就是将group by子句中的聚合键的“所有可能的组合”的汇总结果集中到一个结果中

grouping sets--取得期望的积木该运算符可以用于从rollup或者cube中取出部分记录

——End——