I'm working on a MySQL database system that will allow my company to process our journey time survey data in a fraction of the time it currently takes. I have got the following MySQL command that does the calculation:
我正在开发一个MySQL数据库系统,它允许我的公司在目前所用时间的一小部分时间内处理我们的旅程时间调查数据。我有以下MySQL命令进行计算:
select anpr_1_unique.date as "Date",
anpr_1_unique.NETBIOSNAME as "ID for site A",
anpr_1_unique.time as "Timestamp at site A",
anpr_3_unique.NETBIOSNAME as "ID for site B",
anpr_3_unique.time as "Timestamp at site B",
anpr_1_unique.plate as "Plate",
if (timediff(anpr_3_unique.time,anpr_1_unique.time) like "%-%", null,timediff(anpr_3_unique.time,anpr_1_unique.time)) as "Journey time in direction 1",
if (timediff(anpr_1_unique.time,anpr_3_unique.time) like "%-%", null,timediff(anpr_1_unique.time,anpr_3_unique.time)) as "Journey time in direction 1",
if (timediff(anpr_1_unique.time,anpr_3_unique.time) like "%-%", "A->B","B->A") as "Direction of travel"
from anpr_1_unique inner join anpr_3_unique on anpr_1_unique.plate=anpr_3_unique.plate
where anpr_1_unique.date = "2013-02-26" and anpr_3_unique.date="2013-02-26" and anpr_1_unique.time like "%06:%%:%%%" order by anpr_1_unique.time
which produces a table in the following format:
它以下列格式生成一个表:
+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
| Date | ID for site A | Timestamp at site A | ID for site B | Timestamp at site B | Plate | Journey time in direction 1 | Journey time in direction 1 | Direction of travel |
+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
| 2013-02-26 | Farnham N 1 | 06:50:52 | Farnham S | 06:54:32 | LS60JAO | 00:03:40 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:51:33 | Farnham S | 06:53:32 | FH53WGW | 00:01:59 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:51:51 | Farnham S | 06:54:23 | V987USD | 00:02:32 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:52:17 | Farnham S | 06:54:28 | BK61RNY | 00:02:11 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:52:30 | Farnham S | 06:54:35 | WU59SXP | 00:02:05 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:53:02 | Farnham S | 12:23:33 | NA08UKV | 05:30:31 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:53:44 | Farnham S | 06:56:09 | KC04CTF | 00:02:25 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:53:46 | Farnham S | 06:56:11 | LL58YAJ | 00:02:25 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:54:41 | Farnham S | 06:57:44 | AP57CWE | 00:03:03 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:54:46 | Farnham S | 14:55:29 | EU55LRF | 08:00:43 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:55:13 | Farnham S | 06:58:08 | AJ60KVK | 00:02:55 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:55:19 | Farnham S | 06:58:44 | T96ALO | 00:03:25 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:55:57 | Farnham S | 06:58:46 | F604WNV | 00:02:49 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:56:22 | Farnham S | 06:58:50 | S905AAP | 00:02:28 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:56:52 | Farnham S | 06:59:35 | LO08SKV | 00:02:43 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:56:55 | Farnham S | 06:59:37 | KT53TNK | 00:02:42 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:57:45 | Farnham S | 07:00:33 | LC60EOR | 00:02:48 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:58:44 | Farnham S | 07:06:15 | S278VNA | 00:07:31 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:58:49 | Farnham S | 07:00:46 | X906GVT | 00:01:57 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:59:15 | Farnham S | 07:01:35 | YS11AWP | 00:02:20 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:59:44 | Farnham S | 07:01:46 | LP12NWA | 00:02:02 | NULL | A->B |
| 2013-02-26 | Farnham N 1 | 06:59:59 | Farnham S | 07:02:29 | RJ11BUA | 00:02:30 | NULL | A->B |
+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
22 rows in set (0.09 sec)
This is just a 10-minute sample of the results. I am happy with this, and how it has turned out, however I want to be able to group these results into aggregation periods of 5,10 or 15 minutes
这只是结果的10分钟样本。我对此感到满意,结果如何,但我希望能够将这些结果分组为5,10或15分钟的聚合期
I have looked around for other potential solutions on this site and others, most notably advising the use of GROUP BY UNIX_TIMESTAMP(<time_stamp>) DIV <time> to produce the aggregation. I have tried this, and it didn't work with this query.
我在这个网站和其他网站上寻找其他可能的解决方案,最值得注意的是建议使用GROUP BY UNIX_TIMESTAMP(
What can I do to get this data aggregated efficiently? can it be done with the query i have produced, or does this need to be rewritten?
我该怎么做才能有效地汇总这些数据?可以用我生成的查询完成,还是需要重写?
Update
I managed to get the calculation to do roughly what i wanted with the following SQL code:
我设法通过以下SQL代码大致完成了我想要的计算:
select
anpr_1_unique.date as "Date",
str_to_date(concat(date_format(anpr_1_unique.time, '%H'),':',(floor(date_format(anpr_1_unique.time, '%i')/30)*30), ':00'), '%H:%i:%s') as starttime,
anpr_1_unique.NETBIOSNAME as "ID for site A",
anpr_3_unique.NETBIOSNAME as "ID for site B",
if (avg(timediff(anpr_3_unique.time,anpr_1_unique.time) like "%-%"), null,time(avg(timediff(anpr_3_unique.time,anpr_1_unique.time)))) as "Journey time in direction A->B"
from anpr_1_unique
inner join anpr_3_unique on anpr_1_unique.plate=anpr_3_unique.plate
where anpr_1_unique.date = "2013-02-26" and anpr_3_unique.date="2013-02-26"
and timediff(anpr_3_unique.time,anpr_1_unique.time) between "00:00:00" and "00:30:00"
GROUP BY starttime;
show warnings;
which produces data like this:
它产生这样的数据:
+------------+-----------+---------------+---------------+---------------------------------+
| Date | starttime | ID for site A | ID for site B | Journey time in direction A->B |
+------------+-----------+---------------+---------------+---------------------------------+
| 2013-02-26 | 06:30:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 07:00:00 | Farnham N 1 | Farnham S | 00:03:46.3276 |
| 2013-02-26 | 07:30:00 | Farnham N 1 | Farnham S | 00:04:51.5588 |
| 2013-02-26 | 08:00:00 | Farnham N 1 | Farnham S | 00:11:10.8462 |
| 2013-02-26 | 08:30:00 | Farnham N 1 | Farnham S | 00:11:36.6410 |
| 2013-02-26 | 09:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 09:30:00 | Farnham N 1 | Farnham S | 00:05:39.0714 |
| 2013-02-26 | 10:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 10:30:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 11:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 11:30:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 12:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 12:30:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 13:00:00 | Farnham N 1 | Farnham S | 00:05:17.1250 |
| 2013-02-26 | 13:30:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 14:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 14:30:00 | Farnham N 1 | Farnham S | 00:05:06.8864 |
| 2013-02-26 | 15:00:00 | Farnham N 1 | Farnham S | 00:09:07.2308 |
| 2013-02-26 | 15:30:00 | Farnham N 1 | Farnham S | 00:13:02.2558 |
| 2013-02-26 | 16:00:00 | Farnham N 1 | Farnham S | NULL |
| 2013-02-26 | 16:30:00 | Farnham N 1 | Farnham S | 00:26:45.7143 |
| 2013-02-26 | 17:30:00 | Farnham N 1 | Farnham S | 00:28:04.0000 |
| 2013-02-26 | 18:00:00 | Farnham N 1 | Farnham S | 00:22:56.0667 |
| 2013-02-26 | 18:30:00 | Farnham N 1 | Farnham S | NULL |
+------------+-----------+---------------+---------------+---------------------------------+
however, as shown, this generates a number of NULL results where there should be data. the warnings show an equal number of " | Warning | 1292 | Truncated incorrect time value: '(number)' | " messages. I had a similar problem when working out the calculation, which i solved by using the timediff() command instead of manually calculating. I'm not sure what i can do with this now to "de-null" these results.
但是,如图所示,这会产生许多NULL结果,其中应该有数据。警告显示相同数量的“|警告| 1292 |截断的错误时间值:'(数字)'|”消息。在计算时我遇到了类似的问题,我使用timediff()命令而不是手动计算解决了这个问题。我现在不确定我能做些什么来“去除”这些结果。
any suggestions on what i could do here, now?
关于我现在可以做什么的任何建议,现在?
1 个解决方案
#1
1
you need to round your division result. Do this with floor, ceil or round
你需要围绕你的分裂结果。用地板,天花板或圆形做
GROUP BY floor(UNIX_TIMESTAMP(<time_stamp>)/(seconds*minutes))
GROUP BY floor( UNIX_TIMESTAMP(<time_stamp>)/(60*15) ) - group into 15 minute intervals
specifically;
GROUP BY floor( UNIX_TIMESTAMP(CONCAT(anpr_1_unique.date, ' ', npr_1_unique.time))/(60*15) )
#1
1
you need to round your division result. Do this with floor, ceil or round
你需要围绕你的分裂结果。用地板,天花板或圆形做
GROUP BY floor(UNIX_TIMESTAMP(<time_stamp>)/(seconds*minutes))
GROUP BY floor( UNIX_TIMESTAMP(<time_stamp>)/(60*15) ) - group into 15 minute intervals
specifically;
GROUP BY floor( UNIX_TIMESTAMP(CONCAT(anpr_1_unique.date, ' ', npr_1_unique.time))/(60*15) )