如何在MySQL中创建记录之间的平均时间差? [英] How do you create an average time difference between records in MySQL?

问题描述

我希望在设备连接之间建立平均时间差.

I desire to create an average time difference between device connections.

由于有了ajreal，我现在有了这个查询.我现在需要做的是创建和发布每条设备记录之间的平均时间.

I have this query now thanks to ajreal. What I need to do now is create and average time between each device record being posted.

select device, count(*) as cnt, max(time)
from database.table
group by device
having cnt>1
order by device;

我一直在玩TIMEDIFF，但我需要所有设备记录之间的平均值，而不仅仅是最小值和最大值.

I have been playing around with TIMEDIFF but I need the average between all device records not just the min and max.

表的结构如下:

ID, device(string),
data1(int),data2(int), 
time(timestamp), 
data3(int),
data4(int)

我如何实现我的目标?

这是表的定义.

字段类型空键默认额外

id int(11)NO PRI NULL auto_increment 设备varchar(15)否MUL NULL
data1小数(5,2)是NULL
data2十进制(5,2)是NULL
time时间戳记是NULL
data3十进制(3,0)是NULL
data4小数(3,0)是NULL

id int(11) NO PRI NULL auto_increment device varchar(15) NO MUL NULL
data1 decimal(5,2) YES NULL
data2 decimal(5,2) YES NULL
time timestamp YES NULL
data3 decimal(3,0) YES NULL
data4 decimal(3,0) YES NULL

感谢您查看我的问题@克里斯·亨利(Chris Henry)！

Thanks for looking at my problem @Chris Henry!

推荐答案

您必须将时间戳转换为数字才能四舍五入.而当您拥有平均时间戳时，请将其转换回日期.

You have to convert your timestamp to a number in order to be able to round it. And when you have the average timestamp, convert it back to a date.

select device, count(*) as cnt, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(time)), '%i:%s')
from database.table
group by device
having cnt>1
order by device;

---

注意:如果您考虑负数时间，那么您的平均值是显而易见的！如果您不这样做，那么最好使用脚本语言(php，c#，ruby ...)进行计算

---

Note: If you consider negative times, your average is obvious! If you don't then it is a better idea to calculate it with a scripting language (php, c#, ruby...)

average := avg(ts1–ts2, ts2-ts3, ts3-ts4)
= (ts1–ts2 + ts2-ts3 + ts3-ts4)/3
= (ts4-ts1)/3

概括:

average = (last entry - first entry) / (number of entries - 1)

这篇关于如何在MySQL中创建记录之间的平均时间差?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！