Differenz berechnen

[rAdiuM]

Hallo liebe Community,

ich habe mir für zuhause ein kleines Projekt gebaut um einige Statistiken erheben zu können. Ich möchte die Differenz zwischen den km berechnen. So sieht meine Tabelle aus:

MariaDB [villa_lindhorst]> select id,vr_id,km,zeitstempel from vehicle_refuel where vr_id = 8;
+-----+-------+-------+---------------------+
| id  | vr_id | km    | zeitstempel         |
+-----+-------+-------+---------------------+
|  95 | 8     | 21658 | 2022-09-23 12:53:06 |
|  96 | 8     | 21763 | 2022-10-05 16:56:07 |
|  97 | 8     | 22186 | 2022-12-16 17:29:28 |
|  98 | 8     | 22785 | 2023-01-09 16:51:24 |
|  99 | 8     | 23308 | 2023-01-19 16:31:11 |
| 100 | 8     | 23934 | 2023-01-27 18:57:20 |
| 101 | 8     | 24504 | 2023-02-06 17:04:00 |
| 102 | 8     | 25049 | 2023-02-15 16:12:35 |
| 103 | 8     | 25636 | 2023-02-27 16:49:52 |
| 104 | 8     | 26195 | 2023-03-12 17:28:02 |
| 105 | 8     | 26765 | 2023-03-28 17:37:01 |
| 106 | 8     | 27378 | 2023-04-08 11:26:37 |
| 107 | 8     | 28011 | 2023-04-20 11:41:08 |
| 108 | 8     | 28661 | 2023-05-04 17:23:42 |
| 109 | 8     | 29236 | 2023-05-22 18:39:16 |
| 110 | 8     | 29813 | 2023-06-09 16:22:07 |
| 111 | 8     | 30338 | 2023-06-27 18:34:40 |
| 112 | 8     | 30874 | 2023-07-06 16:46:44 |
| 113 | 8     | 31478 | 2023-07-22 16:04:09 |
| 114 | 8     | 32124 | 2023-08-25 13:00:19 |
| 115 | 8     | 32732 | 2023-09-09 17:43:39 |
| 116 | 8     | 33307 | 2023-09-25 17:14:36 |
| 117 | 8     | 33899 | 2023-10-09 17:02:13 |
| 118 | 8     | 34525 | 2023-10-22 15:03:31 |
| 119 | 8     | 35095 | 2023-11-06 14:59:58 |
| 120 | 8     | 35670 | 2023-11-18 16:36:56 |
| 121 | 8     | 36212 | 2023-12-01 13:45:46 |
| 122 | 8     | 36800 | 2023-12-15 08:58:24 |
| 123 | 8     | 37410 | 2023-12-29 19:10:30 |
| 124 | 8     | 37635 | 2024-01-05 16:57:41 |
| 125 | 8     | 38179 | 2024-01-16 17:09:15 |
| 126 | 8     | 38679 | 2024-01-29 16:35:30 |
| 127 | 8     | 39264 | 2024-02-10 17:12:53 |
+-----+-------+-------+---------------------+
33 rows in set (0,000 sec)

Ich habe etwas gegoogelt und bin über die Anweisung LAG und LEAD gestolpert, wobei ich mir nicht sicher bin ob das überhaupt das richtige ist. Jedenfalls muss ich gleich gestehen ich habe es nicht verstanden. Also wie erreiche ich es das die Differenz z.B. zwischen der id 95 und 96 der beiden Werte von km berechnen?

Kann mir da jemand helfen?

Danke im Voraus.

 

[akretschmer]

lead() oder lag() ist das richtige, wie es geht steht hier: PostgreSQL LAG() Function By Practical Examples

 

[rAdiuM]

Danke dir, ich war jetzt immerhin im stande eine abfrage zu bauen die funktioniert.

SELECT km, price, zeitstempel, LAG(km, 1) OVER ( ORDER BY zeitstempel DESC ) difference FROM vehicle_refuel WHERE vr_id = 8;

+-------+-------+---------------------+------------+
| km    | price | zeitstempel         | difference |
+-------+-------+---------------------+------------+
| 39264 | 70.01 | 2024-02-10 17:12:53 |       NULL |
| 38679 | 60.77 | 2024-01-29 16:35:30 |      39264 |
| 38179 | 66.00 | 2024-01-16 17:09:15 |      38679 |
| 37635 | 23.65 | 2024-01-05 16:57:41 |      38179 |
| 37410 | 90.00 | 2023-12-29 19:10:30 |      37635 |
| 36800 | 65.00 | 2023-12-15 08:58:24 |      37410 |
| 36212 | 71.50 | 2023-12-01 13:45:46 |      36800 |
| 35670 | 59.50 | 2023-11-18 16:36:56 |      36212 |
| 35095 | 67.02 | 2023-11-06 14:59:58 |      35670 |
| 34525 | 70.00 | 2023-10-22 15:03:31 |      35095 |

Aber das ergebnis stimmt nicht. Bei difference sollte stehen der Wert: 39264 - 38679 also = 585.
Was muss ich da noch ändern damit mir der SQL Query mir das gleich errechnet?

Gruß

 

[akretschmer]

Was muss ich da noch ändern damit mir der SQL Query mir das gleich errechnet?

Du musst den einen Wert vom anderen abziehen...

 

[rAdiuM]

Das ist bestimmt ganz einfach, aber ich weiß nicht wie!

MariaDB [villa_lindhorst]> SELECT
    ->   LAG(km, 1) OVER (ORDER BY zeitstempel DESC) Minuend, km AS Subtrahend
    -> FROM
    ->   vehicle_refuel
    -> WHERE vr_id = 8;
+---------+------------+
| Minuend | Subtrahend |
+---------+------------+
|    NULL |      39264 |
|   39264 |      38679 |
|   38679 |      38179 |
|   38179 |      37635 |
|   37635 |      37410 |
|   37410 |      36800 |
|   36800 |      36212 |
|   36212 |      35670 |
|   35670 |      35095 |
|   35095 |      34525 |
|   34525 |      33899 |
|   33899 |      33307 |
...

Wie würde denn der Query aussehen zur Subtraktion?

 

[akretschmer]

km - lag(...)

 

[rAdiuM]

Ich Danke dir. Es funktioniert!

MariaDB [villa_lindhorst]> SELECT DATE_FORMAT(zeitstempel, '%d.%m.%Y - %H:%i:%s'), price AS 'Preis in Euro', km AS 'letzter KM Stand',
    ->   LAG(km, 1) OVER ( ORDER BY zeitstempel DESC ) - km 'gefahrene kilometer'
    -> FROM
    ->   vehicle_refuel
    -> WHERE vr_id = 8;
+-------------------------------------------------+---------------+------------------+---------------------+
| DATE_FORMAT(zeitstempel, '%d.%m.%Y - %H:%i:%s') | Preis in Euro | letzter KM Stand | gefahrene kilometer |
+-------------------------------------------------+---------------+------------------+---------------------+
| 10.02.2024 - 17:12:53                           |         70.01 |            39264 |                NULL |
| 29.01.2024 - 16:35:30                           |         60.77 |            38679 |                 585 |
| 16.01.2024 - 17:09:15                           |         66.00 |            38179 |                 500 |
| 05.01.2024 - 16:57:41                           |         23.65 |            37635 |                 544 |
| 29.12.2023 - 19:10:30                           |         90.00 |            37410 |                 225 |
| 15.12.2023 - 08:58:24                           |         65.00 |            36800 |                 610 |
| 01.12.2023 - 13:45:46                           |         71.50 |            36212 |                 588 |
| 18.11.2023 - 16:36:56                           |         59.50 |            35670 |                 542 |
| 06.11.2023 - 14:59:58                           |         67.02 |            35095 |                 575 |
| 22.10.2023 - 15:03:31                           |         70.00 |            34525 |                 570 |
| 09.10.2023 - 17:02:13                           |         79.00 |            33899 |                 626 |
| 25.09.2023 - 17:14:36                           |         70.65 |            33307 |                 592 |
....