MYSQL - 确定一行中的先前值

Question

我希望在将时间戳分为 3 分钟组后确定连续的前 2 个值；例如使用下表

Timestamp             Total
2013-10-11 00:01:00     1
2013-10-11 00:02:00     5
2013-10-11 00:03:00     6
2013-10-11 00:04:00     3
2013-10-11 00:05:00     9
2013-10-11 00:06:00     10
2013-10-11 00:07:00     12
2013-10-11 00:08:00     10
2013-10-11 00:09:00     15
2013-10-11 00:10:00     12

       .                .
       .                .

我必须将时间戳分为 3 分钟组并显示每个 3 分钟组的最大总数的代码是

SELECT 
    max (Timestamp)
     AS tstamp
     max(total)
FROM table
Group by ROUND(UNIX_TimeStamp(timestamp)/180)

给出的输出是

 tstamp               max( Total)
2013-10-11 00:01:00       1
2013-10-11 00:04:00       6
2013-10-11 00:07:00       12
2013-10-11 00:10:00       15

       .                

我希望在每个 3 分钟组中添加前 2 个值，以便找到最大值，这样我就得到了

tstamp               max( Total) Previous1  Previous2
2013-10-11 00:01:00       1          0         0
2013-10-11 00:04:00       6          5         1
2013-10-11 00:07:00       12         10        9
2013-10-11 00:10:00       15         10        12

       .                

我正在思考，一种方法是在嵌套选择中使用 max(timestamp) - INTERVAL 1 MINUTE，然后使用 max(timestamp) -INTERVAL 2 MINUTE。沿着这些思路，但我不完全确定该方法

有没有一种更简单/更干净的方法来解决这个问题，这是问题吗？

Answer 1

所以我从你拥有的开始:

select 
max(`Timestamp`) AS 'Timestamp'
,max(total) as total
, ROUND(UNIX_TimeStamp(`Timestamp`)/180) as roundBy3
FROM `time`
Group by ROUND(UNIX_TimeStamp(`Timestamp`)/180)
) summary

然后我添加了一些更多细节:

select `Timestamp`
, total
, ROUND(UNIX_TimeStamp(Timestamp)/180) as roundBy3
, @lastT3 := if(@lastT3 is null, 0, @lastT2) as saveForNext3
, @lastT2 := if(@lastT2 is null, 0, @lastT1) as saveForNext2
, @lastT1 := if(@lastT1 is null, 0, total) as saveForNext1

from `time`
, ( select @lastT1 := null
    , @lastT2 := null 
    , @lastT3 := null ) sqlvars
order by roundBy3 asc, total ASC

最后我把它们组合在一起:

select detail.Timestamp
, detail.total
, detail.saveForNext3
, detail.saveForNext2 
from (
    select 
    max(`Timestamp`) AS 'Timestamp'
    ,max(total) as total
    , ROUND(UNIX_TimeStamp(`Timestamp`)/180) as roundBy3
    FROM `time`
    Group by ROUND(UNIX_TimeStamp(`Timestamp`)/180)
    ) summary
join (
    select `Timestamp`
    , total
    , ROUND(UNIX_TimeStamp(Timestamp)/180) as roundBy3
    , @lastT3 := if(@lastT3 is null, 0, @lastT2) as saveForNext3
    , @lastT2 := if(@lastT2 is null, 0, @lastT1) as saveForNext2
    , @lastT1 := if(@lastT1 is null, 0, total) as saveForNext1

    from `time`
    , ( select @lastT1 := null
        , @lastT2 := null 
        , @lastT3 := null ) sqlvars
    order by roundBy3 asc, total ASC 
    ) detail
on summary.roundBy3 = detail.roundBy3 and summary.total = detail.total
group by ROUND(UNIX_TimeStamp(summary.Timestamp)/180);