[大数据] 阿里天池SQL训练营task4学习笔记

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 阿里天池SQL训练营task4学习笔记 -> 正文阅读

[大数据]阿里天池SQL训练营task4学习笔记

1.注意区分关联子查询、两表求并集、表连接三者间的关系。

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

求解此问题，用where＋and肯定可以解决，但此处的问题是让你用not谓词解决，所以在and后另外一个条件不能直接表达，要用not in +子查询的方式，这种方式的语法就相当于一个比较条件语句。

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

3.使用not in + union可达到求两表对称差的目的，但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差.

4.借助两表的并集和对称差可实现求交集目的，两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。

5.书写顺序：

SELECT》FROM 》WHERE》GROUP BY》HAVE》ORDER BY

执行顺序：

FROM 》WHERE》GROUP BY》HAVE》SELECT》ORDER BY

6.正常的SQL语句执行顺序是先执行子查询，但是关联子查询的顺序是先执行主查询。这样做的目的主要是为了解决，主查询每次查询一行记录与子查询直接输出聚合查询结果之间数量不等的矛盾。通过“关联”到子查询，每次限制子查询的输出数量，从而实现一表中查询不同种类聚合结果的目的，所以关联子查询需要先执行主查询。有点类似python中for循环的嵌套。

关联子查询与表连接能达到同样的效果，但为什么还要有表连接存在呢？

如果你使用过 excel 的 vlookup 函数, 你会发现这个函数正好也能够实现这个功能. 实际上, 在思路上, 关联子查询更像是 vlookup 函数: 以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行.
当数据量较少时, 这种方式并不会有什么性能问题, 但数据量较大时, 这种方式将会导致较大的计算开销: 对于外部查询返回的每一行数据, 都会向内部的子查询传递一个关联列的值, 然后内部子查询根据传入的值执行一次查询然后返回它的查询结果. 这就使得, 例如外部主查询的返回结果有一万行, 那么子查询就会执行一万次, 这将会带来非常恐怖的时间消耗。

为什么要使用关联子查询？

有时候需要在同一张表中查询延展信息，不找一个子查询（视图）作为参照的话无法直接查询，所以此时可使用关联子查询，能用关联子查询的问题一般也可使用内连接解决，只要在其中一个连接表中提前嵌入筛选条件即可。

关于内连结,需要注意以下三点:

要点一: 进行连结时需要在 FROM 子句中使用多张表.

之前的 FROM 子句中只有一张表, 而这次我们同时使用了shopproduct 和product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:

FROMshopproduct AS SP INNER JOINproduct AS P

结合 WHERE 子句使用内连结

第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件.

SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROMshopproduct AS SP
         INNER JOINproduct AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;

还记得我们学习子查询时的认识吗? 子查询的结果其实也是一张表,只不过是一张虚拟的表, 它并不真实存在于数据库中, 只是数据库中其他表经过筛选,聚合等查询操作后得到的一个"视图".
这种写法能很清晰地分辨出每一个操作步骤, 在我们还不十分熟悉 SQL 查询每一个子句的执行顺序的时候可以帮到我们.

但实际上, 如果我们熟知 WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法:

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

（1）在连接条件中写where筛选语句（2）每个查询中筛选后将结果内连接

不管用什么方式解决问题，重要的是思路，拿到一个问题，先分析需不需要连接、如何确定筛选条件、需不需要分组合并等，先把这些问题考虑清楚后，在选择一种合适的语句书写。

例题：

找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:

分析问题可由sql语句的执行顺序一步步代入分析

1.from：来自哪里，product与shopproduct两表，所以需要进行内连接

shopproduct as sp inner join product as p on sp.product_id = p.product_id

2.where：筛选条件是什么，由题意可知条件为product_tape = ‘衣服’?

3.select：SP.shop_id,SP.shop_name,SP.product_id ,P.product_name, P.product_type, P.purchase_price

（对于题目中不需要执行的语句可直接略过）

最终完整的语句为：

-- 参考答案 1--不使用子查询

SELECT SP.shop_id,SP.shop_name,SP.product_id ,P.product_name, P.product_type, P.purchase_price FROM shopproduct AS SP INNER JOIN product AS P ON SP.product_id = P.product_id WHERE P.product_type = '衣服';

-- 参考答案 2--使用子查询

SELECT SP.shop_id, SP.shop_name, SP.product_id ,P.product_name, P.product_type, P.purchase_price FROM shopproduct AS SP INNER JOIN --从 product 表找出衣服类商品的信息 (SELECT product_id, product_name, product_type, purchase_price FROM product WHERE product_type = '衣服')AS P ON SP.product_id = P.product_id;

第二种是直接将用于连接的表进行了一次子查询，所以直接连接的就是筛选过后的子查询（视图），连接完成后不需要再进行筛选。

练习题:

每个商店中, 售价最高的商品的售价分别是多少?

-- 参考答案
SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

1.from

2.group by ，group by 后的分组字段需要根据实际情况选择填写，并不限定求一个最值就一定分组一个字段

3.select

我们日常遇到的多数为两种情况（1）不适用子查询，连接后直接筛选（2）使用子查询，在子查询中进行筛选；另外还有就是内连接与关联子查询，像上述两种情况一般都是直接显示全部聚合结果，所以可以直接连接再筛选或者连接子查询，子查询中筛选；若遇到需要显示“单条记录与聚合结果值得比较后的筛选结果”则需要用关联子查询（子查询后执行，其中有一步聚合操作，结合最先执行的主查询）或者用内连接已聚合后的子查询，再在最外层进行结果的筛选同样可实现。

主义者4种情况的区分。如下题：找出每个商品种类当中售价高于该类商品的平均售价的商品

内连结与关联子查询

找出每个商品种类当中售价高于该类商品的平均售价的商品.

关联子查询

SELECT product_type, product_name, sale_price FROM product AS P1 WHERE sale_price > (SELECT AVG(sale_price) FROM product AS P2 WHERE P1.product_type = P2.product_type GROUP BY product_type);

内连接：

SELECT P1.product_id ,P1.product_name ,P1.product_type ,P1.sale_price ,P2.avg_price FROM product AS P1 INNER JOIN (SELECT product_type,AVG(sale_price) AS avg_price FROM product GROUP BY product_type) AS P2 ON P1.product_type = P2.product_type WHERE P1.sale_price > P2.avg_price;

思路：

1.from：product表，不需要连接

2.where：每个商品种类中的商品售价sale_price高于该类商品平均售价avg_price这里出现一个聚合函数，所以直接用where接group by 肯定对不上号，所以需要先进行关联子查询，抽取每一品类的平均售价，再输出查询结果，这是一种思路。

另外遇到此类问题，可考虑自连接方式解决，就是同一张表的连接，类似同一张表的关联子查询。第一步：先分组计算出平均售价；第二步：按照商品种类进行内连接；第三步：增加where子句，明确筛选条件。

总结：对于记录需要与聚合结果进行比较的情况，要么用子查询得到聚合结果与每条记录比较；要么：先聚合，再与另外一张表进行内连接。

SELECT ?P1.product_id
? ? ? ?,P1.product_name
? ? ? ?,P1.product_type
? ? ? ?,P1.sale_price
? ? ? ?,P2.avg_price
? FROM product AS P1
?INNER JOIN?
? ?(SELECT product_type,AVG(sale_price) AS avg_price?
? ? ? FROM product?
? ? ?GROUP BY product_type) AS P2?
? ? ON P1.product_type = P2.product_type
?WHERE P1.sale_price > P2.avg_price;

（task4-4.2.1.5）背景是：找出每个商品种类当中售价高于该类商品的平均售价的商品？现采用内连接解决此问题，想请教下，P2表已经通过group by聚合了product_type，为什么还可以与未聚合过的P1表通过product_type内连接，是因为“ON P1.product_type = P2.product_type”，写了这句，就算数量不等也可以连么？

这里有个误区：表连接不需要公共字段的数量一致，也不需要名称相同，只要值对的上号就可以，所以这里是可以直接将聚合后的P2与未聚合的P1相连的。

其次不要强行找“要么：先聚合，再与另外一张表进行内连接。”这种方法与之前内连接例题下“使用子查询”与“不使用子查询”两种方法找关联，因为此题中是要将P1表中的记录与P2中经过分组聚合计算出平均值的记录相连，跟上述提到的两种情况个根本不一样，所以两者间没有关联。

-------------------------------------------

这个结果和书上给的结果并不一致, 少了运动 T 恤, 这是由于运动 T 恤的 regist_date 字段为空, 在进行自然连结时, 来自于 product 和 product2 的运动 T 恤这一行数据在进行比较时, 实际上是在逐字段进行等值连结, 回忆我们在 6.2ISNULL,IS NOT NULL 这一节学到的缺失值的比较方法就可得知, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.

练习题: 使用内连结求product 表和product2 表的交集.

SELECT P1.*
  FROMproduct AS P1
 INNER JOINproduct2 AS P2
    ON (P1.product_id  = P2.product_id
   AND P1.product_name = P2.product_name
   AND P1.product_type = P2.product_type
   AND P1.sale_price   = P2.sale_price
   AND P1.regist_date  = P2.regist_date)

得到如下结果

注意上述结果和 P230 的结果并不一致–少了 product_id='0001'这一行, 观察源表数据可发现, 少的这行数据的 regist_date 为缺失值, 回忆第六章讲到的 IS NULL 谓词, 我们得知, 这是由于缺失值是不能用等号进行比较导致的.

如果我们仅仅用 product_id 来进行连结:

SELECT P1.*
  FROMproduct AS P1
 INNER JOINproduct2 AS P2
    ON P1.product_id = P2.product_id

查询结果:

这次就一致了.内连接只会连接共有的记录。

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行.

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处.

所以关联子查询中，主查询的每一条数据会与子查询进行一次组合。

习题：

4.1select *
form product as p1 full outer join product2 as p2
where sale_price > 5000;

交集：两个集合的并去掉两个集合的对称差

对称差：A-B 并上B-A，两个差集的并

差集：
A-B：select * from product where product_id not in ?(select product_id from product 2);

B-A：select * from product2 where product_id not in ?(select product_id from product 1);
对称差：
select * from product where product_id not in ?(select product_id from product 2)

union

select * from product2 where product_id not in ?(select product_id from product 1);

4.2.select * from
(select * from product?

union?

select * from product2) as p1

where product_id not in?

(select * from product where product_id not in ?(select product_id from product 2)

union

select * from product2 where product_id not in ?(select product_id from product 1);

4.3
select p2.shop_name,p1.product_id,p1.product_type,max(sale_price)
from product as p1 right outer join shopproduct as p2 on product_id
group by product_type;

4.4
select product_type,product_id,sale_price
from product as p1?
where sale_price = (select product_type,product_id,max(sale_price)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? from product as p2
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? where p1.product_type = p2.product_type
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? group by p2.product_type);

select product_type,product_id,sale_price
from product as p1 inner join (select product_type,product_id,max(sale_price)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? from product as p2
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? group by p2.product_type)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? on p1.product_type = p2.product_type;

4.5
select product_id, produc_name, slae_price
from product
order by sale_price;
?