SQL数据分析——笔记系列【上篇】

一.数据分析基础

数据分析：利用合适的工具，在统计学理论的支撑下，对数据进行一定程度的预处理，然后结合具体业务分析数据，帮助公司相关部门监控、定位、分析、解决问题，从而提高业务部门的决策能力和经营效率，发现业务机会，让企业取得持续竞争优势。

数据分析三大作用：现状分析、原因分析、预测分析。

数据分析分析内容层面：

1.总体概览指标（日销售额、日订单量、购买人数……）；

2.对比性指标（同比、环比、差指标）;

3.集中趋势指标（平均数、众数、中位数）；

4.离散程度指标（全距(极差)、方差、标准差）；

5.相关性指标（相关系数r，范围[-1,1]，正相关/负相关）；

6.因果关系

数据分析一般流程：

1.熟悉工具；2.明确目的；3.获取数据；4.熟悉数据；5.处理数据；

6.分析数据；7.得出结论；8.验证结论；9.展现结论

二.数据库基础

数据库：用来存储数据的仓库，用户可以对存放在这个仓库中的数据进行新增、查询、更新、删除等操作。

数据库管理系统（DBMS）：对数据库进行管理的一个系统，这个系统负责建立、使用和维护数据库，对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

结构化数据：有行有列的二维数据。——>【关系型数据库】

非结构化数据：不适合用行和列的形式来存储的数据（Word文档、HTML文档、图片等）。——>【非关系型数据库】

SQL: Structured Query Language，结构化查询语言。

SQL包括：

1.DDL（数据定义）：create、drop、alter，主要作用于数据库、表、视图、索引。

2.DML（数据操纵）：select、insert、update、delete，主要作用于表。

3.DCL（数据控制）：grant、revoke，主要作用于表、列。

SQL查询处理步骤

1.查询分析：扫描全部SQL语句，进行词法和语法分析，判断查询语句是否符合SQL语法规则；

2.查询检查：进行语义检查，根据数据字典判断查询语句中涉及的数据库、数据表、列等信息是否存在；

3.查询优化：根据具体情况选择一个效率最高的执行策略；

4.查询执行：执行查询语句，返回查询结果。

主流数据库：

Mysql，受欢迎，比较主流，已被Oracle收购；

SQL Server，微软开发，与自己的产品交互比较好；

Oracle，甲骨文，数据库领域引领者；

DB2，IBM开发，主要目标用户是企业；

Hive SQL，大公司比较常见，Hive是基于Hadoop构建的一套数据仓库分析系统，与Mysql的语法基本一致。Hadoop是一种分布式处理架构，可大幅提高程序运行效率。

三.数据库工具准备

Mysql官网：www.mysql.com

DBeaver:一款免费的、开源的，开发人员和数据管理员通用的数据库管理工具。

DBeaver官网：DBeaver Community | Free Universal Database Tool

四.数据源

常规数据源：外部数据、公司现有数据、新建数据。

熟悉数据：

1.数据库是什么数据库；

2.数据库包含哪些表；

3.各表存储了什么信息，表中每列分别代表什么。

Mysql附属数据库information_schema：

show databases;

use information_schema;

show tables;

select * from information_schema.SCHEMATA;--查询Mysql中所有与库有关的信息

select * from information_schema.TABLES;--查询Mysql中与表相关的信息

select * from information_schema.COLUMNS;--查询Mysql每张表的列信息

五.数据的获取

select * from table;

select id,name,class,age,score from table;

select * from table limit 5;--前5行

select * from table limit 2,3;--第2行(不含第2行)以后的3行数据

select * from table where age = 18;

select * from table where age = 18 and/or class = “一班”;--where后面用逻辑符来连接多个条件，where后面的比较运算符可用等于、大于、小于、介于等。

select id,name,”age<20” as label from table where age < 20;--给查询结果插入一列固定值

JSON列解析：

有时数据库中的数据是按照JSON格式存储的，即字典这种数据结构{key,value}，如score列{“语”:88, “数”:91, “外”:97} 。

select id,json_extract(score,’$.数’) as “数学成绩” from table;--获取JSON中key对应的value值

select id,json_keys(score) as “科目” from table;--查询JSON中都有哪些key

select * from table order by class asc,age desc;--排序默认升序asc可省略，降序desc

六.数据预处理

缺失值处理：

缺失值有空格、null和空值形式，前两种形式单元格是有值的，后一种单元格没有值。

1.删除，select * from table where profession != “”；--where后面!= “ ”/is not null/!= “”

2.填充，select id,coalesce(profession,”其他”) from table;--coalesce(null,”填充值”)

重复值处理：

删除，只保留其中一条记录。

select distinct * from table;--针对全表删除重复值

select distinct order_id,member_id from table;--针对指定的列删除重复值

select order_id,member_id from table group by 1,2;--group by也可以实现删除重复值

数据类型转换：

SQL中更改列的数据类型，可使用cast(列名 as type) 和convert(列名,type)函数。

select age,cast(age as decimal) age_2,convert(age,char) age_3 from table;

数据类型：浮点型decimal，整型signed，字符型char，二进制binary，日期date，时间time，日期时间datetime。

重命名：

英文名变中文名或者计算字段名称需要起别名，as前面是原始字段名，as后面是别名。as可以省略，为了代码可读性，建议不省略。

select date as “下单日期”,age as “年龄”,sex as “性别” from table;

七.数据运算

算数运算：+,-,*,/,整除div,取余%和mod。

select (sa * pa + sb * pb) as gmv from table;

select 7 div 2;--结果为3

select 7 % 2;--结果为1

select 7 mod 2;--结果为1

null与任何数进行运算，结果都为null。

比较运算：>,<,=,>=,<=,<>,!=,between,is null,is not null。

主要用于两列或者某一列与具体值之间的比较，也可用于where条件筛选。

select sa >= sb as “大于等于”,sa != sb as “不等于”,sa is null as “空值” from table；

select id,sa from table where sa between 10 and 30;

SQL中比较运算返回结果：正确1，错误0 。

逻辑运算：and,or,not。

主要用于连接多个条件。

select id,sa,sb,((sa > 90) and (sb > 90)) as “优秀” from table;--字段“优秀”返回0/1

数学运算：比如三角函数、指数、对数、精度运算等。

abs(x)绝对值

sqrt(x)返回x的平方根

exp(n)返回e的n次方

sign(x)返回x的正负符号，可用于判断大小(差值比较，返回值1正，-1负，0)

ceiling(x)/ceil(x)返回不小于x的最小整数值,

floor(x)返回不大于x的最大整数值

mod(x,y)返回x除以y的余数

pow(x,y)返回x的y次幂

rand()返回(0,1)范围内的一个随机浮点数

round(x,y)返回y精度的x值

truncate(x,y)返回保留y位小数的x值

select ceil(2.1),floor(2.1);--返回3,2

字符串运算：

replace(str,a,b)将str字符串中的a替换为b

concat(str1,str2,……)合并为一个字符串

concat_ws(s,str1,str2,……)用连接符s合并为一个字符串

left(str,n) / right(str,n) / substring(str,m,n)获取从m位置开始，长度为n的字符

ltrim(str) / rtrim(str) / trim(str)去掉开头和结尾的空格

char_length(str)字符长度

length(str)字节长度

repeat(str,n)字符串重复n遍

select replace(“AAAaaa”,”a”,”A”);

select id,replace(id,”E”,”e”) as id_2 from table;--对某列中的每个值进行替换

select concat(“a”,”b”)；

select id,name,concat(id,name) as id_name from table;

select id,name,concat_ws(“-”,id,name) as id_name from table;

select left(日期时间列,10),right(日期时间列,8),substring(日期时间列,6,2);

select * from table where name like “_哈%”;--"%”任意长度字符，可以是0，”_”单个长度字符

select * from table where name not like “张%”;

字符是由字节组成：英文字母1个字符由1个字节组成；中文1个字符在utf-8编码环境下是由3个字节组成，在gbk编码环境下是由2个字节组成。

select char_length(“sql”);--3个字符

select length(“sql”);--3个字节

select char_length(“好好学习”);--4个字符

select length(“好好学习”);--utf-8下12个字节，gbk下8个字节

select ltrim(“  abc  “), rtrim(“  abc  “),trim(“  abc  “);--一个空格也算一个字符

select repeat(“sql”,3);--sqlsqlsql

聚合运算：

count()非缺失值计数，null和空值是不算入计数，而空格是算入计数的。

select count(id) from table;

select count(*) from table;--一共有多少行

select count(“  “);

select count(null);

select count(distinct sa) from table;--非重复非缺失值计数

sum()求和，对某列值汇总求和

avg()求平均值，对某列值求平均值

max()获取某列最大值

min()获取某列最小值

var_pop()求总体方差，σ2=∑(X-μ)2/N

var_samp()求样本方差，σ2=∑(X-μ)2/(N-1)

std()求总体标准差

stddev_samp()求样本标准差

方差和标准差都是用来反映数据的离散程度的，但标准差与实际数据的单位一致，更具有实际业务意义。

select sum(sa) as a,sum(sb) as b,sum(sa)+sum(sb) as c from table;--聚合函数之间的运算

八.控制函数

If()函数：if(条件,a,b)

select id,score,if(score >= 60,”及格”,”不及格”) as result from table;--对某列每个值进行判断

select if(70<60,”不及格”,if(70<90,”良好”,”优秀”));--if函数可以嵌套

case when函数：两种形式

形式1：对某一列进行多层判断，条件只能是具体的列值，不能进行比较运算。

case 列名

     when 条件1 then 返回值1

     when 条件2 then 返回值2

     ………

     when 条件n then 返回值n

     else 返回默认值

end

select id,class,(case clsss

                when “一班” then “class1”

                when “二班” then “class2”

when “三班” then “class3”

                else “其他”

            end) class_result

from table;

形式2：支持对列名进行比较运算。

case

     when 列名满足条件1 then 返回值1

     when 列名满足条件2 then 返回值2

     ………

     when 列名满足条件n then 返回值n

     else 返回默认值

end

select id,score,(case

               when score <60 then “不及格”

               when score <90 then “良好”

               else “优秀”

             end) score_result

from table;

select id,score,(case

               when score between 0 and 59 then “不及格”

               when score between 60 and 89 then “良好”

               else “优秀”

             end) score_result

from table;--对列名进行比较运算

九.日期和时间函数

日期：年月日

时间：时分秒

select now();--获取当前时刻（日期和时间）

select curdate();--获取当前时刻的日期

select date(now());--通过date()函数可将日期时间转化为日期

select year(now());--只获取年

select month(now());--只获取月

select day(now());--只获取日

select curtime();--获取当前时刻的时间

select time(now());--通过time()函数可将日期时间转化为时间

select hour(now());--只获取小时

select minute(now());--只获取分钟

select second(now());--只获取秒

select weekofyear(now());--查看当前时刻是全年中的第几周

select dayofweek(now());--查看当天是一周中的第几天，默认一周中星期日是第一天

select quarter(curdate());--查看当天是第几个季度

日期和时间格式转换：

date_format(datetime,format)函数, datetime要转换的具体日期和时间，format要转换的格式。可选的格式如下：

年——%Y——4位数字年

月——%b——月份英文缩写

月——%M——月份英文全称

月——%m——月份01-12表示

月——%c——月份1-12表示

日——%d——日01-31表示月中第几天

日——%e——日1-31表示月中第几天

日——%D——日th后缀表示月中第几天

日——%j——日001-366表示一年中的第几天

周——%a——星期几英文缩写

周——%W——星期几英文全称

时——%H——小时00-23表示

时——%h——小时01-12表示

分——%i——分钟00-59表示

秒——%S——秒00-59表示

秒——%f——微秒

时分秒——%T——时分秒(hh:mm:ss)

select date_format(“日期时间”,”%Y-%m-%d”);--年月日

select date_format(“日期时间”,”%H:%i:%S”);--时间

extract(unit from datetime)函数，返回一个具体日期和时间中的单独部分，如年year、月month、日day、时hour、分minute、秒second、周数/全年第几周week。

select extract(year from ‘’日期和时间”) as y, extract(month from ‘’日期和时间”) as m, extract(day from ‘’日期和时间”) as d;

日期和时间计算：

向后偏移：date_add(date,interval x 单位)，日期加法。

select date_add(“2023-01-01”,interval 7 year/month/day);

select date_add(“2023-01-01 01:01:01”,interval 7 hour/minute/second);

向前偏移：date_sub(date,interval x 单位)，日期减法。

select date_sub(“2023-01-01”,interval 7 year/month/day);

select date_sub(“2023-01-01 01:01:01”,interval 7 hour/minute/second);

向前偏移也可以使用date_add函数，偏移量加负号即可。

select date_add(“2023-01-01”,interval -7 year/month/day);

select date_add(“2023-01-01 01:01:01”,interval -7 hour/minute/second);

两日期做差：datediff(end_date,start_date)，返回两日期相差的天数。

select datediff(“2023-04-01”,”2022-12-01”);

两日期比较：与两数字比较一样，比较运算符均可使用。

select “2023-04-01” >/</=/!= ”2022-12-01” as a;--a的值是0或1（0错误，1正确）

十.数据分组&透视表

group by对表进行分组:

select * from table group by c1,c2;

分组后进行聚合运算：

select province,city,sum(sales) as a,count(shop) as b from table group by province,city;

使用注意：1.除了参与聚合运算的列外，其他select列必须先group by分组；2.group by后的列必须是原始列名，而非别名。

聚合后进行筛选：

select province,sum(sales) as a from table

group by province

having sum(sales) >100;--having后的sum(sales)也可替换为别名a

group_concat()函数：对组内的字符串进行连接。

select id,group_concat(score) as score_group from table

group by id;--按id列进行分组，对组内score列进行合并为一列，各值之间用逗号分隔

rollup:在group by的具体列后加上with rollup即可。一般分组聚合后，再实现对父级分组指标汇总聚合，两者结果纵向合并到同一张表中，该效果用union all 也能实现，但代码相比较rollup太繁琐。

select province,city,sum(sales) from table group by province,city with rollup;

同样的效果用union all 实现如下：

select province,null as city,sum(sales) from table group by province

union all

select province,city,sum(sales) from table group by province,city;

数据透视表：对行列同时分组，满足条件的值进行聚合运算。一般用group by 与 case when 组合使用来实现数据透视功能。

select date,

count(case when shop = tm then order_id end) as A,

count(case when shop = jd then order_id end) as B,

count(case when shop = pdd then order_id end) as C

from table

group by 1;

十一.窗口函数

窗口函数：与数据分组类似，可以将组内数据聚合成一个值，并且还保留原始的每条数据。

形式：

聚合函数+over()函数

序列函数+over()函数

聚合函数+over(partition by 列名 order by 列名)函数

序列函数+over(partition by 列名 order by 列名)函数

select shop,date,saes,(select avg(sales) from table) as avg_sales from table;

等同于

select shop,date,sales,avg(sales) over() as avg_sales from table;--over()函数将聚合结果显示在每条单独的记录中

select a.shop,a.date,a.sales,b.avg_sales from table a

left join

(select shop,avg(sales) as avg_sales from table group by shop) b

on a.shop = b.shop;

等同于

select shop,date,sales,avg(sales) over(partition by shop order by date) as avg_sales from table;

序列函数：将数据整理成一个有序的序列，便于筛选所需序列对应的数据。

ntile()函数:对整表数据进行切片分组。

select shop,date,sales,ntile(4) over() as group from table;--把table分成4组

select shop,date,sales,ntile(2) over(partition by shop order by sales) as group from table;--先按shop分组，然后组内排序，最后切片分组。

rank()函数：并列排名占位，排名离散，无参数

dense_rank()函数：并列排名不占位，排名连续，无参数

row_number()函数：行号，不会出现重复排名，无参数

select shop,date,sales,row_number() over(partition by shop order by sales) as rank_num

from table;--筛选rank_num = 1，便可得每个店铺销量最差的记录。

lag()函数：数据后移

lead()函数：数据前移

select shop,date,sales,lead(sales,1) over(partition by shop order by date) as lead_sales

from table;-- 向前移动1行

first_value()函数：截止当前第一个值

last_value()函数：截止当前最后一个值

select shop,date,sales,

first_value(date) over(partition by shop order by date) as first_date,

last_value(date) over(partition by shop order by date) as last_date

from table;--取每个店铺的最早销售日期和截止目前最后一次销售日期

十二.多表连接

纵向连接：

union：对表连接结果去重处理；

union all：对结果不做任何处理，只是把两表连接在一起。

select * fron table1

union all

select * fron table2

union all

select * fron table3;--若表中无重复值，union all的运行效率大于union。

注意：纵向连接的各表中列的顺序必须一致。

横向连接：

left join:左连接，以左表为主表，根据两表公共列，将右表往左表上连接；

right join:右连接，以右表为主表，根据两表公共列，将左表往右表上连接；

inner join:内连接，两表取交集，即获取公共列都出现的信息；

outer join:外连接，两表取并集，即获取公共列在任一表中出现的信息，若版本不支持，可通过左连接+右连接，然后union all 去重即可实现外连接的效果。

select a.user_id,a.sex,a.city,a.level,b.first_time,b.orders_7,b.orders_14

from table1 as a

left join table2 as b

on a.user_id = b.user_id;--left join 可替换成其他连接方式。

多张表横向连接：

select * from table1 as a

left join table2 as b

on a.user_id = b.user_id

left join table3 as c

on a.user_id = c.user_id;--3表横向连接

横向公共连接字段是多列：on后的条件用and连接即可。

select * from table1 as a

left join table2 as b

on a.c1= b.c1 and a.c2= b.c2;--2表横向公共列是2列的连接

连接类型：

一对一：用于连接的公共列在左表和右表中都是唯一的，无重复值。

一对多：用于连接的公共列在左表或右表有重复值。

多对多：用于连接的公共列在左表和右表都有重复值，此时就是笛卡尔积，连接结果会有m*n条记录。多对多的情况应尽量避免，在连接之前应先检查用于连接的公共列是否有重复值，有的话先进行去重处理。

十三.子查询

概念：若一个查询是另一个查询的一部分时，则称内层的查询为子查询，外层的查询为主查询！

子查询位置类型：

select子查询:

select c1,(select x from t2) as c2 from t1;

select id,score,(select avg(score) from t1) as avg_score from t1;

from子查询:

select c1, c2 from (select x from t2) as t1;

select a.id,a.avg_score,b.name,b.sex,b.class from

(select id,avg(score) as avg_score from t1 group by id having(avg(score) > 500) as a

left join t2 as b on a.id = b.id;

where子查询:

select c1, c2 from t1 where c1 in(select x from t2); --in可换为>,<,!=等其他比较运算符

select id,score,month from t1 where score > (select avg(score) from t1);

with 建临时表：可以高效重复使用子查询部分，在后面的代码可以调用临时表，代码量少了，代码整体结构也简洁明了。

with

t1 as (临时表创建语句),

t2 as (临时表创建语句)

select * from t1 left join t2 on t1.id = t2.id；--with临时表创建结构

with

a as (select id,avg(score) as avg_score from t1 group by id),

b as (select id,name,sex from t2 where sex = ‘女’)

select a.id,a.avg_score,b.name,b.sex from a

left join b on a.id = b.id

where b.sex is not null;