在sql中实现windows函数时出错

1zmg4dgp 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(285)

我有一张table如下：
客户费用交易actionsbs:1003314/30/2012177.43bs：1003315/31/201296.9bs:1003316/30/201281.31bs:1003317/31/201298.13bs:1003318/31/201299.95bs:1006994/30/2012403.99bs:1006995/31/20120bs:1006996/30/20123.24bs:1006997/31/201211.02bs:1006998/31/201211.27
我的预期输出如第列所示 expense_transactions_3_month_max . 为了到达这一列，我们首先轮班 expense_transactions 一行，如中所示 expense_transactions_shifted 然后计算3行的最大值。其中3是窗口大小。
客户ID日期费用交易费用交易转移费用交易3个月_maxbs:1003314/30/2012177.43bs：1003315/31/201296.9177.43bs:1003316/30/201281.3196.9bs:1003317/31/201298.1381.31177.43bs:1003318/31/201299.9598.1398.13bs:1006994/30/2012403.99bs:1006995/31/20120403.99bs:1006996/30/20123.240bs:1006997/31/201211.023.24403.99bs：1006998/31/201211.2711.0211.02
我尝试过使用这个sql查询，但是我不确定我哪里出错了。

WITH shifted AS
 (
     SELECT  
         customer_ID, date,
         LAG(expense_transactions, 1) OVER (PARTITION BY customer_ID ORDER BY customer_ID ASC) AS shiftedBy1Month 
     FROM
         FundsFlowAfterMerge ffam
)
SELECT
    customer_ID, date, 
    MAX(shiftedBy1Month) OVER (PARTITION BY customer_ID, date ORDER BY customer_ID ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Rolling3Window 
FROM 
    shifted

我的方法正确吗？对于上述查询，我得到以下错误：
sql错误[2809][s0001]：对过程“fundsflowaftermerge”的请求失败，因为“fundsflowaftermerge”是表对象

sql sql-server

来源：https://stackoverflow.com/questions/67288358/error-in-implementing-windows-function-in-sql

2条答案

按热度按时间

vd8tlhqk1#

当前查询按错误的列进行分区和排序。
你的 lag 说 partition by customer_ID order by customer_ID ASC 这意味着它将得到一个任意的结果 customer_ID .
你的 max 说 PARTITION BY customer_ID,date order by customer_ID ASC rows between 2 PRECEDING and CURRENT row 这意味着每个单独的日期是另一个分区。
此外，当您实际有3行时，您似乎只需要一个结果，您应该考虑到这一点
你可以把这个写短一点。 ROWS 2 PRECEDING 是的缩写 rows between 2 PRECEDING and CURRENT ROW ，也 lag 默认为上一行，并且 ASC 是默认顺序。

with shifted as (

    SELECT
        customer_ID,
        date,
        lag(expense_transactions) over
          (partition by customer_ID order by date) as shiftedBy1Month
    from FundsFlowAfterMerge ffam
)

select
    customer_ID,
    date,
    CASE WHEN LAG(shiftedBy1Month, 2) OVER
            (PARTITION BY customer_ID order by date) IS NOT NULL
        THEN max(shiftedBy1Month) over
          (PARTITION BY customer_ID order by date ROWS 2 PRECEDING)
    END as Rolling3Window
FROM shifted

赞(0）回复(0）举报 2021-07-26

oalqel3c2#

它不是很优雅，但你可以直接编写代码

Greatest(expense_transactions, lag(expense_transactions,1) over *blah blah*, lag(expense_transactions,2) over *same window*)

如果您的sql风格不包含greatest函数，请改用更详细的case语法。不美观，因为很难推广到n个月的时间间隔，但它的优点是可以在一个简单的、非递归的select语句中完成。

赞(0）回复(0）举报 2021-07-26

我来回答

在sql中实现windows函数时出错

2条答案

相关问题

热门标签

最新问答