电商各区域点击量Top3 商品-尚硅谷大数据培训

各区域点击量Top3 商品

按大区计算商品被点击的次数前3名，并计算出前2名占比及其他城市综合占比，结果数据展示：

1 MySQL建表

CREATE TABLE `area_count_info` (

  `task_id` text,

  `area` VARCHAR(20),

  `product_name` VARCHAR(20),

  `product_count` BIGINT,

  `city_click_ratio` VARCHAR(200)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2 思路分析

1）读取用户行为及城市信息表，取出其中的大区信息以及点击品类信息；

2）按照大区以及品类聚合统计点击总数；

3）大区内计算品类点击次数的排名；

4）使用自定义UDAF函数统计城市占比。

3 代码实现

1）创建样例类

package com.atguigu.bean

case class CityRatio(cityName: String, ratio: Double) {

  override def toString: String = s”$cityName:$ratio%”

}

2）CityTopUDAF

package com.atguigu.udf

import com.atguigu.bean.CityRatio

import org.apache.spark.sql.Row

import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}

import org.apache.spark.sql.types._

class CityTopUDAF extends UserDefinedAggregateFunction {

  // 输入的数据类型 String

  override def inputSchema: StructType = StructType(StructField(“city_name”, StringType) :: Nil)

  // 存储数据的类型  Map  Long

  override def bufferSchema: StructType = StructType(StructField(“city_count”, MapType(StringType, LongType)) :: StructField(“total_count”, LongType) :: Nil)

  // 输出的类型  String

  override def dataType: DataType = StringType

  // 相同的输入是否应该返回相同的输出

  override def deterministic: Boolean = true

  // 给存储数据进行初始化

  override def initialize(buffer: MutableAggregationBuffer): Unit = {

    // 存储 map 和 Long

    // 初始化Map

    buffer(0) = Map[String, Long]()

    // 初始化 计算式总的点击

    buffer(1) = 0L

  }

  // 分区内合并操作  executor内合并

  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {

    // 北京

    val map: Map[String, Long] = buffer.getAs[Map[String, Long]](0)

    val cityName: String = input.getString(0)

    buffer(0) = map + (cityName -> (map.getOrElse(cityName, 0L) + 1L))

    // 总量总是 +1

    buffer(1) = buffer.getLong(1) + 1L

  }

  // 分区间的合并  executor间合并

  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {

    // 两个map的合并.  map1 map2     map1 ++ map2

    val map1: Map[String, Long] = buffer1.getAs[Map[String, Long]](0)

    val map2: Map[String, Long] = buffer2.getAs[Map[String, Long]](0)

    buffer1(0) = map1.foldLeft(map2) {

      case (m, (k, v)) =>

        m + (k -> (m.getOrElse(k, 0L) + v))

    }

    // 总量的合并

    buffer1(1) = buffer1.getLong(1) + buffer2.getLong(1)

  }

  // 输出  把存储的数据转换成字符串输出

  override def evaluate(buffer: Row): Any = {

    val cityCount: Map[String, Long] = buffer.getAs[Map[String, Long]](0)

    val totalCount: Long = buffer.getLong(1)

    //取出前2名的城市

    val top2CityCount: List[(String, Long)] = cityCount.toList.sortWith { case (c1, c2) =>

      c1._2 > c2._2

    }.take(2)

    //计算前2名城市点击量占比

    val top2CityRatio: List[CityRatio] = top2CityCount.map { case (city, count) =>

      val cityRatio: Double = Math.round(count.toDouble * 1000 / totalCount) / 10.toDouble

      CityRatio(city, cityRatio)

    }

    //计算其他城市占比

    var otherRatio = 100.0

    for (elem <- top2CityRatio) {

      otherRatio -= elem.ratio

    }

    //去精度

    otherRatio = Math.round(otherRatio * 10) / 10.0

    //计算其他城市的占比

    val otherCityRatio = CityRatio(“其他”, otherRatio)

    //将其他城市占比追加至集合

    val ratios: List[CityRatio] = top2CityRatio :+ otherCityRatio

    //拼接为字符串

    ratios.mkString(“,”)

  }

}

3）AreaTop3ProductApp

package com.atguigu.app

import java.util.{Properties, UUID}

import com.atguigu.udf.CityTopUDAF

import com.atguigu.utils.PropertiesUtil

import org.apache.spark.SparkConf

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object AreaTop3ProductApp {

  def main(args: Array[String]): Unit = {

    //1.创建SparkConf

    val sparkConf: SparkConf = new SparkConf().setAppName(“AreaTop3ProductApp”).setMaster(“local[*]”)

    //2.创建SparkSession

    val spark: SparkSession = SparkSession.builder()

      .master(“local[*]”)

      .appName(“AreaTop3ProductApp”)

      .enableHiveSupport()

      .getOrCreate()

    //3.注册UDAF函数

    spark.udf.register(“cityRatio”, new CityTopUDAF)

    //4.读取用户行为及城市信息表，取出其中的大区信息以及点击品类信息

    spark.sql(“select ci.area,ci.city_name,uv.click_product_id from user_visit_action uv join city_info ci on uv.city_id=ci.city_id where uv.click_product_id>0”)

      .createOrReplaceTempView(“tmp_area_product”)

    //5.按照大区以及品类聚合统计点击总数

    spark.sql(“select area,click_product_id,count(*) product_count,cityRatio(city_name) city_click_ratio from tmp_area_product group by area,click_product_id”)

      .createOrReplaceTempView(“tmp_area_product_count”)

    //6.大区内计算品类点击次数的排名

    spark.sql(“select area,click_product_id,product_count,city_click_ratio,rank() over(partition by area order by product_count desc) rk from tmp_area_product_count”).createOrReplaceTempView(“tmp_sorted_area_product”)

    //7.获取TaskId

    val taskId: String = UUID.randomUUID().toString

    //8.取出大区品类前三名,并关联商品表取出商品名称

    val cityCountRatioDF: DataFrame = spark.sql(s”select ‘$taskId’ task_id,area,product_name,product_count,city_click_ratio from tmp_sorted_area_product tmp join product_info pi on tmp.click_product_id=pi.product_id where rk<=3″)

    //9..读取MySQL配置信息

    val properties: Properties = PropertiesUtil.load(“config.properties”)

    //10.将结果保存到MySQL

    cityCountRatioDF

      .write

      .format(“jdbc”)

      .option(“url”, properties.getProperty(“jdbc.url”))

      .option(“user”, properties.getProperty(“jdbc.user”))

      .option(“password”, properties.getProperty(“jdbc.password”))

      .option(“dbtable”, “area_count_info”)

      .mode(SaveMode.Append)

      .save()

    //11.关闭连接

    spark.close()

  }

}