MySQL Grouping Problem

raydar2000 · March 14, 2007

Hi All,

i am trying to generate a report that shows idle time for some vehicles... (i can do all the calcs etc but......

I am not sure if this is possible but i need to group this data by street, however vehicles often visit the same street more than once in a day and therefore cannot group those two visits together. I need to somehow group them together while the street is the same, when it changes... its a new group and so on.

DATA

This is just a sample of the data, it is produced by a query that finds records where the speed is zero.

Vehicle Date Time SpeedInst Suburb Street Latitude Longitude SpeedMax SpeedAv CourseInst CourseAv

1000 9/2/2007 06:44:44 0 Punchbowl LUMEAH AV 33.56.184S 151.03.333E 0 0 106 106

1000 9/2/2007 06:47:46 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 06:50:48 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106

1000 9/2/2007 06:53:50 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106

1000 9/2/2007 06:56:50 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 06:59:52 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:02:54 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:05:56 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:08:58 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:11:58 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:12:14 0 Punchbowl LUMEAH AV 33.56.184S 151.03.334E 0 0 106 106

1000 9/2/2007 07:15:20 0 Punchbowl LUMEAH AV 33.56.183S 151.03.334E 0 0 106 106

1000 9/2/2007 07:23:24 0 Riverwood KEATS AV 33.56.959S 151.03.158E 0 0 196 196

1000 9/2/2007 07:23:34 0 Riverwood KEATS AV 33.56.959S 151.03.158E 0 0 196 196

1000 9/2/2007 08:20:10 0 Bundeena HORDERN S L 34.05.037S 151.08.710E 0 0 278 278

1000 9/2/2007 08:30:28 0 Bundeena BRIGHTON ST 34.05.084S 151.09.000E 0 0 48 48

1000 9/2/2007 08:36:38 0 Bundeena BRIGHTON ST 34.05.084S 151.08.999E 0 0 243 243

1000 9/2/2007 08:39:38 0 Bundeena BRIGHTON ST 34.05.084S 151.08.999E 0 0 243 243

1000 9/2/2007 08:42:40 0 Bundeena BRIGHTON ST 34.05.084S 151.08.998E 0 0 243 243

1000 9/2/2007 13:08:56 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:11:56 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:14:58 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:18:00 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:21:02 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:24:02 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:27:04 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:30:06 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:33:08 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:35:20 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 13:35:30 0 Bundeena BRIGHTON ST 34.05.083S 151.08.998E 0 0 243 243

1000 9/2/2007 14:27:00 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286

1000 9/2/2007 14:30:02 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286

1000 9/2/2007 14:33:04 0 Riverwood NETTLETON AV 33.57.057S 151.03.190E 0 0 286 286

1000 9/2/2007 14:36:00 0 Riverwood NETTLETON AV 33.57.058S 151.03.190E 0 0 286 286

1000 9/2/2007 14:51:46 0 Punchbowl LUMEAH AV 33.56.205S 151.03.421E 0 0 273 273

1000 9/2/2007 14:54:34 0 Punchbowl LUMEAH AV 33.56.204S 151.03.422E 0 0 273 273

1000 9/2/2007 14:56:14 0 Punchbowl LUMEAH AV 33.56.177S 151.03.355E 0 0 278 278

1000 9/2/2007 14:58:08 0 Punchbowl LUMEAH AV 33.56.191S 151.03.341E 0 0 278 278

1000 9/2/2007 14:58:12 0 Punchbowl LUMEAH AV 33.56.190S 151.03.341E 0 0 278 278

1000 9/2/2007 15:41:02 0 Punchbowl LUMEAH AV 33.56.190S 151.03.344E 0 0 201 201

1000 9/2/2007 15:41:08 0 Punchbowl LUMEAH AV 33.56.191S 151.03.344E 0 0 201 201

1000 9/2/2007 16:24:30 0 Pyrmont PIRRAMA RD 33.52.156S 151.11.771E 0 0 15 15

1000 9/2/2007 16:26:54 0 Pyrmont PIRRAMA RD 33.52.157S 151.11.763E 0 0 15 15

1000 9/2/2007 16:26:58 0 Pyrmont PIRRAMA RD 33.52.158S 151.11.763E 0 0 15 15

1000 9/2/2007 16:33:44 0 Pyrmont PIRRAMA RD 33.52.142S 151.11.766E 0 0 31 31

1000 9/2/2007 16:33:48 0 Pyrmont PIRRAMA RD 33.52.143S 151.11.766E 0 0 31 31

As you can see... vehicle visits LUMEAH AV twice in one day so i do't want to group these to visits together...

Thanks

Ray

artacus · March 14, 2007

Yeah, were you the same one that asked about this last week? If so I told you it would be a problem then.

I'm just shooting from the hip here, but the first thing that comes to mind is to add a stop_number field. So for each vehicle, each day the first stop would be #1. If it is stopped in the same position the next time it is checked then it would be recorded still at stop #1, if it had moved that would be stop #2.

raydar2000 · March 14, 2007

Yes! Last week i sorted out my issue... this is about accuracy! I can't change the DB structure.

Thanks for the help.

artacus · March 14, 2007

I can't change the DB structure.

Then tell your drivers not to park in the same spot.

I don't know then. You may have to pull it back into PHP and calc it there. But I really hate doing that. Let me think about this some more.

raydar2000 · March 15, 2007

I tried that!

made me less popular than when we introduced the actual tracking!

Thank you very much.

artacus · March 15, 2007

LOL. I wasn't serious. That's funny though.

So how come you can't edit the db structure?

If its not YOUR database, then I would create another mysql database and import the data into that one. When you do that, change is so you have both the start_time and end_time for each stop. That way you will only have 1 row for each stop. It will make your life easier.

btherl · March 15, 2007

Hmm.. it may be possible, but very messy.

Basically, you can do what you want to do by distinguishing rows based on the condition "What was the most recent row which does not match this location?". That translates to a subquery selecting the first row with date < this row's date, and location != this row's location.

Does that make sense? It half makes sense to me.

But the easiest way by far is to select all the rows and process it in php. Then it's simple.

raydar2000 · March 15, 2007

Hey!

artacus - The problem is that is data is coming in 300 times a day from 25 vehicles... So i need to run the reports all the time. Means i would have to do that each time i wanted to see a report.

btherl - I don't mean to be too dependant but this is my query...

SELECT *,

timediff(MAX(`vehiclehistory`.`Time`) ,MIN(`vehiclehistory`.`Time`) ) as idle

FROM

`vehiclehistory`

WHERE

`vehiclehistory`.`Vehicle` = "1000"

AND

`vehiclehistory`.`Date`= "2007-02-09"

AND

`vehiclehistory`.`SpeedInst` < "1"

GROUP BY <<<MY PROBLEM LIES HERE>>>

Can you point me in the right direction a little more??

I did it in PHP... not really simple! (Not for me anyway) 200 lines of code later, just seemed over the top. I guess i just thought that it would be easier to deal with the data if the right data was coming out in the first place.

Thank you both very much!

Ray

btherl · March 15, 2007

Ok you're right, it's not simple in php But it's simpler in php than sql. For me anyway, being an experienced programmer.

Hmm.. I am a bit stuck on how to group by a subquery, since I'm not too familiar with mysql. I'm a postgres user, which handles a lot of things differently. The idea I have is like this:

GROUP BY (SELECT max(time) FROM vehiclehistory AS inner WHERE inner.time < time AND inner.location != location) AS group_time

But I don't know if that is valid syntax. Probably not. The idea is that the subquery gives you a value which is unique for each vehicle stop, even when there is more than one stop at the same location in that day. But the value will be the same for every entry within that stop.

For doing it in php, the general structure is

$last_location = null;
$locations = array();
$start_time = null;
$last_time = null;
while ($row = mysql_fetch_assoc($res)) {
  if ($row['location'] !== $last_location && $last_location !== null) {
    # We have reached a new location.  Store the old data and prepare the new
    $locations[] = array(
      'location' => $last_location,
      'time' => $last_time - $start_time,
    );
    $last_location = $row['location'];
    $last_time = $start_time = $row['time'];
  } else {
    # Still at same stop.  Just update the time only.  No other work to do.
    $last_time = $row['time'];
  }
}

# loop is finished.  Record the final stop (unless there were 0 stops that day)
if ($last_location !== null) {
  $locations[] = array(
    'location' => $last_location,
    'time' => $last_time - $start_time,
  );
}

The time arithmetic won't work in there.. you will need a function that calculates differences between times. Or you can get mysql to give you a time in seconds instead of as hours:minutes:seconds.

raydar2000 · March 15, 2007

Thanks!!

I will try both tomorrow! I appreciate your help!

I will post back with how things went!!!!

Ray

fenway · March 15, 2007

You should be able to do this in SQL with user variables too, but see what you come up with.

shoz · March 15, 2007

If you're using MYSQL 5.0 you should also be able to use a Stored Procedure. The logic should be similar to what you'd do in PHP. If the performance of the two turned out to be similar, I'd use PHP.

If you have concerns about the speed of doing this, keep in mind that you can limit how many entries you handle at a time by perhaps displaying per month statistics.

artacus · March 15, 2007

Well you've got several options. Personally, I like to handle as much of the data processing on the database as I can. So if it was me, I'd be ripping the data out of their database and importing it to mine... every 5 minutes if that's what it takes.

But it sounds like you're more inclined to do it on their database so here's a quick synopsis on how to do it in php. You'd have to focus on a single vehicle & day at a time. You're not going to be able to use any group bys. So you'll select all of your positions for that vehicle/day where the speed is 0, ordered by time of day.

Then in php, your while loop will look something like so:

<?php
... query stuff
$stops = array();
$i = 0;
while($row = mysql_fetch_assoc($result)) {
    if($row['loc'] == $stops[$i]['loc']) {
       //same stop
       $stops[$i]['end_time'] = $row['end_time'];
    } else {
       //different stop
       $stops[++$i] = $row;   
    }
}
print_r($stops);

raydar2000 · March 17, 2007

Hey!

Thank you all for your replys!

i really appreciate all of your help! I will try the PHP suggestions from btherl and artacus if i can work it out! But leave it with me, i have been far to dependant already and should do some work! Might take a while tho... not real good at this but i will post back with how i went. . The SQL stuff i think my be too hard for me!!!

Sign In

MySQL Grouping Problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information