DB2:滞后函数真的很慢

Question

我有一个很大的CTE，可以输出大约50行的数据。我们称之为output_no_1。现在，如果我在我的 CTE 中添加另一个步骤来操作output_no_1，即添加一些依赖于output_no_1列的列，我的代码需要 20 倍的时间才能运行。

我想了解为什么会这样...

我需要在output_no_2中制作一堆新列(output_no_1包含year_month，我想比较一个时期到下一个时期的销售和其他内容(：

upper part of the CTE
.
.
.
.
,output_no_2 as (
select output_no_1.*
        --comment
        ,lag(sales_bread_shelf_50000   ) over(partition by year_month order by year_month) as lag_sales_bread_shelf_50000
        ,lag(sales_bread_shelf_50001   ) over(partition by year_month order by year_month) as lag_sales_bread_shelf_50001
        ,lag(sales_bread_shelf_50002   ) over(partition by year_month order by year_month) as lag_sales_bread_shelf_50002
        ,lag(sales_bread_shelf_50003   ) over(partition by year_month order by year_month) as lag_sales_bread_shelf_50003
        ,lag(sales_jam_shelf__50000   ) over(partition by year_month order by year_month) as lag_sales_jam_shelf__50000
        ,lag(sales_jam_shelf__50001   ) over(partition by year_month order by year_month) as lag_sales_jam_shelf__50001
        ,lag(sales_jam_shelf__50002   ) over(partition by year_month order by year_month) as lag_sales_jam_shelf__50002
        ,lag(sales_jam_shelf__50003   ) over(partition by year_month order by year_month) as lag_sales_jam_shelf__50003
        ,lag(sales_honey_shelf__50000   ) over(partition by year_month order by year_month) as lag_sales_honey_shelf__50000
        ,lag(sales_honey_shelf__50001   ) over(partition by year_month order by year_month) as lag_sales_honey_shelf__50001
        ,lag(sales_honey_shelf__50002   ) over(partition by year_month order by year_month) as lag_sales_honey_shelf__50002
        ,lag(sales_honey_shelf__50003   ) over(partition by year_month order by year_month) as lag_sales_honey_shelf__50003
        --comment
        ,lag(NO_honey_shelf_50000  ) over(partition by year_month order by year_month) as lag_NO_honey_shelf_50000
        ,lag(NO_honey_shelf_50001  ) over(partition by year_month order by year_month) as lag_NO_honey_shelf_50001
        ,lag(NO_honey_shelf_50002  ) over(partition by year_month order by year_month) as lag_NO_honey_shelf_50002
        ,lag(NO_honey_shelf_50003  ) over(partition by year_month order by year_month) as lag_NO_honey_shelf_50003
        ,lag(NO_jam_shelf_50000  ) over(partition by year_month order by year_month) as lag_NO_jam_shelf_50000
        ,lag(NO_jam_shelf_50001  ) over(partition by year_month order by year_month) as lag_NO_jam_shelf_50001
        ,lag(NO_jam_shelf_50002  ) over(partition by year_month order by year_month) as lag_NO_jam_shelf_50002
        ,lag(NO_jam_shelf_50003  ) over(partition by year_month order by year_month) as lag_NO_jam_shelf_50003
        ,lag(NO_bread_shelf_50000  ) over(partition by year_month order by year_month) as lag_NO_bread_shelf_50000
        ,lag(NO_bread_shelf_50001  ) over(partition by year_month order by year_month) as lag_NO_bread_shelf_50001
        ,lag(NO_bread_shelf_50002  ) over(partition by year_month order by year_month) as lag_NO_bread_shelf_50002
        ,lag(NO_bread_shelf_50003  ) over(partition by year_month order by year_month) as lag_NO_bread_shelf_50003
        --comment
        ,lag(all_merch_50000 ) over(partition by year_month order by year_month) as lag_all_merch_50000
        ,lag(all_merch_50001 ) over(partition by year_month order by year_month) as lag_all_merch_50001
        ,lag(all_merch_50002 ) over(partition by year_month order by year_month) as lag_all_merch_50002
        ,lag(all_merch_50003 ) over(partition by year_month order by year_month) as lag_all_merch_50003
        ,lag(NO_all_merch_50000) over(partition by year_month order by year_month) as lag_NO_all_merch_50000
        ,lag(NO_all_merch_50001) over(partition by year_month order by year_month) as lag_NO_all_merch_50001
        ,lag(NO_all_merch_50002) over(partition by year_month order by year_month) as lag_NO_all_merch_50002
        ,lag(NO_all_merch_50003) over(partition by year_month order by year_month) as lag_NO_all_merch_50003
        --comment
        ,lag(customer_paid_cash) over(partition by year_month order by year_month) as lag_customer_paid_cash
        ,lag(customer_paid_card) over(partition by year_month order by year_month) as lag_customer_paid_card
        ,lag(customer_paid_stole) over(partition by year_month order by year_month) as lag_customer_paid_stole
        ,lag(customer_paid_cried) over(partition by year_month order by year_month) as lag_customer_paid_cried
from output_no_1
)
select * from output_no_2

Answer 1

可以尝试像这样的东西：

with output_no_1b as ( 
select rownumber() over(partition by year_month order by year_month) rang, f1.*
from output_no_1 f1
)
,output_no_2 as (
select f1.*, 
f2.sales_bread_shelf_50000  Lag_sales_bread_shelf_50000 ,
f2.sales_bread_shelf_50001  Lag_sales_bread_shelf_50001 ,
f2.sales_bread_shelf_50002  Lag_sales_bread_shelf_50002 ,
f2.sales_bread_shelf_50003  Lag_sales_bread_shelf_50003 ,
f2.sales_jam_shelf__50000   Lag_sales_jam_shelf__50000  ,
f2.sales_jam_shelf__50001   Lag_sales_jam_shelf__50001  ,
f2.sales_jam_shelf__50002   Lag_sales_jam_shelf__50002  ,
f2.sales_jam_shelf__50003   Lag_sales_jam_shelf__50003  ,
f2.sales_honey_shelf__50000 Lag_sales_honey_shelf__50000,
f2.sales_honey_shelf__50001 Lag_sales_honey_shelf__50001,
f2.sales_honey_shelf__50002 Lag_sales_honey_shelf__50002,
f2.sales_honey_shelf__50003 Lag_sales_honey_shelf__50003,
f2.NO_honey_shelf_50000 Lag_NO_honey_shelf_50000,
f2.NO_honey_shelf_50001 Lag_NO_honey_shelf_50001,
f2.NO_honey_shelf_50002 Lag_NO_honey_shelf_50002,
f2.NO_honey_shelf_50003 Lag_NO_honey_shelf_50003,
f2.NO_jam_shelf_50000   Lag_NO_jam_shelf_50000  ,
f2.NO_jam_shelf_50001   Lag_NO_jam_shelf_50001  ,
f2.NO_jam_shelf_50002   Lag_NO_jam_shelf_50002  ,
f2.NO_jam_shelf_50003   Lag_NO_jam_shelf_50003  ,
f2.NO_bread_shelf_50000 Lag_NO_bread_shelf_50000,
f2.NO_bread_shelf_50001 Lag_NO_bread_shelf_50001,
f2.NO_bread_shelf_50002 Lag_NO_bread_shelf_50002,
f2.NO_bread_shelf_50003 Lag_NO_bread_shelf_50003,
f2.all_merch_50000  Lag_all_merch_50000 ,
f2.all_merch_50001   Lag_all_merch_50001  ,
f2.all_merch_50002   Lag_all_merch_50002  ,
f2.all_merch_50003   Lag_all_merch_50003  ,
f2.NO_all_merch_50000 Lag_NO_all_merch_50000,
f2.NO_all_merch_50001 Lag_NO_all_merch_50001,
f2.NO_all_merch_50002 Lag_NO_all_merch_50002,
f2.NO_all_merch_50003 Lag_NO_all_merch_50003,
f2.customer_paid_cash Lag_customer_paid_cash,
f2.customer_paid_card Lag_customer_paid_card,
f2.customer_paid_stole Lag_customer_paid_stole,
f2.customer_paid_cried Lag_customer_paid_cried,
from output_no_1b f1
left outer join lateral
(
   select * from output_no_1b f0
   where (f0.year_month, f0.rang - 1)=(f1.year_month, f1.rang)
   fetch first rows only
) f2 on 1=1
)
select * from output_no_2