主页 / dba / 问题 / 329883
Accepted
Sarov
Asked: 2023-08-02 05:55:03 +0800 CST

通过比较组值的总和来查询分组

  • 772

有没有一种方法使用 SQL 对数据进行分组，以便将特定值的总和与每个组的不同值进行比较？

例如，考虑以下架构：

Table: ITEM
Columns:
    ITEM_NUMBER (VARCHAR(15))
    QUANTITY_ON_HAND (DECIMAL(5,2))
Primary Key: ITEM_NUMBER

Table: ORDER_ITEM_REQUIREMENTS
Columns:
    ITEM_NUMBER (VARCHAR(15), Foreign Key to ITEM)
    DATE_ORDERED (TIMESTAMP)
    QUANTITY (DECIMAL(4,2))
    COST_PER (DECIMAL(10,2))
Primary Key: ITEM_NUMBER+DATE

请求：对于每个项目编号，按日期升序排列订单项目要求，并获取最大数量，使数量总和 <= 该项目编号的现有数量（如果总和为 1，则获取所有订单项目要求）小于现有数量）。然后输出包含 ItemNumber、NumShipments、QtySum 和 CostSum（CostPer 与 Quantity 的乘积之和）的结果。

如果重要的话，我们正在使用 IBM DB2 Version 7 Revision 3。

例如，给定以下数据：

{ITEM_NUMBER: "APPLE", QUANTITY_ON_HAND: 5}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 1, 12:01, QUANTITY: 2, COST_PER: 1.23}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 1, 12:02, QUANTITY: 2, COST_PER: 2.34}
{ITEM_NUMBER: "APPLE", DATE_ORDERED: Jan 2, 12:03, QUANTITY: 2, COST_PER: 5.55}

或者用 SQL 来说：

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

CREATE TABLE MYLIB.ORDER_ITEM_REQUIREMENTS (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) , FOREIGN KEY (ITEM_NUMBER) REFERENCES MYLIB.ITEM (ITEM_NUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION  ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;

INSERT INTO MYLIB.ITEM (ITEM_NUMBER, QUANTITY_ON_HAND) VALUES ('APPLE', 5);

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

我想要以下结果：

{ITEM_NUMBER: "APPLE", NUM_SHIPMENTS: 2, QTY_SUM: 4, COST_SUM: 7.14}
db2

1 个回答

  1. Best Answer

    这样做的一种方法是

    with t( item_number, num_shipments, qty_sum, cost_sum, qty_on_hand) as (
  select 
   i.item_number
   ,sum(1) over (
    partition by o.item_number order by o.date_ordered
   )
   ,sum(o.quantity) over (
    partition by o.item_number order by o.date_ordered
   )
   ,sum(o.quantity*o.cost_per) over (
    partition by o.item_number order by o.date_ordered
   ) 
  , i.quantity_on_hand
  from 
    item i 
  inner join
    order_item_requirements o
     on i.item_number = o.item_number
)
select 
  item_number
  ,max(num_shipments) num_shipments
  ,max(qty_sum) qty_sum
  ,max(cost_sum) cost_sum
from t where qty_sum <= qty_on_hand
group by item_number

    我无法访问 IBM i，因此在 Db2 for LUW 上进行了测试，但它应该可以工作。

    小提琴

    • 2

相关问题