第5章Excel数据分析之数据透视表遇见SQL

第5章 数据透视表遇见SQL

5-1如何在查询中使用SQL语句？

1、制作数据透视表时编写SQL语句

外部数据源制作动态数据透视表

找到文件数据

统计工资>=8000各部门有多少人

select * from [数据源$] where 基础工资>=8000

2、3d快捷键导入外部数据时SQL语句编写

先按Alt 松开，再按d松开，再按d松开，再按d松开

Alt+d+d+d

5-2SQL查询语句（数据透视表的辅助列）

1.如何编写SQL查询语句

alt+d+d+d导入外部数据源放在新工作表，成超级表格

编写SQL语句

获取全部字段

select * from [销售表$]

获取某个字段

select 日期,数量 from [销售表$]

2.如何添加新增列（辅助列）

新增金额

select  *,数量*单价 as 金额  from [销售表$]

操作步骤：

1）导入数据

2）数据透视表新增一列

5-3SQL常用运算符（案例：添加分析维度）

1. 算术运算符：+、-、*、/、\

   select *,单价+1 from  [销售表$]

2. 比较运算符：>、>=、<、<=、<>、=

   select *,数量>=400 from [销售表$]

3. 连接运算符：&

   select *,名称&"-"&单价 from [销售表$]

4. 逻辑运算符：and、or、not

   select *,-1 and -1 and -1 from [销售表$]

select *,-1 or 0 or -1 from [销售表$]

select *,not 0 from [销售表$]

判断金额>=2000

5-4SQL筛选语句（数据透视表数据源的过滤）

1.普通筛选

select * from [销售表$] where 数量>=200

2.与关系筛选

select * from [销售表$] where 数量>=200 and 数量<=300

两个条件同时满足

3.或关系筛选

select * from [销售表$] where 单价>=5 or 数量<=400

只要一个条件满足

4.日期数据筛选

日期书写使用 ##

select * from [销售表$] where (单价>=5 or 数量<=400) and 日期<=#2022-1-5#

制作数据透视表

5-5SQL语句（逻辑运算符升级版）

1.Between（可以看作and的简化）

 select * from [销售表$] where 数量>=100 and  数量<=200       
 select * from [销售表$] where 数量 between 100  and 200      
 select * from [销售表$] where 日期 between  #2022-1-5# and #2022-1-10# 

2.In（可以看作or的简化）

select * from [销售表$] where  名称="小米" or 名称="大豆" or 名称="红豆" 
 select * from [销售表$] where 名称 in  ("小米", "大豆" ,"红豆") 
 select * from [销售表$$]  where 名称 in (select  名称 from [条件表$]) 

5-6SQL的like运算符-1（字符串模糊筛选）

1.任意多个字符%

2.任意单个字符_

3.%与_的常见应用方法

like 是 等于
select * from [销售表$] where 名称 like “大%”	以"大"字开头的名称
select * from [销售表$] where 名称 like “%豆”	以"豆"字结尾的名称
select * from [销售表$] where 名称 like “%水%”	包含"水"字的名称
select * from [销售表$] where 名称 like “___”	三根下划线就是三个字符名称
select * from [销售表$] where 名称 like “_稻”	以“稻”结尾的两个字符的名称

5-7SQL的like运算符-2（字符组的应用）

字符组表示方法：[……]，表示字符长度为1。[!……]表示排除字符组中的字符。
汉字：[一-龥]
字母：[a-z]
数字：[0-9]

要求	语句
以字母开头的品名	select * from [订单表$] where 品名 like “[a-z]%”
以abcd开头的品名	select * from [订单表$] where 品名 like “[abcd]%”
以数字开头的品名	select * from [订单表$] where 品名 like “[0-9]%”
以13579开头的品名	select * from [订单表$] where 品名 like “[13579]%”
包含%的品名	select * from [订单表$] where 品名 like “%[%]%”
包含_的品名	select * from [订单表$] where 品名 like “%[_]%”
不以字母开头的品名，加!	select * from [订单表$] where 品名 like “[!a-z]%”
以汉字开头的品名	select * from [订单表$] where 品名 like “[一-龥]%”

5-8SQL语句之前几项与唯一值

1.TOP前几项

2.Distinct获取唯一值

前三项	select top 3 * from [销售表$]
去重	select distinct * from [销售表$]
对日期去重	select distinct 日期 from [销售表$]

3.实例：统计每天的采购品名数

select distinct 日期,名称 from [销售表$]

5-9SQL语句之排序（采购数量前100汇总）

1.排序（升序asc、降序desc）

以数量进行升序	select * from [销售表$] order by 数量 asc
以数量进行降序	select * from [销售表$] order by 数量 desc
以数量、单价进行降序	select * from [销售表$] order by 数量 asc,单价 asc
以日期进行排序	select * from [销售表$] order by 日期

2.实例：对采购数量前100汇总

select top 100 * from [销售表$] order by 数量 desc

5-10SQL语句之分组聚合-1（数据汇总分析）

1.group by分组基本写法

统计每一日的总金额

select 日期,sum(数量*单价) as 总金额 from [销售表$] group by 日期

2.实例1：统计出总金额大于等于10000的日期。
3.实例2：对采购金额用千为频段，统计记录数。

获取频段：

select  数量*单价\1000 as 频段 from [销售表$] group by 数量*单价\1000

统计记录数：

select  数量*单价\1000 as 频段,count(*) as 计数  from [销售表$] group by 数量*单价\1000

5-11SQL语句之分组聚合-2（条件分组）

1.多字段分组

select 日期,名称,count(*) as 计数 from [销售表$]  gr