CODEWITHSUNDEEP
mysqlsqldate
Can Kagan
Can Kagan

Reputation: 13

How can I subtract two row's within same column in same date?

I have a query. I want to do an subtraction of the first and last row in the same day. I wrote the this query, but I was not sure of the performance. Is there an alternative way to this problem?

| imei  |    date                 | km    |
|-----------------------------------------|
| 123   | 2019-01-15 00:00:01     | 15    |
| 123   | 2019-01-15 12:12:08     | 8     |
| 123   | 2019-01-15 23:00:59     | 30    |
| 456   | 2019-01-15 00:03:12     | 232   |
| 456   | 2019-01-15 07:04:00     | 123   |
| 456   | 2019-01-15 23:16:18     | 464   |

My query:

SELECT 
gg.imei,
DATE_FORMAT(gg.datee, '%Y-%m-%d'),
gg.km - (SELECT 
        g.km
    FROM
        gps g
    WHERE
        g.datee LIKE '2019-01-15%'
            AND g.datee = (SELECT 
                MIN(t.datee)
            FROM
                gps t
            WHERE
                t.datee LIKE '2019-01-15%'
                    AND t.imei = g.imei)
            AND g.imei = gg.imei
    GROUP BY g.imei) AS km
FROM
    gps gg
WHERE
gg.datee LIKE '2019-01-15%'
    AND gg.datee = (SELECT 
        MAX(ts.datee)
    FROM
        gps ts
    WHERE
        ts.datee LIKE '2019-01-15%'
            AND gg.imei = ts.imei)

Result is true.

| imei    |    date    | km    |
|------------------------------|
| 123     | 2019-01-15 | 15    |
| 456     | 2019-01-15 | 232   |

But the query is too complicated.

Edit: There are 3 million records in the table.

Upvotes: 1

Views: 165

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272106

You can find first and last datetime for each imei-date pair in a sub query then join with it:

SELECT agg.imei, agg.date_date, gps_last.km - gps_frst.km AS diff
FROM (
    SELECT imei, DATE(date) AS date_date, MIN(date) AS date_frst, MAX(date) AS date_last
    FROM gps
    GROUP BY imei, DATE(date)
) AS agg
JOIN gps AS gps_frst ON agg.imei = gps_frst.imei AND agg.date_frst = gps_frst.date
JOIN gps AS gps_last ON agg.imei = gps_last.imei AND agg.date_last = gps_last.date

You need appropriate indexes on your table though. The DATE(date) part in particular will be slow, so you might want to consider adding another column for storing the date part only.

Upvotes: 4

Related Questions