使用SQL 实现一个指标算法，计算货龄

需求说明：ERP系统中计算货龄，采用SQL方式实现，使用PostgreSQL

前提：假设要计算t时刻某item的货龄，记t时刻其库存量为n

create or replace function cargoAge(coding varchar) returns integer as

$body$

declare

    rawMaterials          integer:= 0;
    semiFinishedProducts  integer:= 0;
    product               integer:= 0;
    cargoAge              float;

begin
    
    -- 计算 当前的库存量
    select sum(quantity) into rawMaterials from isc_raw_material_warehouse where material_code = coding;
    select sum(quantity) into semiFinishedProducts from isc_semi_finished_product_warehouse where material_code = coding;
    select sum(quantity) into product from isc_finished_product_warehouse where material_code = coding;


    -- 物料
    IF rawMaterials > 0 THEN
	    -- 临时表 函数运行完成后自动删除
		-- 按照时间倒排对 数量进行累加
		-- 在使用过程中 发现日期相同时，如下情形 （会出现一个错误的临界点）
		-- 故先试用 row_number 进行排序

 --  M01040200000150	4268.00	2021-10-23 00:00:00	10000	4268.00
 --  M01040200000150	4497.00	2021-10-20 00:00:00	10000	17149.00
 --  M01040200000150	4344.00	2021-10-20 00:00:00	10000	17149.00
 --  M01040200000150	4040.00	2021-10-20 00:00:00	10000	17149.00
 --  M01040200000150	1692.00	2021-10-08 00:00:00	10000	18841.00
 --  M01040200000150	4345.00	2021-09-22 00:00:00	10000	23186.00
 --  M01040200000150	3174.00	2021-09-08 00:00:00	10000	29360.00
 --  M01040200000150	3000.00	2021-09-08 00:00:00	10000	29360.00
 --  M01040200000150	4000.00	2021-08-19 00:00:00	10000	33628.00
 --  M01040200000150	268.00	2021-08-19 00:00:00	10000	33628.00
 --  M01040200000150	6000.00	2021-07-10 00:00:00	10000	40793.00
 --  M01040200000150	1165.00	2021-07-10 00:00:00	10000	40793.00

        CREATE TEMP TABLE temp_cargoAge ON COMMIT DROP as
        select  
            material_code,
            invoice_inventory_quantity,
            receipt_date,
            inventory,
            rank,
            sum(invoice_inventory_quantity) over(partition by material_code order by rank ) sum_quantity
        from (
                select 
                    material_code, 
                    invoice_inventory_quantity, 
                    receipt_date,
                    rawMaterials as inventory,
                    row_number()  over(partition by material_code order by receipt_date desc) rank 
                from (
                    select isc_purchase_receipt_order_no, receipt_date
                    from isc_purchase_receipt_order_header
                ) a 
                inner join (
                    select inbound_order_number,invoice_inventory_quantity, material_code
                    from isc_purchase_receipt_order_row 
                    where material_code = coding
                ) b 
                on a.isc_purchase_receipt_order_no = b.inbound_order_number 
        ) c ;


        -- 临界条件是 inventory > sum_quantity  但是要加上去临界条件后的第一条数据
		-- 故使用 union 进行拼接数据，此处使用 postgresql 可使用 limit 1 ，
		-- 其他数据库 未知

        select 
            sum(
                case when  inventory >= sum_quantity then invoice_inventory_quantity*(date_part('day', current_date - receipt_date ))/inventory
                     when  inventory < sum_quantity  then (invoice_inventory_quantity + inventory - sum_quantity)*(date_part('day', current_date - receipt_date ))/inventory end
                ) into cargoAge
        from (
            select material_code, invoice_inventory_quantity, receipt_date, inventory, sum_quantity from temp_cargoAge where inventory > sum_quantity 
            union 
            (select material_code, invoice_inventory_quantity, receipt_date, inventory, sum_quantity from temp_cargoAge where inventory <= sum_quantity limit 1 )
        ) c ;
        
        return cargoAge;
        
    END IF;


    -- 半成品 
    IF rawMaterials > 0 THEN
        CREATE TEMP TABLE temp_rawMaterials ON COMMIT DROP as
            select  
            material_code,
            tracking_date,
            storage_quantity ,
            inventory,
            rank,
            sum(storage_quantity) over(partition by material_code order by rank ) sum_quantity
        from (
            SELECT
                material_code,
                tracking_date,
                storage_quantity ,
                rawMaterials as inventory,
                row_number()  over(partition by material_code order by tracking_date desc) rank
            FROM( 
                SELECT isc_production_receipt_order_header_id, tracking_date 
                FROM isc_production_receipt_order_header 
            ) A 
            INNER JOIN ( 
                SELECT material_code, storage_quantity, isc_production_receipt_order_header_id 
                FROM isc_production_receipt_order_row 
                WHERE material_code = coding 
            ) b ON A.isc_production_receipt_order_header_id = b.isc_production_receipt_order_header_id
        ) c ;

        select  
            sum(
                case when  inventory >= sum_quantity then storage_quantity*(date_part('day', current_date - tracking_date ))/inventory
                     when  inventory < sum_quantity  then (storage_quantity + inventory - sum_quantity)*(date_part('day', current_date - tracking_date ))/inventory end
                ) into cargoAge 
        from (
            select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_rawMaterials where inventory > sum_quantity
            union 
            (select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_rawMaterials where inventory <= sum_quantity limit 1 )
        ) c ;
        return cargoAge;
        
    END IF;



    -- 半成品 
    IF product > 0 THEN
        CREATE TEMP TABLE temp_product ON COMMIT DROP as
            select  
            material_code,
            tracking_date,
            storage_quantity ,
            inventory,
            rank,
            sum(storage_quantity) over(partition by material_code order by rank ) sum_quantity
        from (
            SELECT
                material_code,
                tracking_date,
                storage_quantity ,
                product as inventory,
                row_number()  over(partition by material_code order by tracking_date desc) rank
            FROM( 
                SELECT isc_production_receipt_order_header_id, tracking_date 
                FROM isc_production_receipt_order_header 
            ) A 
            INNER JOIN ( 
                SELECT material_code, storage_quantity, isc_production_receipt_order_header_id 
                FROM isc_production_receipt_order_row 
                WHERE material_code = coding 
            ) b ON A.isc_production_receipt_order_header_id = b.isc_production_receipt_order_header_id
        ) c ;

        select  
            sum(
                case when  inventory >= sum_quantity then storage_quantity*(date_part('day', current_date - tracking_date ))/inventory
                     when  inventory < sum_quantity  then (storage_quantity + inventory - sum_quantity)*(date_part('day', current_date - tracking_date ))/inventory end
                ) into cargoAge 
        from (
            select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_product where inventory > sum_quantity
            union 
            (select material_code,tracking_date,storage_quantity ,inventory,rank,sum_quantity from temp_product where inventory <= sum_quantity limit 1 )
        ) c ;
        return cargoAge;
        
    END IF;


    return cargoAge;
end
$body$
    language plpgsql;