时间戳

首页 > 解决方案 > Scala Slick：通过查询获取组中具有特定值的字段数

scala - Scala Slick：通过查询获取组中具有特定值的字段数

问题描述

我有一张这样的桌子：

|``````````````````````````|
|imgId, pageId, isAnnotated|
|1,      1,       true     |
|2,      1,       false    |
|3,      2,       true     |
|4,      1,       false    |
|5,      3,       false    |
|6,      2,       true     |
|7,      3,       true     |
|8,      3,       true     |
|__________________________|

我希望结果为：

|`````````````````````````````````````|
|pageId, imageCount, noOfAnotatedImage|
|   1          3          1           |
|   2          2          2           |
|   3          3          2           |
|_____________________________________|

我希望基于数字字段设置为 true 的注释图像的数量。

我试过的相关代码触发了异常：

  def get = {
    val q = (for {
      c <- WebImage.webimage
    } yield (c.pageUrl, c.lastAccess, c.isAnnotated)).groupBy(a => (a._1, a._3)).map{
      case(a,b) => (a._1, b.map(_._2).max, b.filter(_._3 === true).length, b.length)
    }
    db.run(q.result)
  }

例外：

[SlickTreeException: Cannot convert node to SQL Comprehension
| Path s6._2 : Vector[t2<{s3: String', s4: Long', s5: Boolean'}>]
]

注意：这个计数包含特定值线程清除的总记录表明，在普通 SQL 中，我需要的东西是可能的。

SELECT
   Type
  ,sum(case Authorization when 'Accepted' then 1 else 0 end) Accepted
  ,sum(case Authorization when 'Denied' then 1 else 0 end) Denied
 from MyTable
 where Type = 'RAID'
 group by Type

更改了代码，但仍然出现异常：

Execution exception
[SlickException: No type for symbol s2 found for Ref s2]

In /home/ravinder/IdeaProjects/structurer/app/scrapper/Datastore.scala:60
56  def get = {
57    val q = (for {
58      c <- WebImage.webimage
59    } yield (c.pageUrl, c.lastAccess, c.isAnnotated)).groupBy(a => (a._1, a._3)).map{
[60]      case(a,b) => (a._1, b.map(_._2).max, b.map(a => if (a._3.result == true) 1 else 0 ).sum, b.length)
61    }
62    db.run(q.result)
63  }
64

标签： scalaslick

解决方案

根据您的要求，您应该只分组，pageUrl以便对同一页面的所有行执行聚合。您可以使用条件聚合 lastAccessmax和 isAnnotated 。Slick 查询应如下所示：sumCase.If-Then-Else

val q = (for {
    c <- WebImage.webimage
  } yield (c.pageUrl, c.lastAccess, c.isAnnotated)).
  groupBy( _._1 ).map{ case (url, grp) =>
    val lastAcc = grp.map( _._2 ).max
    val annoCnt = grp.map( _._3 ).map(
        anno => Case.If(anno === true).Then(1).Else(0)
      ).sum
    (url, lastAcc, annoCnt, , grp.length)
  }

推荐阅读