SQL面试题挑战09：有效值追溯

SQL面试题挑战09：有效值追溯

问题

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

product_id   cost       date    
101         300.39    2023-11-01  
102          500      2023-11-02
101           0       2023-11-03
101                   2023-11-04
102          600      2023-11-04
102                   2023-11-05
103          983      2023-11-06    

​

建表语句

drop table if exists goods;
create table goods(
    product_id varchar(20),
    cost decimal(16,2),
    date date
);
 INSERT INTO goods (product_id, cost, date) VALUES ('101', 300.39, '2023-11-01');
 INSERT INTO goods (product_id, cost, date) VALUES ('102', 500.00, '2023-11-02');
 INSERT INTO goods (product_id, cost, date) VALUES ('101', 0.00, '2023-11-03');
 INSERT INTO goods (product_id, cost, date) VALUES ('101', null, '2023-11-04');
 INSERT INTO goods (product_id, cost, date) VALUES ('102', 600.00, '2023-11-04');
 INSERT INTO goods (product_id, cost, date) VALUES ('102', null, '2023-11-05');
 INSERT INTO goods (product_id, cost, date) VALUES ('103', 983.00, '2023-11-06'

解答

思路：

这里主要掌握last_value的使用，last_value()函数第二个参数设置为true，则他会跳过空值（Hive中）。因此直接使用这个函数即可，额外需要注意的就是记得把0替换为空值

完整查询如下

select
    product_id,
    last_value(cost,true) over (partition by product_id order by date)cost,
    date
from
    (
        select
            product_id,
            if(cost=0,null,cost)cost,
            date
        from
            goods
    )t