這段sql錯誤，在sum開窗函數中，說recent_days-1錯誤，如果想要實現同樣的效果，應該如何修改

select
    recent_days,
    sum(total_amount_1d) over (order by FKDAT rows between recent_days-1 preceding and current row ),
    FKDAT
from dws_tb_readonly_invoice_data_1d
lateral view explode(`array`(7,30)) tmp as recent_days;

原因：
The error in the SQL query is due to the use of the column recent_days in the ROWS BETWEEN clause of the SUM window function. The recent_days column is not a constant value, so it cannot be used directly in the ROWS BETWEEN clause.

To achieve the same rolling sum effect based on the value of recent_days, you can use a CASE statement inside the SUM window function to conditionally sum up the total_amount_1d values for each date based on the value of recent_days. Here’s an updated query that should work:

SELECT
    recent_days,
    SUM(
        CASE 
            WHEN recent_days = 7 THEN total_amount_1d 
            ELSE 0 
        END
    ) OVER (ORDER BY FKDAT ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_7days,
    SUM(
        CASE 
            WHEN recent_days = 30 THEN total_amount_1d 
            ELSE 0 
        END
    ) OVER (ORDER BY FKDAT ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS sum_30days,
    FKDAT
FROM dws_tb_readonly_invoice_data_1d
LATERAL VIEW EXPLODE(`array`(7,30)) tmp AS recent_days;