按时间间隔分组，包括下一个开始？

I'm trying to build a query that groups my data by a timed interval (i.e. 30 minutes) and then calculate the difference between the first and last row in each group.

我正在尝试构建一个查询，按时间间隔（即30分钟）对数据进行分组，然后计算每个组中第一行和最后一行之间的差异。

select (max(v) - min(v)) as x, from_unixtime(FLOOR(UNIX_TIMESTAMP(d)/(30*60))*(30*60)) as timeKey from y group by timeKey

选择（max（v） - min（v））为x，from_unixtime（FLOOR（UNIX_TIMESTAMP（d）/（30 * 60））*（30 * 60））作为timeKey从y group by timeKey

The above correctly returns my data in 30 minute chunks however I need to somehow tweak it to include everything between 12:00 and 12:30 rather than 12:00 and 12:29.

以上正确地以30分钟的方式返回我的数据然而我需要以某种方式调整它以包括在12:00和12:30而不是12:00和12:29之间的所有内容。

I had attempted to use the following however max can't be used when assigning a value.

我曾尝试使用以下内容，但在分配值时无法使用max。

select max(v) - @lastV, from_unixtime(FLOOR(UNIX_TIMESTAMP(d)/(30*60))*(30*60)) as timeKey, @lastv := v from `test`, (select @lastV:=0) lastV group by timeKey

选择max（v） - @lastV，from_unixtime（FLOOR（UNIX_TIMESTAMP（d）/（30 * 60））*（30 * 60））作为timeKey，@ shapev：=来自`test`的v，（选择@lastV：= 0）lastV group by timeKey

http://sqlfiddle.com/#!9/e8d74/4

I've created the above SQL Fiddle with some data that can be used as an example.

我创建了上面的SQL Fiddle，其中包含一些可用作示例的数据。

Does anyone have any suggestions?

有没有人有什么建议？

1 个解决方案

#1

A potential solution that I managed to come up with this morning:

我今天早上想出的一个潜在解决方案：

select *, IF(@lastV, @lastV - minV, 0) as `usage`, @lastV := minV from ( select min(v) as minV, max(v) as maxV, from_unixtime(FLOOR(UNIX_TIMESTAMP(d)/(30*60))*(30*60)) as timeKey from `test` group by timeKey order by timeKey desc ) items, (select @lastV:=0) lastV

select *，IF（@lastV，@ originalV - minV，0）为`usage`，@ popV：= minV from（选择min（v）为minV，max（v）为maxV，from_unixtime（FLOOR（UNIX_TIMESTAMP（d）） /（30 * 60））*（30 * 60））作为timeKey从`test` group by timeKey order by timeKey desc）items，（select @lastV：= 0）lastV

http://sqlfiddle.com/#!9/e8d74/18/0

It unfortunately only works when sorting descending and produces a redundant row (first row has nothing to compare to) however based on my tests, this row isn't required and can be ignored when reading the data.

遗憾的是，它仅在排序降序时生效并产生冗余行（第一行无法比较）但是根据我的测试，此行不是必需的，在读取数据时可以忽略。

Would be useful if anyone had any other solutions?

如果有人有任何其他解决方案会有用吗？

#1