Operacion con campo anterior de una columna

[emiajsiul]

El problema es el siguiente: Se tienen dos columnas KM y LITROS ambas con valores numéricos, y se requiere ordenar la columna KM en forma descendente y restar al ultimo valor de la lista el valor anterior, despues el resultado se divide entre el valor LITROS correspondiente al ultimo valor de KM.

Valores de tabla donde KM y LITROS componen un registro:

Código HTML:

KM              LITROS
261253.000	43.668
261504.000	33.349
261911.000	43.668
262245.000	35.388
262522.000	37.575

Tengo que obtener el ultimo KM 262522 Y Restarle el anterior KM 262245, esto lo ubico en una columna RECORRIDO, después este resultado dividirlo entre el valor de Litros correspondiente al ultimo KM en este caso seria 37.575 y así sucesivamente.

Lo que se me ocurrio fue lo siguiente

Código:

SELECT V.KM AS UKM, X.KM AS PKM,V.LITROS, 
			(X.KM - V.KM) AS RECORRIDO, 
			((X.KM-V.KM)/V.LITROS) AS CONSUMO 
FROM VALES AS V, VALES AS X
WHERE V.VN_UNIDAD = 'DR 031' 
	AND X.VN_UNIDAD = 'DR 031' 
	AND X.KM = V.KM
ORDER BY V.KM  DESC
LIMIT 5

Obtengo este resultado

Código HTML:

UKM              PKM            LITROS      RECORRIDO     CONSUMO
262522.000	262522.000	37.575	   0.000	   0.0000000
262245.000	262245.000	35.388	   0.000	   0.0000000
261911.000	261911.000	43.668	   0.000	   0.0000000
261504.000	261504.000	33.349	   0.000	   0.0000000
261253.000	261253.000	43.668	   0.000	   0.0000000

Por lo que veo necesito recorrer el valor del alias PKM y acer la resta pero no tengo idea de como acerlo?

[leonardo_josue]

Hola emiajsiul:

Tu problema se parece a algo que me tocó responder hace algunos días, esta es la liga para el foro:

devolver la diferencia de valor entre una fila siguente

Igual y deberías de darle un vistazo para ver si te sirve, y si continuas con problemas postea algo del código que intentaste hacer para tratar de ayudarte a completarlo o corregirlo.

Saludos
Leo

[gnzsoloyo]

Habría que probar así:

Código MySQL:

SELECT KM, LITROS, RECORRIDO, SUM(RECORRIDO) R_ACUMULADO, SUM(LITROS) L_ACUMULADO
FROM
    (SELECT KM, LITROS, (KM-@kml) RECORRIDO, @kml:= KM
    FROM VALES JOIN (SELECT @kml:=0) T1) T2
GROUP BY KM, LITROS WITH ROLLUP;
 

[emiajsiul]

Primero que nada gracias por contestar, en principio inicie con una consulta basada en logica la cual es muy grande y brumosa, por tanto poco eficiente, peo funciono bien para un elemento, he intentado reducirla o simplificarla y dar le la funcionalidad para 5 o mas registros, se que da risa esta consulta pero aqui la pongo para darme mas a entender:

Código MySQL:

SELECT 
(SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1) AS UKM, 
(SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1,1) AS PKM,
LITROS,
((SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1)-
(SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1,1))AS RECORRIDO,
(((SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1)-
(SELECT KM FROM VALES WHERE KM>10 AND VN_UNIDAD = 'DR 031' ORDER BY KM DESC LIMIT 1,1))/LITROS)AS CONSUMO
FROM
VALES
WHERE VN_UNIDAD ='DR 031'
ORDER BY KM DESC
LIMIT 1
 

Esto me muestra el resultado:

Código HTML:

UKM             PKM                LITROS       RECORRIDO      CONSUMO
262522.000      262245.000         37.575          277.000      7.3719228
 

[gnzsoloyo]

¿Probaste lo que te pasé?

[emiajsiul]

Si ya probe el codigo, y tiene algo de logica pero no es lo que busco, mira esto es el resultado:

Código MySQL:

SELECT KM, LITROS, RECORRIDO, SUM(RECORRIDO) R_ACUMULADO, SUM(LITROS) L_ACUMULADO
FROM
    (SELECT KM, LITROS, (KM-@kml) RECORRIDO, @kml:= KM
    FROM VALES JOIN (SELECT @kml:=0) T1) T2
GROUP BY KM, LITROS WITH ROLLUP;
 

Código HTML:

260842  33.898  255         255           33.898
260842          255         255           33.898
261253  43.668  411         411           43.668
261253          411         411           43.668
261504  33.349  261502      261502        33.349
261504          261502      261502        33.349
261911  43.668 -611        -611         43.668
261911         -611        -611         43.668
262245  35.388  334         334          35.388
262245          334         334          35.388
262522  37.575  1018      1018          37.575
262522          1018      1018              37.575
 

[leonardo_josue]

Hola emiajsiul:

Lamentablemente no estoy familiarizado con el tipo de consultas que te propuso el compañero gnzsoloyo, estos sólo los había visto aplicados en SQL Server, así es que me imagino que existe alguna forma de corregir para que te dé la salida que necesitas, pues creo que sería más eficiente que lo que voy a proponerte.

Por lo pronto, y retomando la idea de la liga que te pasé podrías hacerlo más o menos así:

Código SQL:

mysql> CREATE TABLE Vales (KM REAL, LITROS REAL);
Query OK, 0 ROWS affected (0.23 sec)
 
mysql> INSERT INTO Vales VALUES (261253.000, 43.668), (261504.000,33.349),
    -> (261911.000,43.668), (262245.000,35.388), (262522.000,37.575);
Query OK, 5 ROWS affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM Vales;
+--------+--------+
| KM     | LITROS |
+--------+--------+
| 261253 | 43.668 |
| 261504 | 33.349 |
| 261911 | 43.668 |
| 262245 | 35.388 |
| 262522 | 37.575 |
+--------+--------+
5 ROWS IN SET (0.00 sec)
 
mysql> SELECT V3.UKM, V3.PKM, V2.LITROS, (V3.UKM - V3.PKM) RECORRIDO,
    -> (V3.UKM - V3.PKM) / V2.LITROS CONSUMO
    -> FROM vales V2 INNER JOIN
    -> (SELECT
    -> (SELECT MIN(KM) FROM vales WHERE KM > V1.KM) UKM, V1.KM PKM
    -> FROM vales V1
    -> ) V3 ON V3.UKM = V2.KM
    -> ORDER BY 1 DESC;
+--------+--------+--------+-----------+-------------------+
| UKM    | PKM    | LITROS | RECORRIDO | CONSUMO           |
+--------+--------+--------+-----------+-------------------+
| 262522 | 262245 | 37.575 |       277 | 7.371922821024617 |
| 262245 | 261911 | 35.388 |       334 | 9.438227647790212 |
| 261911 | 261504 | 43.668 |       407 | 9.320326096913071 |
| 261504 | 261253 | 33.349 |       251 | 7.526462562595581 |
+--------+--------+--------+-----------+-------------------+
4 ROWS IN SET (0.06 sec)
 

Creo que la consulta puede mejorarse retomando la lógica de gnzsoloyo, pero mientras investigo un poco y obtengo alguna otra propuesta con esto alcanza.

Saludos
Leo.

[emiajsiul]

Excelente la lógica leonardo_jose, nunca se me ocurrió un tercer alias que buscara el mínimo, ahora solo hay que optimizar el código, para que la consulta sea mas rápida. Gracias por abrirme la mente, es que hay días en los que uno se ahoga en un vaso de agua.

Creo que la consulta puede mejorarse retomando la lógica de gnzsoloyo, pero mientras investigo un poco y obtengo alguna otra propuesta con esto alcanza.

Buscare una forma de reducir y optimizar la búsqueda muchísimas gracias!, lo lindo de esto es no parar en la primer solución sino encontrar la mejor!

Saludos!

[gnzsoloyo]

Bueno, en realidad mi propuesta fue hecha un poco a las apuradas y mirando de costado, pero la idea sería mas o menos así, en realidad:

Código MySQL:

mysql> DROP TABLE IF EXISTS VALES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE IF NOT EXISTS VALES
    ->     (KM DECIMAL(12,3),
    ->     LITROS DECIMAL(8,3));
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO VALES(KM, LITROS)
    -> VALUES
    ->     (261253.000,43.668),
    ->     (261504.000,33.349),
    ->     (261911.000,43.668),
    ->     (262245.000,35.388),
    ->     (262522.000,37.575);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql>
mysql> SELECT
    ->     IF(KM IS NULL, 'Total:', KM) KM,
    ->     IF(KM IS NULL, '', LITROS) LITROS,
    ->     TRUNCATE(SUM(LITROS),3) LITROS_acum,
    ->     TRUNCATE(SUM(RECORRIDO),3) R_ACUMULADO
    -> FROM
    ->     (SELECT KM, LITROS, IF(@kml=0, @kml:=KM, @kml)km1, (KM-@kml) RECORRIDO, @kml:= KM
    ->     FROM VALES JOIN (SELECT @kml:=0) T1) T2
    -> GROUP BY KM WITH ROLLUP;
+------------+--------+-------------+-------------+
| KM         | LITROS | LITROS_acum | R_ACUMULADO |
+------------+--------+-------------+-------------+
| 261253.000 | 33.349 |      43.668 |       0.000 |
| 261504.000 | 43.668 |      33.349 |     251.000 |
| 261911.000 | 35.388 |      43.668 |     407.000 |
| 262245.000 | 37.575 |      35.388 |     334.000 |
| 262522.000 | 37.575 |      37.575 |     277.000 |
| Total:     |        |     193.648 |    1269.000 |
+------------+--------+-------------+-------------+
6 rows in set, 1 warning (0.00 sec)
 

Este es un ejemplo de la flexibilidad que permiten las variables de usuario.

[emiajsiul]

He estado analisando el codigo que has presentado y no estoy muy familiarizado con este tipo de consulta, y me e quedado ahora si que no se como logras filtrar los datos, miro que el resultado de la consulta es lo que se requiere pero no entiendo al 100% la sintaxis, e intentado aplicar el codigo

Código MySQL:

SELECT
    ->     IF(KM IS NULL, 'Total:', KM) KM,
    ->     IF(KM IS NULL, '', LITROS) LITROS,
    ->     TRUNCATE(SUM(LITROS),3) LITROS_acum,
    ->     TRUNCATE(SUM(RECORRIDO),3) R_ACUMULADO
    -> FROM
    ->     (SELECT KM, LITROS, IF(@kml=0, @kml:=KM, @kml)km1, (KM-@kml) RECORRIDO, @kml:= KM
    ->     FROM VALES JOIN (SELECT @kml:=0) T1) T2
    -> GROUP BY KM WITH ROLLUP;
 

para restringir la búsqueda a un campo mas, digamos que la lista es de n Km de n Unidades, no capto el punto en el que puedo insertar una condición digamos WHERE = UNIDAD ='X', digamos que esto siempre lo he echo en php, de tal forma que hacia una consulta y luego la filtraba en PHP, pero me doy cuenta que con mysql se puede enviar solo los datos que se requieren sin necesidad de despues depurar con PHP.

[emiajsiul]

De esta forma logro, hacer el calculo que requiero y puedo ver a que unidad corresponden los valores pero no puedo restringir a una sola unidad de la lista que se despliega, esto es lo que tengo :

Código MySQL:

SELECT
  IF(KM IS NULL, 'Total:', KM) KM,
  IF(KM IS NULL, '', LITROS) LITROS,
  TRUNCATE(SUM(LITROS),3) LITROS_acum,
  TRUNCATE(SUM(RECORRIDO),3) R_ACUMULADO
     FROM
         (SELECT 
                        VN_UNIDAD
                        KM, 
                        LITROS, 
                        IF(@kml=0, @kml:=KM, @kml)km1, 
                        (KM-@kml) RECORRIDO, 
                        @kml:= KM 
         FROM VALES JOIN (SELECT @kml:=0) T1) T2 
             GROUP BY KM WITH ROLLUP;
 

[gnzsoloyo]

...Si. es una sintaxis algo complicada porque requiere conocer las subconsultas y tener cierto dominio sobre las variables de usuario en MySQL...

El tema es así:
Las variables de usuario son variables que se crean en una conexión establecida por un usuario y que tienen algunas características:
- No tienen tipo de dato inicialmente.
- No se las declara; directamente se las crea y usa al invocarlas.
- Se las debe incializar siempre con el tipo de dato a usar.
- Cuando no se las incializa su valor es NULL, por lo que cualquier operación con ellas dará NULL.
- Existen sólo en la conexión, lo que significa que dejan de existir al cerrarse esta.
- Como dependen de la conexión, y sólo existen en ella, dos conexiones diferentes pueden usar el mismo nombre de variable, porque se refieren a objetos diferentes. No hay conflictos. Incluso si las conexiones son abiertas por el mismo usuario, las variables pueden usar el mismo nombre y contener cosas diferentes.
- Toman su valor dinámicamente, por lo que si en una operación vas asignando valores distintos a la misma variable, en cada paso tendrá un valor diferente.
- Se les puede asignar un valor dinámicamente en una consulta con ayuda de ":=". Eso permite variar el valor en cada registro y columna leídos.
- Conservan su último valor, por lo que cada vez que se las usa se deben reinicializar.

Esto te puede dar un ejemplo:

Código MySQL:

mysql> SELECT @A;
+------+
| @A   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
 
mysql> SELECT @A:=0, @A, @A:=33, @A, @A:=@A -344, @A, @A:=1, @A;
+-------+------+--------+------+-------------+------+-------+------+
| @A:=0 | @A   | @A:=33 | @A   | @A:=@A -344 | @A   | @A:=1 | @A   |
+-------+------+--------+------+-------------+------+-------+------+
|     0 | 0    |     33 | 33   |        -311 | -311 |     1 | 1    |
+-------+------+--------+------+-------------+------+-------+------+
1 row in set (0.00 sec)
 
mysql> SELECT @A;
+------+
| @A   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)
 

Ahora veamos la consulta por partes:
1) Creando la variable.
El único objetivo de esta parte es crear la variable, incializarla y dejarla disponible para la subconsulta, sin necesidad de ejecutar una consulta previa para eso:

Código MySQL:

(SELECT @kml:=0)
 

2) Creando la suma y cálculos:

Código MySQL:

SELECT KM, LITROS, IF(@kml=0, @kml:=KM, @kml)km1, (KM-@kml) RECORRIDO, @kml:= KM
    FROM VALES JOIN (SELECT @kml:=0) T1
 

Es la que hace casi todo el trabajo:
1) Toma el valor de KM y LITROS.
2) Luego, si el valor de @kml es cero significa que es el primer registro, entonces le asigna el valor de KM a @kml, sino simplemente reproduce el último valor tomado (tiene que hacer algo).
3) Luego calcula la resta entre el valor de KM actual y el último valor tomado de @kml y le da el nombre de "RECORRIDO".
4) Asigna el valor actual de KM a @kml.
Esto devuelve una tabla que contiene: KM, LITROS, @kml anterior, RECORRIDO (parcial) y @kml actualizado.

Con estos datos, la consulta exterior simplemente hace:
1) Agrupa haciendo un ROLLUP sobre KM, sumando todos los parciales de litros y recorrido. Esto devolverá un registro por cada KM distinto, más un registro final con la suma total y NULL en todos los campos que no estén en el GROUP BY.
2) Si KM es NULL, significa que es el último renglón, entonces reemplaza el NULL por "Total en la primera columna y un vacío en la segunda.

¿Se entiende la idea?

[emiajsiul]

Que bien ahora entiendo mas las cosas, ya que mi problema estaba en la seccion:

Código MySQL:

IF(@kml=0, @kml:=KM, @kml)km1
 

gracias por la explicación, seguiré intentando, para poder hacer exactamente esta consulta, pero apuntando a una unidad, ya que al correr esta consulta me muestra el Recorrido y Kml actualizado pero de todas las unidades que contiene mi base de datos, ahora solo tengo que poner alguna condición para restringir a una unidad en especifico, intentare con la sintaxis que usas, ya que me llamo la atención este método. saludos

[gnzsoloyo]

Para una sola unidad es bastante simple: Mete el WHERE en la segunda subconsulta.
Por ejemplo:

Código MySQL:

SELECT
    IF(KM IS NULL, 'Total:', KM) KM,
    IF(KM IS NULL, '', LITROS) LITROS,
    TRUNCATE(SUM(LITROS),3) LITROS_acum,
    TRUNCATE(SUM(RECORRIDO),3) R_ACUMULADO
FROM
    (SELECT KM, LITROS, IF(@kml=0, @kml:=KM, @kml)km1, (KM-@kml) RECORRIDO, @kml:= KM
    FROM VALES JOIN (SELECT @kml:=0) T1
    WHERE PATENTE = 'ABC-123') T2
GROUP BY KM WITH ROLLUP;
 

[emiajsiul]

Perfecto pero lo he solucionado con algo mas simple, basado en lo que me acabas de enseñar :), ahora solo me falta una cosa ordenar de mayor a menor, se que se puede usar una clausula order by pero estoy analizando, este es el código:

Código MySQL:

SELECT 
            VN_UNIDAD UNIDAD,
            KM, 
            LITROS, 
            IF(@kml=0, @kml:=KM, @kml)km1, 
                        (KM-@kml) RECORRIDO, 
                        @kml:= KM 
                        FROM VALES 
                                 WHERE VN_UNIDAD = 'Unidad x'
 
GROUP BY KM WITH ROLLUP
LIMIT 10;
 

[gnzsoloyo]

Ojo: la cláusula ORDER BY es siempre posterior a la GROUP BY, por lo que la debes poner dentro de la subconsulta, o te causará serios problemas, porque afectaría también a los registros con NULL.
Por eso dejo el GROUP BY en la exterior.

[emiajsiul]

OK GRACIAS, pues ya metido en esto, estoy aplicando aplicando este codigo:

Código MySQL:

SELECT 
            VN_UNIDAD UNIDAD,
            KM, 
            LITROS, 
            IF(@kml=0, @kml:=KM, @kml)km1, 
                        (KM-@kml)*(-1) RECORRIDO, 
                        (@kml:= KM) km2
                        FROM VALES 
                                 WHERE VN_UNIDAD = 'DR 031'
ORDER BY KM DESC
LIMIT 10;
 

funciona muy bn solo me falta inicializar @km1:=0 por el siguiente detalle:

Código MySQL:

DR 031  262522.000  37.575  260025  -2497   262522.000
DR 031  262245.000  35.388  262522  277 262245.000
DR 031  261911.000  43.668  262245  334 261911.000
DR 031  261504.000  33.349  261911  407 261504.000
DR 031  261253.000  43.668  261504  251 261253.000
 

El primer registro hace una resta de un valor desconocido y los valores reales inician en la siguiente linea

[gnzsoloyo]

Realmente no entiendo ese "*(-1)".
¿CUál es el objetivo de esa multiplicación por -1?

[emiajsiul]

Iniciado por gnzsoloyo

Realmente no entiendo ese "*(-1)".
¿CUál es el objetivo de esa multiplicación por -1?

, lo siento fue un arreglo rapido solo queria ver el resultado, es lo mismo que voltear la resta, solo para cabiar el signo, en realidad quedo asi el codigo:

Código MySQL:

SELECT 
            VN_UNIDAD UNIDAD,
                        VNOM_EMP EMPLEADO,
                        FOLIO,
                        KM, 
            LITROS, 
            IF(@kml=0, @kml:=KM, @kml)KM_MAYOR, 
                        @KML2:=(@kml-KM) RECORRIDO, 
                        (@kml:= KM) KM_MENOR,
                        IF(@X=0, @X:= LITROS, @X) LITRO_MAYOR,
                            (@KML2/@X) CONSUMO, 
                        (@X:= LITROS) LITRO_MENOR         
            FROM VALES 
                                 WHERE VN_UNIDAD = 'DR 031' AND KM > 10
ORDER BY KM DESC
LIMIT 10;
 

Solo necesito eliminar el primer registro, y listo.

[gnzsoloyo]

Mandale un LIMIT 1, 10 y listo...

[emiajsiul]

Iniciado por gnzsoloyo

Mandale un LIMIT 1, 10 y listo...

Ya lo intente, pero lo que pasa esque me va a recorrer el registro y esta bien, pero como @km1 no esta definido a 0 toma un valor raro que le resta al primer registro de la consulta, ya se aun LIMIT 1,10 O LIMIT 5,10 No importa cuanto lo limite el primer registro siempre me sale con un valor que no es:

CODIGO:

Código MySQL:

SELECT 
            VN_UNIDAD UNIDAD,
                        VNOM_EMP EMPLEADO,
                        FOLIO,
                        KM, 
            LITROS, 
            IF(@kml=0, @kml:=KM, @kml) KM_MAYOR, 
                        @KML2:=(@kml-KM) RECORRIDO, 
                        (@kml:= KM) KM_MENOR,
                        IF(@X=0, @X:= LITROS, @X) LITRO_MAYOR,
                            (@KML2/@X) CONSUMO, 
                        (@X:= LITROS) LITRO_MENOR         
            FROM VALES 
                                 WHERE VN_UNIDAD = 'DR 031' AND KM > 10
ORDER BY KM DESC
LIMIT 10;
 

Ejemplo: con LIMIT 1,10

Código MySQL:

262245.000  35.388  260025  -2220   262245.000  40.298  -55.0895826095588   35.388
261911.000  43.668  262245  334 261911.000  35.388  9.43822764779021    43.668
261504.000  33.349  261911  407 261504.000  43.668  9.32032609691307    33.349
261253.000  43.668  261504  251 261253.000  33.349  7.52646256259558    43.668
261185.000  42.660  261253  68  261185.000  43.668  1.55720436017221    42.660
260842.000  33.898  261185  343 260842.000  42.66   8.04031879981247    33.898
260587.000  38.216  260842  255 260587.000  33.898  7.52256770310933    38.216
260281.000  35.269  260587  306 260281.000  38.216  8.00711743772242    35.269
260025.000  40.298  260281  256 260025.000  35.269  7.25849896509683    40.298
259621.000  43.511  260025  404 259621.000  40.298  10.0253114298476    43.511
 

LIMIT 10

Código MySQL:

262522.000  37.575  259621  -2901   262522.000  43.511  -66.672795385075    37.575
262245.000  35.388  262522  277 262245.000  37.575  7.37192282102462    35.388
261911.000  43.668  262245  334 261911.000  35.388  9.43822764779021    43.668
261504.000  33.349  261911  407 261504.000  43.668  9.32032609691307    33.349
261253.000  43.668  261504  251 261253.000  33.349  7.52646256259558    43.668
261185.000  42.660  261253  68  261185.000  43.668  1.55720436017221    42.660
260842.000  33.898  261185  343 260842.000  42.66   8.04031879981247    33.898
260587.000  38.216  260842  255 260587.000  33.898  7.52256770310933    38.216
260281.000  35.269  260587  306 260281.000  38.216  8.00711743772242    35.269
260025.000  40.298  260281  256 260025.000  35.269  7.25849896509683    40.298
 

Como se puede observar los resultados correctos siempre inician en el registro 2 del regreso de la consulta.

Saludos