统计分析

统计分析是数据分析师的基本要求，下面部分主要针对数据分析师的常用场景进行练习。分组统计查询是数据分析师的瑞士军刀，大部分数据分析操作，都可以通过查询、分组、统计、排序进行级联组合进行，如果一个数据分析师用不好分组统计查询，那不是一个合格的数据分析师。
开始聚合统计前，我们先看下Excel的透视表，我们看到主要的四个功能块，行标签、列标签、报表筛选和数值分析。对应数据库SQL查询就是分组统计、汇总数据和筛选查询，行列转换等。

汇总分析

汇总查询主要有count，avg，sum，max和min五个函数，它们也称为aggregate function，我们接下来依次看下相关功能。它们可以实现计算表中记录行数（或者满足某个条件或包含某个特定值的行数），获得表中某些行中列的和，找出每列（所有行或某些特定的行）的最大值、最小值、平均值。他们获取的只需要汇总表中的数据，而不需要实际数据本身，因此这样效率会比较高。

            -- COUNT：计数
			
SELECT COUNT(*) AS total_student FROM student;

SELECT COUNT(grade) FROM coursegrade;

-- SUM：求和

SELECT SUM(score) AS total_score FROM stuscore;

-- AVG：平均值

SELECT AVG(score) AS avg_score FROM stuscore;

-- 使用distinct进行平均统计，结果不一样是因为分数中有相同值进行了distinct处理。

SELECT AVG(DISTINCT grade) FROM coursegrade;

-- MAX/MIN：最大值/最小值

SELECT MAX(score) AS max_score,
       MIN(score) AS min_score
FROM stuscore;

Sum函数用来返回指定列值的和，同样分析列必须为数值型同时可以使用统计函数聚集不同的值需要注意DISTINCT必须使用列名，不能用于计算表达式。

分组统计

GROUP BY按指定列对数据进行分组统计，数据分析中按各种属性、时间、地区等进行分组查询，比如如果想按班级，按课程，按性别，年龄等分组统计汇总，用where语句每种条件进行查询太复杂，这时候是分组统计展现价值的地方。分组查询经常和汇总函数进行搭配查询，达到分类汇总统计信息的目的。

            -- 按年级统计学生人数
			
SELECT department, COUNT(*) AS student_count
FROM student
GROUP BY department;

-- 按课程统计平均分

SELECT course, AVG(score) AS avg_score
FROM stuscore
GROUP BY course;

-- 多列分组

SELECT course, name, AVG(score) AS avg_score FROM stuscore GROUP BY course, name;

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套更细致地进行数据分组。如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回，如果列中有多行 NULL 值，它们将分为一组。

HAVING过滤

对分组后的结果进行过滤，过滤分组 having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，可以使用统计函数。Having和where有些类似，having过滤分组，where过滤记录。

            -- 查询平均分大于80的年级
			
SELECT course, AVG(score) AS avg_score
FROM stuscore
GROUP BY course 
HAVING AVG(score) > 80;

-- 查询学生人数超过30的年级

SELECT department, COUNT(*) AS student_count
FROM student
GROUP BY department
HAVING COUNT(*) > 30;

-- where过滤后分组

SELECT cno, AVG(grade) FROM coursegrade WHERE grade>60 GROUP BY cno;

💡

WHERE在分组前过滤，HAVING在分组后过滤

ORDER BY分组排序

对查询结果进行排序

            -- 升序排序（默认）
			
SELECT * FROM student
ORDER BY age ASC;

-- 降序排序

SELECT * FROM student
ORDER BY score DESC;

-- 分组统计排序

SELECT sex,department,COUNT(*) AS num FROM student GROUP BY sex,department ORDER BY num;

综合示例

组合使用多个子句

            -- 查询每个课程的平均分，只显示平均分大于75的课程，按平均分降序排列
			

SELECT course,
       COUNT(*) AS student_count,
       AVG(score) AS avg_score,
       MAX(score) AS max_score,
       MIN(score) AS min_score
FROM stuscore
WHERE score IS NOT NULL
GROUP BY course
HAVING AVG(score) > 75
ORDER BY avg_score DESC;
          

OLAP和行列转换

OLAP是面向数据分析师的高级查询技巧，Excel数据透视表中可以任意切换行列，也就是维度旋转，通过sql怎么实现是下面主要内容。
数据库应用分为两种，oltp和olap，oltp联机事务处理面向于基本日常事务和处理，比如售票，银行交易等事务处理。Olap联机分析处理面向决策支持，复杂的分析操作。面向决策Olap联机分析处理需要特定的数据库系统，和面向事务处理的数据库系统在模式设计、索引结构，事务管理都要不同。专为决策服务的数据库系统是面向主题、由多数据源集成、拥有当前和历史总结数据，以读为主的数据库系统，也称为数据仓库。
OLAP一般包含五种操作，上卷（Roll-up）：从细粒度数据向粗粒度的聚合，比如从区县的人口统计到省市的人口，最后到全国的人口统计；钻取（Drill-down）：上卷的相反操作，将汇总数据拆分到更细节的数据；切片（Slice）：选择维中特定的值进行分析，通过选择某个维度的单一值进行分析，比如只看北京的数据；切块（Dice）：选择维中特定区间的数据或者某批特定值进行分析，和切片的不同是得到的结果维度更多；旋转（Pivot）：即维的位置的互换，类似二维表的行列转换，可以从多个维度进行处理。这儿介绍其中pivot维度旋转操作。

行转列

通过把行转为列，便于统计分析

            
		-- 第一种方案  
		
		SELECT  NAME AS '姓名',
 MAX(IF(course = '语文', score, 0)) AS '语文',
 MAX(IF(course = '数学', score, 0)) AS '数学',
 MAX(IF(course = '英语', score, 0)) AS '英语',
 MAX(IF(course = '计算机', score, 0)) AS '计算机'
FROM stuscore  GROUP BY NAME;

--第二种方案 

Select name as '姓名',

 max(case course when '语文' then score else 0 end) as '语文',
 max(case course when '数学' then score else 0 end) as '数学',
 max(case course when '英语' then score else 0 end) as '英语',
 max(case course when '计算机' then score else 0 end) as '计算机'
from stuscore group by name;

-- 第三种方案可以使用子查询，在后面子查询中进行介绍。

-- 获取每个人按照基础学科和专业学科得分

SELECT NAME AS '姓名',
 SUM(IF(course = '语文' OR course = '数学' OR course = '英语', score, 0)) AS '基础',
 SUM(IF(course = '计算机' , score, 0)) AS '专业'
FROM stuscore GROUP BY NAME;

上面查询语句里面用到了以下函数：
If函数：具体语法如下： IF(expr1,expr2,expr3)，如果expr1的值为真，则返回expr2的值，如果expr1的值为假，则返回expr3的值。 if(sex=0,'女','男') 如果sex字段值为0，则返回女，否则返回男。 IF(course = '语文', score, 0) 如果course字段值是语文，则返回score的值，否则返回0.
Case when then else end函数： case 字段 when 值 then 结果 else 其他情况 end； case when 表达式 then 结果 else 其他情况 end； case course when '语文' then score else 0 end course字段的值为语文时返回score，否则返回0。 Max函数：指定列取最大值。

列转行

            
		select name, '语文' as 'course',chinesescore as score from stuscoreline where chinesescore>60
union all
select name, '数学' as 'course', mathscore as score from stuscoreline where mathscore>60
union all
select name, '英语' as 'course', englishscore as score from stuscoreline where englishscore>60
union all
select name, '计算机'  as 'course', computerscore as score from stuscoreline where computerscore>60;


          

字典表

也是维度表，用于数据仓库或者数据建模中维度的管理。数据字典，往往都是维度表。数据字典是关于数据库中数据的描述，即元数据，而不是数据本身。数据库中的数据都可以通过数据字典找到相关定义。
使用数据字典的好处：数据字典维护，第一便于维护管理，如果用户需求要增加变更配置项，只需要修改数据字典表记录即可，不需要修改代码。第二便于数据分析。相应的模型有星型模型和雪花模型。

            
			--通过关联事实表和字典表实现数据查询
			
		SELECT stu.no, stu.name, age,sex.name,nation.name,addr.name FROM studentdict stu, sex,nation,addr WHERE stu.sex=sex.no AND stu.nation=nation.no AND stu.addr=addr.no;

表studentdict中的sex，nation和addr都是通过字典表来实现，这样进行修改字典表对于事实表本身没有影响，而且进行数据分析非常方便。从数据模型来说是一个典型的星型模型，如果地址字段继续分为国家、省市、区县等级别的话就是一个典型的雪花模型。

窗口函数

窗口函数也称为OLAP函数，对数据库数据进行分析处，窗口函数就是为了实现OLAP分析而添加的标准SQL功能。不同数据库支持情况不同，mysql只有高版本才可以支持，数据分析功能本身在传统关系数据库支持较弱，在使用时确认下是否支持。与对一组输入行返回单个聚合值的聚合函数不同，分析函数对一组输入行计算函数，并为每一行返回一个值。分析函数是一种强大的机制，可简洁地表示复杂的分析操作.窗口函数同时具有分组和排序的功能，不减少原表的行数。应用场景包括分区排序、动态Group By、TopN计算、累计计算、层次查询等。

窗口函数

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
PARTITION BY 子句将输入行划分成多个分区。该子句与 GROUP BY 类似，但并不会实际合并具有相同键的行;
ORDER BY 子句指定每个分区中的排序方式;
窗口函数位置可以放两种函数，专用窗口排名函数和聚合函数。专用窗口函数有rank, dense_rank, row_number排名函数。聚合函数包括sum, avg, count, max, min等。group by分组汇总后改变了表的行数，一行只有一个类别，而partiition by和rank函数不会减少原表中的行数。

            
			--根据所选课程进行分数排名
			
		SELECT *, rank() over (PARTITION BY cno ORDER BY grade DESC) AS ranking FROM coursegrade;
		
--rank函数、dense_rank函数、row_number函数区别

		SELECT  *,
 rank() over (partition by cno order by grade) as rank,
 dense_rank() over (partition by cno order by grade) as dense_rank,
 row_number() over (partition by cno order by grade) as row_number
FROM
 coursegrade;
 
--窗口函数位置使用聚合函数，对于分组内的数据进行聚合

 SELECT
 cno,
 sum(grade) over (partition by cno) as cnosum,
 count(grade) over (partition by cno) as cnocount,
 avg(grade)over (partition by cno) as cnoavg,
 max(grade) over (partition by cno) as cnomax
FROM
coursegrade;

rank: 对人按照分数排名，排名代表有多少个人（设为n）的分数比你高，有并列名次的行占用后面的排名。
dense_rank: 对分数按照分数排名，排名代表有多少个分数（设为n）比你的分数高，有并列名次的行，不占用后面排名。
row_number: 对人按照次序排名，有多少人（设为n）在你前面，不考虑并列名次的情况。
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
窗口函数与聚合函数（Aggregate Function）有些类似，他们都是对一系列的行进行聚合计算，然而不同于聚合函数的是，窗口函数的输出并不是被聚集到单独的一行。像 SUM, AVG, COUNT 这些聚合函数最终输出都是一行记录。窗口函数可以通过计算每行周围窗口上的集合值来分析数据，例如：计算一定记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等。之所以使用窗口这个术语，是因为对结果的处理使用了一个滑动的查询结果集范围。窗口函数查询指定使用 OVER 关键字，默认情况下，用于计算的行集合（Window窗口）是整个数据集，并且可以用 ORDER BY 子句排序，PARTITION BY 子句可以用于将窗口缩小到特定的集合内。

group_concat函数

对于Group By查询结果，经常要把同一个分组中的值连接起来，返回一个字符串结果，下面介绍下GROUP_CONCAT 函数。
group_concat( [distinct]要连接的字段[orderby排序字段asc/desc ] [separator '分隔符'] )
通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

            
		SELECT cno, GROUP_CONCAT(grade order by grade) FROM coursegrade GROUP BY cno;
		
		SELECT cno, GROUP_CONCAT(CONCAT_WS('-',sno,grade) ORDER BY grade) FROM coursegrade GROUP BY cno;

ROLLUP和CUBE函数

WITH ROLLUP在group分组字段的基础上再进行统计数据。用户需要对N个维度进行聚合查询操作，普通的group by语句需要N个查询和N次groupby操作。而rollup一次可以进行N次group by的结果，这样可以提高查询效率。

            
SELECT sno,cno,SUM(grade) FROM coursegrade GROUP BY cno,sno WITH ROLLUP;

--多个group by实现方式

select sno,cno,sum(grade) from coursegrade
group by sno,cno
union
select sno,cno,sum(grade) from coursegrade
group by sno
union
select null,null, sum(grade) from coursegrade;

除去rollup函数，还有cube函数，rollup是cube的一种特殊情况，和rollup一样，cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube进行2的N次方次分组操作，而rollup只进行N次分组操作。假设有n个维度，rollup会有n个聚合：
rollup(a,b)统计列包含：(a,b)、(a)、()
cube(a,b,c)统计列包含：(a,b,c)、(a,b)、(a)、()

时间聚合分析

在日常数据分析过程中，经常需要对于各种粒度时间进行处理分析，这时候需要实现MTD（Month To Date）当月累计，YTD（Year To Date）当年累计，同比（去年同期相比），环比（相邻两个时间段相比）等各种数据计算，由于sql本身和数据库支持情况，实现相对比较复杂，因此也有专门的数据分析软件进行处理，不建议使用sql语句实现，相关方式可以参考下面语句。

            
select
 (select sum(isok)
  from logs
  where open_date>= date(now()) - interval 1 day
  )as last_24,
 (select sum(isok) 
  from logs
  where year(curdate())=year(open_date) and month(curdate())=month(open_date)
   )as mtd,
  (select sum(isok) 
   from logs
   where year(curdate())=year(open_date)
   )as ytd
from logs;

          

环比增长率＝（本期数－上期数）/上期数*100％反映本期比上期增长了多少；
同比增长率＝（本期数－同期数）/同期数*100％指和去年同期相比较的增长率；