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

Duda con sum y group by

Estas en el tema de Duda con sum y group by en el foro de Bases de Datos General en Foros del Web. Hola foreros. Tengo una duda. Quisiera hacer una consulta y no se me ocurre como hacerla de manera simple. Tampoco estoy seguro de que se ...
  #1 (permalink)  
Antiguo 07/03/2012, 13:22
 
Fecha de Ingreso: agosto-2008
Mensajes: 198
Antigüedad: 11 años, 4 meses
Puntos: 27
Duda con sum y group by

Hola foreros. Tengo una duda. Quisiera hacer una consulta y no se me ocurre como hacerla de manera simple. Tampoco estoy seguro de que se pueda.

Miren, la tabla a consultar es algo así:
-------------------------------------
cantidad | codigo_producto
500 | 1
100 | 2
150 | 2
300 | 1
400 | 1
-------------------------------------

En realidad hay más campos, como la PK y otros datos pero para la consulta sólo me interesan esos. Lo que yo quiero es obtener filas que sumen la cantidad, pero si dicha suma supera un valor x, colocarla en otra fila.

Me explico un poco mejor con un ejemplo. Supongamos que x es 1000. Yo de momento tengo esta query:
Código:
SELECT sum(cantidad) AS suma,codigo_producto FROM detalle GROUP BY codigo_producto;
esto devolverá:
-------------------------------------
suma | codigo_producto
1200 | 1
250 | 2
-------------------------------------
1200 es más que 1000, a mí me hubiese gustado obtener algo como:

-------------------------------------
suma | codigo_producto
1000 | 1
200 | 1
250 | 2
-------------------------------------

Igual me hubiera servido:
-------------------------------------
suma | codigo_producto
800 | 1
400 | 1
250 | 2
-------------------------------------
es decir, sin necesidad de dividar la fila con 300, o la de 400 en 2 partes.

Realmente no se si se pueda sin usar código en la app, o programar algún procedimiento almacenado.

Saludos.

P.D.:Uso postgresql 8.4. No lo he actualizado para trabajar con la misma versión que hay en el server, al cual no puedo acceder directamente.
  #2 (permalink)  
Antiguo 07/03/2012, 15:21
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 12 años, 11 meses
Puntos: 447
Respuesta: Duda con sum y group by

Hola refreegrata:

Lamentablemente no he trabajado con Postgres, por lo que no puedo darte un ejemplo real, pero sí puedo darte una lógica para resolver el problema y mostrarte cómo lo hice con MySQL... sería cuestión de que busques la equivalencia.

En primer lugar, sería necesario numerar tus registros, para el ejemplo voy a considerar una tabla más o menos así:

Código MySQL:
Ver original
  1. mysql> SELECT * FROM detalle;
  2. +------+----------+-----------------+
  3. | id   | cantidad | codigo_producto |
  4. +------+----------+-----------------+
  5. |    1 |      500 |               1 |
  6. |    2 |      200 |               2 |
  7. |    3 |      300 |               1 |
  8. |    4 |      100 |               1 |
  9. |    5 |      100 |               2 |
  10. |    6 |      200 |               2 |
  11. |    7 |      800 |               1 |
  12. |    8 |      600 |               1 |
  13. |    9 |      500 |               2 |
  14. |   10 |     1000 |               2 |
  15. +------+----------+-----------------+
  16. 10 rows in set (0.00 sec)

El primer paso sería obtener una frecuencia acumulada para cada registro (para eso era necesaria el campo ID) que sería obtener la suma de todos los registros que tengan un id menor o igual al que se está evaluando:

Código MySQL:
Ver original
  1. mysql> SELECT T1.id, T1.codigo_producto, T1.cantidad,
  2.     -> (SELECT SUM(cantidad)
  3.     -> FROM detalle T2
  4.     -> WHERE T1.codigo_producto = T2.codigo_producto
  5.     -> AND T2.id <= T1.id) cantidad_acumulada
  6.     -> FROM detalle T1
  7.     -> ORDER BY codigo_producto, id;
  8. +------+-----------------+----------+--------------------+
  9. | id   | codigo_producto | cantidad | cantidad_acumulada |
  10. +------+-----------------+----------+--------------------+
  11. |    1 |               1 |      500 |                500 |
  12. |    3 |               1 |      300 |                800 |
  13. |    4 |               1 |      200 |               1000 |
  14. |    7 |               1 |      800 |               1800 |
  15. |    8 |               1 |      600 |               2400 |
  16. |    2 |               2 |      200 |                200 |
  17. |    5 |               2 |      100 |                300 |
  18. |    6 |               2 |      200 |                500 |
  19. |    9 |               2 |      500 |               1000 |
  20. |   10 |               2 |     1000 |               2000 |
  21. +------+-----------------+----------+--------------------+
  22. 10 rows in set (0.06 sec)

Observa que ordene los registros por codigo_producto id para que sea más claro cómo se obtiene la acumulada.

A partir de esto, la lógica fue hacer grupos obteniendo la división entera de la cantidad_acumulada entre la cantidad (1000), es decir todos los valores comprendidos entre 1 y 999 tendrían una división entera de 0, los valores entre 1000 y 1999 tendrían una división entera de 1 y así sucesivamente, observa que hay un problema con el 1000, pues en debería pertenecer al primer grupo, para eso utilicé una condición IF evaluando el residuo , de tal suerte que la consulta quedaría así:

Código MySQL:
Ver original
  1. mysql> SELECT
  2.     -> T.*, IF(cantidad_acumulada MOD 1000 = 0,
  3.     -> (cantidad_acumulada DIV 1000) - 1,
  4.     -> (cantidad_acumulada DIV 1000)) division_entera
  5.     -> FROM
  6.     -> (SELECT T1.id, T1.codigo_producto, T1.cantidad,
  7.     -> (SELECT SUM(cantidad)
  8.     -> FROM detalle T2
  9.     -> WHERE T1.codigo_producto = T2.codigo_producto
  10.     -> AND T2.id <= T1.id) cantidad_acumulada
  11.     -> FROM detalle T1 ) T
  12.     -> ORDER BY codigo_producto, id;
  13. +------+-----------------+----------+--------------------+-----------------+
  14. | id   | codigo_producto | cantidad | cantidad_acumulada | division_entera |
  15. +------+-----------------+----------+--------------------+-----------------+
  16. |    1 |               1 |      500 |                500 |               0 |
  17. |    3 |               1 |      300 |                800 |               0 |
  18. |    4 |               1 |      200 |               1000 |               0 |
  19. |    7 |               1 |      800 |               1800 |               1 |
  20. |    8 |               1 |      600 |               2400 |               2 |
  21. |    2 |               2 |      200 |                200 |               0 |
  22. |    5 |               2 |      100 |                300 |               0 |
  23. |    6 |               2 |      200 |                500 |               0 |
  24. |    9 |               2 |      500 |               1000 |               0 |
  25. |   10 |               2 |     1000 |               2000 |               1 |
  26. +------+-----------------+----------+--------------------+-----------------+
  27. 10 rows in set (0.00 sec)

Finalmente, utilizas esta columna división entera para agrupar:

Código MySQL:
Ver original
  1. mysql> SELECT
  2.     -> codigo_producto, SUM(cantidad) cantidad_final
  3.     -> FROM
  4.     -> (SELECT T1.id, T1.codigo_producto, T1.cantidad,
  5.     -> (SELECT SUM(cantidad) cantidad
  6.     -> FROM detalle T2
  7.     -> WHERE T1.codigo_producto = T2.codigo_producto
  8.     -> AND T2.id <= T1.id) cantidad_acumulada
  9.     -> FROM detalle T1 ) T
  10.     -> GROUP BY
  11.     -> codigo_producto,
  12.     -> IF(cantidad_acumulada MOD 1000 = 0,
  13.     -> (cantidad_acumulada DIV 1000) - 1,
  14.     -> (cantidad_acumulada DIV 1000))
  15.     -> ORDER BY codigo_producto, id;
  16. +-----------------+----------------+
  17. | codigo_producto | cantidad_final |
  18. +-----------------+----------------+
  19. |               1 |           1000 |
  20. |               1 |            800 |
  21. |               1 |            600 |
  22. |               2 |           1000 |
  23. |               2 |           1000 |
  24. +-----------------+----------------+
  25. 5 rows in set (0.00 sec)

La consulta es algo compleja, pero espero que con la explicación quede un poco más clara. Es posible que exista manera de simplificarla, pero por lo pronto es lo que se me ocurrió. Finalmente podrías también optar por usar un SP o hacerlo mediante programación, pero creo que la lógica tendría que ser semejante a lo que pongo.

Saludos y espero que te sirva el código.
Leo.
  #3 (permalink)  
Antiguo 08/03/2012, 11:15
 
Fecha de Ingreso: agosto-2008
Mensajes: 198
Antigüedad: 11 años, 4 meses
Puntos: 27
Respuesta: Duda con sum y group by

Vale, muchas gracias, agradezco la respuesta y el tiempo que te diste para explicarla de forma tan detallada. Gracias a eso pude entender el código, voy a tratar de adaptarlo a mi tabla.

Saludos, eres un grande.

Etiquetas: group, sum, tabla, campos
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 17:21.