SQL 查詢以求和日期之前的列並顯示該日期之後的所有條目

我有一個數據庫函式，可以在某個日期之前對一列求和，然後在該日期之後一個一個地顯示所有條目。該函式返回按公共欄位排序的數據，並在每次公共欄位更改時按日期應用總和。

我使用 Postgres 9.4。

該函式執行以下操作：

-- configuramos los parametros de la funcion
CREATE OR REPLACE FUNCTION "informeMovimientoStockHacienda"("fechaDesde" timestamp without time zone, "fechaHasta" timestamp without time zone)
-- configuramos el retorno con la tabla virtual
 RETURNS SETOF informe_movimiento_stock_hacienda AS
$BODY$
-- declaro los datos de la vista y las variables
DECLARE datosInforme informe_movimiento_stock_hacienda;
DECLARE datosInformeAnterior informe_movimiento_stock_hacienda;
DECLARE stockAnterior INTEGER;
DECLARE tropaAnterior INTEGER;
BEGIN
       -- @ARTEF Informe con Saldo Anterior - carga los datos para le informe calculando el saldo anterior a una fecha

   tropaAnterior := 0;
   stockAnterior := 0;

   -- recorro y proceso los datos del informe de stock
   FOR datosInforme IN SELECT * FROM movimiento_stock WHERE fecha <= "fechaHasta" ORDER BY tropa, fecha
   LOOP
       -- chequeo si cumple la tropa
       IF tropaAnterior <> datosInforme.tropa THEN 
           IF stockAnterior <> 0 THEN
               RETURN QUERY SELECT 0 AS id, datosInformeAnterior.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInformeAnterior.usr_id AS usr_id, datosInformeAnterior.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;

               -- vuelvo el saldo anterior a 0
               stockAnterior := 0;
           END IF;
       END IF; 

       -- chequeo si muestro los datos
       IF datosInforme.fecha < "fechaDesde"  THEN
           stockAnterior := stockAnterior + datosInforme.cantidad;
       ELSE

           -- devuelvo la fila con el saldo anterior
           IF stockAnterior <> 0 THEN
               RETURN QUERY SELECT 0 AS id, datosInforme.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInforme.usr_id AS usr_id, datosInforme.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;
           END IF;

           -- vuelvo el saldo anterior a 0
           stockAnterior := 0;

           -- traigo los conceptos de las filas
           RETURN NEXT datosInforme;               
       END IF;

       datosInformeAnterior := datosInforme;
       tropaAnterior := datosInforme.tropa;

   END LOOP;

   -- si sali del bucle y me falta mostrar un dato lo muestro
   IF stockAnterior <> 0 THEN
       RETURN QUERY SELECT 0 AS id, datosInformeAnterior.tropa AS tropa, "fechaDesde" AS fecha, 'Stock anterior a la fecha'::VARCHAR AS movimiento, datosInformeAnterior.usr_id AS usr_id, datosInformeAnterior.usuario AS usuario, 0 AS clasani_id, ''::VARCHAR AS clasificacion, stockAnterior AS cantidad, -1 AS tipo;

       -- vuelvo el saldo anterior a 0
       stockAnterior := 0;
   END IF;

END;
$BODY$ LANGUAGE plpgsql;

表結構：

CREATE TABLE movimiento_stock (
 id serial NOT NULL,
 tropa integer,
 fecha timestamp without time zone,
 detalle text,
 usr_id integer,
 usuario text,
 clasani_id integer,
 clasificacion text,
 cantidad numeric(19,3),
 tipo integer,
 CONSTRAINT pk_movimiento_stock PRIMARY KEY (id)
);

範例數據：

id  |tropa  |fecha      |detalle    |usr_id |usuario    |clasani_id |clasificacion  |cantidad   |tipo
1   |1000   |2015-03-01 |S Inicial  |1      |USR1       |1          |VA             |30         |1
2   |1000   |2015-03-05 |Entrada    |1      |USR1       |1          |VA             |30         |1
3   |2000   |2015-03-05 |S Inicial  |2      |USR2       |1          |VA             |50         |1
4   |1000   |2015-03-06 |Entrada    |1      |USR1       |1          |VA             |10         |1
5   |2000   |2015-03-07 |Entrada    |2      |USR2       |1          |VA             |20         |1
6   |1000   |2015-03-10 |Salida     |1      |USR1       |1          |VA             |-10        |1

期望的結果：

tropa   |fecha      |detalle    |usr_id |usuario    |clasani_id |clasificacion  |cantidad   |saldo - SUM(cantidad)
1000    |2015-03-05 |S. Ant     |1      |USR1       |1          |VA             |60         |60 (SUM BEFORE 2015-03-06 FOR tropa 1000)
1000    |2015-03-06 |Entrada    |1      |USR1       |1          |VA             |10         |70
1000    |2015-03-10 |Salida     |1      |USR1       |1          |VA             |-10        |60
2000    |2015-03-05 |S. Ant     |2      |USR2       |1          |VA             |50         |50 (SUM BEFORE 2015-03-06 FOR tropa 2000)
2000    |2015-03-07 |Entrada    |2      |USR2       |1          |VA             |20         |70

此功能有效，但難以維護和更改，我們想知道是否可以使用查詢來做同樣的事情。

WITH parametros AS (
  SELECT '2015-03-06'::timestamp AS fecha_desde  -- provide parameters here
       , '2015-03-12'::timestamp AS fecha_hasta
  )
SELECT tropa, max(fecha) AS fecha, sum(cantidad) AS sum_cantidad
    , sum(sum(cantidad)) OVER (PARTITION BY tropa
                               ORDER BY max(fecha)) AS saldo
FROM   movimiento_stock, parametros p
WHERE  fecha <= p.fecha_hasta
GROUP  BY tropa, **CASE WHEN fecha >= p.fecha_desde THEN id END**
ORDER  BY 1, 2;

核心特徵是粗體。閱讀有關CASE.

較舊的行獲得 NULL id（表達式中的預設值CASE）；id位於NOT NULL基礎表中，因此不可能發生衝突。

這會將所有早於的行分組fecha_desde到一個組中，tropa並將較新的行不分組（形成單獨的“組”）。不清楚如何聚合其他列，所以我只包括了fecha.

同樣，包裝在一個SQL 函式中：

CREATE OR REPLACE FUNCTION informe_tldr(fecha_desde timestamp
                                     , fecha_hasta timestamp)
 RETURNS TABLE(tropa int, fecha timestamp, sum_cantidad int, saldo int) AS
$func$
SELECT m.tropa, max(m.fecha), sum(m.cantidad)::int
    , sum(sum(m.cantidad)) OVER (PARTITION BY m.tropa
                                 ORDER BY max(m.fecha))::int AS saldo
FROM   movimiento_stock m
WHERE  m.fecha <= fecha_hasta
GROUP  BY m.tropa, CASE WHEN fecha >= fecha_desde THEN m.id END
ORDER  BY 1, 2;
$func$ LANGUAGE sql;

SQL小提琴。

引用自：https://dba.stackexchange.com/questions/96983