postgresql - 如何计算销售成本

Question

我有一个库存表。库存表SQL:

CREATE TABLE INVENTORY
(
    INVENTORY_ID             SERIAL PRIMARY KEY,
    INVENTORY_DATE           date NOT NULL,
    ITEM_NAME                text NOT NULL,
    PURCHASED_QUANTITY       INTEGER DEFAULT 0,
    SOLD_QUANTITY            INTEGER DEFAULT 0,
    AMOUNT                   MONEY NOT NULL,
    TOTAL                    MONEY NOT NULL       
);

INSERT INTO INVENTORY(INVENTORY_DATE, ITEM_NAME, PURCHASED_QUANTITY, SOLD_QUANTITY, AMOUNT, TOTAL)
SELECT '1/1/2014'::date, 'ITEM-001', 10, NULL, 100, 1000 UNION ALL
SELECT '1/2/2014'::date, 'ITEM-001', NULL, 2, 200, 400 UNION ALL
SELECT '1/3/2014'::date, 'ITEM-001', 20, NULL, 110, 2200 UNION ALL
SELECT '1/4/2014'::date, 'ITEM-001', NULL, 4, 200, 800 UNION ALL
SELECT '1/5/2014'::date, 'ITEM-001', 20, NULL, 80, 1600;

表格

SELECT * FROM INVENTORY;

INVENTORY_DATE    ITEM_NAME    PURCHASED_QUANTITY    SOLD QUANTITY     AMOUNT    TOTAL
-----------------------------------------------------------------------------------------
1/1/2014          ITEM-001     10                                      $100      $1000
1/2/2014          ITEM-001                           2                 $200      $400
1/3/2014          ITEM-001     20                                      $110      $2200
1/4/2014          ITEM-001                           4                 $200      $800
1/5/2014          ITEM-001     20                                      $80       $1600

我正在尝试创建这样的函数:

CREATE FUNCTION GET_FIFO_COGS(ITEM_NAME TEXT, QUANTITY INTEGER)
RETURNS MONEY
AS
$$
BEGIN

END
$$
LANGUAGE "PLPGSQL";

这样，我可以:

SELECT * FROM GET_FIFO_COGS('ITEM-001', 4) --> $400
--Remaining 4. Each for $100.

SELECT * FROM GET_FIFO_COGS('ITEM-001', 12) --> $1280
--4 for $100, 8 for 110.

SELECT * FROM GET_FIFO_COGS('ITEM-001', 34) --> $3400
--4 for $100, 20 for $110, 10 for $80.

如果指向正确的方向，我可以改进这个问题。我正在学习 SQL，这个给我带来了麻烦，我无法解决这个问题，因为我发现它对我来说很棘手。

Answer 1

SQL Fiddle

此解决方案中的方法是使用 generate_series 将包含 n 项的每一行扩展为包含单个项的 n 行。然后为 purchased 和 sold 构建两个单独的集合。每组的行都按 inventory_id 顺序编号，因此可以消除已经已售出 的已购买 项目。然后 order by inventory_id 和 limit 数量。

create or replace function get_fifo_cogs(_item_name text, _quantity int)
returns money as $$
    with inventory as (
        select 
            inventory_id, inventory_date,
            generate_series(1, greatest(purchased_quantity, sold_quantity)),
            amount,
            purchased_quantity is not null as purchased
        from inventory
        where item_name = _item_name
    ), purchased as (
        select *, row_number() over(order by inventory_id) as rn
        from inventory
        where purchased
    ), sold as (
        select *, row_number() over(order by inventory_id) as rn
        from inventory
        where not purchased
    )
    select sum(amount) as cogs
    from (
        select amount
        from purchased
        where not exists (
            select 1
            from sold
            where rn = purchased.rn
        )
        order by inventory_id
        limit _quantity
    ) s;
$$ language sql;

用作

select cogs from get_fifo_cogs('ITEM-001', 4) gfc(cogs);
  cogs   
---------
 $400.00
(1 row)

select cogs from get_fifo_cogs('ITEM-001', 12) gfc(cogs);
   cogs    
-----------
 $1,280.00
(1 row)

select cogs from get_fifo_cogs('ITEM-001', 34) gfc(cogs);
   cogs    
-----------
 $3,400.00

或者只是

select get_fifo_cogs('ITEM-001', 4) as cogs;