首页 文章

将Scala Slick对象汇总/聚合到另一个对象中

提问于
浏览 次
1

我本质上是尝试使用Scala Slick重新创建以下SQL查询：

select labelOne, labelTwo, sum(countA), sum(countB) from things where date > 'blah' group by labelOne, labelTwo;

正如您所看到的，它采用标记事物的表格并聚合它们，总结各种计数 . 包含以下信息的表：

ID | date | labelOne | labelTwo | countA | countB
-------------------------------------------------
0  | 0    | foo      | cheese   | 1      | 2
1  | 0    | bar      | wine     | 0      | 3
2  | 1    | foo      | cheese   | 3      | 4
3  | 1    | bar      | wine     | 2      | 1
4  | 2    | foo      | beer     | 1      | 1

如果在所有日期查询，应该产生以下结果：

labelOne | labelTwo | countA | countB
-------------------------------------
foo      | cheese   | 4      | 6
bar      | wine     | 2      | 4
foo      | beer     | 1      | 1

这就是我的Scala代码：

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.jdbc.StaticQuery
import StaticQuery.interpolation
import org.joda.time.LocalDate
import com.github.tototoshi.slick.JodaSupport._

case class Thing(
  id: Option[Long],
  date: LocalDate,
  labelOne: String,
  labelTwo: String,
  countA: Long,
  countB: Long)

// summarized version of "Thing": note there's no date in this object
// each distinct grouping of Thing.labelOne + Thing.labelTwo should become a "SummarizedThing", with summed counts
case class SummarizedThing(
  labelOne: String,
  labelTwo: String,
  countASum: Long,
  countBSum: Long)

trait ThingsComponent {
  val Things: Things

  class Things extends Table[Thing]("things") {
    def id       = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def date     = column[LocalDate]("date", O.NotNull)
    def labelOne = column[String]("labelOne", O.NotNull)
    def labelTwo = column[String]("labelTwo", O.NotNull)
    def countA   = column[Long]("countA", O.NotNull)
    def countB   = column[Long]("countB", O.NotNull)

    def * = id.? ~ date ~ labelOne ~ labelTwo ~ countA ~ countB <> (Thing.apply _, Thing.unapply _)

    val byId = createFinderBy(_.id)
  }
}

object Things extends DAO {
  def insert(thing: Thing)(implicit s: Session) { Things.insert(thing) }

  def findById(id: Long)(implicit s: Session): Option[Thing] = Things.byId(id).firstOption

  // ???
  def summarizeSince(date: LocalDate)(implicit s: Session): Set[SummarizedThing] = {
    Query(Things).where(_.date > date).groupBy(x => (x.labelOne, x.labelTwo)).map {
      case(thing: Thing) => {
        // obviously this line below is wrong, but you can get an idea of what I'm trying to accomplish:
        // create a new SummarizedThing for each unique labelOne + labelTwo combo, summing the count columns
        new SummarizedThing(thing.labelOne, thing.labelTwo, thing.countA.sum, thing.countB.sum)
      }
    } // presumably need to run the query and map to SummarizedThing here, perhaps?
  }
}

summarizeSince 功能是我遇到麻烦的地方 . 我似乎能够很好地查询 Things ，按日期过滤，并按我的字段分组......但是，我无法总结 countA 和 countB . 根据求和结果，我想为每个独特的 labelOne + labelTwo 组合创建一个 SummarizedThing . 希望这是有道理的 . 任何帮助将不胜感激 .

mysql scala playframework playframework-2.0 slick

2 回答

  • 4

    可能需要在这里运行查询并映射到SummarizedThing？

    究竟 .

    Query(Things).filter(_.date > date).groupBy(x => (x.labelOne, x.labelTwo)).map {
  // match on (key,group) 
  case ((labelOne, labelTwo), things) => {
    // prepare results as tuple (note .sum returns an Option)
    (labelOne, labelTwo, things.map(_.countA).sum.get, things.map(_.countB).sum.get)
  }
}.run.map(SummarizedThing.tupled) // run and map tuple into case class
    回复于
  • 1

    与其他答案相同，但表达为理解，除了 .get 是例外，所以你可能需要 getOrElse .

    val q = for { 
  ((l1,l2), ts) <- Things.where(_.date > date).groupBy(t => (t.labelOne, t.labelTwo)) 
} yield (l1, l2, ts.map(_.countA).sum.getOrElse(0L), ts.map(_.countB).sum.getOrElse(0L))

// see the SQL that generates.
println( q.selectStatement )
// select x2.`labelOne`, x2.`labelTwo`, sum(x2.`countA`), sum(x2.`countB`) 
// from `things` x2 where x2.`date` > '2013' group by x2.`labelOne`, x2.`labelTwo`

// map the result(s) of your query to your case class
q.map(SummarizedThing.tupled).list
    回复于

相关问题