计算每个品牌每个月的销售额，到当前月的销售额，每个月到当前月的最大销售额

Penny ·

更新时间:2024-09-21

· 840 次阅读

数据说明：
日期，品牌，销售额

计算每个品牌每个月的销售额，到当前月的销售额，每个月到当前月的最大销售额。

结果展示：
品牌月份当月销售额截至到当前月的销售额截至当前月的最大销售额
1 100 100 100
2 249 349 249
都要保存到MYSQL


import java.util.Properties
import org.apache.spark.rdd.RDD
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{DataFrame, SQLContext, SaveMode}
object Need1 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setMaster("local").setAppName(this.getClass.getSimpleName)
    val sc = new SparkContext(conf)
    val sQLContext = new SQLContext(sc)
    import sQLContext.implicits._
    val sorceFile: RDD[String] = sc.textFile("E:\\五道口大数据\\吉祥spark开始\\20200205\\sparkSQL第二天需求\\需求1\\data.txt")
    val df: DataFrame = sorceFile.map(line => {
      val splits = line.split(",")
      (splits(0).substring(0,7), splits(1),splits(2))
    }).toDF("month", "brand", "money")
    df.createTempView("salesperson ")
    /**
      * 品牌	月份	当月销售额	截至到当前月的销售额	截至当前月的最大销售额
      * 1        100                   100                      100
      * 2        249                   349                      249
      *select name,months,sum(money) as money from t_store group by name,months
      * 我的答案select brand,year,month,sum(money) as money ,sum(sum(money))over(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  as culmoney  ,
      * max(money) as maxmoney from salesperson group by brand,year,month
      * select brand,years,month from (select brand,month,month,sum(money) as money ,sum(sum(money))over(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  as culmoney ,
      * max(money) as maxmoney from salesperson group by brand,month,years)
      */
    val result: DataFrame = sQLContext.sql(
      """
         select brand,month,month_money,
         sum(month_money) over(partition by brand order by month ) end_money,
         max(month_money) over(partition by brand order by month ) max_money
         from
         (select brand,month,sum(money) as month_money from salesperson
         group by brand,month)
      """.stripMargin)
    val url:String="jdbc:mysql://localhost:3306/stu?characterEncoding=utf-8&serverTimezone=Asia/Shanghai"
    val table:String="need02"
    val conn = new Properties()
    conn.setProperty("user","root")
    conn.setProperty("password","123")
    conn.setProperty("driver","com.mysql.jdbc.Driver")
    result.write.mode(SaveMode.Overwrite).jdbc(url,table,conn)
      sc.stop()
  }
}

作者：weixin_38677730

销售

1024 个赞