SparkSQL 实现窗口函数

开窗函数介绍

• 开窗函数的引入是为了既显示聚合前的数据，又显示聚合后的数据。即在每一行的最后一列添加聚合函数的结果。开窗用于为定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用Group BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
• 聚合函数和开窗函数的区别：
  • 聚合函数是多行变一行；如果要显示其他列必须加入到group by中。
  • 开窗函数是一行变多行；不需要加group by就可以将所有信息显示出来。
• 开窗函数分类：
  • 聚合开窗函数：聚合函数(col) + OVER()。
  • 排序开窗函数：排序函数(col) + OVER()。
  • 分区类型NTILE开窗函数：NTILE(num) + OVER()。

开窗函数使用语法

• 聚合类型

sum()/count()/avg()/max()/min() OVER([PARTITION BY XXX] [ORDER BY XXX [DESC]]) 

• 排序类型

ROW_NUMBER() OVER([PARTITION BY XXX] [ORDER BY XXX [DESC]])

• 分区类型

NTILE(num) OVER([PARTITION BY XXX] [ORDER BY XXX [DESC]])

开窗函数代码示例

# coding : utf8
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StringType, IntegerType, FloatType, ArrayType
from pyspark.sql import functions as F

if __name__ == '__main__':
    ss = SparkSession.builder \
        .appName("test") \
        .master("local[*]") \
        .getOrCreate()
    sc = ss.sparkContext

    rdd_dota = sc.parallelize([
        ('AME', 'LGD', 90),
        ('YATORO', 'TS', 95),
        ('Yuragi', 'OG', 88),
        ('miCKe', 'Liquid', 95),
        ('Monet', 'Aster', 90),
        ('K1', 'BC', 90),
        ('RTZ', 'EG', 85),
        ('Crystallis', 'Secret', 60),
        ('NTS', 'LGD', 70),
        ('TorontoTokyo', 'TS', 75),
        ('bzm', 'OG', 90),
        ('Nisha', 'Liquid', 97),
        ('Ori', 'Aster', 82),
        ('DarkMago', 'BC', 79),
        ('Abed', 'EG', 86),
        ('Boom', 'Secret', 77),
        ('Topson', 'OLDG', 99),
        ('Noone', 'OLDG', 89)
    ])

    schema = StructType().add("Player", StringType()) \
        .add("Team", StringType()) \
        .add("Score", IntegerType())

    dota_df = rdd_dota.toDF(schema)

    dota_df.createTempView("dota_player")

    # TODO ：聚合函数
    ss.sql(
        '''
        SELECT *, AVG(Score) OVER(PARTITION BY Team) AS avg_score FROM dota_player
        '''
    ).show()

+------------+------+-----+---------+
|      Player|  Team|Score|avg_score|
+------------+------+-----+---------+
|       Monet| Aster|   90|     86.0|
|         Ori| Aster|   82|     86.0|
|          K1|    BC|   90|     84.5|
|    DarkMago|    BC|   79|     84.5|
|         RTZ|    EG|   85|     85.5|
|        Abed|    EG|   86|     85.5|
|         AME|   LGD|   90|     80.0|
|         NTS|   LGD|   70|     80.0|
|       miCKe|Liquid|   95|     96.0|
|       Nisha|Liquid|   97|     96.0|
|      Yuragi|    OG|   88|     89.0|
|         bzm|    OG|   90|     89.0|
|      Topson|  OLDG|   99|     94.0|
|       Noone|  OLDG|   89|     94.0|
|  Crystallis|Secret|   60|     68.5|
|        Boom|Secret|   77|     68.5|
|      YATORO|    TS|   95|     85.0|
|TorontoTokyo|    TS|   75|     85.0|
+------------+------+-----+---------+

    # TODO ：排序窗口
    ss.sql(
        '''
        SELECT *, ROW_NUMBER() OVER(ORDER BY Score DESC )  AS row_number_rank,
         DENSE_RANK() OVER(PARTITION BY Team ORDER BY Score DESC ) AS  dense_rank,
         RANK() OVER(ORDER BY Score) AS  rank
         FROM dota_player
        '''
    ).show()

+------------+------+-----+---------------+----------+----+
|      Player|  Team|Score|row_number_rank|dense_rank|rank|
+------------+------+-----+---------------+----------+----+
|       Monet| Aster|   90|              6|         1|  11|
|         Ori| Aster|   82|             13|         2|   6|
|          K1|    BC|   90|              7|         1|  11|
|    DarkMago|    BC|   79|             14|         2|   5|
|        Abed|    EG|   86|             11|         1|   8|
|         RTZ|    EG|   85|             12|         2|   7|
|         AME|   LGD|   90|              5|         1|  11|
|         NTS|   LGD|   70|             17|         2|   2|
|       Nisha|Liquid|   97|              2|         1|  17|
|       miCKe|Liquid|   95|              4|         2|  15|
|         bzm|    OG|   90|              8|         1|  11|
|      Yuragi|    OG|   88|             10|         2|   9|
|      Topson|  OLDG|   99|              1|         1|  18|
|       Noone|  OLDG|   89|              9|         2|  10|
|        Boom|Secret|   77|             15|         1|   4|
|  Crystallis|Secret|   60|             18|         2|   1|
|      YATORO|    TS|   95|              3|         1|  15|
|TorontoTokyo|    TS|   75|             16|         2|   3|
+------------+------+-----+---------------+----------+----+

    # TODO ：NTILE分区
    ss.sql(
        '''
        SELECT *, NTILE(4) OVER(ORDER BY Score DESC) AS ntile FROM dota_player
        '''
    ).show()

+------------+------+-----+-----+
|      Player|  Team|Score|ntile|
+------------+------+-----+-----+
|      Topson|  OLDG|   99|    1|
|       Nisha|Liquid|   97|    1|
|      YATORO|    TS|   95|    1|
|       miCKe|Liquid|   95|    1|
|         AME|   LGD|   90|    1|
|       Monet| Aster|   90|    2|
|          K1|    BC|   90|    2|
|         bzm|    OG|   90|    2|
|       Noone|  OLDG|   89|    2|
|      Yuragi|    OG|   88|    2|
|        Abed|    EG|   86|    3|
|         RTZ|    EG|   85|    3|
|         Ori| Aster|   82|    3|
|    DarkMago|    BC|   79|    3|
|        Boom|Secret|   77|    4|
|TorontoTokyo|    TS|   75|    4|
|         NTS|   LGD|   70|    4|
|  Crystallis|Secret|   60|    4|
+------------+------+-----+-----+