代码之家 › 专栏 › 技术社区 › Helen

db2 sql:获取许多列滞后值的最快方法

lag db2 sql

Helen · 技术社区 · 7 年前

在sql中有很多方法可以获得某一列的滞后值,例如:

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1

,或:

select variable_of_interest 
               ,lag(variable_of_interest ,1) 
                    over(partition by
                    some_group order by variable_1,...,variable_n) 
                    as lag_variable_of_interest
from DATA

我使用第二个版本,但是当“滞后”许多变量时,我的代码运行非常缓慢,因此我的代码变成:

select        variable_of_interest_1
              ,variable_of_interest_2
              ,variable_of_interest_3
                   ,lag(variable_of_interest_1 ,1) 
                        over(partition by
                        some_group order by variable_1,...,variable_n) 
                        as lag_variable_of_interest_1
                    ,lag(variable_of_interest_2 ,1) 
                        over(partition by
                        some_group order by variable_1,...,variable_n) 
                        as lag_variable_of_interest_2
                   ,lag(variable_of_interest_3 ,1) 
                        over(partition by
                        some_group order by variable_1,...,variable_n) 
                        as lag_variable_of_interest_3
    from DATA

我想知道,这是因为每个滞后函数都必须按自己的分区和顺序排列整个数据集,即使它们使用的是相同的分区和顺序吗?

2 回复 | 直到 7 年前

Helen 7 年前

我不能百分之百确定db2是如何优化这些查询的。如果它独立地执行每个延迟,那么优化器肯定还有改进的余地。

你可以使用的一种方法是 lag() 用一个 join 上主键 :

select t.*, tprev.*
from (select t.*, lag(id) over ( . . . ) as prev_id
      from t
     ) t left join
     t tprev
     on t.id = tprev.prev_id ;

根据你的描述,这可能是做你想做的事情的最有效的方法。

这应该比 row_number() 因为连接可以使用索引。

Paul Vernon 7 年前

如果所有olap函数都使用相同的方法,那么db2只对数据进行一次排序 PARTITION BY 和 ORDER BY . 你可以通过查看解释计划来确认这一点。

create table data(v1 int, v2 int, v3 int, g1 int, g2 int, o1 int, o2 int) organize by row
;
explain plan for
select  g1
,       g2
,       o1
,       o2
,       v1
,       v2
,       v3
,       lag(v1) over(partition by g1, g2 order by o1, o2 ) as lag_v1
,       lag(v2) over(partition by g1, g2 order by o1, o2 ) as lag_v2
,       lag(v3) over(partition by g1, g2 order by o1, o2 ) as lag_v3
from
    data
;

将给出以下计划(使用 db2exfmt -1 -d $DATABASE )。你可以看到只有一个 SORT 操作人员

Access Plan:
-----------

    Total Cost:             14.839
    Query Degree:           4



      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
      1000 
     LMTQ  
     (   2)
     14.839 
        2 
       |
      1000 
     TBSCAN
     (   3)
     14.5555 
        2 
       |
      1000 
     SORT  
     (   4)
     14.5554 
        2 
       |
      1000 
     TBSCAN
     (   5)
     14.2588 
        2 
       |
      1000 
 TABLE: PAUL    
      DATA
       Q1

顺便说一句,如果你用一个真正的sql查询(以及一些ddl和一些数据量的概念)来发布一个问题,我们可能会建议一些可以提高获取滞后值性能的方法。在没有看到更好的例子的情况下,很难给出详细的建议