Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

Obtener y calcular las ultimo dos registros de cada Elemento

Estas en el tema de Obtener y calcular las ultimo dos registros de cada Elemento en el foro de Mysql en Foros del Web. Hola amigos, estoy tratando de obtener y calcular la últimos dos campos del registro de cada usuario. ejemplo: @import url("http://static.forosdelweb.com/clientscript/vbulletin_css/geshi.css"); Código MySQL: Ver original | ...
  #1 (permalink)  
Antiguo 01/03/2017, 14:02
Avatar de gjx2  
Fecha de Ingreso: agosto-2008
Ubicación: R.D
Mensajes: 1.153
Antigüedad: 11 años, 3 meses
Puntos: 139
Obtener y calcular las ultimo dos registros de cada Elemento

Hola amigos, estoy tratando de obtener y calcular la últimos dos campos del registro de cada usuario.
ejemplo:


Código MySQL:
Ver original
  1. |   fecha    |  elem | costo  |
  2. | -------    |-------|--------|
  3. | 2017-01-02 |  jose |  32.33 |
  4. | 2017-01-03 |  jose |  11.63 |
  5. | 2017-01-04 |  jose | 484.23 |
  6. | 2017-01-05 |  jose | 292.43 |
  7. | 2017-01-01 | pedro | 532.53 |
  8. | 2017-01-03 | pedro |   2332 |
  9. | 2017-01-04 | pedro | 352.13 |
  10. | 2017-01-01 |  juan |   6732 |
  11. | 2017-01-03 |  juan | 422.63 |
  12. | 2017-01-05 |  juan | 552.43 |

RESULTADO:


Código MySQL:
Ver original
  1. |   fecha1   | fecha2     | elem | total  |
  2. | -----------|------------|------|--------|
  3. | 2017-01-04 | 2017-01-05 | jose | 776.66 |
  4. | 2017-01-03 | 2017-01-04 | pedro| 2684.13|
  5. | 2017-01-03 | 2017-01-05 | juan | 975.06 |
Me pueden dar una manito para completar este Query?
He intentado de todas forma y nada.

sqlfiddle
  #2 (permalink)  
Antiguo 01/03/2017, 16:45
Avatar de wunderwaffen077  
Fecha de Ingreso: marzo-2014
Mensajes: 45
Antigüedad: 5 años, 8 meses
Puntos: 3
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

Ya que solo haces uso de una tabla tiene que calcular varios valores agrupados.

Código SQL:
Ver original
  1. /*OBTENER FECHA 1*/
  2.  SELECT fecha FROM tabla
  3.  ORDER BY fecha DESC
  4.  LIMIT 1;
  5.  
  6.  /*OBTENER FECHA 2*/
  7.  SELECT fecha FROM tabla
  8.  ORDER BY fecha DESC
  9.  LIMIT 1,1;
  10.  
  11.  /*OBTENER COSTO POR USUARIO*/
  12.  SELECT SUM(costo) AS costo FROM tabla
  13. WHERE usuario='jose'
  14. ;
  15.  
  16.  /*AGRUPAR POR USUARIO, Y AÑADIR ACUMULADOS*/
  17.  
  18.  SELECT
  19.  (SELECT fecha FROM tabla  ORDER BY fecha DESC  LIMIT 1) AS fecha1,
  20.  (SELECT fecha FROM tabla  ORDER BY fecha DESC  LIMIT 1,1) AS fecha2,
  21.  (SELECT SUM(costo) AS costo FROM tabla WHERE usuario=t.usuario) AS suma_costo,
  22.  t.usuario
  23.  FROM tabla t
  24.  GROUP BY t.usuario;
  25.  
  26.  
  27. /*CON TUS DATOS SERIA ASI*/
  28.  SELECT
  29.  (SELECT fecha FROM tbDemo  ORDER BY fecha DESC  LIMIT 1,1) AS fecha1,
  30.  (SELECT fecha FROM tbDemo  ORDER BY fecha DESC  LIMIT 1) AS fecha2,
  31.  (SELECT SUM(costo) AS costo FROM tbDemo WHERE elem=t.elem) AS suma_costo,
  32.  t.elem
  33.  FROM tbDemo t
  34.  GROUP BY t.elem;

[/PHP]

Última edición por gnzsoloyo; 03/03/2017 a las 08:08 Razón: falto datos
  #3 (permalink)  
Antiguo 02/03/2017, 14:03
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 12 años, 10 meses
Puntos: 447
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

Hola gjx2:

Tienes muchas formas de resolver tu consulta, algunas más complejas que otras, aquí te pongo otro ejemplo que puede resultarte más simple.

Lo que quieres hacer sería el equivalente a obtener la función ROW_NUM de tus datos, si tienes dudas investiga un poco a qué se refiere, lamentablemente MySQL no tiene esta función, aunque hay algunas formas de implementarla. Supongamos tus datos de ejemplo:

Código MySQL:
Ver original
  1. mysql> SELECT * FROM tabla;
  2. +------------+-------+---------+
  3. | fecha      | elem  | costo   |
  4. +------------+-------+---------+
  5. | 2017-01-02 | jose  |   32.33 |
  6. | 2017-01-03 | jose  |   11.63 |
  7. | 2017-01-04 | jose  |  484.23 |
  8. | 2017-01-05 | jose  |  292.43 |
  9. | 2017-01-01 | pedro |  532.53 |
  10. | 2017-01-03 | pedro | 2332.00 |
  11. | 2017-01-04 | pedro |  352.13 |
  12. | 2017-01-01 | juan  | 6732.00 |
  13. | 2017-01-03 | juan  |  422.63 |
  14. | 2017-01-05 | juan  |  552.43 |
  15. +------------+-------+---------+
  16. 10 rows in set (0.00 sec)

la función ROW_NUM lo que hace es "numerar" cada registro para asignarle una posición o "rank", una forma de hacerlo es con una subconsulta y con la función COUNT, así:

Código MySQL:
Ver original
  1. mysql> SELECT T1.fecha, T1.elem, T1.costo, COUNT(*) AS row_number
  2.     -> FROM tabla T1
  3.     -> INNER JOIN  tabla t2 ON T1.elem =  T2.elem
  4.     ->                         AND T1.fecha <= T2.fecha
  5.     -> GROUP BY T1.elem, T1.fecha DESC;
  6. +------------+-------+---------+------------+
  7. | fecha      | elem  | costo   | row_number |
  8. +------------+-------+---------+------------+
  9. | 2017-01-05 | jose  |  292.43 |          1 |
  10. | 2017-01-04 | jose  |  484.23 |          2 |
  11. | 2017-01-03 | jose  |   11.63 |          3 |
  12. | 2017-01-02 | jose  |   32.33 |          4 |
  13. | 2017-01-05 | juan  |  552.43 |          1 |
  14. | 2017-01-03 | juan  |  422.63 |          2 |
  15. | 2017-01-01 | juan  | 6732.00 |          3 |
  16. | 2017-01-04 | pedro |  352.13 |          1 |
  17. | 2017-01-03 | pedro | 2332.00 |          2 |
  18. | 2017-01-01 | pedro |  532.53 |          3 |
  19. +------------+-------+---------+------------+
  20. 10 rows in set (0.00 sec)

Lo que se hace es ordenar las por elem y FECHAS, y contar el número de elementos que hay antes que una fecha en específico, en lo particular a ti te interesa encontrar sólo los dos últimos registros, entonces utilizas la cláusula HAVING para filtrar solo los que te interesan;

Código MySQL:
Ver original
  1. mysql> SELECT T1.fecha, T1.elem, T1.costo, COUNT(*) AS row_number
  2.     -> FROM tabla T1
  3.     -> INNER JOIN  tabla t2 ON T1.elem =  T2.elem
  4.     ->                         AND T1.fecha <= T2.fecha
  5.     -> GROUP BY T1.elem, T1.fecha DESC
  6.     -> HAVING COUNT(*) <= 2;
  7. +------------+-------+---------+------------+
  8. | fecha      | elem  | costo   | row_number |
  9. +------------+-------+---------+------------+
  10. | 2017-01-05 | jose  |  292.43 |          1 |
  11. | 2017-01-04 | jose  |  484.23 |          2 |
  12. | 2017-01-05 | juan  |  552.43 |          1 |
  13. | 2017-01-03 | juan  |  422.63 |          2 |
  14. | 2017-01-04 | pedro |  352.13 |          1 |
  15. | 2017-01-03 | pedro | 2332.00 |          2 |
  16. +------------+-------+---------+------------+
  17. 6 rows in set (0.00 sec)

Ahora, esto puedes meterlo en una subconsulta y utilizar las funciones de agrupación MIN, MAX y SUM para obtener el resultado que necesitas:

Código MySQL:
Ver original
  1. mysql> SELECT MIN(fecha) fecha1, MAX(fecha) fecha2, elem, SUM(costo) total
  2.     -> FROM
  3.     -> ( SELECT T1.fecha, T1.elem, T1.costo, COUNT(*) AS row_number
  4.     ->   FROM tabla T1
  5.     ->   INNER JOIN  tabla t2 ON T1.elem =  T2.elem
  6.     ->                           AND T1.fecha <= T2.fecha
  7.     ->   GROUP BY T1.elem, T1.fecha DESC
  8.     ->   HAVING COUNT(*) <= 2 ) T
  9.     -> GROUP BY elem;
  10. +------------+------------+-------+---------+
  11. | fecha1     | fecha2     | elem  | total   |
  12. +------------+------------+-------+---------+
  13. | 2017-01-04 | 2017-01-05 | jose  |  776.66 |
  14. | 2017-01-03 | 2017-01-05 | juan  |  975.06 |
  15. | 2017-01-03 | 2017-01-04 | pedro | 2684.13 |
  16. +------------+------------+-------+---------+
  17. 3 rows in set (0.00 sec)

Es muy importante hacerte notar que esta consulta SÓLO FUNCIONA SI NO TIENES FECHAS REPETIDAS EN TU TABLA PARA UN MISMO ELEMENTO, si fuera el caso, entonces esta técnica no funciona.

Te recomiendo que le des un vistazo a esta página:

http://www.artfulsoftware.com/infotr...ip.php?id=1098

http://www.artfulsoftware.com/infotree/queries.php

Ahí vienen muchas consultas de alto nivel que te pueden servir.

Saludos
Leo.
  #4 (permalink)  
Antiguo 02/03/2017, 17:06
Avatar de gjx2  
Fecha de Ingreso: agosto-2008
Ubicación: R.D
Mensajes: 1.153
Antigüedad: 11 años, 3 meses
Puntos: 139
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

Hola wunderwaffen077 ,
Mucha gracias por tu tiempo y dedicación.
  #5 (permalink)  
Antiguo 02/03/2017, 17:07
Avatar de gjx2  
Fecha de Ingreso: agosto-2008
Ubicación: R.D
Mensajes: 1.153
Antigüedad: 11 años, 3 meses
Puntos: 139
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

Gracias leonardo_josue por el código y los links.
Finalmente me funciono a la perfección TU solución.

Gracias amigos.
  #6 (permalink)  
Antiguo 29/05/2017, 12:39
Avatar de gerardo_goh  
Fecha de Ingreso: noviembre-2008
Mensajes: 107
Antigüedad: 11 años
Puntos: 1
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

Muchas gracias leonardo_josue, tu aportación me ayudo demasiado, ya que yo tenia el mismo problema, como siempre excelentes aportaciones leo, saludos!!!
  #7 (permalink)  
Antiguo 31/05/2017, 14:05
Avatar de gerardo_goh  
Fecha de Ingreso: noviembre-2008
Mensajes: 107
Antigüedad: 11 años
Puntos: 1
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

hola buenas tardes, eh tratado de acoplar el query y no he podido obtener el resultado, la intencion es obtener las dos ultimas fechas por cada cliente por cada item entregado en cada tienda, ya que necesito saber que tanto se mueve el item por cliente.


Código SQL:
Ver original
  1. CREATE TABLE tblventas_almacen (fec_solicitud       DATE        NULL,
  2.                                 id_compra           tinytext    NULL,
  3.                                 id_cliente          tinytext    NULL,
  4.                                 id_tienda           tinytext    NULL,
  5.                                 item                tinytext    NULL,
  6.                                 desc_item           tinytext    NULL,
  7.                                 cantidad            FLOAT(4)    NULL,
  8.                                 status_compra       tinytext    NULL,
  9.                                 fec_entrega         DATE        NULL,
  10.                                 origen              tinytext    NULL);





aqui esta mi bd


Código SQL:
Ver original
  1. fec_solicitud|id_compra|id_cliente|id_tienda|item|desc_item|cantidad|status_compra|fec_entrega|origen
  2. 2017/04/20|A-7878|C-2563|MX-15|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|20|ENTREGADO|2017/04/22|tblventas_almacen
  3. 2017/04/20|A-7878|C-2563|MX-15|A-2266|REPRODUCTOR MP3 8GB SONY NEGRO|3|ENTREGADO|2017/04/22|tblventas_almacen
  4. 2017/04/20|A-7878|C-2563|MX-15|B-5566|ESTEREO PARA CARRO SONY MOD.3456MLP|5|ENTREGADO|2017/04/22|tblventas_almacen
  5. 2017/04/22|B-1516|O-1155|MX-17|C-5444|BOCINAS PARA CARRO SONY 5 VIAS DE 16x24CMS|20|ENTREGADO|2017/04/23|tblventas_almacen
  6. 2017/04/22|B-1516|O-1155|MX-17|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|15|ENTREGADO|2017/04/23|tblventas_almacen
  7. 2017/04/23|A-8888|C-2563|MX-16|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|45|ENTREGADO|2017/04/26|tblventas_almacen
  8. 2017/04/23|A-8888|C-2563|MX-16|A-2266|REPRODUCTOR MP3 8GB SONY NEGRO|8|ENTREGADO|2017/04/26|tblventas_almacen
  9. 2017/04/23|A-8888|C-2563|MX-16|A-3131|REPRODUCTOR MP3 16GB SONY NEGRO|20|ENTREGADO|2017/04/26|tblventas_almacen
  10. 2017/04/24|S-1256|Y-1474|MX-16|B-9066|ESTEREO PARA CARRO PIONEER MOD.5669|5|ENTREGADO|2017/04/28|tblventas_almacen
  11. 2017/04/24|S-1256|Y-1474|MX-16|A-3131|REPRODUCTOR MP3 16GB SONY NEGRO|3|ENTREGADO|2017/04/28|tblventas_almacen
  12. 2017/04/24|S-1256|Y-1474|MX-16|A-1260|AUDIFONOS PLUG PIONEER DJ NEGRO|2|ENTREGADO|2017/04/28|tblventas_almacen
  13. 2017/04/24|S-1256|Y-1474|MX-16|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|8|ENTREGADO|2017/04/28|tblventas_almacen
  14. 2017/04/24|S-1256|Y-1474|MX-16|C-6444|BOCINAS COAXIAL DE 5 VIAS DE 16CMS|4|ENTREGADO|2017/04/28|tblventas_almacen
  15. 2017/05/26|D-5688|C-2563|MX-15|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|10|ENTREGADO|2017/05/28|tblventas_almacen
  16. 2017/05/26|D-5688|C-2563|MX-15|B-5566|ESTEREO PARA CARRO SONY MOD.3456MLP|8|ENTREGADO|2017/05/28|tblventas_almacen
  17. 2017/05/26|D-5688|C-2563|MX-15|B-9066|ESTEREO PARA CARRO PIONEER MOD.5669|22|ENTREGADO|2017/05/28|tblventas_almacen
  18. 2017/05/26|D-5688|C-2563|MX-15|C-5444|BOCINAS PARA CARRO SONY 5 VIAS DE 16x24CMS|22|ENTREGADO|2017/05/28|tblventas_almacen
  19. 2017/05/26|D-5688|C-2563|MX-15|C-6444|BOCINAS COAXIAL DE 5 VIAS DE 16CMS|22|ENTREGADO|2017/05/28|tblventas_almacen
  20. 2017/05/28|S-1344|Y-1474|MX-16|B-9066|ESTEREO PARA CARRO PIONEER MOD.5669|8|ENTREGADO|2017/05/28|tblventas_almacen
  21. 2017/05/28|S-1344|Y-1474|MX-16|A-3131|REPRODUCTOR MP3 16GB SONY NEGRO|8|ENTREGADO|2017/05/28|tblventas_almacen
  22. 2017/05/28|S-1344|Y-1474|MX-16|B-5566|ESTEREO PARA CARRO SONY MOD.3456MLP|15|ENTREGADO|2017/05/28|tblventas_almacen
  23. 2017/05/29|S-1400|Y-1474|MX-16|B-9066|ESTEREO PARA CARRO PIONEER MOD.5669|8|ENTREGADO|2017/05/31|tblventas_almacen
  24. 2017/05/29|S-1400|Y-1474|MX-16|A-3131|REPRODUCTOR MP3 16GB SONY NEGRO|8|ENTREGADO|2017/05/31|tblventas_almacen
  25. 2017/05/31|B-1200|O-1155|MX-17|A-1256|AUDIFONOS 3.5MM SONY BLANCO 95DB|15|ENTREGADO|2017/05/31|tblventas_almacen
  26. 2017/05/31|B-1200|O-1155|MX-17|A-1260|AUDIFONOS PLUG PIONEER DJ NEGRO|15|ENTREGADO|2017/05/31|tblventas_almacen
  27. 2017/05/31|B-1200|O-1155|MX-17|A-2266|REPRODUCTOR MP3 8GB SONY NEGRO|15|ENTREGADO|2017/05/31|tblventas_almacen
  28. 2017/05/31|B-1200|O-1155|MX-17|A-3131|REPRODUCTOR MP3 16GB SONY NEGRO|15|ENTREGADO|2017/05/31|tblventas_almacen
  29. 2017/05/31|B-1200|O-1155|MX-17|B-5566|ESTEREO PARA CARRO SONY MOD.3456MLP|15|ENTREGADO|2017/05/31|tblventas_almacen
  30. 2017/05/31|B-1200|O-1155|MX-17|B-9066|ESTEREO PARA CARRO PIONEER MOD.5669|15|ENTREGADO|2017/05/31|tblventas_almacen
  31. 2017/05/31|B-1200|O-1155|MX-17|C-5444|BOCINAS PARA CARRO SONY 5 VIAS DE 16x24CMS|15|ENTREGADO|2017/05/31|tblventas_almacen
  32. 2017/05/31|B-1200|O-1155|MX-17|C-6444|BOCINAS COAXIAL DE 5 VIAS DE 16CMS|15|ENTREGADO|2017/05/31|tblventas_almacen
[/FONT]

La idea es que pueda pueda obtner el siguiente resultado

http://uploads.im/VLXCl.gif


Última edición por gnzsoloyo; 02/06/2017 a las 14:21
  #8 (permalink)  
Antiguo 31/05/2017, 14:06
Avatar de gerardo_goh  
Fecha de Ingreso: noviembre-2008
Mensajes: 107
Antigüedad: 11 años
Puntos: 1
Respuesta: Obtener y calcular las ultimo dos registros de cada Elemento

anexo mi query:

Código SQL:
Ver original
  1. SELECT t1.fec_entrega,
  2.        t1.id_compra,
  3.        t1.id_cliente,
  4.        t1.id_tienda,
  5.        t1.item,
  6.        t1.cantidad,
  7.        COUNT(*) AS ROW_NUMBER
  8. FROM tblventas_almacen t1
  9. INNER JOIN tblventas_almacen t2 ON
  10.            t1.id_cliente=t2.id_cliente AND
  11.            t1.fec_entrega <= t2.fec_entrega
  12. GROUP BY t1.id_cliente,t1.fec_entrega DESC,t1.item, t1.id_tienda
  13. ORDER BY id_cliente ASC, id_tienda ASC, item ASC;


pero veo que no me respeta la numeración de las filas

ya que con ese error no puedo poner el having, me pueden ayudar, muchas gracias.

Última edición por gerardo_goh; 06/06/2017 a las 13:19 Razón: Por favor, usar HIGHLIGHT correspondiente.

Etiquetas: cada, calcular, campos, elemento, forma, registro, registros, sql, ultimo
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 01:54.