SQL知识大全(六):SQL中的开窗函数

点击上方蓝字关注我们

在数据分析中，窗口函数是我们经常用到的函数，今天的文章我们总结了常用的各类窗口函数并给出实例。

一 创建数据集

from pyspark.sql import SprkSession
import pandas as pd
spark = SparkSession.builder.appName('Windowfunction').enableHiveSupport().getOrCreate()
import pyspark.sql.functions 
# 原始数据 
test = spark.createDataFrame([('001','1',100,87,67,83,98), ('002','2',87,81,90,83,83), ('003','3',86,91,83,89,63),
                            ('004','2',65,87,94,73,88), ('005','1',76,62,89,81,98), ('006','3',84,82,85,73,99),
                            ('007','3',56,76,63,72,87), ('008','1',55,62,46,78,71), ('009','2',63,72,87,98,64)],
                             ['number','class','language','math','english','physic','chemical'])

#查看原始数据
test.show()
#将原始数据存入中间表
test.createOrReplaceTempView('test_temp_table')

number|class|language|math|english|physic|chemical|
+------+-----+--------+----+-------+------+--------+
|   001|    1|     100|  87|     67|    83|      98|
|   002|    2|      87|  81|     90|    83|      83|
|   003|    3|      86|  91|     83|    89|      63|
|   004|    2|      65|  87|     94|    73|      88|
|   005|    1|      76|  62|     89|    81|      98|
|   006|    3|      84|  82|     85|    73|      99|
|   007|    3|      56|  76|     63|    72|      87|
|   008|    1|      55|  62|     46|    78|      71|
|   009|    2|      63|  72|     87|    98|      64|
+------+-----+--------+----+-------+------+--------+

#将数据转换为长数据
# 逆透视Unpivot
test_long =test.selectExpr("`number`","`class`",
                        "stack(5, 'language', `language`,'math', `math`, 'english', `english`, 'physic', `physic`,'chemical', `chemical`) as (`subject`,`grade`)").orderBy(["`class`", "`number`"]) 

test_long.show()
test_long.createOrReplaceTempView('test_long_temp_table')

+------+-----+--------+-----+
|number|class| subject|grade|
+------+-----+--------+-----+
|   001|    1| english|   67|
|   001|    1|language|  100|
|   001|    1|  physic|   83|
|   001|    1|    math|   87|
|   001|    1|chemical|   98|
|   005|    1|chemical|   98|
|   005|    1| english|   89|
|   005|    1|  physic|   81|
|   005|    1|    math|   62|
|   005|    1|language|   76|
|   008|    1|  physic|   78|
|   008|    1|    math|   62|
|   008|    1|chemical|   71|
|   008|    1|language|   55|
|   008|    1| english|   46|
|   002|    2|language|   87|
|   002|    2|    math|   81|
|   002|    2|  physic|   83|
|   002|    2|chemical|   83|
|   002|    2| english|   90|
+------+-----+--------+-----+
only showing top 20 rows

二  聚合函数

聚合函数也可用于窗口函数当中，用法和专用窗口函数相同。

聚合函数sum、avg、count、max、min都是针对自身记录以及自身记录以上的所有数据进行计算的。

聚合函数作为窗口函数，可以在每一行的数据里直观看到截止到本行数据，统计数据是多少，比如：按照时间的顺序，计算各时期的销售总额就需要用到这种累计的统计方法。同时也可以看出每一行数据对整体数据的影响。聚合函数的开窗和专用的窗口函数是一致的，其形式为：

‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

聚合函数的窗口函数中，加不加order by,order by的列名是否是用于分组的列名，这些情况都会影响到最终的结果，下面我们分别来讨论各种不同的情况。

2.1 窗口函数有无order by的区别

2.1.1 有order by且order by的字段不是用于分组的字段

这种情况下得到的结果是每个partition的累加的结果

https://zhuanlan.zhihu.com/p/104402435

test_sum=spark.sql("""
select *,sum(grade)over(partition by class,subject order by number) total_grade,
avg(grade)over(partition by subject,class  order by number) avg_grade,
count(grade)over(partition by subject,class  order by number) total_classmate,
max(grade)over(partition by subject,class  order b