将Scala Slick对象汇总/聚合到另一个对象 [英] Summarizing/aggregating a Scala Slick object into another

问题描述

我实质上是在尝试使用Scala Slick重新创建以下SQL查询:

I'm essentially trying to recreate the following SQL query using Scala Slick:

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

如您所见，它将获取一张带有标签的事物表并将其汇总，汇总各种计数.包含以下信息的表:

As you can see, it takes what a table of labeled things and aggregates them, summing various counts. A table with the following info:

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

如果查询所有日期应产生以下结果:

Should yield the following result if queried across all dates:

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

这是我的Scala代码如下:

This is what my Scala code looks like:

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.希望这是有道理的.任何帮助将不胜感激.

The summarizeSince function is where I'm having trouble. I seem to be able to query Things just fine, filtering by date, and grouping by my fields... however, I'm having trouble summing countA and countB. With the summed results, I'd then like to create a SummarizedThing for each unique labelOne + labelTwo combination. Hopefully that makes sense. Any help would be greatly appreciated.

推荐答案

大概需要运行查询并映射到此处的SummarizedThing吗?

presumably need to run the query and map to SummarizedThing here, perhaps?

完全正确.

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

这篇关于将Scala Slick对象汇总/聚合到另一个对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！