Ebean RawSql未解析查询

Question

因此，由于Ebean不支持聚合函数以及许多其他功能，所以我使用RawSql尝试让复杂的查询工作。此外，我需要使用.unparsed(sql)，因为有效的查询字符串将不能使用.parse(sql)。

这是我的代码：

final String sql = "SELECT DISTINCT d.user_id, CAST(d.date as DATE) AS date," +
            " MAX(d.speed_KPH) OVER (PARTITION BY CAST(d.date as DATE)) AS maxSpeed," +
            " AVG(d.speed_KPH) OVER (PARTITION BY CAST(d.date as DATE)) AS avgSpeed" +
            " FROM Driver as d" +
            " WHERE d.date BETWEEN '" + sdf.format(from) + "' and '" + sdf.format(to) + "'" +
            " ORDER BY date DESC";

final RawSql rawSql = RawSqlBuilder.unparsed(sql)
        .columnMapping("user_id", "user.userId")
        .columnMapping("date", "message.date")
        .columnMapping("maxSpeed", "maxSpeed")
        .columnMapping("avgSpeed", "avgSpeed")
        .create();

final List<MessageAggregate> theList = Ebean.find(MessageAggregate.class).setRawSql(rawSql)
        .findList();

Ebean生成以下(有效)查询：

SELECT DISTINCT d.user_id, CAST(d.date as DATE) AS date, 
        MAX(d.speed_KPH) OVER (PARTITION BY CAST(d.date as DATE)) AS maxSpeed,
        AVG(d.speed_KPH) OVER (PARTITION BY CAST(d.date as DATE)) AS avgSpeed
    FROM Driver AS d
    WHERE d.date 
    BETWEEN '2014-01-02 09:00:00' and '2014-03-12 17:00:00' 
    ORDER BY date DESC

在MSSQL中运行时，将生成：

    user_id date    maxSpeed    avgSpeed
4   2014-02-20  74.1    1.1935294117647
311 2014-02-20  74.1    1.1935294117647
314 2014-02-20  74.1    1.1935294117647
347 2014-02-20  74.1    1.1935294117647
... etc ...

在Ebean中运行时(通过PlayFramework 2.2.2)：

Caused by: javax.persistence.PersistenceException: Query threw SQLException:Invalid column index 0.
Bind values:[]

我尝试过使用和不使用.columnMapping，并发现了这个小的(3+岁的母块这里)，它表明您可以使用.columnMapping()，但它们必须是正确的线性顺序，在我的例子中，它们是。

Answer 1

目前没有办法做到这一点。对聚合函数的支持根本不存在。因此，我最终简化了我的查询，并通过代码(Java)完成了其余的结果过滤。如果你有类似的情况，不要浪费你的时间(和你的头发！)试着让这些东西发挥作用，因为它就是行不通。