按日期范围的间隔MySQL组
我将绘制存储在MySQL数据库中的netflow数据,我需要一种有效的方式来获取相关的数据点。自纪元以来,它们的记录与日期一起存储为一个整数。我想能够像这样的东西:按日期范围的间隔MySQL组
Select SUM(bytes) from table where stime > x and stime < Y
group by (10 second intervals)
有没有办法做到这一点?或者,在Python中本地处理它会更快吗?即使是500K的排桌子?
编辑 我的错误,时间存储为一个无符号的双精度,而不是一个INT。 我正在使用GROUP BY (FLOOR(stime/I))
,其中I是所需的时间间隔。
我使用了答案和同事的建议。最终结果如下:
Select FROM_UNIXTIME(stime), bytes
from argusTable_2009_10_22
where stime > (UNIX_TIMESTAMP()-600)
group by floor(stime /10)
我也尝试了舍入解决方案,但结果不一致。
可能有
您是否尝试过以下方法?只需将tyiem列除以10,并将结果舍弃。
SELECT SUM(bytes)
FROM table
WHERE stime > x
AND stime < Y
GROUP BY ROUND(stime/10, -1)
我不知道ROUND()函数和函数调用的分组工作在MySQL中,但上面是T-SQL。
您可以使用整数除法来完成此操作。不确定的表现。
让我成为你想要的间隔秒数。
SELECT SUM(bytes), ((stime - X) DIV I) as interval
FROM table
WHERE (stime > X) and (stime < Y)
GROUP BY interval
Example, let X = 1500 and I = 10
stime = 1503 -> (1503 - 1500) DIV 10 = 0
stime = 1507 -> (1507 - 1500) DIV 10 = 0
stime = 1514 -> (1514 - 1500) DIV 10 = 1
stime = 1523 -> (1523 - 1500) DIV 10 = 2
FLOOR
组由有时会失败。例如,当您将值除以3时,它有时会将不同的时间分组为不同的时间,但当您用4除时它不会相同,尽管这两个值之间的差异远远大于它应该组合为3或4两个不同的组。更好地将它转换为无符号的地板其工作方式后:
CAST(FLOOR(UNIX_TIMESTAMP(time_field)/I) AS UNSIGNED INT)
问题:
有时GROUP BY FLOOR(UNIX_TIMESTAMP(time_field)/3)
得到较少的群体相比,GROUP BY FLOOR(UNIX_TIMESTAMP(time_field)/4)
在数学上应该是不可能的。
这在数学上非常好。假设数值是“3”和“4”,然后除以3得到1,然后除以4得出0和1.因此,在这种情况下,由/ 4分组将给出更多组。 – sth 2010-02-23 19:07:12
我做了这几个时间以前,所以我创造了一些功能(与SQL Server,但我认为这几乎是相同的):
首先我创建了一个标量函数,返回我取决于日期的ID在某个区间和日期部分(分钟,小时,天,防蛀,年):
CREATE FUNCTION [dbo].[GetIDDate]
(
@date datetime,
@part nvarchar(10),
@intervalle int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @res int
DECLARE @date_base datetime
SET @date_base = convert(datetime,'01/01/1970',103)
set @res = case @part
WHEN 'minute' THEN datediff(minute,@date_base,@date)/@intervalle
WHEN 'hour' THEN datediff(hour,@date_base,@date)/@intervalle
WHEN 'day' THEN datediff(day,@date_base,@date)/@intervalle
WHEN 'month' THEN datediff(month,@date_base,@date)/@intervalle
WHEN 'year' THEN datediff(year,@date_base,@date)/@intervalle
ELSE datediff(minute,@date_base,@date)/@intervalle END
-- Return the result of the function
RETURN @res
END
然后,我创建了一个返回我所有的ID betweend的日期范围表函数:
CREATE FUNCTION [dbo].[GetTableDate]
(
-- Add the parameters for the function here
@start_date datetime,
@end_date datetime,
@interval int,
@unite varchar(10)
)
RETURNS @res TABLE (StartDate datetime,TxtStartDate nvarchar(50),EndDate datetime,TxtEndDate nvarchar(50),IdDate int)
AS
begin
declare @current_date datetime
declare @end_date_courante datetime
declare @txt_start_date nvarchar(50)
declare @txt_end_date nvarchar(50)
set @current_date = case @unite
WHEN 'minute' THEN dateadd(minute, datediff(minute,0,@start_date),0)
WHEN 'hour' THEN dateadd(hour, datediff(hour,0,@start_date),0)
WHEN 'day' THEN dateadd(day, datediff(day,0,@start_date),0)
WHEN 'month' THEN dateadd(month, datediff(month,0,@start_date),0)
WHEN 'year' THEN dateadd(year, datediff(year,0,dateadd(year,@interval,@start_date)),0)
ELSE dateadd(minute, datediff(minute,0,@start_date),0) END
while @current_date < @end_date
begin
set @end_date_courante =
case @unite
WHEN 'minute' THEN dateadd(minute, datediff(minute,0,dateadd(minute,@interval,@current_date)),0)
WHEN 'hour' THEN dateadd(hour, datediff(hour,0,dateadd(hour,@interval,@current_date)),0)
WHEN 'day' THEN dateadd(day, datediff(day,0,dateadd(day,@interval,@current_date)),0)
WHEN 'month' THEN dateadd(month, datediff(month,0,dateadd(month,@interval,@current_date)),0)
WHEN 'year' THEN dateadd(year, datediff(year,0,dateadd(year,@interval,@current_date)),0)
ELSE dateadd(minute, datediff(minute,0,dateadd(minute,@interval,@current_date)),0) END
SET @txt_start_date = case @unite
WHEN 'minute' THEN CONVERT(VARCHAR(20), @current_date, 100)
WHEN 'hour' THEN CONVERT(VARCHAR(20), @current_date, 100)
WHEN 'day' THEN REPLACE(CONVERT(VARCHAR(11), @current_date, 106), ' ', '-')
WHEN 'month' THEN REPLACE(RIGHT(CONVERT(VARCHAR(11), @current_date, 106), 8), ' ', '-')
WHEN 'year' THEN CONVERT(VARCHAR(20), datepart(year,@current_date))
ELSE CONVERT(VARCHAR(20), @current_date, 100) END
SET @txt_end_date = case @unite
WHEN 'minute' THEN CONVERT(VARCHAR(20), @end_date_courante, 100)
WHEN 'hour' THEN CONVERT(VARCHAR(20), @end_date_courante, 100)
WHEN 'day' THEN REPLACE(CONVERT(VARCHAR(11), @end_date_courante, 106), ' ', '-')
WHEN 'month' THEN REPLACE(RIGHT(CONVERT(VARCHAR(11), @end_date_courante, 106), 8), ' ', '-')
WHEN 'year' THEN CONVERT(VARCHAR(20), datepart(year,@end_date_courante))
ELSE CONVERT(VARCHAR(20), @end_date_courante, 100) END
INSERT INTO @res (
StartDate,
EndDate,
TxtStartDate,
TxtEndDate,
IdDate) values(
@current_date,
@end_date_courante,
@txt_start_date,
@txt_end_date,
dbo.GetIDDate(@current_date,@unite,@interval)
)
set @current_date = @end_date_courante
end
return
end
因此,如果我要计算33分钟的每个间隔中添加的所有用户数:
SELECT count(id_user) , timeTable.StartDate
FROM user
INNER JOIn dbo.[GetTableDate]('1970-01-01',datedate(),33,'minute') as timeTable
ON dbo.getIDDate(user.creation_date,'minute',33) = timeTable.IDDate
GROUP BY dbo。getIDDate(user.creation_date, '分',33) ORDER BY timeTable.StartDate
:)
SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(10)) as intervals,SUM(bytes)
FROM table
WHERE where stime > x and stime < Y
group by intervals
一轮让我非常可变间隔,在10分钟内,我得到了一些间隔为小如7秒,有的大到1分钟...... – Chance 2009-10-22 17:02:01