主页 / dba / 问题 / 329987
Accepted
Sarov
Asked: 2023-08-05 03:03:40 +0800 CST

通过比较分组值的总和来查询分组，达到数量时停止

  • 772

尝试计算 FIFO 报告的值。有没有一种方法使用 SQL 对数据进行分组，以便将特定值的总和与每个组的不同值进行比较，包括在必要时仅取行的一部分？

请求：对于每个商品编号，按日期对已购买商品进行降序排序，并获取最大金额，使数量总和 = 该商品编号的已售出金额，如有必要，仅取最后一行的一部分获得匹配的数量。然后输出未售出剩余库存的结果，其中包含 ItemNumber、NumShipments 和 CostSum（CostPer 与 Quantity 的乘积之和）。

例如，考虑以下架构：

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);

预期结果：

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

因为 5.55+5.55+2.34 = 13.44。

db2

1 个回答

  1. Best Answer

    我不确定我是否很好地理解了问题的口头描述，但从我所能辨别的以及您的预期结果似乎表明的内容来看，我认为您想要以下内容。您想要QUANTITY_SOLD从 中获取并将其与(per ) in 中MYLIB.ITEM的运行总计进行匹配（按 的升序排列）。然后，您想要聚合运行总计超过销售数量的行，调整第一个此类行以反映减去的总数量。QUANTITYITEM_NUMBERMYLIB.ITEM_PURCHASEDATE_ORDEREDQUANTITY

    或者，用 SQL 术语来说，是这样的：

    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
;

    输出：

    项目编号 剩余数量 COST_SUM
    苹果 3.00 13.4400

    小提琴。

    注意：此解决方案假设MYLIB.TOTAL_ITEM_SOLD每个 包含一行ITEM_NUMBER。如果情况并非如此，您可以聚合QUANTITY_SOLD每个ITEM_NUMBER并在连接中使用聚合集而不是原始表。

    • 1

相关问题