Datawhale开源学习SQL: 3. TASK04 集合运算

开源地址：https://github.com/datawhalechina/wonderful-sql

集合运算

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

在数据库中, 所有的表–以及查询结果–都可以视为集合, 因此也可以把表视为集合进行上述集合运算, 在很多时候, 这种抽象非常有助于对复杂查询问题给出一个可行的思路。

表的加减法

加法UNION

< TABLE 1 > UNION < TABLE 2 >
返回去重上下合并数据。若要全部结果则需要使用< TABLE 1 > UNION ALL < TABLE 2 >

UNION 与 OR 谓词

使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果。但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了。

即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION

隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM Product
 UNION
SELECT product_id, product_name,sale_price
  FROM Product2;

这个查询还是能执行并返回

需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

交运算INTERSECT

MySQL 8.0 不支持，用inner join代替

差集,补集与表的减法 EXCEPT

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。

习题1

1.1 假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

SELECT * FROM product
	WHERE sale_price >= 1.5*purchase_price
UNION
SELECT * FROM product
	WHERE sale_price < 800;

1.2 分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品。

-- 使用 OR 谓词
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;
-- 使用UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price < 1.3
 
 UNION
SELECT * 
  FROM Product 
 WHERE sale_price / purchase_price IS NULL;

1.3 商店决定对product表中利润低于50% 或者 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集。

SELECT * 
FROM product 
WHERE sale_price / purchase_price < 1.5
UNION ALL
SELECT * 
FROM Product 
WHERE sale_price < 1000;

1.4 使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 UNION
SELECT 'chars', 123,  null

1.5 找出只存在于Product表但不存在于Product2表的商品。

SELECT * FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2);

1.6 使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示。

SELECT * FROM product
WHERE sales_price >= 2000 AND 
product_id NOT IN (SELECT product_id FROM product 
	WHERE sales_price < 1.3*purchase_price);

1.7 使用Product表和Product2表的对称差来查询哪些商品只在其中一张表

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

连结(JOIN)

INNER JOIN

FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
注意：

1. 进行连结时需要在 FROM 子句中使用多张表.
2. 必须使用 ON 子句来指定连结条件.
3. SELECT 子句中的列最好按照 表名.列名 的格式来使用。

结合 WHERE 子句使用INNER JOIN

三种写法

-- 1  查询的执行顺序: FROM 子句->WHERE 子句->SELECT 子句
SELECT  SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type
       ,P.sale_price,SP.quantity
FROM ShopProduct AS SP 
	INNER JOIN Product AS P
    ON SP.product_id = P.product_id
WHERE SP.shop_name = '东京'
	AND P.product_type = '衣服' ;

-- 2  查询的执行顺序: FROM 子句->SELECT 子句
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type
       ,P.sale_price,SP.quantity
FROM ShopProduct AS SP
	INNER JOIN Product AS P
    ON (SP.product_id = P.product_id
    	AND SP.shop_name = '东京'
    	AND P.product_type = '衣服') ;

-- 3   任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type
       ,P.sale_price,SP.quantity
FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
	SELECT *
		FROM ShopProduct
		WHERE shop_name = '东京' ) AS SP
	INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
	(SELECT *
		FROM Product
		WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

自然连结 NATRUAL JOIN

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

ON 子句进阶–非等值连结

非等值自左连结(SELF JOIN)

使用非等值自左连结实现排名。
E.g. : 希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,

SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS my_rank
FROM (--使用自左连结对每种商品找出价格不低于它的商品
	SELECT P1.product_id,P1.product_name,P1.sale_price
		,P2.product_id AS P2_id,P2.product_name AS P2_name,P2.sale_price AS P2_price 
	FROM Product AS P1 
	LEFT OUTER JOIN Product AS P2 
	ON P1.sale_price <= P2.sale_price 
	) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY my_rank; 

交叉连结—— CROSS JOIN(笛卡尔积)

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
FROM ShopProduct AS SP
CROSS JOIN Product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
FROM ShopProduct AS SP , Product AS P;