天池龙珠计划SQL 知识点总结与习题解析

最新推荐文章于 2023-07-17 20:44:28 发布

jialun0116

最新推荐文章于 2023-07-17 20:44:28 发布

阅读量248

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_30031221/article/details/115757034

版权

mysql 专栏收录该内容

44 篇文章 3 订阅

订阅专栏

天池龙珠计划SQL训练营知识点总结与习题解析

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为https://tianchi.aliyun.com/specials/promotion/aicampsql

1.1 DBMS的种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型.

层次数据库（Hierarchical Database，HDB）
关系数据库（Relational Database，RDB）
- 关系数据库管理系统（Relational Database Management System，RDBMS）
- Oracle Database：甲骨文公司的RDBMS
- SQL Server：微软公司的RDBMS
- DB2：IBM公司的RDBMS
- MySQL：开源的RDBMS
面向对象数据库（Object Oriented Database，OODB）
XML数据库（XML Database，XMLDB）
键值存储系统（Key-Value Store，KVS），举例：MongoDB

1.2 SQL语句类型

DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。CREATE、DROP 、ALTER
DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。SELECT 、INSERT、UPDATE、DELETE
DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。COMMIT 、ROLLBACK 、GRANT 、REVOKE

1.3 drop、delete、truncate区别

DELETE
- 每次从表中删除一行，语句执行后要事务提交后才生效，如果有相应的trigger将触发，可以进行回滚
- 不会返回数据占用的空间
- 可以对 table 和 view操作
TRUNCATE
- 一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。不用提交，不能回滚
- 返还数据所占空间
- 只能对table操作
- 在功能上与不带 WHERE 子句的 DELETE 语句相同，如果和事务无关可用truncate
DROP
- 删除整个表包括表结构和数据，不用提交，不能回滚
- 表所占用的空间全部释放

2.1 函数

聚合函数会将NULL排除在外。但COUNT(* )例外，并不会排除NULL 。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数
希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。
NOT IN 的参数中不能包含 NULL ，否则，查询结果通常为空 NULL不能进行比较
在 GROUP BY 子句中指定的列称为聚合键或者分组列。聚合键中包含NULL时，NULL会作为一组特殊数据处理
在使用聚合函数及GROUP BY子句时，经常出现的错误有：
- 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
- 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
- 在WHERE中使用聚合函数 原因是聚合函数的**使用前提是结果集已经确定，**而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数。

2.2 执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

2.3 各种函数

绝对值：ABS( )
求余数：MOD(被除数，除数)
四舍五入：ROUND(对象数值，保留小数的位数 )
字符串拼接：CONCAT(str1, str2, str3)
字符串替换：REPLACE( 对象字符串，替换的字符串，替换后的字符串 )
字符串截取：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
字符串按索引截取：SUBSTRING_INDEX (原始字符串，分隔符，n)
长度：LENGTH(字符串)
获取当前日期：CURRENT_DATE( )
获取当前日期和时间：CURRENT_TIMESTAMP( )
截取日期元素：EXTRACT(日期元素 FROM 日期) 元素日期：year、month、day、hour、minute、second 函数返回的是数值类型不是日期类型
转换函数：CAST(转换前的值 AS 想要转换的数据类型）
返回可变参数 A 中左侧开始第 1个不是NULL的值: COALESCE(数据1，数据2，数据3……)
匹配任意一个字符： _
匹配多个字符：%
判断是否为NULL：IS NULL
可以用IN(val1,val2,val3) 代替多个or语句 IN是无法选择出NULL数据的 NOT IN 的参数中不能包含 NULL ，否则，查询结果通常为空
是否存在这样的记录：EXIST(sql 语句)

-- substring_index 第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com                                 |
+-------------------------------------------+

-- 获取第2个元素/第n个元素可以采用二次拆分的写法。
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql                                                              |
+--------------------------------------------------------------------+

-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
samplelike);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务
SELECT * FROM samplelike;

-- 使用 EXIST 选取出大阪门店在售商品的销售单价 EXIST 的左侧并没有任何参数
--  EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT * FROM shopproduct AS sp
               WHERE sp.shop_id = '000C'
               AND sp.product_id = p.product_id);
               
-- 实现行转列
-- CASE WHEN 实现数字列行转列 可以用 SUM AVG MAX MIN等聚合函数
-- 待转换列为文本时，可以用 MAX MIN聚合函数
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
FROM score
GROUP BY name;

3. 1视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的。操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
SELECT 语句中列的排列顺序和视图中列的排列顺序相同，视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图(不建议)
一般的DBMS中定义视图不能使用ORDER BY（MySQL中可以），因为表和视图一样，数据行是没有顺序的
因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容，创建视图的时候尽量不允许视图来修改表

create view view_name(col1,col2) as select col1,col2 from table_name

alter view view_name as select语句

3.2 视图与表的区别

是否保存了实际的数据。视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据

3.3 视图存在的意义

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

4.1 表的连接

以行方向为单位进行操作
并集 UNION 通常都会除去重复的记录.
包含重复行的集合运算 UNION ALL
MySQL 仍然不支持交运算INTERSECT 、减法EXCEPT操作

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合

-- 使用 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)

减法操作可以用 NOT IN代替
交运算可以用两个集合的并去掉两个集合的对称差

4.2 连结 JOIN

内连结 INNER JOIN
自然连结 NATURAL JOIN 按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件
外连结 OUTER JOIN
交叉连接 CROSS JOIN

5.1 窗口函数

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理
PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。
PRECEDING（“之前”），将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”），将框架指定为 “截止到之后 n 行”，加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”
ROLLUP 计算合计

<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>)  

专用窗口函数
RANK()   -- 1、1、1、4 存在相同位次的记录，则会跳过之后的位次
DENSE_RANK() -- 1、1、2、3 存在相同位次的记录，也不会跳过之后的位次
ROW_NUMBER() --1、2、3 赋予唯一的连续位次

聚合函数
SUM()
AVG()

<窗口函数> OVER (ORDER BY <排序用列名> ROWS n PRECEDING )  
                 
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP  

/*
计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。
排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）
*/
SELECT regist_date, product_name, sale_price,
SUM(sale_price) OVER 
(ORDER BY COALESCE(regist_date, CAST('0001-01-01'AS DATE))) AS current_sum_price
FROM Product;
 
SELECT regist_date, product_name, sale_price,
SUM(sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS
current_sum_price
FROM Product;

-- 每类商品中售价最高的商品都在哪些商店有售 
select s.product_id, shop_name, product_type
from 
(
		select product_id, m.product_type
		from (select * from product union select * from product2) all_product
		inner join
  	(
				select product_type,max(sale_price) max, 
				from (select * from product union select * from product2) all_product
				group by product_type
		) m
		on all_product.product_type = m.product_type
		WHERE all_product.sale_price = max
) id
left join ShopProduct s
on id.product_id = s.product_id;

-- 分别使用内连结和关联子查询每一类商品中售价最高的商品
# 内连结
select *
from (select * from product union select * from product2) all_product
inner join 
(
   select max(sale_price) max, product_type 
	 from (select * from product union select * from product2) all_product
	 group by product_type
)m
on all_product.product_type = m.product_type
where sale_price = max;

#关联子查询
select * 
from (select * from product union select * from product2) p1
where sale_price = 
(
		select max(sale_price)
		from (select * from product union select * from product2) p2
    where p2.product_type = p1.product_type
		group by product_type
);

1-5 习题全解

重点看 4.3、4.4

-- 1.1 编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 
-- Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束
CREATE TABLE Addressbook
(
  regist_no INTEGER NOT NULL,
  name VARCHAR(128) NOT NULL,
  address VARCHAR(256) NOT NULL,
  tel_no CHAR(10) ,
  mail_address CHAR(20),
	PRIMARY KEY(regist_no) 
);

-- 1.2 假设在创建练习1.1中的 Addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请把此列添加到 Addressbook 表中。
-- 列名 ： postal_code 数据类型 ：定长字符串类型（长度为 8） 约束 ：不能为 NULL
ALTER TABLE ADD COLUMN postal_code CHAR(8) NOT NULL;

-- 1.3 编写 SQL 语句来删除 Addressbook 表。
DROP TABLE Addressbook;

-- 1.4 编写 SQL 语句来恢复删除掉的 Addressbook 表
删除后的表无法使用命令进行恢复，重新创建

-- 2.1 编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，
-- 查询结果要包含product_name和regist_date两列。
SELECT product_name, regist_date
FROM product
WHERE regist_date > '2009-04-28';

/* 2.2 执行如下3条SELECT语句时的返回结果
  SELECT *
    FROM product
   WHERE purchase_price = NULL;

  SELECT *
    FROM product
   WHERE purchase_price <> NULL;

  SELECT *
    FROM product
   WHERE product_name > NULL;
 */
判断NULL 需要用函数 IS NULL 判断所以结果都是 空值

-- 2.3 从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品
-- 请写出两条可以得到相同结果的SELECT语句。
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price >= purchase_price + 500;

SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - 500 >= purchase_price;

-- 2.4 请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。
-- 查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。
SELECT product_name,product_type, sale_price * 0.9 - purchase_price AS profit
FROM product
WHERE sale_price * 0.9 - purchase_price > 100
AND (product_type = '办公用品' OR product_type = '厨房用具');

/* 2.5 请指出下述SELECT语句中所有的语法错误。
  SELECT product_id, SUM（product_name）
    FROM product 
   GROUP BY product_type 
   WHERE regist_date > '2009-09-01';
*/
1. 字符型字段 product_name 不可以进行 SUM 聚合
2. WHERE 语句应该书写在 GROUP BY 语句之前( FROM 语句之后) 
3. GROUP BY 字段( product_type )与 SELECT 字段不同( product_id )

-- 2.6 求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > SUM(purchase_price) * 1.5;

-- 2.7 排序
SELECT *FROM product
ORDER BY regist_date DESC, sale_price;

/*3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。
  条件 1：销售单价大于等于 1000 日元。
  条件 2：登记日期是 2009 年 9 月 20 日。
  条件 3：包含商品名称、销售单价和登记日期三列。
*/
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price >= 1000 AND regist_date = '2009-09-20';

-- 3.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？
视图插入数据时，原表也会插入数据，而原表数据插入时不满足约束条件，所以会报错。
(因为ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段)

-- 3.3 其中 sale_price_all 列为全部商品的平均销售单价
SELECT product_id,product_name,
       product_type,sale_price,
       SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;

-- 3.4 这里需要计算出的 是各商品种类的平均销售单价
CREATE VIEW AvgPriceByType AS 
SELECT product_id,product_name,
       product_type,sale_price,
       (SELECT AVG(sale_price)FROM product p2
        WHERE p1.product_type = p2.product_type
        GROUP BY p1.product_type) AS avg_sale_price
FROM product p1;

CREATE VIEW AvgPriceByType AS 
SELECT product_id,product_name,
       product_type,sale_price,
       AVG(sale_price) PARTITION BY (product_type) AS avg_sale_price
FROM product;

-- 3.5 运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
是的 

-- 3.6 对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器 			 | 						320 | 
| 擦菜板 			 |	 					790 | 
+--------------+----------------+ 
查询语句仅仅取出了 purchase_price 不是 500、2800、5000 的商品，
而不包含 purchase_price 为 NULL 的商品，这是因为 谓词无法与 NULL 进行比较。
    
mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
Empty set (0.00 sec)
实际上它却返回了零条记录， 这是因为 NOT IN 的参数中不能包含 NULL ，否则，查询结果通常为空。

-- 3.7 按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。
-- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
-- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
-- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
 
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
       SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
       SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price
FROM product;

-- 4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息
SELECT * FROM product WHERE sale_price > 500
UNION
SELECT * FROM product2 WHERE sale_price > 500;

-- 4.2 借助对称差的实现方式, 求product和product2的交集
SELECT * FROM product WHERE product_id in (SELECT product_id FROM product2);

-- 4.3 每类商品中售价最高的商品都在哪些商店有售 
select s.product_id, shop_name, product_type
from 
(
		select product_id, m.product_type
		from (select * from product union select * from product2) all_product
		inner join
  	(
				select product_type,max(sale_price) max, 
				from (select * from product union select * from product2) all_product
				group by product_type
		) m
		on all_product.product_type = m.product_type
		WHERE all_product.sale_price = max
) id
left join ShopProduct s
on id.product_id = s.product_id;

-- 4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品
# 内连结
select *
from (select * from product union select * from product2) all_product
inner join 
(
   select max(sale_price) max, product_type 
	 from (select * from product union select * from product2) all_product
	 group by product_type
)m
on all_product.product_type = m.product_type
where sale_price = max;

#关联子查询
select * 
from (select * from product union select * from product2) p1
where sale_price = 
(
		select max(sale_price)
		from (select * from product union select * from product2) p2
    where p2.product_type = p1.product_type
		group by product_type
);

-- 4.5 用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 
-- 并按照商品的售价从低到高进行排序、对售价进行累计求和
select product_id, product_name, sale_price, 
(select sum(sale_price)from product p2
where p2.sale_price <= p1.sale_price) sum
from product p1
order by sale_price;

-- 5.1 请说出针对本章中使用的product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product

按照 product_id 升序排列，计算出截至当前行的最高 sale_price 

-- 5.2 计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。
-- 排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

regist_date为NULL时，显示“1年1月1日”。 
SELECT regist_date, product_name, sale_price,
       SUM(sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01'AS DATE)))
AS current_sum_price
FROM Product;

regist_date为NULL时，将该记录放在最前显示。 
SELECT regist_date, product_name, sale_price,
       SUM(sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS
current_sum_price
FROM Product;

-- 5.3 窗口函数不指定PARTITION BY的效果是什么？
针对排序列进行全局排序

-- 5.4 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
本质上是因为 SQL 语句的执行顺序。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
如果在 WHERE, GROUP BY, HAVING 使用了窗口函数，就是说提前进行了一次排序，
排序之后再去除 记录、汇总、汇总过滤，第一次排序结果就是错误的，没有实际意义。
而 ORDER BY 语句执行顺序在SELECT 语句之后，自然是可以使用的。

6. 综合练习 - 10道经典SQL

-- 练习1 将三张表中的TICKER_SYMBOL为600383和600048的 信息合并在⼀起。
SELECT MarketData.*,OperatingData.INDIC_NAME_EN,
 OperatingData.VALUE,IncomeStatement.N_INCOME,
 IncomeStatement.T_COGS,IncomeStatement.T_REVENUE
 FROM 
 (
   SELECT TICKER_SYMBOL,END_DATE,CLOSE_PRICE
   FROM 'Market Data'
   WHERE TICKER_SYMBOL IN ('600383','600048')
 )MarketData
 LEFT JOIN 
 (
   SELECT TICKER_SYMBOL,INDIC_NAME_EN,END_DATE,VALUE
   FROM 'company operating'
   WHERE TICKER_SYMBOL IN ('600383','600048')
 ) OperatingData
 ON MarketData.TICKER_SYMBOL = OperatingData.TICKER_SYMBOL
 AND MarketData.END_DATE = OperatingData.END_DATE
 LEFT JOIN 
 (
   SELECT DISTINCT TICKER_SYMBOL,END_DATE,T_REVENUE,T_COGS,N_INCOME
   FROM 'income statement'
   WHERE TICKER_SYMBOL IN ('600383','600048') 
 ) IncomeStatement
 ON MarketData.TICKER_SYMBOL = IncomeStatement.TICKER_SYMBOL
 AND MarketData.END_DATE = IncomeStatement.END_DATE
 ORDER BY MarketData.TICKER_SYMBOL, MarketData.END_DATE
 
 
-- 练习2 找出 pH=3.03的所有红葡萄酒，然后，对其 citric acid 进⾏中式排名（相同排名的下⼀个名次应该是下⼀个连续的整数值。换句话说，名次之间不应该有“间隔”）
SELECT PH,'citric acid',DENSE_RANK() OVER(ORDER BY 'citric acid') AS rank
FROM 'winequality-red'
WHERE PH = 3.03

-- 练习3 试分别找出在2016年7⽉期间，发放优惠券总⾦额最多和发放优惠券张数最多的商家。
SELECT Merchant_id,
	SUM(SUBSTRING_INDEX('Discount_rate',':',-1)) AS discount_amount
FROM ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY discount_amont desc
LIMIT 1

SELECT Merchant_id,COUNT(1) AS cnt
FROM ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY cnt DESC
LIMIT 1;

-- 练习4 在2015年⽤电最⾼峰是发⽣在哪⽉？并且相⽐去年同期增⻓/减少了多少个百分⽐
-- 2015年⽤电最⾼峰是发⽣在哪⽉
SELECT PERIOD_DATE,MAX(DATA_VALUE) FianlValue
FROM 'macro industry'
WHERE INDIC_ID = '2020101522' AND YEAR(PERIOD_DATE) = 2015
GROUP BY PERIOD_DATE
ORDER BY FianlValue DESC
LIMIT 1;
-- 并且相⽐去年同期增⻓/减少了多少个百分⽐？
SELECT BaseData.*,(BaseData.FianlValue - YoY.FianlValue) / YoY.FianlValue YoY
FROM 
(
  SELECT PERIOD_DATE,MAX(DATA_VALUE) FianlValue
  FROM `macro industry`
  WHERE INDIC_ID = '2020101522' AND YEAR(PERIOD_DATE) = 2015
  GROUP BY PERIOD_DATE
  ORDER BY FianlValue DESC
  LIMIT 1
)BaseData
LEFT JOIN 
(
  SELECT PERIOD_DATE,MAX(DATA_VALUE) FianlValue
  FROM `macro industry`
  WHERE INDIC_ID = '2020101522' AND YEAR(PERIOD_DATE) = 2014
  GROUP BY PERIOD_DATE 
) YoY
ON YEAR(BaseData.PERIOD_DATE) = YEAR(YoY.PERIOD_DATE) + 1
AND MONTH(BaseData.PERIOD_DATE) = MONTH(YoY.PERIOD_DATE);

-- 练习5 试统计在2016年6⽉间，线上总体优惠券弃⽤率为多少？并找出优惠券弃⽤率最⾼的商家
-- 2016年6⽉期间，线上总体优惠券弃⽤率为多少？
SELECT 
SUM(CASE WHEN Date='0000-00-00' AND Coupon_id IS NOT NULL THEN 1 ELSE 0 END) /
SUM(CASE WHEN Coupon_id IS NOT NULL THEN 1 ELSE 0 END) AS discard_rate
FROM ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30';
-- 2016年6⽉期间，优惠券弃⽤率最⾼的商家？
SELECT Merchant_id,
SUM(CASE WHEN Date='0000-00-00' AND Coupon_id IS NOT NULL THEN 1 ELSE 0 END) /
SUM(CASE WHEN Coupon_id IS NOT NULL THEN 1 ELSE 0 END) AS discard_rate
FROM ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'
GROUP BY Merchant_id
ORDER BY discard_rate DESC
LIMIT 1;

-- 练习6 找出 pH=3.63的所有⽩葡萄酒，然后，对其 residual sugar 量进⾏英式排名（⾮连续的排名）
SELECT pH,'residual sugar',RANK() OVER (ORDER BY 'residual sugar') AS rankn
FROM 'winequality-white'
WHERE pH= 3.63;

-- 练习7 计算截⽌到2018年底，市值最⼤的三个⾏业是哪些？以及这三个⾏业⾥市值最⼤的三个公司是哪些？
-- 计算截⽌到2018年底，市值最⼤的三个⾏业是哪些？
SELECT TYPE_NAME_CN,SUM(MARKET_VALUE)
FROM 'market data'
WHERE YEAR(END_DATE) <= 2018
GROUP BY TYPE_NAME_CN
ORDER BY SUM(MARKET_VALUE) DESC
LIMIT 3
-- 这三个⾏业⾥市值最⼤的三个公司是哪些？ 9个
SELECT BaseData.TYPE_NAME_CN,BaseData.TICKER_SYMBOL
FROM 
(
  SELECT TYPE_NAME_CN,TICKER_SYMBOL,MARKET_VALUE,
  ROW_NUMBER() OVER(PARTITION BY TYPE_NAME_CN ORDER BY MARKET_VALUE) CompanyRanking
  FROM `market data` 
) BaseData
LEFT JOIN
( 
  SELECT TYPE_NAME_CN,SUM(MARKET_VALUE)
  FROM 'market data'
  WHERE YEAR(END_DATE) <= 2018
  GROUP BY TYPE_NAME_CN
  ORDER BY SUM(MARKET_VALUE) DESC
  LIMIT 3 
) top3Type
ON BaseData.TYPE_NAME_CN = top3Type.TYPE_NAME_CN
WHERE CompanyRanking <= 3 AND top3Type.TYPE_NAME_CN IS NOT NULL

-- 练习8 试找出在2016年6⽉期间，线上线下累计优惠券使⽤次数最多的顾客
SELECT User_id,SUM(couponCount) couponCount
FROM 
(
  SELECT User_id,count(*) couponCount
  FROM 'ccf_online_stage1_train'
  WHERE (Date IS NOT NULL AND Coupon_id IS NOT NULL AND YEAR(DATE)=2016)
  GROUP BY User_id
  UNION ALL
  SELECT User_id,COUNT(*) couponCount
  FROM 'ccf_offline_stage1_train'
  WHERE (Date IS NOT NULL AND Coupon_id IS NOT NULL AND YEAR(DATE)=2016)
  GROUP BY User_id
) BaseData
GROUP BY User_id
ORDER BY SUM(couponCount) DESC
LIMIT 1

-- 练习9 按季度统计，⽩云机场旅客吞吐量最⾼的那⼀季度对应的净利润是多少
-- 因为正好是第⼀季度，所以不需要减。 如果是2季度，单季度净利润需要⽤2季度的值减去1⽉份的
SELECT *
FROM 
(
 SELECT TICKER_SYMBOL,YEAR(END_DATE) Year,QUARTER(END_DATE) QUARTER,SUM(VALUE) Amount
 FROM 'company operating'
 WHERE INDIC_NAME_EN = 'Baiyun Airport:Passenger throughput'
 GROUP BY TICKER_SYMBOL,YEAR(END_DATE),QUARTER(END_DATE)
 ORDER BY SUM(VALUE) DESC
 LIMIT 1 
) BaseData
LEFT JOIN 
(
 SELECT TICKER_SYMBOL,YEAR(END_DATE) Year,QUARTER(END_DATE) QUARTER,SUM(N_INCOME) Amount
 FROM 'income statement'
 GROUP BY TICKER_SYMBOL,YEAR(END_DATE),QUARTER(END_DATE) 
) Income
ON BaseData.TICKER_SYMBOL = Income.TICKER_SYMBOL
AND BaseData.Year = Income.Year
AND BaseData.QUARTER = Income.QUARTER

-- 练习10 试找出在2016年6⽉期间，线上线下累计被使⽤优惠券满减最多的前3名商家
SELECT Merchant_id, SUM(discount_amount) discount_amount
FROM 
(
  SELECT Merchant_id,
  SUM(SUBSTRING_INDEX('Discount_rate',':',-1)) AS discount_amount
  FROM 'ccf_online_stage1_train'
  -- WHERE (Date != 'null' AND Coupon_id != 'null') AND (LEFT(DATE,4)=2016) AND MID(DATE,5,2) = '06'
  WHERE (Date IS NOT NULL  AND Coupon_id IS NOT NULL) AND YEAR(DATE)=2016 AND MONTH(DATE) = '06'
  GROUP BY Merchant_id
  UNION ALL
  SELECT Merchant_id,
  SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount
  FROM 'ccf_offline_stage1_train'
  WHERE (Date IS NOT NULL  AND Coupon_id IS NOT NULL) AND YEAR(DATE)=2016 AND MONTH(DATE) = '06'
  GROUP BY Merchant_id 
) BaseData
GROUP BY Merchant_id
ORDER BY SUM(discount_amount) DESC
LIMIT 1