Duda con sum y group by

[refreegrata]

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.

[leonardo_josue]

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:

mysql> SELECT * FROM detalle;
+------+----------+-----------------+
| id   | cantidad | codigo_producto |
+------+----------+-----------------+
|    1 |      500 |               1 |
|    2 |      200 |               2 |
|    3 |      300 |               1 |
|    4 |      100 |               1 |
|    5 |      100 |               2 |
|    6 |      200 |               2 |
|    7 |      800 |               1 |
|    8 |      600 |               1 |
|    9 |      500 |               2 |
|   10 |     1000 |               2 |
+------+----------+-----------------+
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:

mysql> SELECT T1.id, T1.codigo_producto, T1.cantidad,
    -> (SELECT SUM(cantidad)
    -> FROM detalle T2
    -> WHERE T1.codigo_producto = T2.codigo_producto
    -> AND T2.id <= T1.id) cantidad_acumulada
    -> FROM detalle T1
    -> ORDER BY codigo_producto, id;
+------+-----------------+----------+--------------------+
| id   | codigo_producto | cantidad | cantidad_acumulada |
+------+-----------------+----------+--------------------+
|    1 |               1 |      500 |                500 |
|    3 |               1 |      300 |                800 |
|    4 |               1 |      200 |               1000 |
|    7 |               1 |      800 |               1800 |
|    8 |               1 |      600 |               2400 |
|    2 |               2 |      200 |                200 |
|    5 |               2 |      100 |                300 |
|    6 |               2 |      200 |                500 |
|    9 |               2 |      500 |               1000 |
|   10 |               2 |     1000 |               2000 |
+------+-----------------+----------+--------------------+
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:

mysql> SELECT
    -> T.*, IF(cantidad_acumulada MOD 1000 = 0,
    -> (cantidad_acumulada DIV 1000) - 1,
    -> (cantidad_acumulada DIV 1000)) division_entera
    -> FROM
    -> (SELECT T1.id, T1.codigo_producto, T1.cantidad,
    -> (SELECT SUM(cantidad)
    -> FROM detalle T2
    -> WHERE T1.codigo_producto = T2.codigo_producto
    -> AND T2.id <= T1.id) cantidad_acumulada
    -> FROM detalle T1 ) T
    -> ORDER BY codigo_producto, id;
+------+-----------------+----------+--------------------+-----------------+
| id   | codigo_producto | cantidad | cantidad_acumulada | division_entera |
+------+-----------------+----------+--------------------+-----------------+
|    1 |               1 |      500 |                500 |               0 |
|    3 |               1 |      300 |                800 |               0 |
|    4 |               1 |      200 |               1000 |               0 |
|    7 |               1 |      800 |               1800 |               1 |
|    8 |               1 |      600 |               2400 |               2 |
|    2 |               2 |      200 |                200 |               0 |
|    5 |               2 |      100 |                300 |               0 |
|    6 |               2 |      200 |                500 |               0 |
|    9 |               2 |      500 |               1000 |               0 |
|   10 |               2 |     1000 |               2000 |               1 |
+------+-----------------+----------+--------------------+-----------------+
10 rows in set (0.00 sec)
 

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

Código MySQL:

mysql> SELECT
    -> codigo_producto, SUM(cantidad) cantidad_final
    -> FROM
    -> (SELECT T1.id, T1.codigo_producto, T1.cantidad,
    -> (SELECT SUM(cantidad) cantidad
    -> FROM detalle T2
    -> WHERE T1.codigo_producto = T2.codigo_producto
    -> AND T2.id <= T1.id) cantidad_acumulada
    -> FROM detalle T1 ) T
    -> GROUP BY
    -> codigo_producto,
    -> IF(cantidad_acumulada MOD 1000 = 0,
    -> (cantidad_acumulada DIV 1000) - 1,
    -> (cantidad_acumulada DIV 1000))
    -> ORDER BY codigo_producto, id;
+-----------------+----------------+
| codigo_producto | cantidad_final |
+-----------------+----------------+
|               1 |           1000 |
|               1 |            800 |
|               1 |            600 |
|               2 |           1000 |
|               2 |           1000 |
+-----------------+----------------+
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.

[refreegrata]

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.