hivesql集合函数 hive 集合运算

转载

mob6454cc6ba5a5 2023-07-14 23:43:37

文章标签 hivesql集合函数子查询 MySQL 多表 文章分类 Hive 大数据

第4章集合运算

摘要：感觉不是很难，但是知识点比较零碎，自己做了两张表格，可能有不大准确的地方，欢迎指正

第4章集合运算

4.1 表的加减法
4.2 连结 JOIN
练习题

hivesql集合函数 hive 集合运算_子查询

4.1 表的加减法

表的加法-UNION,两个SELECT FROM 语句中间使用UNION

同一张表，可以使用UNION或者OR，不同的表只能使用UNION
如果想要保留重复行，采用UNION ALL语句

SELECT *
FROM product AS p1
WHERE product_id NOT IN (SELECT product_id FROM product2) -- 这儿用SELECT 语句不能用p1.product_id，这--- 儿不能写p2否者报错
UNION
SELECT *
FROM product2 AS p2
WHERE  product_id NOT IN (SELECT product_id FROM product)

如果用UNION，两个SELECT语句各有各的WHERE,而不是两个SELECT对应一个WHERE.
UNION可以隐式数据类型转换，hive中join关联时，应该避免隐式数据类型转换，否则容易数据倾斜
日期时间类型可以和数值、字符串、NULL兼容
MySQL 8.0 不支持交运算INTERSECT,用 inner join 来求得交集,或者AND

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

MySQL 8.0 不支持交运算EXCEPT ,用 NOT IN
对称差=并集-交集

表格	加法		减法
集合	并	交	差	对称差
关键字	UNION	INTERSECT	EXCEPT
MySQL 8.0是否支持	支持	不支持	不支持	否
替代方案	单表用OR，多表只能UNION	INNER JOIN ON	WHERE NOT IN	NOT IN UNION NOT IN
方案简记	两个SELECT FROM 语句中间使用UNION	内连结	NOT IN	A-B UNION B-A
用法	SELECT FROM UNION SELECT FROM	两个SELECT FROM 语句中使用INNER JOIN,最后放ON
注意事项	合并后删除重复记录，不想删除用UNION ALL	也可以用对称差，并集-对称差

4.2 连结 JOIN

能够简化关联子查询，关联子查询在数据量较多的时候时间消耗很大
内连结，INNER JOIN

FROM tab_1 
INNER JOIN
tab_2
ON conditions

表先连结后筛选，

自然连结，NATURAL JOIN ,不需要使用ON,就是一个定义表示一张表的连结
外连接，LETF/RIGHT/FULL OUTER JOIN,那个连结就会保留哪个的不匹配的数据
全连接

含义：对左表和右表的所有数据予以保留，能用ON关联的，就搞在一起一行显示，不能用ON 关联的就分别显示，多余的行用缺失值进行填充
MySQL 8.0不支持全连接，😲，惊呆！替代方案是：左连结+右连结+UNION

多表连结

一般是2-3张表，原则上无限制

ON 子句非等值连结

SELECT column1,column2,**
FROM 
(
    连结语句
)

GROUP BY
ORDER BY

连结语句内部，使用不等式，如果有相等的数值，可以进一步细化约束规则

交叉连结

CROSS JOIN，第一项每一行与第二项的每一行相乘，得出的结果意义不大
交叉连结又称为笛卡尔积

多表连结，多加INNER JOIN ON 语句即可，最后才是WHERE，外连结同理。
连结与集合的区别，连结一般有ON条件，集合没有
在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）。
duplicate column name，有列重名，内连结的时候ON条件的列不需要都选出来。

内连结	外连结
ON决定筛选条件	有选择保留无法匹配到的列，左/右/全连结
SELECT FROM AS INNER JOIN AS ON	SELECT FROM AS LEFT OUTER JOIN AS ON
所有的列写在SELECT语句里面，表名.列名，公共列做桥梁，写在ON语句后面	先筛选后连结，可以避免NULL被筛掉，
可以用小括号把子查询封装再写SELECT *，最后加WHERE	不支持全连结，左右连结之后用UNION
WHERE在FROM 后执行，直接最后加WHERE即可	右连结可以用左连结+调整表的顺序
可以把WHERE条件拆开分别写在两个SELECT中

练习题

4.1

找出 product 和 product2 中售价高于 500 的商品的基本信息。

SELECT *
FROM product
WHERE sale_price >500
UNION
SELECT *
FROM product2
WHERE sale_price >500;

hivesql集合函数 hive 集合运算_多表_02

4.2

借助对称差的实现方式, 求product和product2的交集

SELECT *
FROM (SELECT *
FROM product
UNION
SELECT *
FROM product2 ) AS bingji
WHERE product_id NOT IN (SELECT product_id FROM 
(
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))AS duichencha);

hivesql集合函数 hive 集合运算_MySQL_03

4.3

每类商品中售价最高的商品都在哪些商店有售？

USE shop1;
SELECT product_id,product_type,sale_price,product_name
	,shop_id,shop_name
FROM (SELECT p.product_id,p.product_type,p.sale_price,p.product_name
	,sp.shop_id,sp.shop_name
	FROM product AS p
	INNER JOIN
	shopproduct AS sp
	ON p.product_id=sp.product_id
    )AS lianjie1		-- 内连结表格
WHERE product_id IN (
	SELECT product_id FROM 
	(SELECT product_id,MAX(sale_price),product_type
	FROM product
	GROUP BY product_type) AS t1)
ORDER BY product_type;		-- 每类价钱最高的商品的ID

hivesql集合函数 hive 集合运算_hivesql集合函数_04