Início / dba / Perguntas / 184538
Accepted
Alin
Asked: 2017-08-29 14:07:49 +0800 CST

Possível obter ledger de histórico (saldo) com consulta sql?

  • 772

Não tenho certeza se este é o lugar para perguntar, mas aqui vai. Estou usando um servidor Postgres e para simplificar, vou tentar mostrar um breve cenário do que preciso.

CREATE TABLE operations(id,type,item,price,amount)
 AS VALUES
  ( 1::int, 'buy' , 'item1',  2, 10::int ),
  ( 2::int, 'buy' , 'item1',  3,  4::int ),
  ( 3::int, 'sell', 'item1',  4, 12::int )
;

  • Se eu passar por uma base de custo FIFO, o livro-razão ou saldos após cada pedido deve se parecer com:

    id  |  price   | amount |

1   |      2   |     10 |

2   |      2   |     10 |
    |      3   |      4 |

3   |      3   |      2 |

Uma coisa que tentei é ter uma tabela como operation_balances na qual para cada operação processada eu insiro o saldo correspondente. Isso funciona, no entanto, há um problema com a enorme quantidade de saldos inseridos para um grande número de operações, portanto, não é uma solução válida. Eu queria saber se é possível obter a parte do saldo de cada pedido como parte de uma consulta sql. Pode demorar um pouco para ser executado, mas economizaria muito espaço no banco de dados.

postgresql

1 respostas

  1. Best Answer

    Para começar, e isso é apenas para levá-lo a algum lugar,

    SELECT *,
  jsonb_agg(jsonb_build_object('p',price,'a',amount))
    FILTER (WHERE type='buy')
    OVER (PARTITION BY item ORDER BY id)
    AS cumpurchased
FROM operations;
 id | type | item  | price | amount |             cumpurchased              
----+------+-------+-------+--------+---------------------------------------
  1 | buy  | item1 |     2 |     10 | [{"a": 10, "p": 2}]
  2 | buy  | item1 |     3 |      4 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]
  3 | sell | item1 |     4 |     12 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]

    Aqui, nós serializamos as compras em arquivos cumpurchased. Isso parece simples, agora podemos explodir isso expandindo cumpurchasedpara as transações individuais.

    SELECT id, type, item, price, amount, cumsale, e, eid
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
ORDER BY id, eid;
 id | type | item  | price | amount | cumsale |         e         | eid 
----+------+-------+-------+--------+---------+-------------------+-----
  1 | buy  | item1 |     2 |     10 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 4, "p": 3}  |   2
  3 | sell | item1 |     4 |     12 |      12 | {"a": 10, "p": 2} |   1
  3 | sell | item1 |     4 |     12 |      12 | {"a": 4, "p": 3}  |   2
(5 rows)

    Você deveria estar ale para ver onde estou indo com isso...

    SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
  AS qty_remaining
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid);
 id | type | item  |         e         | eid | qty_remaining 
----+------+-------+-------------------+-----+---------------
  1 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 4, "p": 3}  |   2 |            14
  3 | sell | item1 | {"a": 10, "p": 2} |   1 |            -2
  3 | sell | item1 | {"a": 4, "p": 3}  |   2 |             2
(5 rows)

    A partir deste ponto, você precisa filtrar o -2, envolvemos a consulta novamente e colocamos essa condicional lá. Então nós apenas selecionamos a partir disso.

    SELECT id, e->>'p' AS price, least(qty_remaining,(e->>'a')::int) AS amount
FROM (
      SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
      ) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
        AS qty_remaining
      FROM (
        SELECT *,
          sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
          jsonb_agg(jsonb_build_object('p',price,'a',amount))
            FILTER (WHERE type='buy')
            OVER (PARTITION BY item ORDER BY id)
            AS cumpurchased
        FROM operations
      ) AS with_agg
      CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
      ) AS t
WHERE qty_remaining > 0;
 id | price | amount 
----+-------+--------
  1 | 2     |     10
  2 | 2     |     10
  2 | 3     |      4
  3 | 3     |      2
(4 rows)
    • 0

relate perguntas