集合运算
1. 表的加减法
集合运算:A∪B,A∩B,A-B,A⊕B,~A
集合运算符:UNION,INTERSECT, EXCEPT。集合运算符通常会去重
所有的表–以及查询结果–都可以视为集合,所以可以把表视为集合进行上述集合运算
理解为记录行数的增减。
1.1 并集运算A∪B - UNION
-- 表的加法-UNION
select product_id,product_name
from product p
union
select product_id,product_name
from product2;
product_id|product_name|
----------+------------+
0001 |T恤 |
0002 |打孔器 |
0003 |运动T恤 |
0004 |菜刀 |
0005 |高压锅 |
0006 |叉子 |
0007 |擦菜板 |
0008 |圆珠笔 |
0009 |手套 |
0010 |水壶 |
- UNION 与 OR的区别:对同一个表做并集运算,两者都可以;但是对不同的表做并集运算就需要UNION了。
-- 分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
select *
from product p
where sale_price /purchase_price <1.3
or sale_price /purchase_price is null;
等价于
select *
from product p
where sale_price /purchase_price <1.3
union
select *
from product p2
where sale_price /purchase_price is null;
- 并集运算时保留重复行,可以用:UNION ALL
-- 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量
select product_type,count(product_type)
from
(select product_type
from product p
union all
select product_type
from product2) as a
group by product_type ;
这里报错:Every derived table must have its own alias
解决办法:每一个派生出来的表都必须有一个自己的别名,所以给派生表加上别名即可
- 隐式数据类型转换:数据类型不同的列也可以放在一列来显示。hive中进行join关联时,关联列要避免使用隐式数据类型转换,否则容易导致数据倾斜
- hive 数据倾斜:由于数据分布不均匀,造成数据大量的集中到一点,造成数据热点
- 表现现象:任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大,单一reduce的记录数与平均记录数差异过大,最长时长远大于平均时长。
- 产生原因:
- key分布不均匀
- 业务数据本身的特性
- 建表时考虑不周
- 某些SQL语句本身就有数据倾斜
SELECT product_id, product_name, sale_price
FROM Product
union
SELECT product_id, product_name, 'sale_price'
FROM Product2;
product_id|product_name|sale_price|
----------+------------+----------+
0001 |T恤 |1000 |
0002 |打孔器 |500 |
0003 |运动T恤 |4000 |
0004 |菜刀 |3000 |
0005 |高压锅 |6800 |
0006 |叉子 |500 |
0007 |擦菜板 |880 |
0008 |圆珠笔 |100 |
0001 |T恤 |sale_price|
0002 |打孔器 |sale_price|
0003 |运动T恤 |sale_price|
0009 |手套 |sale_price|
0010 |水壶 |sale_price|
--时间日期类型和字符串,数值以及缺失值均能兼容。
select sysdate(),sysdate(),sysdate()
union
select 123,'123',null;
sysdate() |sysdate() |sysdate() |
-------------------+-------------------+-------------------+
2022-07-18 16:08:08|2022-07-18 16:08:08|2022-07-18 16:08:08|
123 |123 | |
1.2 交集运算A∩B - INTERSECT
MySQL 8.0 不支持 INTERSECT。用 inner join 代替。
-- 求product表和product2表的交集
select p1.product_id, p1.product_name
from product p1
inner join product p2
on p1.product_id =p2.product_id ;
也可以等价地将两个查询的检索条件用AND谓词连接来实现。
1.3 减法运算A-B - EXCEPT
MySQL 8.0 不支持 EXCEPT 。用not in 来实现
-- 找出只存在于Product表但不存在于Product2表的商品。
select *
from product p
where product_id not in (select product_id from product2);
1.4 对称差A⊕B
对称差 = UNION求两个表的并集 - INTERSECT求两个表的交集 = (A-B) UNION (B-A)
-- 使用Product表和Product2表的对称差来查询哪些商品只在其中一张表
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);
2. 联结 JOIN
与表达加减法实现的‘行的增减’不同,联结将其他表的列拿过来进行‘添加列’的操作。
与关联子查询的区别:
与关联子查询的区别:
关联子查询以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行。如果外部主查询返回结果有10000行,那么内部子查询也要执行10000次,导致计算开销大。
2.1 内联结 INNER JOIN
语法:
select xxx
from table1
inner join tabel2
on 联结条件
找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。
对比两张表如下,用 product_id 将Product和ShopProduct这两张表连接起来。
select s.shop_id,s.shop_name,s.product_id,
p.product_name,p.product_type,p.sale_price,s.quantity
from product p
inner join shopproduct s
on p.product_id = s.product_id ;
--加上where。执行顺序:FROM 子句(先联结两张表)->WHERE 子句->SELECT 子句
select s.shop_id,s.shop_name,s.product_id,
p.product_name,p.product_type,p.sale_price,s.quantity
from product p
inner join shopproduct s
on p.product_id = s.product_id
where shop_name = '东京' and product_type = '衣服' ;
2.1.1 结合 GROUP BY 子句使用内连结
-- 每个商店中, 售价最高的商品的售价分别是多少?
select s.shop_id ,s.shop_name ,max(p.sale_price)as max_price
from shopproduct s
inner join product p
on s.product_id =p.product_id
group by s.shop_id ;
2.1.2 自联结
一张表也可以与自身作连结。自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。
2.1.3 内联结与关联子查询
-- 找出每个商品种类当中售价高于该类商品的平均售价的商品
-- 法1.子查询
select product_type,product_name,sale_price
from product p
where sale_price > (select avg(sale_price)
from product p2
where p.product_type=p2.product_type
group by product_type
);
select product_type,avg(sale_price)as avg_price
from product p
group by product_type ;
-- 法2.内联结
select *
from product p1
inner join (select product_type,avg(sale_price)as avg_price
from product p
group by product_type )as p2
on p1.product_type =p2.product_type
where p1.sale_price >p2.avg_price;
2.1.4 自然连结(NATURAL JOIN)
自动按照两个表都包含的列(比如product_id)进行等值内联结,不需要on。
SELECT * FROM shopproduct NATURAL JOIN Product
等价于
SELECT SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
,P.product_name,P.product_type,P.sale_price
,P.purchase_price,P.regist_date
FROM shopproduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
2.1.5 使用联结求交集
-- 使用内连结求 Product 表和 Product2 表的交集
select p1.*
from product p1
inner join product2 p2
on p1.product_id =p2.product_id ;
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001 |T恤 |衣服 | 1000| 500| 2009-09-20|
0002 |打孔器 |办公用品 | 500| 320| 2009-09-11|
0003 |运动T恤 |衣服 | 4000| 2800| |
2.2 外联结
外连结会根据外连结的种类有选择地保留无法匹配到的行。有三种:左连结, 右连结和全外连结。
-- 左连结
FROM tabel1 LEFT OUTER JOIN tabel2 ON 条件
-- 右连结
FROM tabel1 RIGHT OUTER JOIN tabel2 ON 条件
-- 全外连结
FROM tabel1 FULL OUTER JOIN tabel2 ON 条件
select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price
from product p
left outer join shopproduct s
on s.product_id = p.product_id
where p.product_name not in (select p2.product_name
from product p2
inner join shopproduct s2
on p2.product_id =s2.product_id );
shop_id|shop_name|product_id|product_name|sale_price|
-------+---------+----------+------------+----------+
| | |高压锅 | 6800|
| | |圆珠笔 | 100|
多出的 2 条记录是高压锅和圆珠笔,这两条记录不在shopproduct表中。
2.2.1 选择主表
最终结果会包含主表内所有的数据。
LEFT 时 ,左侧的表是主表; RIGHT 时,右侧的表是主表.
--使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店
select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price ,s.quantity
from product p
left outer join shopproduct s
on s.product_id = p.product_id
where s.quantity<50;
shop_id|shop_name|product_id|product_name|sale_price|quantity|
-------+---------+----------+------------+----------+--------+
000A |东京 |0001 |T恤 | 1000| 30|
000A |东京 |0003 |运动T恤 | 4000| 15|
000B |名古屋 |0002 |打孔器 | 500| 30|
000B |名古屋 |0004 |菜刀 | 3000| 20|
000B |名古屋 |0006 |叉子 | 500| 10|
000B |名古屋 |0007 |擦菜板 | 880| 40|
000C |大阪 |0003 |运动T恤 | 4000| 20|
这样就少了在所有商店都无货的高压锅和圆珠笔。
原因是先外联结,那么高压锅和圆珠笔的quantity就为null,再进行where筛选所以就筛掉了。
解决办法,先进行where筛选quantity<50的,再进行外联结就有高压锅和圆珠笔了。
select s.shop_id ,s.shop_name ,s.product_id ,p.product_name ,p.sale_price ,s.quantity
from product p
left outer join (select * from shopproduct where quantity < 50)as s
on s.product_id = p.product_id ;
shop_id|shop_name|product_id|product_name|sale_price|quantity|
-------+---------+----------+------------+----------+--------+
000A |东京 |0001 |T恤 | 1000| 30|
000B |名古屋 |0002 |打孔器 | 500| 30|
000A |东京 |0003 |运动T恤 | 4000| 15|
000C |大阪 |0003 |运动T恤 | 4000| 20|
000B |名古屋 |0004 |菜刀 | 3000| 20|
| | |高压锅 | 6800| |
000B |名古屋 |0006 |叉子 | 500| 10|
000B |名古屋 |0007 |擦菜板 | 880| 40|
| | |圆珠笔 | 100| |
2.2.2 全外联结
对左表和右表的所有行都予以保留,
on可以关联的记录显示在一行,不能关联的记录就分别显示,null补充。
MySQL8.0 不支持全外联结,解决办法:左联结结果 UNION 右联结结果
2.2.3 多表联结
-- 多表内联结
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
-- 多表外联结
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
2.2.4 on子句的非等值联结
比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)都可以作为联结条件。
-
- 非等值自左联结(SELF JOIN)
希望对 Product 表中的商品按照售价赋予排名.
- 非等值自左联结(SELF JOIN)
--使用自左连结对每种商品找出价格不低于它的商品
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 ;
product_id|product_name|sale_price|P2_id|P2_name|P2_price|
----------+------------+----------+-----+-------+--------+
0001 |T恤 | 1000|0001 |T恤 | 1000|
0001 |T恤 | 1000|0003 |运动T恤 | 4000|
0001 |T恤 | 1000|0004 |菜刀 | 3000|
0001 |T恤 | 1000|0005 |高压锅 | 6800|
0002 |打孔器 | 500|0001 |T恤 | 1000|
0002 |打孔器 | 500|0002 |打孔器 | 500|
0002 |打孔器 | 500|0003 |运动T恤 | 4000|
0002 |打孔器 | 500|0004 |菜刀 | 3000|
0002 |打孔器 | 500|0005 |高压锅 | 6800|
0002 |打孔器 | 500|0006 |叉子 | 500|
0002 |打孔器 | 500|0007 |擦菜板 | 880|
0003 |运动T恤 | 4000|0003 |运动T恤 | 4000|
0003 |运动T恤 | 4000|0005 |高压锅 | 6800|
0004 |菜刀 | 3000|0003 |运动T恤 | 4000|
0004 |菜刀 | 3000|0004 |菜刀 | 3000|
0004 |菜刀 | 3000|0005 |高压锅 | 6800|
0005 |高压锅 | 6800|0005 |高压锅 | 6800|
0006 |叉子 | 500|0001 |T恤 | 1000|
0006 |叉子 | 500|0002 |打孔器 | 500|
0006 |叉子 | 500|0003 |运动T恤 | 4000|
0006 |叉子 | 500|0004 |菜刀 | 3000|
0006 |叉子 | 500|0005 |高压锅 | 6800|
0006 |叉子 | 500|0006 |叉子 | 500|
0006 |叉子 | 500|0007 |擦菜板 | 880|
0007 |擦菜板 | 880|0001 |T恤 | 1000|
0007 |擦菜板 | 880|0003 |运动T恤 | 4000|
0007 |擦菜板 | 880|0004 |菜刀 | 3000|
0007 |擦菜板 | 880|0005 |高压锅 | 6800|
0007 |擦菜板 | 880|0007 |擦菜板 | 880|
0008 |圆珠笔 | 100|0001 |T恤 | 1000|
0008 |圆珠笔 | 100|0002 |打孔器 | 500|
0008 |圆珠笔 | 100|0003 |运动T恤 | 4000|
0008 |圆珠笔 | 100|0004 |菜刀 | 3000|
0008 |圆珠笔 | 100|0005 |高压锅 | 6800|
0008 |圆珠笔 | 100|0006 |叉子 | 500|
0008 |圆珠笔 | 100|0007 |擦菜板 | 880|
0008 |圆珠笔 | 100|0008 |圆珠笔 | 100|
-- 然后对售价不低于它的商品使用 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;
product_id|product_name|sale_price|my_rank|
----------+------------+----------+-------+
0005 |高压锅 | 6800| 1|
0003 |运动T恤 | 4000| 2|
0004 |菜刀 | 3000| 3|
0001 |T恤 | 1000| 4|
0007 |擦菜板 | 880| 5|
0002 |打孔器 | 500| 7|
0006 |叉子 | 500| 7|
0008 |圆珠笔 | 100| 8|
使用非等值自左连结进行累计求和,思路:
1.对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品
2.按照 P1.product_Id 分组,对 P2_price 求和
-
- 交叉连结—— CROSS JOIN(笛卡尔积)
交叉联结:在连结去掉 ON 子句,对两张表中的全部记录进行交叉组合。
如a表10条记录,b表5条记录,则交叉联结结果有50条记录。
交叉连接没有应用到实际业务中的原因,一是没有应用价值,二是运算消耗大。
- 交叉连结—— CROSS JOIN(笛卡尔积)
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
from a
cross join b
--2.使用逗号分隔两个表,并省略 ON 子句
from a,b
交叉联结 + where (内联结中的on子句条件) = 内联结
3 习题
3.1 找出 product 和 product2 中售价高于 500 的商品的基本信息
select *
from product
where sale_price >500
union
select *
from product2
where sale_price >500;
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001 |T恤 |衣服 | 1000| 500| 2009-09-20|
0003 |运动T恤 |衣服 | 4000| 2800| |
0004 |菜刀 |厨房用具 | 3000| 2800| 2009-09-20|
0005 |高压锅 |厨房用具 | 6800| 5000| 2009-01-15|
0007 |擦菜板 |厨房用具 | 880| 790| 2008-04-28|
0009 |手套 |衣服 | 800| 500| |
0010 |水壶 |厨房用具 | 2000| 1700| 2009-09-20|
3.2 借助对称差的实现方式, 求product和product2的交集
A∩B = A∪B - A⊕B
对称差 = UNION求两个表的并集 - INTERSECT求两个表的交集 = (A-B) UNION (B-A)
select * from product
union
select * from product2
where product_id not in (select product_id from product
where product_id not in (select product_id from product2)
union
select product_id from product2
where product_id not in (select product_id from product)
);
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001 |T恤 |衣服 | 1000| 500| 2009-09-20|
0002 |打孔器 |办公用品 | 500| 320| 2009-09-11|
0003 |运动T恤 |衣服 | 4000| 2800| |
0004 |菜刀 |厨房用具 | 3000| 2800| 2009-09-20|
0005 |高压锅 |厨房用具 | 6800| 5000| 2009-01-15|
0006 |叉子 |厨房用具 | 500| | 2009-09-20|
0007 |擦菜板 |厨房用具 | 880| 790| 2008-04-28|
0008 |圆珠笔 |办公用品 | 100| | 2009-11-11|
/*
结果不对,分析得到
select product_id from product
where product_id not in (select product_id from product2)
union
select product_id from product2
where product_id not in (select product_id from product)
的结果包含4-10,而
select * from product2
where product_id not in (select product_id from product
where product_id not in (select product_id from product2)
union
select product_id from product2
where product_id not in (select product_id from product)
);
的结果为1-3,是目标结果。所以整个语句将product和(1-3)并起来了,所以结果不对。希望先对product和product1并起来,再not in。更改:
*/
select *
from (select * from product
union
select * from product2)as a
where product_id not in (select product_id from product
where product_id not in (select product_id from product2)
union
select product_id from product2
where product_id not in (select product_id from product)
);
product_id|product_name|product_type|sale_price|purchase_price|regist_date|
----------+------------+------------+----------+--------------+-----------+
0001 |T恤 |衣服 | 1000| 500| 2009-09-20|
0002 |打孔器 |办公用品 | 500| 320| 2009-09-11|
0003 |运动T恤 |衣服 | 4000| 2800| |
3.3 每类商品中售价最高的商品都在哪些商店有售 ?
-- 1.求出每类商品的最高sale_price,使用自联结找到最高sale_price对应的商品id/name/type
select p1.product_id ,p1.product_name ,p1.sale_price ,p1.product_type
from product p1
inner join
(select product_type, max(sale_price) as max_sale_price
from product
group by product_type )as a
on p1.product_type =a.product_type
and p1.sale_price =a.max_sale_price ;
product_id|product_name|sale_price|product_type|
----------+------------+----------+------------+
0002 |打孔器 | 500|办公用品 |
0003 |运动T恤 | 4000|衣服 |
0005 |高压锅 | 6800|厨房用具 |
-- 2.用product_id和shopproduct联结,找到商品对应的商店
select p1.product_id ,p1.product_name ,p1.sale_price ,p1.product_type ,
a.max_sale_price,s.shop_name
from product p1
inner join shopproduct s
on s.product_id = p1.product_id
inner join (select product_type, max(sale_price) as max_sale_price
from product
group by product_type )as a
on p1.product_type =a.product_type
and p1.sale_price =a.max_sale_price ;
product_id|product_name|sale_price|product_type|max_sale_price|shop_name|
----------+------------+----------+------------+--------------+---------+
0002 |打孔器 | 500|办公用品 | 500|东京 |
0003 |运动T恤 | 4000|衣服 | 4000|东京 |
0002 |打孔器 | 500|办公用品 | 500|名古屋 |
0003 |运动T恤 | 4000|衣服 | 4000|名古屋 |
0003 |运动T恤 | 4000|衣服 | 4000|大阪 |
3.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
这题比3.3简单
-- 内联结
select p1.product_id,p1.product_name ,p1.product_type ,p2.max_sale_price
from product p1
inner join (select product_type,max(sale_price) as max_sale_price
from product
group by product_type )as p2
on p1.product_type =p2.product_type
and p1.sale_price =p2.max_sale_price;
-- 关联子查询
select product_id,product_name ,product_type ,sale_price as max_sale_price
from product p
where (product_type,sale_price)
in (select product_type,max(sale_price)
from product
group by product_type);
product_id|product_name|product_type|max_sale_price|
----------+------------+------------+--------------+
0002 |打孔器 |办公用品 | 500|
0003 |运动T恤 |衣服 | 4000|
0005 |高压锅 |厨房用具 | 6800|
3.5 用关联子查询实现:在 product 表中,取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
select p.product_id ,p.product_name ,p.sale_price ,
(select sum(sale_price)
from product p1
where p.sale_price >=p1.sale_price)as cur_sum_price
from product p
order by p.sale_price ;
product_id|product_name|sale_price|cur_sum_price|
----------+------------+----------+-------------+
0008 |圆珠笔 | 100| 100|
0002 |打孔器 | 500| 1100|
0006 |叉子 | 500| 1100|
0007 |擦菜板 | 880| 1980|
0001 |T恤 | 1000| 2980|
0004 |菜刀 | 3000| 5980|
0003 |运动T恤 | 4000| 9980|
0005 |高压锅 | 6800| 16780|
结果中打孔器对应cur_sum_price不对,应该为600。分析子查询的执行:
- 当主查询的圆珠笔100传进子查询时,
select sum(sale_price)
from product p1
where 100 >= p1.sale_price)as cur_sum_price
符合条件的有圆珠笔100.所以圆珠笔cur_sum_price=100 - 当主查询的打孔器500传进子查询时,
select sum(sale_price)
from product p1
where 500 >= p1.sale_price)as cur_sum_price
符合条件的有圆珠笔100,打孔器500,叉子500.所以打孔器cur_sum_price=1100.
这里两个物品的sale_Price相等就是问题了。
得想办法在打孔器和叉子相等时,不计算这个叉子500。发现打孔器和叉子的product_id 不同,所以可以在sale_price相等时加上对product_id的限制。
select p.product_id ,p.product_name ,p.sale_price ,
(select sum(sale_price)
from product p1
where p.sale_price > p1.sale_price
or (p.sale_price=p1.sale_price and p.product_id>=p1.product_id))as cur_sum_price
from product p
order by p.sale_price,p.product_id ;
product_id|product_name|sale_price|cur_sum_price|
----------+------------+----------+-------------+
0008 |圆珠笔 | 100| 100|
0002 |打孔器 | 500| 600|
0006 |叉子 | 500| 1100|
0007 |擦菜板 | 880| 1980|
0001 |T恤 | 1000| 2980|
0004 |菜刀 | 3000| 5980|
0003 |运动T恤 | 4000| 9980|
0005 |高压锅 | 6800| 16780|
这样就正确了。
旧题新做:使用内联结实现task03-复杂一点的查询-习题6.4
方法一:关联子查询
Create view avgpricebytype
as
select product_id, product_name, product_type,sale_price,
(select avg(sale_price) as sale_price_avg_type
from product P2
where p1.product_type = p2.product_type) # 使用关联子查询进行结算
from product p1;
方法二:内联结
select p1.product_id, p1.product_name, p1.product_type,p1.sale_price,p2.sale_price_avg_type
from product p1
inner join (select product_type, avg(sale_price) as sale_price_avg_type
from product
group by product_type
) as p2
on p1.product_type =p2.product_type ;