Spark排序与取TopN问题

(一)TopN统计

一.Hive sql实现方法

需求描述：

根据活跃天数排名，选取每个用户使用的Top10的appid与app名称

思路解析：

1.使用row_number()函数达到排序目的

2.以用户的手机号为分组，（统计每一个用户的app排名），以活跃天数排序，排序字段命名为rn_vnt字段。并取出前十的app信息。

    sql(sqlText = s"select serv_number,prod_name,prod_id,statis_month,active_days,rn_vnt " +
      s"from (select serv_number,prod_name,prod_id," +
      s"statis_month,active_days,row_number() " +
      s"over(partition by serv_number order by active_days desc) as rn_vnt from " +
      s"profile_score_2i_visit_${this_month}_m ) t where t.rn_vnt <= '10'")
      .write.mode(SaveMode.Append).saveAsTable(tableName = s"total_visit_${this_month}_m")

输出结果(为保证数据安全，数据为模拟数据)

serv_number	prod_name	prod_id	statis_month	active_days	rn_vnt
1366666666	某app1	C9999	201905	21	1
1366666666	某app2	C8888	201905	16	2
1366666666	某app3	C7777	201905	12	3

二.Spark sql实现方法

需求描述：

按照用户数排名，取出Top10的appid与app名称

思路解析：

1.首先引入spark中functions和implicits两个数据包

2.本例中需先统计每个app使用人数，使用人数命名字段为p_sum。之后对使用人数p_sum字段，进行降序排序

3.增加a_rank，表示app的排名。其中使用monotonically_increasing_id表示一个自增键，其是从0开始每一轮加一。monotonically_increasing_id+1来让app排名从1开始排名

4.使用limit（10）代表取出前10名的app

     import org.apache.spark.sql.functions._
     import spark.implicits._

     total_profile_score_df.select( "prod_id", "prod_name","statis_month","serv_number")
       .filter(s"prod_id <> 'C9999' ")   //统计中去除某app
       .groupBy("prod_id","prod_name","statis_month")
       .agg(count("serv_number").alias("p_sum"))
       .sort($"p_sum".desc)
       .withColumn("a_rank", monotonically_increasing_id+1)
       .limit(10)
       .createTempView(viewName = "total_app_view")

输出结果(为保证数据安全，数据为模拟数据)

prod_id	prod_name	statis_month	p_sum	a_rank	province_code	flag
C1111	某app1	201904	99999999	1	北京	全量
C2222	某app2	201904	8888888	2	北京	全量
C3333	某app3	201904	777777	3	北京	全量
C4444	某app4	201904	666666	4	北京	全量

(二).百分之TopN统计

需求描述：

把指定的app，前20%访问次数的用户输出（对指定app的访问量的狂热用户）

思路解析：

1.首先计算出每个app前20%的用户数（top%n 相当于 top 中填入 n%的用户数）

2.使用row_number()函数，以app为分组，以用户对于app的访问次数排序，排序字段命名为rn_vnt字段。并取出前20%的使用当前app的用户信息。

//计算每个app的人数
    val c0 = sql(sqlText = s"select distinct serv_number " +
      s"from ads_app_bsn_${this_month}_m where prod_id = '${app_code(0)}'").count() * 0.2


    //App访问次数排名
    /**
      * 这里需要注意使用row_number()函数，要做分组取TopN的时候，必须再外围写
      * select * from () as t where t.rank <= x
      * 不然会报错
      * （写的有问题，由于row_number函数分组的时候额外加入了serv_number，导致维度有问题）
      * \t符号影响了排行，需要把手机号为\t的排除
      */

    sql(sqlText = s"select serv_number,prod_name,prod_id,statis_month " +
      s"from (select serv_number,prod_name,prod_id," +
      s"statis_month,row_number() " +
      s"over(partition by prod_name,prod_id order by visit_cnt desc) as rn_vnt from " +
      s"ads_app_bsn_${this_month}_m where prod_id = '${app_code(0)}') t where t.rn_vnt <= '$c0'")
      .write.mode(SaveMode.Overwrite).saveAsTable(tableName = s"ads_bsn_hobby_${this_month}_m")

输出结果(为保证数据安全，数据为模拟数据)

serv_number	prod_name	prod_id	statis_month
1366666666	某app1	C9999	201905
1366664444	某app1	C9999	201905
1366662222	某app1	C9999	201905