java - SQLDeveloper 运行查询但从 hibernate 中获取 "ORA-00979: Not a Group By expression"

Question

我的数据看起来像这样:

  sensorid |  sampletime             |  correctedvalue   | qualityflag
-----------------------------------------------------------------------
  4472     | 27-OCT-10 00:00:00.123  |   3.75            | 0
  4472     | 27-OCT-10 00:00:01.324  |   3.85            | 0
  4472     | 27-OCT-10 00:00:02.123  |   3.92            | 0
  4472     | 27-OCT-10 00:00:03.324  |   4.05            | 0

还有一个在 Oracle SQL Developer 中运行良好的查询(它返回 15 秒周期内的平均数据):

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/15)*15)/24/60/60 as tspan, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
group by sensorid, 
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/15)*15)/24/60/60
order by tspan

但是当我将它插入到我的 java 代码中时，出现错误:

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:221)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:118)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:224)
...

我在 Jave 中使用的实际查询字符串看起来更像这样:

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60 as tspan, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
where sampletime between ? and ?
    and sensorid = ?
group by sensorid, 
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60
order by tspan

参数通过调用设置:

SQLQuery q = session.createSQLQuery(queryString);
q.setInteger(0, averagingWindowInSeconds);
q.setInteger(1, averagingWindowInSeconds);
q.setTimestamp(2, dateFrom);
q.setTimestamp(3, dateTo);
q.setInteger(4, sensorId);
q.setInteger(5, averagingWindowInSeconds);
q.setInteger(6, averagingWindowInSeconds);
q.addEntity(ScalarSampleState.class);

有人知道为什么我会收到“不是 GROUP BY 表达式”错误吗？

我从 Tom 那里学到了这种对时间间隔进行平均的方法: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4222062043865

编辑它似乎几乎可以通过以下方式解决:

select sensorid,
    trunc(sampletime,'hh24') + 
    (trunc(to_char(sampletime,'mi')))/24/60 +
    (trunc(to_char(sampletime,'ss')/?)*?)/24/60/60 as sampletime, 
    avg(correctedvalue),
    max(qualityflag)
from scalarsample
where sampletime between ? and ?
    and sensorid = ?
group by sensorid, sampletime
order by sampletime

注意:我使用 sampletime 重命名了列，这是表中其中一个列的名称。它不适用于名称“tspan”而不是 sampletime:

ORA-00904: "TSPAN": invalid identifier
00904. 00000 -  "%s: invalid identifier"

当我将新列命名为 sampletime，并在 GROUP BY by 子句中使用 sampletime 时，错误消失了，查询在 SQLDeveloper 中运行完美。不幸的是，当从 Java 运行时，它为每个采样时间返回了几个相同的行。呜呜……

解决方案:下面选择的解决方案起作用了——我去掉了字符串周围的引号和加号，以使其在此处更具可读性:

SELECT
    sensorid, 
    TRUNC( sampletime, hh24) +
    (TRUNC(to_char(sampletime,'mi')))/24/60 +
    (TRUNC(to_char(sampletime,'ss')//averagingWindowInSeconds )*averagingWindowInSeconds)/24/60/60 as sampletime,
    AVG( correctedvalue) as correctedvalue, 
    MAX(qualityflag)
FROM scalarsample
WHERE 
    sampletime BETWEEN ? AND ?
    AND sensorid = ?
GROUP BY sensorid,
    TRUNC( sampletime, hh24) +
    (TRUNC(to_char(sampletime,'mi')))/24/60 +
    (TRUNC(to_char(sampletime,'ss')//averagingWindowInSeconds 
ORDER BY sampletime ";  

Answer 1

看起来 JDBC 驱动程序正在为每个“?”创建一个唯一的绑定(bind)变量名称。不幸的是，在 Oracle 中，group by 子句必须逐个字符与 select 子句匹配，因此，事实并非如此。我在 Oracle 中用您的数据模拟了一个表，并使用动态 SQL 和绑定(bind)变量运行了一些测试查询。首先，使用顺序命名的绑定(bind)变量:

SQL> l
  1  declare
  2  cur sys_refcursor;
  3  begin
  4  open cur for 'select sensorid, ' ||
  5  '    trunc(sampletime,''hh24'') +  ' ||
  6  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
  7  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b2)/24/60/60 as tspan,  ' ||
  8  '    avg(correctedvalue), ' ||
  9  '    max(qualityflag) ' ||
 10  'from scalarsample ' ||
 11  'where sampletime between DATE ''2010-10-27'' and DATE ''2010-10-28'' ' ||
 12  '    and sensorid = 4472 ' ||
 13  'group by sensorid,  ' ||
 14  '    trunc(sampletime,''hh24'') +  ' ||
 15  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
 16  '    (trunc(to_char(sampletime,''ss'')/:b3)*:b4)/24/60/60 ' ||
 17  'order by tspan'
 18  using 15, 15, 15, 15;
 19  close cur;
 20* end;

结果:

SQL> @test
declare
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 4

其次，使用相同命名的绑定(bind)变量:

SQL> l
  1  declare
  2  cur sys_refcursor;
  3  begin
  4  open cur for 'select sensorid, ' ||
  5  '    trunc(sampletime,''hh24'') +  ' ||
  6  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
  7  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b1)/24/60/60 as tspan,  ' ||
  8  '    avg(correctedvalue), ' ||
  9  '    max(qualityflag) ' ||
 10  'from scalarsample ' ||
 11  'where sampletime between DATE ''2010-10-27'' and DATE ''2010-10-28'' ' ||
 12  '    and sensorid = 4472 ' ||
 13  'group by sensorid,  ' ||
 14  '    trunc(sampletime,''hh24'') +  ' ||
 15  '    (trunc(to_char(sampletime,''mi'')))/24/60 + ' ||
 16  '    (trunc(to_char(sampletime,''ss'')/:b1)*:b1)/24/60/60 ' ||
 17  'order by tspan'
 18  using 15, 15, 15, 15;
 19  close cur;
 20* end;

结果:

SQL> @test
PL/SQL procedure successfully completed.

因此，请尝试将您的 averagingWindowInSeconds 值连接到您的查询，而不是使用这 4 个绑定(bind)变量。