Início / dba / Perguntas / 329987
Accepted
Sarov
Asked: 2023-08-05 03:03:40 +0800 CST

Consulta para agrupamento comparando a soma do valor do grupo, parando quando a quantidade é atingida

  • 772

Tentando calcular valores para um relatório FIFO. Existe uma maneira de usar o SQL para agrupar dados de forma que você compare a soma de um valor específico com valores diferentes para cada grupo, incluindo apenas parte de uma linha, se necessário?

Solicitação: Para cada número de item, ordene os itens comprados em ordem decrescente por data e obtenha o valor máximo de modo que a soma das quantidades seja = a quantidade vendida para esse número de item, tomando apenas uma parte da linha final, se necessário obter as quantidades correspondentes. Em seguida, gere um resultado contendo ItemNumber, NumShipments e CostSum (que é a soma do produto de CostPer e Quantity) para o estoque restante que não foi vendido.

Por exemplo, considere o seguinte esquema:

CREATE TABLE MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , QUANTITY_SOLD DECIMAL(5, 2) NOT HIDDEN , PRIMARY KEY (ITEM_NUMBER)  ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;

CREATE TABLE MYLIB.ITEM_PURCHASE (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , DATE_ORDERED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL NOT HIDDEN , QUANTITY DECIMAL(4, 2) NOT NULL NOT HIDDEN , COST_PER DECIMAL(10, 2) NOT NULL NOT HIDDEN , PRIMARY KEY (DATE_ORDERED, ITEM_NUMBER) ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;

INSERT INTO MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER, QUANTITY_SOLD) VALUES ('APPLE', 5);

INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 1.23);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 4, 2.34);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 5.55);

Resultado esperado:

{ITEM_NUMBER: "APPLE", QTY_REMAINING: 3, COST_SUM: 13.44}

Porque 5,55+5,55+2,34 = 13,44.

db2

1 respostas

  1. Best Answer

    Não tenho certeza se entendi muito bem a descrição verbal do problema, mas pelo que posso discernir mais o que seu resultado esperado parece sugerir, acho que você quer o seguinte. Você deseja tirar QUANTITY_SOLDe MYLIB.ITEMcompará-lo com o total atual de QUANTITY(por ITEM_NUMBER) em MYLIB.ITEM_PURCHASEconforme obtido na ordem crescente de DATE_ORDERED. Em seguida, você deseja agregar as linhas em que o total acumulado excede a quantidade vendida, ajustando QUANTITYa primeira dessas linhas para refletir a quantidade total subtraída.

    Ou, em termos SQL, algo assim:

    WITH
  item_purchase_with_RT AS
  ( -- get running totals of QUANTITY per ITEM_NUMBER
    SELECT
      *
    , SUM(QUANTITY) OVER (PARTITION BY ITEM_NUMBER ORDER BY DATE_ORDERED ASC)
      AS QUANTITY_RT
    FROM
      ITEM_PURCHASE
  )
, item_purchase_decreased AS
  ( -- filter out the rows where the running total is fully covered by QUANTITY_SOLD;
    -- adjust QUANTITY on those where the running total just about covers it
    SELECT
      p.ITEM_NUMBER
    , CASE
        WHEN p.QUANTITY > (p.QUANTITY_RT - s.QUANTITY_SOLD)
        THEN p.QUANTITY_RT - s.QUANTITY_SOLD
        ELSE p.QUANTITY
      END AS QUANTITY_ADJ
    , p.COST_PER
    FROM
      item_purchase_with_RT AS p
      INNER JOIN TOTAL_ITEM_SOLD AS s ON p.ITEM_NUMBER = s.ITEM_NUMBER
    WHERE
      s.QUANTITY_SOLD < p.QUANTITY_RT
  )
SELECT
  ITEM_NUMBER
, SUM(QUANTITY_ADJ) AS QTY_REMAINING
, SUM(QUANTITY_ADJ * COST_PER) AS COST_SUM
FROM
  item_purchase_decreased
GROUP BY
  ITEM_NUMBER
;

    Saída:

    NÚMERO DE ITEM QTY_REMAINING COST_SUM
    MAÇÃ 3,00 13.4400

    Violino .

    Nota: esta solução assume que MYLIB.TOTAL_ITEM_SOLDcontém uma linha por ITEM_NUMBER. Se esse não for o caso, você pode agregar QUANTITY_SOLDpor ITEM_NUMBERe usar o conjunto agregado na junção em vez da tabela original.

    • 1

relate perguntas