SQL数据分析之数据提取、数据查询、数据清洗【MySQL速查】

2023-05-16

文章目录

- 一、数据提取
- 二、数据查询
- - 1、选取数据（select）
  - 2、筛选（where）
  - 3、范围匹配（IN）
  - 4、排序（order by）
  - 5、条件筛选（case when）
- 三、数据清洗
- 四、数据分组
- 五、连接查询

一、数据提取

1、获得用户表、启动表与功能表
（1）用户表：用户个人信息与订单信息

（2）启动表：日期、时间等

（3）功能表：是否成功、功能、渠道、耗时等

注意：当进入数据分析岗位，需要向数据部门获得数据时必须把关键字段列出来，数据部门才会将相应字段下的数据发给我们。

2、获取数据字典（desc）
数据字典指的就是数据表中的关键字段与关键字段信息；

语法示例：desc user_info; --分号不能少；

也就是宏观查看数据字段。

二、数据查询

1、选取数据（select）

（1）语法示例：select * from user_info; --选取用户信息表中的所有数据

以上的*代表所有内容，关于SQL语句的具体语法规则与详细分析，请参阅：
MySQL数据库基础（数据表的SELECT操作）

（2）查询特定行数（limit）
语法示例：select * from user_info limit 10; --查看数据表前10行数据，无排序

2、筛选（where）

（1）语法结构：select 字段名 from 表名 where 筛选条件;

（2）精准匹配：>、<、=、>=、<=

（3）单条件筛选：

select * from d_function where if_install='install';
--从功能表中找出所有软件已经安装的行

（4）筛选的逻辑操作符：AND表示且（两真才真），OR表示或（一真即真）

（5）多条件筛选：

select * from d_function where if_install='install' AND date='2022-4-15';  
--从功能表中找出条件为软件已安装 且 日期为2022年4月15日的行

（6）注意：当在MySQL中同时用到AND和OR时，AND的执行优先级高于OR。也就是说，在没有小括号()的限制下，总是优先执行AND语句，再执行OR语句。因此，条件很多时为了防止弄混，建议加上小括号()进行优先级限制。

（7）having的用法
①having和where的区别：
having对分组过后的数据进行过滤（而where是分组之前的），不过能用where的地方都可以用having替换

②having用法示例：

--用name分组过后，求每一组jquery的成绩总和，最后筛选jQuery成绩总和大于150的成绩对应的name
select name, sum(jQuery) from ExamResult group by name having sum(jQuery)>150;

--统计JS成绩大于80的name的个数
select count(name) from ExamResult where JavaScript>80;

--计算所有name的JS平均分：JavaScript总分/name个数
select sum(JavaScript)/count(name) from ExamResult;

（8）逻辑运算符
2.2.1

3、范围匹配（IN）

（1）作用：让SQL能返回某个变量部分指定值的结果

（2）格式：IN(值1，值2，值…值n)，满足任一值，即满足了where的筛选条件

（3）语句示例（IN实现）：

select * from d_function  where if_install IN ('install','uninstall') AND date='2022-4-15';
--从功能表中找出所有满足“已安装或未安装，且日期未2022年4月15日”的行

（4）上述示例的OR实现：

select * from d_function  where (if_install='install' OR if_install='uninstall') AND date='2022-4-15';
--从功能表中找出所有满足“已安装或未安装，且日期未2022年4月15日”的行

通过对比可以发现，IN实现就是OR的简便写法，当有很多个条件需要进行OR筛选时，则可以都放进IN的参数中进行条件判断。

4、排序（order by）

（1）作用：将特定字段进行排序

（2）语法结构：select 字段名 from 表名 order 字段名;

（3）默认是升序排序，加上desc则为降序

（4）在已安装软件中按照日期进行降序排序：

select * from d_function 
where if_install='install' 
order by date desc;

5、条件筛选（case when）

（1）作用：根据是否满足语句中的判断条件选取不同的取值，可以对数据进行重新分类、整理和命名。

（2）语法结构：

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE 默认结果
END

（3）筛选成功交易和失败交易的所有订单创建时间和收货地址：

select 订单创建时间,收货地址,
case
when 退款金额=0 then '成功交易'
when 退款金额>0 then '失败交易'
else '无效'
end
from 天猫订单;

注意：这里的case…when筛选语句是在select下面的，因此筛选结果会将case…when的筛选结果新建一列进行显示。
5.1

（4）AS变量重命名
主要用于分组之后进行命名。
如将上面示例中case…when筛选的结果列命名为交易有效性，订单创建时间命名为时间,收货地址命名为地址：

select 订单创建时间 AS 时间,收货地址 AS 地址,
case
when 退款金额=0 then '成功交易'
when 退款金额>0 then '失败交易'
else '无效'
end AS 交易有效性
from 天猫订单;

5.2

三、数据清洗

1、主要工作
检查数据异常、使用代码进行数据清洗，也就是常用的增（insert）、删（delete）、改（update），其中insert较少使用，实际工作中是根据< CheckList >中的要求进行数据清洗。

2、update更新表中具体字段信息
（1）语法结构：update 表名 set 字段=‘更新内容’ where 条件;

（2）用处：当数据非常多、不清楚正确值时，就可以使用delete将异常值删除

（3）操作步骤：
3.2.1
（4）代码示例：

#数据检查
select DISTINCT 订单金额 from 天猫订单;
#查看异常数据
select * from 天猫订单 where 订单金额<10;
#使用update进行字段更新：将异常数据改为0
update 天猫订单 set 订单金额=0 where 订单金额<10;
#数据复查
select DISTINCT 订单金额 from 天猫订单;  #所有数据检查
select * from 天猫订单 where 订单金额<10;  #是否存在异常

3.2.2
3.2.3
（5）注意
执行update时可能遇到的问题：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

报错原因：现在是在mysql的safe-updates模式中，如果where后跟的条件不是主键，就会出现这种错误。
解决方案1：在where后面加上主键的条件；
解决方案2：改模式：执行SET SQL_SAFE_UPDATES = 0; 建议改模式，简单快捷。

3、delete删除表中具体字段信息
（1）语法结构：delete from 表名 where 条件;

（2）用处：当知道正常值、删除异常值对数据整体有影响时，需要使用update进行更新，不影响正常值

（3）清洗掉id长度异常的值

#检查id长度是否异常
select DISTINCT length(user_id) from user_info;  #数据检查
select * from user_info where length(user_id)=29;  #查看异常值：异常长度29
delete from user_info where length(user_id)=29;  #数据清洗
select DISTINCT length(user_id) from user_info;  #数据复查

四、数据分组

1、GROUP BY语句
可以实现按照特定字段包含的分类进行进行汇总计算的效果；
如：求最小值，最大值，平均值，求和等。

2、语法结构
select 函数名(字段名) from 表名 GROUP BY 字段名;

3、求交易成功的不同<付款日期>的<订单量>和<实付金额>，即日销售额，并按<付款日期>降序排序：

#查看交易成功的订单量，即不发生退款的订单量
select count(实付金额) from t1.order where 退款金额=0;

#求交易成功的不同<付款日期>的<订单量>和<实付金额>，即日销售额，并按<付款日期>降序排序
select 订单付款日期 AS 日期,count(实付金额) AS 订单量,sum(实付金额) AS 日销售额 
from t1.order
where 退款金额=0 
group by 订单付款日期
order by 订单付款日期 desc;

4、注意SQL语句的顺序：

select…
from…
where…
group by…
order by…

五、连接查询

1、join连接概念
SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

JOIN语句可将两张数据表进行拼接查询，一般的连接字段都是主键，常为各种编号。

最常用三种连接：JOIN 、LEFT JOIN、RIGHT JOIN

2、语法结构

select 字段1
from 表1
JOIN 表2
ON 表1.字段3=表2.字段3

3、内连接（inner join 或 join）
内连接是等值连接，它使用“=、>、<、<>”等运算符根据每个表共有的列的值匹配两个表中的行。
5.3.1

4、左连接（left join 或 left outer join）
左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，右表中匹配的行。如果左表的某行在右表中没有匹配行，则用空值表示
5.4.1

5、右连接（right join 或 right outer join）
右连接也成右向外连接，查询的结果集包括SQL语句中右表的所有行，左表中匹配的行。如果右表的某行在左表中没有匹配的行，则用空值表示
5.5.1

6、完全外连接（full join 或 full outer join）
完全外连接，查询的结果集包括SQL语句中左表和右表的所有行。如果某行在另一个表中没有匹配行时，则用空值表示。
5.6.1
7、补充：JOIN中的ON和where的区别
（1）on条件是在生成临时表时候使用的，先做笛卡尔乘积生成临时表1，按照on条件生成临时表2，再添加左表中 ON 子句过滤时完全未匹配到的行，最终形成查询结果；

（2）where条件是在临时表（已经on，left join之后）生成好之后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)