记那个需求的求解

思路：先将现有记录中缺失的数据补充进去，即为null的数据；然后再将缺失的记录补充进去
所用表test数据如下：

drop table if exists test_1;
CREATE TABLE IF NOT EXISTS test_1
as 
SELECT  *
        ,row_number() over(partition by id order by rq) - if(qian is null, row_number() over(partition by id,if(qian is null,0,1) order by rq),null) rn -- 需要按分组补的数据，缺失数据才会排序
        ,if(qian is null, lag(hou)over(partition by id order by rq), null) last_hou -- 上一条的变更后余额
from  test
;

-- 需要将null补充的数据
drop table if exists test_2;
CREATE TABLE IF NOT EXISTS test_2
as 
SELECT  *,
       sum(nvl(last_hou,0) + nvl(last_change_amount,0))over(partition by id, rn order by rq) qian_deal
  from (select * 
               ,if(qian is null and last_hou is null, lag(bian)over(partition by id order by rq), null) last_change_amount
          from test_1) t
 where rn is not null
 order by id, rq;


drop table if exists test_3;
CREATE TABLE IF NOT EXISTS test_3
as 
SELECT
       t.rq,
       t.id,
       nvl(t.qian, b.qian_deal) as qian,
       t.bian,
       nvl(t.hou, nvl(t.qian, b.qian_deal) + t.bian) as hou, 
       row_number()over(partition by t.id order by t.rq) rn
  from (select * from test) t
  left join test_2 b
    on t.rq = b.rq and t.id = b.id;


-- 补充缺失记录部分
drop table if exists test_bc;
CREATE TABLE IF NOT EXISTS test_bc
as 
SELECT concat(date_add(t.rq,-1),' 12:30:00') as rq,
       t.id,
       t.last_hou as qian,
       (hou - qian) as bian,
       t.qian as hou
  from (select *, 
               lag(hou)over(partition by id order by rq) last_hou -- 上一条变更后余额
          from test_3 t
         ) t
 where last_hou != qian ;