SQL：具有可变参数的 LAG

发布于 2025-01-10 06:06:35 字数 464 浏览 0 评论 0原文

我想使用滞后函数并使其依赖于一个变量，但它给了我一个错误，因为它必须是整数类型。

我尝试了 lag(pivote,pivote) 但出现以下错误： AnalysisException: LEAD/LAG 的偏移参数必须是常量正整数： lag(pivote,ivote)

您有其他选择吗？

代码如下：

select 
    *
    ,if ( Monto > 0  
     , 0
     , lag( pivote, pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        *, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1
    ) as base
;

原文

I want to use the lag function and make it depend on a variable, but it gives me an error because it must be of type integer.

I tried lag( pivote, pivote ) but I get the following error:
AnalysisException: The offset parameter of LEAD/LAG must be a constant positive integer: lag(pivote, pivote)

Do you have any alternative?

The code is the following:

select 
    *
    ,if ( Monto > 0  
     , 0
     , lag( pivote, pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        *, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1
    ) as base
;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

残花月 2025-01-17 06:06:35

我想你可能在 lag() 中提到了两次pivote。你可以尝试下面的 SQL -

select 
    base.*
    ,if ( Monto > 0  
     , 0
     , lag( base.pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        t.*, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1 t
    ) as base

I think you probably mentioned pivote twice in lag(). can you please try below SQL -

select 
    base.*
    ,if ( Monto > 0  
     , 0
     , lag( base.pivote ) OVER( partition by ID order by Fecha ) 
    ) as B
    
FROM(
    select 
        t.*, 
    row_number() OVER( partition by ID order by Fecha ) as pivote 
    FROM table1 t
    ) as base

回复收藏 0 原文

~没有更多了~