sparkSQL 统计TopN

傷城~ 2023-07-20 12:11 6阅读 0赞

原始数据如下：  
![在这里插入图片描述][20200401145615670.png]  
需求：按天统计uid。  
main方法：

object TopNStatJob { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder().appName("TopNStatJob")
          .config("spark.sql.sources.partitionColumnTypeInference.enabled","false")
          .master("local[2]").getOrCreate()
        val accessDF = spark.read.format("parquet").load("file:///E:/test/clean")
    // accessDF.printSchema()
        accessDF.show(false)
        // 最受欢迎的TopN netType
        netTypeAccessTopNStat(spark, accessDF)
    
        spark.stop
      }
    }

# 方式一:使用DataFrame #

/** * 最受欢迎的TopN netType * @param spark * @param accessDF */
      def netTypeAccessTopNStat(spark: SparkSession, accessDF: DataFrame): Unit = { 
    	val wifiAccessTopNDF = accessDF.filter(accessDF.col("day") === "20190702" && accessDF.col("netType") === "wifi")
    	.groupBy("day", "uid").agg(count("uid").as("times")).orderBy(desc("times"))
    	wifiAccessTopNDF.show(false)
      }

# 方式二：使用sparkSQL #

def netTypeAccessTopNStat(spark: SparkSession, accessDF: DataFrame): Unit = { 
        accessDF.createOrReplaceTempView("access_logs")
        val wifiAccessTopNDF = spark.sql("select day,uid,count(1) as times from access_logs where day='20190702' and netType='wifi' group by day,uid order by times desc")
        wifiAccessTopNDF.show(false)
      }

两种方式都可以实现TopN，控制台打印结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70]

[20200401145615670.png]: /images/20230528/f141b68b91dc4b5e81c52f16350e078b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70]: /images/20230528/17cdf70001414db6a1b8f027caf46ad4.png