AI学习 - 天池
偶然发现阿里天池，里面有系统的学习安排，甚至还有数学基础的补充，知识图如下：
AI训练营SQL-阿里云天池
最近处于秋招，非常急迫的需要补充相关知识，那就从朋友们都说容易学的SQL入手！
-
Task02-五、对表进行分组
5.3 GROUP BY书写位置
GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句书写****顺序为：
1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY（默认为升序排列，降序排列为DESC）
5.5 常见错误
在使用聚合函数及GROUP BY子句时，经常出现的错误有：
- 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
- 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
- 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。
-
Task02-六、为聚合结果指定条件
6.1 用HAVING得到特定分组
将表使用GROUP BY分组后，怎样才能只取出其中两组？
可以在GROUP BY后使用HAVING子句。
-
Task02-七、对查询结果进行排序
7.2 ORDER BY中列名可使用别名
前文讲GROUP BY中提到，GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却可以使用别名。为什么在GROUP BY中不可以而在ORDER BY中可以呢？
这是因为SQL在使用 HAVING 子句时 SELECT 语句的执行****顺序为：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
其中SELECT的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在ORDER BY中使用别名时，已经知道了SELECT设置的别名存在，但是在GROUP BY中使用别名时还不知道别名的存在，所以在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名****。
-
Task03-3.1.6 如何更新视图内容
因为我们刚刚修改的productSum视图不包括以上的限制条件，我们来尝试更新一下视图
UPDATE productsum SET sale_price = '5000' WHERE product_type = '办公用品';此时我们再查看productSum视图，可以发现数据已经更新了
此时观察原表也可以发现数据也被更新了
不知道大家看到这个结果会不会有疑问，刚才修改视图的时候是设置product_type='办公用品'的商品的sale_price=5000，为什么原表的数据只有一条做了修改呢？
还是因为视图的定义，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。
注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表
-
Task03-3.2 子查询/标量子查询
3.2.3 嵌套子查询
与在视图上再定义视图类似，子查询也没有具体的限制，例如我们可以这样
SELECT product_type, cnt_product FROM (SELECT * FROM (SELECT product_type, COUNT(*) AS cnt_product FROM product GROUP BY product_type) AS productsum WHERE cnt_product = 4) AS productsum2;其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
3.2.5 标量子查询有什么用
让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。
SELECT product_id, product_name, sale_price FROM product WHERE sale_price > (SELECT AVG(sale_price) FROM product);我们还可以这样使用标量子查询：
SELECT product_id, product_name, sale_price, (SELECT AVG(sale_price) FROM product) AS avg_price FROM product;
3.2.6 关联子查询 简单易懂教你学会SQL关联子查询 - 知乎
-
Task03-3.5.2 CASE表达式的使用方法
语法：
CASE WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> . . . ELSE <表达式> END上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。无论多么庞大的 CASE 表达式，最后也只会返回一个值。
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显示地写出 ELSE 子句。
此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。
- （扩展内容）应用场景3：实现行转列
总结：
- 当待转换列为数字时，可以使用
SUM AVG MAX MIN等聚合函数；- 当待转换列为文本时，可以使用
MAX MIN等聚合函数
-
Task04-4.1.5对称差（不明白这个例子，哦哦哦明白了）
练习题:
使用product表和product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
提示: 使用 NOT IN 实现两个表的差集.
参考答案:
-- 使用 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)
-
Task04-4.2.1 内连结(INNER JOIN)
首先, 找出每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息.
按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 shopproduct.product_id=product.product_id, 就得到了如下的查询语句:
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 JOIN product AS P ON SP.product_id = P.product_id;在上述查询中, 我们分别为两张表指定了简单的别名, 这种操作在使用连结时是非常常见的, 通过别名会让我们在编写查询时少打很多字, 并且更重要的是, 会让查询语句看起来更加简洁.
4.2.1.2 结合 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 = '衣服' ;但实际上, 如果我们熟知 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 = '衣服' ;
-
Task04-4.2.1 内连结(INNER JOIN) ——左连结, 右连结和全外连结
左连结会保存左表中无法按照 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)>
注：4.2连结部分红框内目前没有掌握（2023.9.7）
-
Task05-5.1 窗口函数
5.1.1窗口函数概念及基本的使用方法
窗口函数的通用形式：
<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>)[]中的内容可以省略。
窗口函数最关键的是搞明白关键字PARTITON BY*和ORDER BY******的作用。PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。
举个栗子:
SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product得到的结果是:
PARTITION BY 能够设定窗口对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。即一个商品种类就是一个小的"窗口"。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC，也就是升序进行排序。本例中就省略了上述关键字 。
5.2.1专用窗口函数
- RANK函数**（英式排序）**
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
- DENSE_RANK函数**（中式排序）**
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
- ROW_NUMBER函数
赋予唯一的连续位次。
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位
运行以下代码：
SELECT product_name ,product_type ,sale_price ,RANK() OVER (ORDER BY sale_price) AS ranking ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num FROM product
5.4.1ROLLUP - 计算合计及小计
常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。
SELECT product_type ,regist_date ,SUM(sale_price) AS sum_price FROM product GROUP BY product_type, regist_date WITH ROLLUP得到的结果为：
4天（2023.9.4-2023.9.7）进程到了task06，下载个SQL进行实际操作巩固学习了。
493
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
