Scala数组函数解决SQL查询问题-每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

下面这张表，使用scala函数完成以下任务
任务：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数。

思路：

• 导表，用Source.fromFile.getLines()方法读表的每一行，转成Array格式

scala> import scala.io.Source
import scala.io.Source

scala>     val lines = Source.fromFile("本地文件路径").getLines().toArray

lines: Array[String] = Array(
A,2015-01,5, A,2015-01,15, B,2015-01,5, 
A,2015-01,8, B,2015-01,25, A,2015-01,5, 
A,2015-02,4, A,2015-02,6, B,2015-02,10, 
B,2015-02,5, A,2015-03,16, A,2015-03,22, 
B,2015-03,23, B,2015-03,10, B,2015-03,11)

• 使用map方法对每一行进行变形（原来是“，”作为间隔符，拆开，又因为不同列格式不一样，组合成元组）

scala> lines.map(x=>{
     |       var y=x.split(",")
     |       (y(0),y(1),y(2).toInt)
     |     })
     
res0: Array[(String, String, Int)] = Array(
(A,2015-01,5), (A,2015-01,15), (B,2015-01,5), 
(A,2015-01,8), (B,2015-01,25), (A,2015-01,5), 
(A,2015-02,4), (A,2015-02,6), (B,2015-02,10), 
(B,2015-02,5), (A,2015-03,16), (A,2015-03,22), 
(B,2015-03,23), (B,2015-03,10), (B,2015-03,11))

• 通过groupBy根据第一列进行分组，得到一个Map，K是去重后的第一列，V是一个数组，元素为K对应的那一行的元组

scala> lines.map(x=>{
     |       var y=x.split(",")
     |       (y(0),y(1),y(2).toInt)
     |     }).groupBy(x=>x._1)
     
res1: scala.collection.immutable.Map[String,Array[(String, String, Int)]] = Map(
A -> Array((A,2015-01,5), (A,2015-01,15), (A,2015-01,8), (A,2015-01,5), (A,2015-02,4), (A,2015-02,6), (A,2015-03,16), (A,2015-03,22)), 
B -> Array((B,2015-01,5), (B,2015-01,25), (B,2015-02,10), (B,2015-02,5), (B,2015-03,23), (B,2015-03,10), (B,2015-03,11)))

• K不用变，对V进行数据处理，目标是每个V中根据月份排序且对每个月的所有值进行求和

scala> lines.map(x=>{
     |       var y=x.split(",")
     |       (y(0),y(1),y(2).toInt)
     |     }).groupBy(x=>x._1).mapValues(x=>x.groupBy(x=>x._2).
     |       toArray.sortWith((x,y)=>x._1<y._1).
     |       map(x=>(x._1,x._2.map(x=>x._3).sum)))
     
res2: scala.collection.immutable.Map[String,Array[(String, Int)]] = Map(
A -> Array((2015-01,33), (2015-02,10), (2015-03,38)), 
B -> Array((2015-01,30), (2015-02,15), (2015-03,44)))

• 到这一步后，基本上数据清洗的雏形就出来了，接下来分别对数据进行累加操作和值比较操作。因为题目要求是截止到本月的sum和max，因此这里我们使用scan函数，取最后一位的值tail
• 通过zip，拼接到原数据上

scala> lines.map(x=>{
     |       var y=x.split(",")
     |       (y(0),y(1),y(2).toInt)
     |     }).groupBy(x=>x._1).mapValues(x=>x.groupBy(x=>x._2).
     |       toArray.sortWith((x,y)=>x._1<y._1).
     |       map(x=>(x._1,x._2.map(x=>x._3).sum))).
     |       toArray.map(x=>(x._1,x._2.map(x=>x._1).
     |       zip(x._2.map(x=>x._2).scan(0)(_+_).tail).
     |       zip(x._2.map(x=>x._2).scan(0)(_.max(_)).tail)))
     
res3: Array[(String, Array[((String, Int), Int)])] = Array(
(A,Array(((2015-01,33),33), ((2015-02,43),33), ((2015-03,81),38))), 
(B,Array(((2015-01,30),30), ((2015-02,45),30), ((2015-03,89),44))))

• 输出遍历，完整代码以及IDEA结果如下

import scala.io.Source

object pv{
  def main(args: Array[String]): Unit = {
    val lines = Source.fromFile("files/03pv/pv.txt").getLines().toArray
    lines.map(x=>{
      var y=x.split(",")
      (y(0),y(1),y(2).toInt)
    }).groupBy(x=>x._1).mapValues(x=>x.groupBy(x=>x._2).
      toArray.sortWith((x,y)=>x._1<y._1).
      map(x=>(x._1,x._2.map(x=>x._3).sum))).
      toArray.map(x=>(x._1,x._2.map(x=>x._1).
      zip(x._2.map(x=>x._2).scan(0)(_+_).tail).
      zip(x._2.map(x=>x._2).scan(0)(_.max(_)).tail))).
      foreach(x=>{
        x._2.foreach(y=> println(
        s"用户:${x._1},日期:${y._1._1},至今为止的最高访问次数:${y._2},到本月的总访问次数:${y._1._2}"))
      })
  }
}