给定一个带有时间戳列的表,例如:
timestamp | id | value
--------------------------------------
2001-01-01 00:00:00 | 1 | 3
2001-01-01 00:00:00 | 2 | 5
--------------------------------------
2001-01-02 00:00:00 | 1 | 6
2001-01-02 00:00:00 | 2 | 10
2001-01-02 00:00:00 | 3 | 7
--------------------------------------
2001-01-03 00:00:00 | 3 | 14
2001-01-03 00:00:00 | 2 | 15
--------------------------------------
2001-01-03 00:00:00 | 1 | 9
2001-01-03 00:00:00 | 2 | 20
和给定的聚合级别,例如2天,我想聚合(求和)结果:
(1)给定agg级别的移动窗口,对于上面的示例:2001-01-01到2001-01-02,2001-01-02到2001-01-03,2001-01-03到2001-01-04,这将导致:
timestamp_1 | timestamp_2 | id | agg_value
-----------------------------------------------------------
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 1 | 9 (=3+6)
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 2 | 15 (=5+10)
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 3 | 7 (=7)
-----------------------------------------------------------
2001-01-02 00:00:00 | 2001-01-03 00:00:00 | 1 | 6 (=6)
2001-01-02 00:00:00 | 2001-01-03 00:00:00 | 2 | 25 (=10+15)
2001-01-02 00:00:00 | 2001-01-03 00:00:00 | 3 | 21 (=7+14)
-----------------------------------------------------------
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 1 | 9 (=9)
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 2 | 35 (=15+20)
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 3 | 14 (=14)
(2)不重叠分割到给定范围,对于上面的例子:2001-01-01到2001-01-02,2001-01-03到2001-01-04,这将导致:
timestamp_1 | timestamp_2 | id | agg_value
-----------------------------------------------------------
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 1 | 9 (=3+6)
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 2 | 15 (=5+10)
2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 3 | 7 (=7)
-----------------------------------------------------------
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 1 | 9 (=9)
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 2 | 35 (=15+20)
2001-01-03 00:00:00 | 2001-01-04 00:00:00 | 3 | 14 (=14)
(基本上和(1)没有重叠)
谢谢!
编辑:添加解决方案
我至少对(1)有一个解决方案:
SELECT t1.timestamp AS timestamp1,
MAX(t2.timestamp) AS timestamp2, t1.id,
SUM(t2.value) AS agg_value
FROM my_table t1
LEFT JOIN my_table t2 ON
(t2.timestamp >= t1.timestamp AND
t2.timestamp <= ADDDATE(t1.timestamp,INTERVAL 2 DAY) AND
t2.id = t1.id)
GROUP BY t1.timestamp, t1.id
(2)的解决方案可能只是过滤到上述的一个子集。
这将通过获取日期部分和日期差并按天数差加上ID分组每X天。这将得到#2解决方案
select
CEILING( datediff( date( now() ), date( myTimeStamp )) / 2 ) DaysDiff,
ID,
min( date( myTimeStamp )) as FirstDateInGroup,
max( date( myTimeStamp )) as LastDateInGroup,
sum( value ) as SumVal
FROM TimeSample
group by DaysDiff, ID
order by FirstDateInGroup, ID
EDIT ---- PER COMMENTS
你的样品展示了如何处理2天…这个也是。"now()"只是对数据进行分组的基线。如果您希望它按年分解,那么我只需基于YEAR(YourDateColumn)作为组进行查询。如果你想要30天,就除以30。每月,我将分别按年(YourDateColumn)和月(YourDateColumn)分组。通过固定的"now()"范围,它除了返回一个数字作为起点外什么也不做。如果你的数据是2年的,那么日期差就是365天* 2年= 730天……除以2,你的背是365。您可以使用任何where子句来进一步限制您感兴趣的时间段…
where myTimeStamp between '2011-01-01' and '2011-06-30'以获取今年的前6个月…这将导致您的DaysDiff分组为208天/2 = 104天。
所以,如果你有一些其他的基线值,你关心你的分组,你可以把now()改成类似'2011-01-01'的东西,它将基于2011年1月1日的基础计算。其中,这只会将DaysDiff计算为负值直到0,然后返回正数。