n

问题

nnnn

现在有一张商品入库表，包括商品id、商品成本和入库日期3个字段，由于某些原因，导致部分商品的成本缺失（为0或者没有值都是缺失），这样不利于我们计算成本。现在要把缺失的商品进价补充完整，补充的依据是使用相同商品的最近一次有效成本作为当前商品的成本。比如2023-11-04号101商品的cost就需要用300.39填充。

nnnn

product_id   cost       date    n101         300.39    2023-11-01  n102          500      2023-11-02n101           0       2023-11-03n101                   2023-11-04n102          600      2023-11-04n102                   2023-11-05n103          983      2023-11-06

nnnn

解答

nnnn

使用last_value()函数，传入第二参数true来跳过null值，当然首先要对数据处理下，将0的变成null。

nnnn

with temp asn(n    select 101 as product_id,300.39 as cost,'2023-11-01' as daten    union alln    select 102 as product_id,500 as cost,'2023-11-02' as daten    union alln    select 101 as product_id,0 as cost,'2023-11-03' as daten    union alln    select 101 as product_id,null as cost,'2023-11-04' as daten    union alln    select 102 as product_id,600 as cost,'2023-11-04' as daten    union alln    select 102 as product_id,null as cost,'2023-11-05' as daten    union alln    select 103 as product_id,983 as cost,'2023-11-06' as daten)nselectnproduct_idn-- last_value第二个参数设置为true，表示取最后一个值时跳过null值。默认不写为falsen,last_value(cost,true) over(partition by product_id order by date) as valid_last_valuen,datenfrom(n   selectn    product_idn    ,if(cost=0 or cost is null,null,cost) as cost  --当cost为0或null时就给null值n    ,daten    from tempn) t1;

n