从零开始学SQL数据分析，SQL窗口函数

　　本文讲解窗口函数的概念，窗口函数与数据分组的功能相似，可以指定数据窗口进行统计分析，但窗口函数与数据分组又有所区别，窗口函数对每个组返回多行，而数据分组对每个组只返回一行；窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，而数据分组是针对所有数据进行统计。
　　窗口函数的写法：
　　<窗口函数> over (partition by <用于分组的列名>order by <用于排序的列名>)
　　窗口函数主要有两种，一种是专用窗口函数，包括rank、dense_rank、row_number等。另一种是聚合函数，包括sum、avg、count、max、min等，本文逐一介绍窗口函数的五个功能，分别是聚合、排序、极值、移动、切片，下面一起来学习。
　　首先创建一个金额表，年份、姓名、国家设置为字符串类型，交易金额设置为整型。 #创建金额表pay CREATE TABLE pay ( year VARCHAR ( 10 ), name VARCHAR ( 10 ), country VARCHAR ( 10 ), payment INT(10) );
　　给金额表插入数值。 # 给金额表插入数据 INSERT INTO pay(year,name,country,payment) VALUES (2017,＂Lining＂,＂China＂,1119), (2018,＂Lining＂,＂China＂,1176), (2018,＂Zhaoqi＂,＂China＂,1388), (2019,＂Zhaoqi＂,＂China＂,1597), (2018,＂Jackie＂,＂USA＂,1028), (2019,＂Jackie＂,＂USA＂,1934), (2020,＂Jackie＂,＂USA＂,1837), (2017,＂Tom＂,＂India＂,1578), (2018,＂Tom＂,＂India＂,1329), (2019,＂Tom＂,＂India＂,1578), (2020,＂Tom＂,＂India＂,1399);
　　将所有的数据查询出来结果如下所示。 SELECT * from pay;
　　一、聚合
　　1、计算列表总金额 SELECT *, SUM(payment) OVER() as Total_payment from pay;
　　计算当前列表的总金额可以使用窗口函数，sum是求和，over()中不添加参数，则对所有数据进行求和，输出的结果都是15963。
　　2、计算各国家总金额 SELECT *, SUM(payment) OVER() as Total_payment, SUM(payment) OVER(PARTITION by country) as country_payment from pay;
　　计算各国家总金额就要对各个国家分组，这里分组使用的是PARTITION by，PARTITION by的功能与GROUP BY的功能类似，指定按照那一列进行分组，用country分组求和，则每个country的输出结果一致。
　　3、按国家降序累加求和金额 SELECT *, SUM(payment) OVER() as Total_payment, SUM(payment) OVER(PARTITION by country) as country_payment, SUM(payment) OVER(PARTITION by country ORDER BY payment DESC) as order_payment from pay;
　　这里使用SQL中常用的向下累计求和的方法，当使用order by时，没有rows between则意味着窗口是从起始行到当前行，所以对不同国家进行累加求和操作。
　　4、不同国家人数计数
　　count()用于计数，与前面sum的用法基本一致，可以用count(distinct country)进行去重，如果用partition by进行分组，则分组后再计数。 SELECT *, COUNT(name) OVER() as Total_people, COUNT(name) OVER(PARTITION by country) as country_people from pay;
　　5、 不同国家平均金额 SELECT *, AVG(payment) OVER() as avg_payment, AVG(payment) OVER(PARTITION by country) as country_ayg_payment from pay;
　　使用avg聚合函数的用法与前面的聚合运算用法一致，PARTITION by同样用来分组，这里分组后求均值。
　　6、各国家最低金额 SELECT *, MAX(payment) OVER() as Max_payment, MIN(payment) OVER(PARTITION by country) as country_min_payment from pay;
　　这里MAX(payment)函数对整个数据计算最大值，使用PARTITION by对于不同的国家分组后然后计算最小值。
　　二、排序
　　1、各国家按金额排序
　　使用窗口函数排序，会使用到三个函数，row_number,rank,dense_rank，他们的使用区别如下： row_number从1开始，按照顺序，生成分组内记录的序列； rank生成数据项在分组中的排名，排名相等会在名次中留下空位； dense_rank生成数据项在分组中的排名，排名相等会在名词中不会留下空位。 SELECT *, ROW_NUMBER()OVER(ORDER BY payment DESC) as ＂顺序排序＂, RANK()OVER(ORDER BY payment DESC) as ＂秩排序＂, DENSE_RANK()over(ORDER BY payment DESC) as ＂数据排序＂ from pay;
　　row_number函数，按照行记录的顺序来排序，此处从1到11按顺序排列；rank函数，在排名相等会在名次中留下空位，此处共同排名为第4名，同时忽略第5名，继续往下排列；dense_rank排名相等会在名词中不会留下空位此处共同排名为第4名，不忽略第5名，继续往下排列。
　　三、极值
　　1、当前行金额最高的人
　　first_value截止当前行的第一个，last_value截止当前行的最后一个。 select *, first_value(name)over(order by payment desc) as max_id, first_value(name)over(order by payment asc) as min_id, last_value(name)over(order by payment desc) as min_id_1, last_value(name)over(partition by country order by payment desc rows between unbounded preceding and unbounded following) as level_min_id from pay;
　　first_value按分组排序后取范围内第1个值，last_value取最后1个值，因为默认窗口的关系，last_value会随着窗口的改变而改变，所以一般不用last_value，如果要用，则改变窗口为所有行，此处用来查询当前金额最大的人，以及截至当前金额最小的人。
　　四、移动
　　1、按国家分组金额排名前1位和后1位人名
　　lag和lead是按照排序规则，取前多少位和后多少位，参数有3个，第1个是要取出来的列，第2个移动多少位，第3个是如果取不到，赋予的值，默认取不到是NULL。 select *, lag(name,1,null)over(partition by country order by payment desc) as lag_id, lead(name,1,＂0＂)over(partition by country order by payment desc) as lead_id from pay;
　　五、切片
　　1、按金额切片
　　ntile(n)用于将分组数据按照顺序切分成N片，返回当前切片值，ntile把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号，ntile(3)表示将表切分为3组，ntile可以分组排序后切分，表示对当前的组内进行切分后排序。 select *, ntile(3) over(order by payment desc) as total_part, ntile(2)over(partition by country order by payment desc) as level_part from pay;