00SQL在数据分析中的应用1

本周导读(第1页/共1页)我的作业目录模式笔记模式这周我们开始SQL在数据分析中的应用这一章节的学习。对于想进入数据分析岗位的同学而言，学习SQL最难解决的问题有哪些？ 市面上学习资料太繁杂，抓不住重点！ 实际的工作场景差距太大，学完无法实践！ 没有真实数据库环境练习，学完就忘！那么本门课程如何解决上面的问题呢？ 4种核心语句 + 3种进阶用法，覆盖实际80%以上工作需求 工作环境原型图高度拟真，数据库搭建贴近实际工作内容 真实数据库导入+真实需求练习，不断练习巩固SQL用法这门课程怎么学才更有效果？SQL的学习需要按照三步走，“视频看一遍，自己敲一遍，练习做一遍”的模式，才能真正将核心语法熟练应用到真实的工作场景中，因此，这门课程的学习，各位同学一定要克服懒惰，多多动手练习！视频问题反馈上一节1/11. SQL基础知识(第1页/共1页)我的作业目录模式笔记模式在正式学习SQL语句之前，你需要对数据库及数据表的基础知识有所了解。你可能没有意识到，其实数据库的概念离你并不遥远：当你从电子邮件地址簿里查找名字时，就是在使用数据库。当你在电商网站上搜索某个商品时，也是运用了数据库的概念。在使用SQL进行数据分析的时候，你需要掌握SQL的定义以及SQL语句的基本规则。在执行数据查询及处理的相关操作时，你需要了解数据表存放的具体位置以及表间的关系，对于数据字典及E-R图的掌握这时就尤为重要。视频问题反馈你可以使用三节课为你准备的MySQL平台来练习代码，也可以通过自己安装MySQL Workbench来练习。电商数据库SQL文件下载：三节课_电商数据库.zip，在下一小节的课程中我们将学习如何导入数据库。MySQL安装教程：Windows版MySQL安装：https:/shimo.im/docs/2d83440586364b5dMac版MySQL安装：https:/shimo.im/docs/2e9e0548bc894dacMac版MySql下Workbench安装：https:/shimo.im/docs/CuhCP2MDNp0Gxjyw（感谢热心学员 keke 提供的Mac版MySql下workbench安装指南）上一节1/12.案例工作概述(第1页/共1页)我的作业目录模式笔记模式在本节课程中 ，你将通过一个电商产品销售案例来了解SQL如何帮助数据分析师解决实际问题。视频问题反馈下载数据库电商数据库SQL文件下载：三节课_电商数据库.zip数据库导入图解上一节1/13.SQL核心语句(第1页/共1页)我的作业目录模式笔记模式在了解了SQL的基础知识以及本周课程所需案例之后，我们将正式进入SQL语句的学习。我们可以将SQL语句划分为核心语句和进阶用法。SQL核心语句 数据查询与过滤 数据聚合 表间连接 数据的增、改、删注意!为方便大家理解SQL数据库以及数据库中的表间关系以及语句的操作，我们在视频中将表名和字段名翻译成中文。但是，同学在MySQL练习平台以及MySQL Workbench中练习使用的数据库表名和字段名为英文，以下为课程使用的【三节课_电商数据库】表结构及字段定义详解，以及生成的E-R图中英文参照。三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解本课程数据库的E-R图对应中文表名和字段名：上一节1/1下一节3.1 数据查询与过滤(第1页/共1页)我的作业目录模式笔记模式在SQL中，数据分析师最常用的语句就是数据查询与过滤语句。在本节中，我们将ECE电商公司的案例来学习SQL的核心语句之一：数据查询与过滤。本节内容 数据查询：基本数据查询语句- SELECT 数据过滤：基于条件过滤数据 WHERE 查询结果处理：查询结果排序 ORDER BY、选取指定查询结果- LIMIT、查询结果去重- DISTINCT视频问题反馈如何运行第一段SQL代码：同学们需要先确保数据库已经导入。在调取数据之前，你需要调用数据库，代码： USE online_sale; 。调用数据库之后你就可以调取你想要的数据啦快去试一试吧！练习数据查询与过滤(第1页/共1页)我的作业目录模式笔记模式请同学学完数据查询与过滤章节，完成数据查询与过滤相关练习。本小节共 11道小题。注意!练习题只为让同学们掌握学习内容，不计入毕业和优秀学员统计。温馨提示：为了避免出错以及节省不必要的debug时间，请同学们在练习时注意1. 符号使用英文半角 逗号：中文长这样， 英文长这样 , 分号：中文长这样； 英文长这样 ;2. 注意括号的位置和数量3. 代码写完要加分号 ; 4. 单词不要拼写错误三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解请同学克服懒惰，勤加练习，才能真正将核心语法熟练应用到真实的工作场景中！练习题1.1 预览顾客信息(customer_info)表中的全部数据。关键语句：SELECT我的回答请输入您的代码1运行练习题1.2 从顾客信息表(customer_info)中选取顾客号码(customer_id)，姓(last_name), 名(first_name)和电话(phone_number)（注意选取列的顺序）关键语句：SELECT我的回答请输入您的代码1运行练习题1.3从顾客信息表中选取顾客号码(customer_id)，姓名(last_name,first_name)和电话(phone_number)（注意选取列的顺序）提示：使用CONCAT函数合并last_name（姓）,first_name（名）字段, 为新产生的字段命名为name（姓名）关键语句：SELECT、CONCAT(字段A,字段B) AS 新字段AB我的回答请输入您的代码1运行练习题1.4 选取来自北京市的顾客的全部信息关键语句：SELECT、WHERE我的回答请输入您的代码1运行练习题1.5 选取价格在20到30之间(包含20，30)的产品列出产品号码(product_id)，产品名称(product_name)，产品单价(price)（注意选取列的顺序）关键语句：SELECT、WHERE+AND或BETWEEN AND我的回答请输入您的代码1运行练习题1.6 选取来自河北省及山西省所有顾客的顾客号码(customer_id)、姓名(last_name first_name)、省份(province)（注意选取列的顺序）；提示：你可以使用CONCAT函数合并last_name,first_name字段，为新生成的字段命名为name。关键语句：SELECT、WHERE+INOR、CONCAT(字段A,字段B) AS 新字段AB我的回答请输入您的代码1运行练习题1.7 选取所有不在珠海市和绍兴市的供应商，列出所有供应商信息(注意选取列的顺序)关键语句：SELECT、WHERE+AND (!=)我的回答请输入您的代码1运行练习题1.8 选取所有单价大于50的商品，以产品单价从小到大排列；列出产品号码(product_id)，产品名称(product_name)以及产品单价(price)（注意选取列的顺序）关键语句：SELECT、WHERE、ORDER BY我的回答请输入您的代码1运行练习题1.9 选取价格最高的前5件产品, 列出产品号码(product_id)，产品名称(product_name)以及产品单价(price)(注意选取列的顺序)关键语句：SELECT、ORDER BY、LIMIT（一个参数）我的回答请输入您的代码1运行练习题1.10 将所有产品以价格从高到低排列，选取价格在第三到第八之间(包含第三和第八)的产品，列出产品号码(product_id)，产品名称(product_name)以及产品单价(price)（注意选取列的顺序）关键语句：SELECT、ORDER BY、LIMIT我的回答请输入您的代码1运行练习题1.11 从顾客信息表(customer_info)中选取不重复的城市(city)以及省份(province)作为顾客来源地区。关键语句：SELECT、DISTINCT我的回答请输入您的代码1运行上一节1/1下一节3.2 数据聚合(第1页/共1页)我的作业目录模式笔记模式在实际的工作中，我们可能会遇到各种各样的问题。比如，老板让你统计下这周每天有多少人登录APP的时长在30分钟以上？在没有SQL的时候我们可能要先把所有的用户信息调取出来，筛选本周登录的用户以及登录的时长，然后再计算用户数量。当我们的的数据量非常大的时候，遇到类似问题我们就需要一个更加快捷更高效的方式。为此，SQL提供了一类专门的函数：聚合函数。使用这一类函数，可以完成检索的例子有： 确定表中行数（或者满足某个条件或包含某个特定值的行数） 获得表中某些行的和； 找出表中某列（或行）的最大值、最小值、平均值等等。视频问题反馈上一节1/1下一节练习数据聚合(第1页/共1页)我的作业目录模式笔记模式请同学学完数据查询与过滤章节，完成数据查询与过滤相关练习。本小节共 5道小题。注意!练习题只为让同学们掌握学习内容，不计入毕业和优秀学员统计。温馨提示：为了避免出错以及节省不必要的debug时间，请同学们在练习时注意1. 符号使用英文半角 逗号：中文长这样， 英文长这样 , 分号：中文长这样； 英文长这样 ;2. 注意括号的位置和数量3. 代码写完要加分号 ; 4. 单词不要拼写错误三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解请同学克服懒惰，勤加练习，才能真正将核心语法熟练应用到真实的工作场景中！练习题2.1 使用订单信息列表(order_info)中数据，计算2017年总营业额(total_revenue)添加备注信息字段，填入2017总营收字符串作为备注信息(other_info)关键语句：SELECT、AS创建新列名、WHERE+AND、SUM我的回答请输入您的代码1运行练习题2.2 在2017年间，计算总共完成了多少个订单，创建订单数目(order_count)字段计算总共有多少位顾客完成过订单，创建顾客数目(customer_count)字段提示：同一位顾客一年内可以消费多次；(注意选取列的顺序)关键语句：SELECT、AS创建新列名、WHERE+AND、COUNT、COUNT+DISTINCT我的回答请输入您的代码1运行练习题2.3 使用产品信息表数据，计算所有产品的最高售价(max_price)，最低售价(min_price)以及平均售价(average_price)。（注意选取列的顺序）关键语句：SELECT、AS创建新列名、MAX、MIN、AVG我的回答请输入您的代码1运行练习题2.4 使用产品信息表数据，为每位供应商计算其销售产品的最高售价(max_price)，最低售价(min_price)以及平均售价(average_price)选择最高售价大于2倍平均售价的结果，列出供应商号码，最高售价，最低售价，平均售价（注意选取列的顺序）关键语句：SELECT、AS创建新列名、MAX、MIN、AVG、GROUP BY、HAVING我的回答请输入您的代码1运行练习题2.5 使用订单明细数据表（order_details）计算每样产品的总销售次数 (total_times) 以及总销售数量 (total_count) ，并选取总销售数量排名前8名的产品号码, 总销售数量，总销售次数 (注意选取列的顺序)提示： 一样产品在一个订单中出现过算一次销售关键语句：SELECT、AS创建新列名、SUM、COUNT+DISTINCT、GROUP BY、LIMIT我的回答请输入您的代码1运行上一节1/1下一节3.3 数据表间连接(第1页/共1页)我的作业目录模式笔记模式本小节将要学习的是第三种核心语句：数据表间连接。在数据分析的日常工作中，我们常常会碰到需要的数据分布在不同数据表中的情况，SQL强大的功能之一就是可以通过表间连接语句JOIN 来合并不同数据表中的数据。因此，能够很好地理解数据表间连接的3种方式及其基本语法是学习SQL非常重要的内容。视频问题反馈上一节1/1下一节练习数据表间连接(第1页/共1页)我的作业目录模式笔记模式请同学学完数据查询与过滤章节，完成数据查询与过滤相关练习。本小节共 5道小题。注意!练习题只为让同学们掌握学习内容，不计入毕业和优秀学员统计。温馨提示：为了避免出错以及节省不必要的debug时间，请同学们在练习时注意1. 符号使用英文半角 逗号：中文长这样， 英文长这样 , 分号：中文长这样； 英文长这样 ;2. 注意括号的位置和数量3. 代码写完要加分号 ; 4. 单词不要拼写错误三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解请同学克服懒惰，勤加练习，才能真正将核心语法熟练应用到真实的工作场景中！练习题3.1 查询所有在2018年1月1日之后消费过的顾客信息列出顾客号码(customer_id)，顾客姓名(last_name,first_name)，城市(city)以及电话号码(phone_number) (注意选取列的顺序)注：每位顾客仅在结果中显示一次关键语句：SELECT、DISTINCT、CONCAT、AS创建新列名、INNER JOIN、WHERE我的回答请输入您的代码1运行练习题3.2 查询所有供应商以及其提供的产品信息列出供应商号码（supplier_id），公司（company），产品号码（product_id）, 产品名称（product_name）注： 结果必须包含所有供应商信息，即使供应商没有提供产品（注意选取列的顺序）关键语句：SELECT、LEFT JOIN我的回答请输入您的代码1运行练习题3.3 查询所有顾客信息以及其最近购买日期, 列出顾客号码(customer_id)，姓名（name），以及最近购买日期(last_create_time)（注意选取列的顺序）注：结果必须包含所有顾客信息，即使顾客没有完成过订单关键语句：SELECT、CONCAT、LEFT JOIN、GROUP BY、MAX我的回答请输入您的代码1运行练习题3.4 查询所有供应商以及其提供的产品信息列出产品号码(product_id), 产品名称(product_name)，供应商号码(supplier_id)，公司(company)（注意选取列的顺序）提示： 使用RIGHT JOIN解答关键语句：SELECT、RIGHT JOIN我的回答请输入您的代码1运行练习题3.5 查询所有2018年1月1日之后各个订单中购买数量超过10的订单以及产品的详细信息列出订单号码（order_id），订单日期（create_time）, 购买数量（sales_count），产品号码（product_id），产品名称（product_name）（注意选取列的顺序）提示：你需要使用订单信息（order_info）、订单明细（order_details）、产品信息（product_info）表。关键语句：SELECT、LEFT JOIN(三个表连接)、WHERE+AND我的回答请输入您的代码1运行上一节1/1下一节3.4 数据的增、改、删(第1页/共1页)我的作业目录模式笔记模式在这一节中，你将了解最后一种核心语句：数据的增、改、删。在日常工作中，分析师要谨慎使用修改和删除语句！本节主要内容 获取数据表信息：DESCRIBE 数据的插入：INSERT INTO 数据的修改：UPDATE SET 数据的删除：DELETE FROM视频问题反馈上一节1/1下一节练习数据的增、改、删(第1页/共1页)我的作业目录模式笔记模式请同学学完数据查询与过滤章节，完成数据查询与过滤相关练习。本小节共 5道小题。注意!练习题只为让同学们掌握学习内容，不计入毕业和优秀学员统计。温馨提示：为了避免出错以及节省不必要的debug时间，请同学们在练习时注意1. 符号使用英文半角 逗号：中文长这样， 英文长这样 , 分号：中文长这样； 英文长这样 ;2. 注意括号的位置和数量3. 代码写完要加分号 ; 4. 单词不要拼写错误三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解请同学克服懒惰，勤加练习，才能真正将核心语法熟练应用到真实的工作场景中！练习题4.1 列出订单信息表（order_info）的详细信息，观察结果回答下列两道选择题关键语句：DESCRIBE(获取数据表信息)我的回答请输入您的代码1运行测试一下【单选题】产品信息表总共有几个字段？A3B4C5D6提交答案测试一下【单选题】产品名称的数据类型是什么？AintBvarcharCcharacterDstring提交答案练习题4.2 添加新的产品信息product_id（产品号码）:81product_name（产品名称）:洗洁精supplier_id（供应商号码） : 20price（产品单价）:9.99description（产品描述）: 350ml关键语句：INSERT INTO我的回答请输入您的代码1运行练习题4.3 上图为“产品信息”表中6号产品的记录信息，请修改#6号产品（product_id）红枣的价格（price）为当前价格减去5元。关键语句：UPDATE我的回答请输入您的代码1运行练习题4.4 从供应商表（supplier_info）中删除#33号供应商（supplier_id）的记录*运行电商数据库SQL重建数据库关键语句：DELETE我的回答请输入您的代码1运行4. SQL进阶用法(第1页/共1页)我的作业目录模式笔记模式在核心语句学习的基础上，我们再来看如何使用SQL语句处理更复杂的分析问题。在实际工作中，掌握SQL的核心语句以及三种常见的进阶用法可以涵盖80%需要用到SQL的工作。作为数据分析师，需要具备构建多层复杂逻辑语句的能力，在熟练使用SQL的基础上有意识的优化语句，进而简化工作流程更加高效的产出数据分析结果。视频问题反馈上一节1/1下一节练习SQL进阶用法(第1页/共1页)我的作业目录模式笔记模式请同学学完数据查询与过滤章节，完成数据查询与过滤相关练习。本小节共 5道小题。注意!练习题只为让同学们掌握学习内容，不计入毕业和优秀学员统计。温馨提示：为了避免出错以及节省不必要的debug时间，请同学们在练习时注意1. 符号使用英文半角 逗号：中文长这样， 英文长这样 , 分号：中文长这样； 英文长这样 ;2. 注意括号的位置和数量3. 代码写完要加分号 ; 4. 单词不要拼写错误三节课_电商数据库表结构初识及字段定义详解文档链接：电商数据库表结构初识及字段定义详解请同学克服懒惰，勤加练习，才能真正将核心语法熟练应用到真实的工作场景中！练习题5.1 选取所有在平均价格以上的产品信息，列出产品号码（product_id）, 产品名称（product_name）, 产品单价（price）。（注意选取列的顺序）关键提示：单行单列过滤条件子查询我的回答请输入您的代码1运行练习题5.2 选取所有在平均价格以上的产品信息，列出产品号码（product_id）, 产品名称（product_name）, 产品单价（price）（注意选取列的顺序）并在此基础上，增加 “product_avg_price” （产品平均售价）常量列，在每一行中显示产品的平均售价（product_avg_price）。关键语句：单行单列过滤条件子查询我的回答请输入您的代码1运行练习题5.3 在不使用表连接(JOIN)的前提下，选取所有供应商来自于珠海市的产品信息列出产品号码（product_id）, 产品名称（product_name）,供应商号码（supplier_id）， 产品单价（price）（注意选取列的顺序）关键语句：多行单列过滤条件子查询我的回答请输入您的代码1运行练习题5.4 在不使用表连接的前提下，计算2018年1月1日以来出售的每样产品的销售总数量（total_sales），选取销量最高的前8样产品，列出产品号码（product_id），销售总数（total_sales），将结果以销售总数从大到小排列关键语句：多行单列过滤条件子查询+GROUPBY+ORDERBY+LIMIT我的回答请输入您的代码1运行练习题5.5 为每样产品计算总销售额，列出产品号码(product_id)，产品名称(product_name)，总销售额(total_sales)（注意选取列的顺序）注：返回答案包含没有出售过的产品提示: 使用订单明细表计算产品的总销售额关键语句：临表子查询我的回答请输入您的代码1运行练习题6.1 返回产品信息，增加新数据列 售价级别（price_level）售价级别由平均产品售价计算得出产品单价(sales_price) 小于平均售价，级别为1产品单价 (sales_price) 大于等于平均售价小于2倍平均售价，级别为2产品单价 (sales_price) 大于等于2倍平均售价，级别为3列出产品号码(product_id)，产品名称(product_name)，产品单价(price)，售价级别(price_level)（注意选取列的顺序）关键语句：CASE WHEN 判定条件 THEN 判定结果ELSE 默认值END AS 售价级别我的回答请输入您的代码1运行5.1 案例供应商营业额分析(第1页/共1页)我的作业目录模式笔记模式接下来，我们将通过供应商营业额分析和网店销售趋势分析案例来了解如何运用SQL做数据分析。在这两个案例中，数据分析师要处理的问题更加复杂，对于SQL的运用也更加灵活。视频问题反馈上一节1/1下一节5.2 案例网店销售趋势分析(第1页/共1页)我的作业目录模式笔记模式在网店销售趋势分析案例中，分析师的目标是为每个供应商计算每年的各个季度销售额。请同学基于供应商营业额分析案例的讲解，先思考一下： 分析师需要用到哪些数据？ 解决此问题的思路是什么？在有了具体思路之后结合老师的讲解，相信你可以更好的掌握SQL！视频问题反馈上一节1/1下一节本周总结(第1页/共1页)我的作业目录模式笔记模式恭喜你，完成本周课程的学习啦！勤加练习，你就能够熟练掌握SQL语句的撰写了！你可以再把前面的内容在复习一下，课程看一遍，自己敲一遍，然后练习做一遍，真正掌握本门课的知识。视频问题反馈SQL核心语句构成及执行顺序数据查询与过滤数据查询 基本数据查询语句：SELECT FROM 数据过滤 基于条件过滤数据：WHERE 运算符 值 AND/OR/NOT 列 运算符 值查询结果处理 查询结果排序：ORDER BY ASC|DESC 选取指定查询结果：LIMIT 常与 ORDER BY 一起使用 查询结果去重：SELECT DISTINCT FROM 数据聚合数据聚合语法 SELECT ， FROM GROUP BY 常见聚合函数 COUNT (列名)：返回指定列中的行数(不包括NULL) COUNT (*)：返回指定列中的行数(包括NULL) SUM (列名)：返回指定数值列的总数(不包括NULL) AVG (列名)：返回指定数值列的平均值(不包括NULL) MIN/MAX (列名)：返回指定列中的最小/最大值(不包括NULL)聚合结果过滤 使用HAVING以及过滤条件过滤聚合结果聚合结果排序与选取 使用ORDER BY 与 LIMIT数据表间连接 等值连接 INNER JOIN - 返回既在左表又在右表的键值对应数据行 左连接 LEFT JOIN 返回左表所有数据行以及右表对应键值数据行 右连接 RIGHT JOIN 返回右表所有数据行以及左表对应键值数据行表间连接基本语法： SELECT FROM INNER/LEFT/RIGHT JOIN ON = 数据的增、改、删列出数据表信息 DESCRIBE 表名增加数据行 INSERT INTO 表名（字段名）VALUES（新记录的字段值）修改指定数据行的字段值 UPDATE 表名 SET 字段名 = 字段值 WHERE 过滤条件删除指定数据行 DELETE FROM 表名 WHERE 过滤条件* 注意 修改和删除语句一定要附带 WHERE 过滤条件保证数据安全SQL进阶用法子查询子查询作用 构建复杂逻辑，简化需求解答过程单行单列过滤条件子查询 子查询语句返回单行单列值作为结果多行单列过滤条件子查询 子查询语句返回多行单列值作为结果临表子查询 子查询语句返回临时列表 (多行多列) 作为结果SQL进阶用法条件判断语句条件判断语句 CASE WHEN 判定条件 THEN 判定结果 WHEN 判定条件 THEN 判定结果 ELSE 默认值 END* 条件判断语句常用于数据转换，基于现有数据创建新的数据列上一节1/1下一节强化练习(第1页/共1页)我的作业目录模式笔记模式在开始本周大作业前，请同学们先完成下方两个进阶练习。同学们在开始写代码前可以先思考一下如何拆解问题以及代码书写逻辑（参考前两节案例），根据提示完成本节练习。完成练习后让我们进入本周的大作业，尝试用SQL解决问题吧！练习题7.1 计算各年度、各季度的订单数量（order_quantity）。按照年度（years）、季度（quarter）、订单数量（order_quantity）顺序列出。提示：使用Year() 获得时间类型字段的年份；使用Month() 获得时间类型字段的月份；季度用1-2-3-4表示（1-3月为1季度，以此类推）；关键语句：CASEWHEN （）THEN （）END、COUNT我的回答请输入您的代码1运行练习题7.2 选取近3天（2018.05.04-2018.05.06）销售额（sales_rev）最高的产品类别TOP3，按照产品号码（product_id）、销售额（sales_rev）顺序输出。提示：使用订单信息表（order_info）获取时间相关信息使用订单明细表（order_details）获取产品相关信息关键语句：JOIN、ORDER BY、LIMIT、临表子查询我的回答请输入您的代码1运行本周推荐书籍SQL必知必会：SQL必知必会（第4版）.zipMySql从入门到精通SQL基础教程SQL进阶教程刷题推荐SQL的学习需要按照三步走，“视频看一遍，自己敲一遍，练习做一遍”的模式，才能真正将核心语法熟练应用到真实的工作场景中。想要更熟练的掌握SQL需要不断的刷题练习，这里给大家推荐一些常用网站。同学也可以使用三节课为大家提供的练习平台反复练习课后习题。/sql/index.asphttps:/www