1.连续两次开窗取数据
select *
from
(
select ORDER_ID_BK ,CURRENT_OPER ,LAST_OPER ,CREATED_TIME ,row_number()over(partition by order_id_bk order by CREATED_TIME desc) rk from
(select ORDER_ID_BK ,CURRENT_OPER ,CREATED_TIME ,lag(CURRENT_OPER,1,0)over(partition by order_id_bk order by CREATED_TIME ) LAST_OPER from ods_orc.crm_case_trans_record
where IS_DELETE = 0 ) a --取前信息
) a where rk = 1 ,这段逻辑先用lag取前一条数据的某个信息,再把取到的所有记录排序取最新一条 ,这个时候查出来的数据全是都正确的
2.用coi表去关联1的结果集
select coi.ORDER_ID_BK ,cctr.* from ods_orc.crm_order_info coi
left join
(select *
from
(
select ORDER_ID_BK ,CURRENT_OPER ,LAST_OPER ,CREATED_TIME ,row_number()over(partition by order_id_bk order by CREATED_TIME desc) rk from
(select ORDER_ID_BK ,CURRENT_OPER ,CREATED_TIME ,lag(CURRENT_OPER,1,0)over(partition by order_id_bk order by CREATED_TIME ) LAST_OPER from ods_orc.crm_case_trans_record
where IS_DELETE = 0 ) a --取前信息
) a where rk = 1
) cctr
on coi.ORDER_ID_BK = cctr.order_id_bk
where cctr.ORDER_ID_BK in ,取in 总共400个order_id_bk,出来的数据出现错误,并且每次查询出的结果数量也不同03e974044247e9394d644869d4d1529f_.png
上面的图片是连续点击三次,出来了却是3次不同的结果,正确结果是出现400条数据
doris 版本是2.1.6
补充:调换coi表和1中的结果集的位置,用cctr left join coi 表也出现同样的错误
目前发现不仅仅是lag,只要是任意窗口函数连续开窗两次均会复现此bug