mysql query that groups the distances travelled each day

Asked

Viewed 46 times

0

Hello, I started a query from a mysql database where the distance of each commute of a driver is saved along with the time:

DATE_FORMAT(l.dateGenerated,'%d/%m/%Y %H:%i') AS Day ,
d.name AS Conductor,
ROUND((l.odometer/1000),3) AS Route

from location l 
left join device d on (d.id = l.deviceId)

| Day              | Conductor | Routes |
|------------------|-----------|--------|
| 05/07/2019 19:12 | PAULO     | 0      |
| 05/07/2019 19:14 | MARCOS    | 0      |
| 05/07/2019 19:14 | MARCOS    | 0,488  |
| 05/07/2019 19:16 | MARCOS    | 0,651  |
| 05/07/2019 19:16 | PAULO     | 0,533  |
| 05/07/2019 19:16 | MARCOS    | 0,703  |
| 05/07/2019 19:16 | PAULO     | 0,571  |
| 05/07/2019 19:17 | MARCOS    | 0,755  |
| 05/07/2019 19:17 | PAULO     | 0,602  |
| 05/07/2019 19:20 | PAULO     | 0,757  |
| 06/07/2019 08:10 | MARCOS    | 0,807  |
| 06/07/2019 08:11 | MARCOS    | 0,911  |
| 06/07/2019 08:12 | MARCOS    | 1,015  |
| 06/07/2019 08:13 | MARCOS    | 1,067  |
| 06/07/2019 08:15 | MARCOS    | 1,223  |
| 06/07/2019 08:16 | PAULO     | 0,791  |
| 06/07/2019 08:16 | PAULO     | 0,833  |
| 06/07/2019 08:19 | PAULO     | 1,01   |
| 06/07/2019 08:20 | PAULO     | 1,063  |
| 06/07/2019 19:16 | PAULO     | 1,114  |
| 06/07/2019 19:17 | PAULO     | 1,165  |

But what I need exactly and am not able to calculate is the daily distance traveled by each driver, I imagined taking the last measurement of the odometer of each day and subtract by the first measurement, to generate something like this:

| Day    | Conductor | Traveled Day |
|--------|-----------|--------------|
| 05/jul | Paulo     | 0,757        |
| 05/jul | Marcos    | 0,755        |
| 06/jul | Paulo     | 0,408        |
| 06/jul | Marcos    | 0,468        |

Some "Hero" could help me?

mysql sql database

  • If you explain how you arrived at the results presented in Traveled Day maybe we can help you.,

  • Getting Traveled Day is my goal here, the above example is the desired that I could not bring.

  • Just add up all the routes of each day to each conductor? I imagine you can explain the calculation that you expect to be done to achieve the desired result, otherwise it will be impossible to guess and try to help you.

  • @anonimo actually is not just add up. You need to add up the day and discount the day before

  • What version of MySQL?

  • Your solution looks ideal @Sorack! Unfortunately I am running version 5.7.26-0 Ubuntu 0.19.04.1

  • @Acimarrocha indicated in my reply also the query referring to previous versions

Show 2 more comments

2 answers

1

I simplified your table as follows only to facilitate resolution:

Schema

CREATE TABLE location(
  dateGenerated DATETIME,
  Conductor     VARCHAR(100),
  odometer      NUMERIC(15, 3)
);

INSERT INTO location(dateGenerated, Conductor, odometer)
VALUES (STR_TO_DATE('05/07/2019 19:12', '%d/%m/%Y %H:%i'), 'PAULO' , 0),
       (STR_TO_DATE('05/07/2019 19:14', '%d/%m/%Y %H:%i'), 'MARCOS' , 0),
       (STR_TO_DATE('05/07/2019 19:14', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.488),
       (STR_TO_DATE('05/07/2019 19:16', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.651),
       (STR_TO_DATE('05/07/2019 19:16', '%d/%m/%Y %H:%i'), 'PAULO' , 0.533),
       (STR_TO_DATE('05/07/2019 19:16', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.703),
       (STR_TO_DATE('05/07/2019 19:16', '%d/%m/%Y %H:%i'), 'PAULO' , 0.571),
       (STR_TO_DATE('05/07/2019 19:17', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.755),
       (STR_TO_DATE('05/07/2019 19:17', '%d/%m/%Y %H:%i'), 'PAULO' , 0.602),
       (STR_TO_DATE('05/07/2019 19:20', '%d/%m/%Y %H:%i'), 'PAULO' , 0.757),
       (STR_TO_DATE('06/07/2019 08:10', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.807),
       (STR_TO_DATE('06/07/2019 08:11', '%d/%m/%Y %H:%i'), 'MARCOS' , 0.911),
       (STR_TO_DATE('06/07/2019 08:12', '%d/%m/%Y %H:%i'), 'MARCOS' , 1.015),
       (STR_TO_DATE('06/07/2019 08:13', '%d/%m/%Y %H:%i'), 'MARCOS' , 1.067),
       (STR_TO_DATE('06/07/2019 08:15', '%d/%m/%Y %H:%i'), 'MARCOS' , 1.223),
       (STR_TO_DATE('06/07/2019 08:16', '%d/%m/%Y %H:%i'), 'PAULO' , 0.791),
       (STR_TO_DATE('06/07/2019 08:16', '%d/%m/%Y %H:%i'), 'PAULO' , 0.833),
       (STR_TO_DATE('06/07/2019 08:19', '%d/%m/%Y %H:%i'), 'PAULO' , 1.01),
       (STR_TO_DATE('06/07/2019 08:20', '%d/%m/%Y %H:%i'), 'PAULO' , 1.063),
       (STR_TO_DATE('06/07/2019 19:16', '%d/%m/%Y %H:%i'), 'PAULO' , 1.114),
       (STR_TO_DATE('06/07/2019 19:17', '%d/%m/%Y %H:%i'), 'PAULO' , 1.165);

Then I created a CTE (only to MySQL from the v8.0) with the maximum daily value. With the values separated by day, subtract the sum of the previous locations to arrive at the value travelled per day:

Query(V8.0)

WITH daily AS (
  SELECT DATE(l.dateGenerated) AS dateGenerated,
         l.Conductor,
         MAX(odometer) AS odometer
    FROM location l
   GROUP BY DATE(l.dateGenerated),
            l.Conductor
)
SELECT DATE_FORMAT(d.dateGenerated,'%d/%m/%Y') AS dateGenerated,
       d.Conductor,
       d.odometer - COALESCE((SELECT MAX(odometer)
                                FROM location l
                               WHERE l.Conductor = d.Conductor
                                 AND l.dateGenerated < d.dateGenerated), 0) AS Route
  FROM daily d
 ORDER BY d.Conductor,
          d.dateGenerated;

For earlier versions you can replace the WITH by a subquery in the FROM.

Query(Versions earlier than 8.0)

SELECT DATE_FORMAT(d.dateGenerated,'%d/%m/%Y') AS dateGenerated,
       d.Conductor,
       d.odometer - COALESCE((SELECT MAX(odometer)
                                FROM location l
                               WHERE l.Conductor = d.Conductor
                                 AND l.dateGenerated < d.dateGenerated), 0) AS Route
  FROM (SELECT DATE(l.dateGenerated) AS dateGenerated,
               l.Conductor,
               MAX(odometer) AS odometer
          FROM location l
         GROUP BY DATE(l.dateGenerated),
            l.Conductor) d
 ORDER BY d.Conductor,
          d.dateGenerated

Resulting in

| dateGenerated | Route | Conductor |
| ------------- | ----- | --------- |
| 05/07/2019    | 0.755 | MARCOS    |
| 06/07/2019    | 0.468 | MARCOS    |
| 05/07/2019    | 0.757 | PAULO     |
| 06/07/2019    | 0.408 | PAULO     |

See working on DB Fiddle

  • You understood exactly what I need! It just went wrong because of the lack of CTE support. And I can’t update this server at the moment because it’s in production.

  • 1

    Would you have a way to do these sub-queries without using the WITH clause? With Join maybe?

  • @Acimarrocha is already added the answer. It is the second query

  • Now I saw the version without with subquery. I’ll try it here and I’ll talk!

  • To simplify I tried to use the driver ids without Join in the Devices table, so I modified as little as possible your query. If you check only the driver of id 2, the results came out weird, with totals turning negative: dateGenerated;deviceid;Route 15/07/2019;2;-509094.28125 14/07/2019;2;-690920.578125 13/07/2019;2;-756583.375 12/07/2019;2;7978.1875 11/07/2019;2;116861.0625&#xA;10/07/2019;2;146732.5

  • @Acimarrocha unfortunately without the structures and real data of its tables I can not produce its result

  • the structure of these used data are two tables, basically: CREATE TABLE device ( id INT(11) , name VARCHAR(255) );

  • CREATE TABLE location ( id INT(11), deviceId INT(11) , odometer FLOAT , dateGenerated TIMESTAMP );

  • @Acimarrocha even so I see nothing wrong with the query. Mount a fiddle with your data so that I can apply the query in it

  • It seems right yes! I was finding it strange to give a negative number. I discovered error in the data, the odometer zeroed.

  • https://www.db-fiddle.com/f/rzbMh3r6JgxPwn9HgEvDas/0

  • @Acimarrocha then, there is the problem. If the data is not consistent there is no answer that solves your problem. The above solution solves if, and only if, the data is correct

Show 7 more comments

0

whereas this odometer is the distance, you can add this and group by driver:

SELECT DATE(l.dateGenerated) AS Day,
d.name AS Conductor,
ROUND((sum(l.odometer)/1000),3) AS Route

from location l 
left join device d on (d.id = l.deviceId)
GROUP BY Day, Conductor

  • I think that’s not what the OP wants. He wants the distance covered in the day. You need to discount the day before.

  • Ahhh, I think I get it. So the field odometro is actually the accumulated value of each user?

  • I believe so

  • Exact Ronaldo! The way you suggested correctly brings the distance of each driver being accumulated every day. If you get the (last distance of the day) - (the first of the day) we have what you traveled every day. Complicated rs!

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.