funciona - manual de postgresql 10 en español pdf

Cálculo de suma acumulativa en PostgreSQL

Question

Quiero encontrar la cantidad acumulativa o en ejecución del campo e insertarlo de la transición a la tabla. Mi estructura de etapas es algo como esto:

ea_month id amount ea_year circle_id
April 92570 1000 2014 1
April 92571 3000 2014 2
April 92572 2000 2014 3
March 92573 3000 2014 1
March 92574 2500 2014 2
March 92575 3750 2014 3
February 92576 2000 2014 1
February 92577 2500 2014 2
February 92578 1450 2014 3

Quiero que mi tabla de objetivos tenga el siguiente aspecto:

ea_month id amount ea_year circle_id cum_amt
February 92576 1000 2014 1 1000
March 92573 3000 2014 1 4000
April 92570 2000 2014 1 6000
February 92577 3000 2014 2 3000
March 92574 2500 2014 2 5500
April 92571 3750 2014 2 9250
February 92578 2000 2014 3 2000
March 92575 2500 2014 3 4500
April 92572 1450 2014 3 5950

Estoy realmente muy confundido con la forma de lograr este resultado. Quiero lograr este resultado usando PostgreSQL.

¿Alguien puede sugerir cómo lograr este conjunto de resultados?

Answer 1

Básicamente, necesitas una función de ventana aquí. Esa es una característica estándar hoy en día. Además de las funciones de ventana genuinas, puede usar cualquier función agregada como función de ventana en Postgres al agregar una cláusula OVER .

La dificultad especial aquí es obtener particiones y ordenar correctamente:

SELECT ea_month, id, amount, ea_year, circle_id
, sum(amount) OVER (PARTITION BY circle_id ORDER BY ea_year, ea_month) AS cum_amt
FROM tbl
ORDER BY circle_id, month;

Y no GROUP BY aquí.

La suma de cada fila se calcula desde la primera fila en la partición hasta la fila actual, o citando el manual para ser precisos:

La opción de marco predeterminada es RANGE UNBOUNDED PRECEDING , que es lo mismo que RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . Con ORDER BY , establece que el marco sea todas las filas desde el inicio de la partición hasta el último par ORDER BY la fila actual .

... cuál es la suma acumulativa o corriente que buscas. Negrita énfasis mío.

Las filas con el mismo (circle_id, ea_year, ea_month) son "pares" en esta consulta. Todos ellos muestran la misma suma acumulada con todos los compañeros agregados a la suma. Pero supongo que su tabla es UNIQUE en (circle_id, ea_year, ea_month) , entonces el orden de clasificación es determinista y ninguna fila tiene peers.

Ahora, ORDER BY ... ea_month no funcionará con cadenas de nombres de meses . Postgres ordenaría alfabéticamente según la configuración regional.

Si tiene valores de date reales almacenados en su tabla, puede ordenarlos correctamente. Si no, sugiero reemplazar ea_year y ea_month con una única columna de tipo date en su tabla.

• Transforme lo que tiene con to_date() :

  to_date(ea_year || ea_month , ''YYYYMonth'') AS mon

• Para visualizar, puede obtener cadenas originales con to_date() :

  to_char(mon, ''Month'') AS ea_month
  to_char(mon, ''YYYY'') AS ea_year

Mientras está atascado con el desafortunado diseño, esto funcionará:

SELECT ea_month, id, amount, ea_year, circle_id
, sum(amount) OVER (PARTITION BY circle_id ORDER BY mon) AS cum_amt
FROM (SELECT *, to_date(ea_year || ea_month, ''YYYYMonth'') AS mon FROM tbl)
ORDER BY circle_id, mon;