问Apache Spark SQL:如何使用GroupBy和Max过滤数据

EN

      scala> val df  = spark.read.format("csv").option("header","true").load("/user/nikhil/acc.csv")
        df: org.apache.spark.sql.DataFrame = [postcode: string, customer: string ... 1 more field]

        scala> df.show()
        +--------+--------+---------+
        |postcode|customer|accidents|
        +--------+--------+---------+
        |       1|  Nikhil|        5|
        |       2|     Ram|        4|
        |       1|   Shyam|        3|
        |       3|  pranav|        1|
        |       1|   Suman|        2|
        |       3|    alex|        2|
        |       2|     Raj|        5|
        |       4|   arpit|        3|
        |       1|   darsh|        2|
        |       1|   rahul|        3|
        |       2|   kiran|        4|
        |       3|    baba|        4|
        |       4|    alok|        3|
        |       1|   Nakul|        5|
        +--------+--------+---------+


        scala> df.createOrReplaceTempView("tmptable")

   scala> spark.sql(s"""SELECT postcode,customer, accidents FROM (SELECT postcode,customer, accidents, row_number() over (PARTITION BY postcode ORDER BY accidents desc) as rn  from tmptable) WHERE rn = 1""").show(false)
+--------+--------+---------+                                                   
|postcode|customer|accidents|
+--------+--------+---------+
|3       |baba    |4        |
|1       |Nikhil  |5        |
|4       |arpit   |3        |
|2       |Raj     |5        |
+--------+--------+---------+