java - SQLite - max(列)的棘手查询

Question

我已经使用 SQL Fiddle 构建了一个架构:

SQL Fiddle - Schema

我们在测试表中有这些列:

1. id [int] 作为主键(未使用 -> 不重要)
2. end [int] - 如果将新流写入表中，则除了最后一次之外的所有流的值为“0”，最后一个流的值为“1”。这是指输入流到这里就完成了。
3. time_abs [int] - 绝对时间(例如以分钟为单位)。
4. r_m [double] - 一段时间内的质量速率总和
5. T_r [双] - 无关紧要
6. 类型 [字符串] - 这里也不重要
7. x0 [字符串] - 出发(例如水从哪里来？)
8. x1 [字符串] - 目的地(例如水流向哪里？)

正如您在 SQL Fiddle Schema 中看到的那样，我们在特定位置和特定时间查询每个质量，如下所示:

SELECT
(SELECT (SELECT total(r_m) 
         FROM testtable 
         WHERE time_abs=11 AND end=0 AND x1='vessel2') + 
        (SELECT total(r_m) 
         FROM testtable 
         WHERE end=1 AND time_abs <=11 AND x1='vessel2')
)
-
(SELECT (SELECT total(r_m) 
         FROM testtable 
         WHERE time_abs=11 AND end=0 AND x0='vessel2') + 
        (SELECT total(r_m) 
         FROM testtable 
          WHERE end=1 AND time_abs <=11 AND x0='vessel2')
)

效果又好又快。

但我们现在要查询的是在某个时间范围下 r_m的 最大值。

例如伪代码:

SELECT max(total(r_m)) 
WHERE time_abs BETWEEN 1 AND 30 & SELECT time_abs WHERE r_m=max ...

因此，这个伪查询的结果是 (123, 13-24) (max(总质量)，总质量=max的时间跨度)(在 SQL Fiddle 架构中手动检查)。

有什么想法吗？

Answer 1

下面的查询显示了容器 2 中 5 到 26 秒之间的液位:

select  times.time_abs
,       sum(
        case when x1 = 'vessel2' and ([end] = 1 or times.time_abs = tt.time_abs)
             then r_m else 0 end - 
        case when x0 = 'vessel2' and ([end] = 1 or times.time_abs = tt.time_abs) 
             then r_m else 0 end
        ) as lvl
from    (
        select  distinct time_abs
        from    testtable
        where   time_abs between 5 and 26
        ) times
join    testtable tt
on      tt.time_abs <= times.time_abs 
        and 'vessel2' in (tt.x0, tt.x1)
group by
        times.time_abs

要仅显示最大值，您可以:

select  max(lvl) 
from    (
        ...query from above...
        ) as SubQueryAlias

Live example at SQL Fiddle.