窗口函数为何疑似使用全量数据集？MAX/AVG计算异常排查

你的问题出在NetSuite SuiteQL对窗口函数的双向frame范围支持上：你指定的ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING子句没有被正确解析，导致MAX和AVG函数使用了默认窗口范围——即从结果集第一行到当前行，这就是为什么MAX始终显示截至当前行的最大值（2048416），AVG也是截至当前行的累计平均值。

解决方法：用LAG/LEAD手动计算目标值 #

由于SuiteQL的frame子句兼容性问题，我们可以通过LAG和LEAD函数手动获取前后行的数据，再计算所需的MAX和AVG：

SELECT 
    UPPER(SUBSTR(first_query.periodname, 1,3)) AS month,
    ROUND(first_query.amount) AS amount,
    -- 计算当前行+前一行+后一行的最大值
    ROUND(GREATEST(
        first_query.amount,
        COALESCE(LAG(first_query.amount) OVER (ORDER BY first_query.postingPeriod), first_query.amount),
        COALESCE(LEAD(first_query.amount) OVER (ORDER BY first_query.postingPeriod), first_query.amount)
    )) AS maxamount,
    -- 计算当前行+前一行+后一行的平均值，根据前后行是否存在调整除数
    ROUND(
        (first_query.amount 
         + COALESCE(LAG(first_query.amount) OVER (ORDER BY first_query.postingPeriod), 0) 
         + COALESCE(LEAD(first_query.amount) OVER (ORDER BY first_query.postingPeriod), 0)
        ) / 
        CASE
            WHEN LAG(first_query.amount) IS NULL AND LEAD(first_query.amount) IS NULL THEN 1
            WHEN LAG(first_query.amount) IS NULL OR LEAD(first_query.amount) IS NULL THEN 2
            ELSE 3
        END
    ) AS avgamount,
    ROUND(LAG(first_query.amount) OVER (ORDER BY first_query.postingPeriod)) AS lagamount,
    ROUND(LEAD(first_query.amount) OVER (ORDER BY first_query.postingPeriod)) AS leadamount
FROM (
    SELECT
        SUM(tl.foreignamount * t.ExchangeRate * -1) AS amount,
        BUILTIN.DF(t.postingPeriod) AS periodname,
        t.postingPeriod
    FROM
        Transaction t
    JOIN transactionline tl ON t.id = tl.transaction
    JOIN account a ON a.id = tl.expenseaccount
    WHERE
        EXTRACT(YEAR FROM t.trandate) = 2023  
    GROUP BY
        BUILTIN.DF(t.postingPeriod),
        t.postingPeriod
) AS first_query
ORDER BY
    first_query.postingPeriod;

代码说明 #

1. maxamount：用GREATEST函数对比当前行、前一行（用COALESCE处理首行无前置的情况）、后一行（用COALESCE处理末行无后置的情况），取最大值。
2. avgamount：先求和当前行+前一行+后一行的数值（无前后行时用0填充），再根据前后行的存在情况调整除数：
   • 首行/末行：只有2个有效数值，除数为2
   • 中间行：3个有效数值，除数为3
   • 唯一行（如果存在）：除数为1

内容的提问来源于stack exchange，提问作者enfrost