窗口函数报错：Window function not allowed 问题排查求助

我的SQL查询代码 #

Sum(
    Get_Business_Days( 
        LAG(CREATED_DT,1) OVER (PARTITION BY OPERATION_ID ORDER BY OPERATION_ID, CREATED_DT, ACTIVITY_ID),
        CREATED_DT )
) OVER (MARCS_OPERATION_ID, CREATED_DT, ACTIVITY_ID)

FROM DATASOURCE

WHERE 
    CREATED_DT BETWEEN 
          @Prompt('Enter Start Date','DT',,Mono,Free,Persistent,,User:1) 
      AND @Prompt('Enter End Date','DT',,Mono,Free,Persistent,,User:2) 
ORDER BY (PARTITION BY OPERATION_ID ORDER BY OPERATION_ID, CREATED_DT, ACTIVITY_ID)

执行错误信息 #

Window function not allowed

说明 #

@Prompt用于执行时获取用户输入的日期范围，Get_Business_Days是已定义好的函数，接收两个日期参数返回工作日天数。移除外层Sum()后查询能正常运行，但我需要汇总这些工作日天数，请问问题出在哪？

问题原因 #

1. 窗口函数嵌套语法不支持：直接把SUM()窗口函数嵌套在LAG()窗口函数的计算结果上，大多数SQL引擎不允许在窗口聚合函数的参数里直接嵌套另一个窗口函数。
2. 外层SUM()的OVER子句语法错误：OVER子句不能直接罗列字段，必须用PARTITION BY指定分组维度，或者用ORDER BY定义累计排序逻辑。
3. ORDER BY子句语法错误：排序语句里不能写PARTITION BY ...，这是窗口函数的专属语法，ORDER BY只需要直接指定排序字段即可。

解决办法 #

先通过子查询或CTE把每条记录的工作日天数计算出来，再在外层做汇总操作，具体分两种场景：

场景1：按OPERATION_ID分组汇总总工作日天数

WITH DailyBusinessDays AS (
    SELECT 
        OPERATION_ID,
        -- 先计算每条记录与上一条的工作日天数
        Get_Business_Days( 
            LAG(CREATED_DT, 1) OVER (PARTITION BY OPERATION_ID ORDER BY CREATED_DT, ACTIVITY_ID),
            CREATED_DT
        ) AS business_days
    FROM DATASOURCE
    WHERE 
        CREATED_DT BETWEEN 
              @Prompt('输入开始日期','DT',,Mono,Free,Persistent,,User:1) 
          AND @Prompt('输入结束日期','DT',,Mono,Free,Persistent,,User:2)
)
-- 按OPERATION_ID分组汇总
SELECT 
    OPERATION_ID,
    SUM(business_days) AS total_business_days
FROM DailyBusinessDays
GROUP BY OPERATION_ID
ORDER BY OPERATION_ID;

场景2：保留每条记录，同时显示分组累计的工作日天数

如果需要保留原始记录，同时查看每个OPERATION_ID下的累计工作日天数，可以用窗口SUM：

SELECT 
    OPERATION_ID,
    CREATED_DT,
    ACTIVITY_ID,
    -- 单条记录的工作日天数
    Get_Business_Days( 
        LAG(CREATED_DT, 1) OVER (PARTITION BY OPERATION_ID ORDER BY CREATED_DT, ACTIVITY_ID),
        CREATED_DT
    ) AS business_days,
    -- 按OPERATION_ID累计的总工作日天数
    SUM(
        Get_Business_Days( 
            LAG(CREATED_DT, 1) OVER (PARTITION BY OPERATION_ID ORDER BY CREATED_DT, ACTIVITY_ID),
            CREATED_DT
        )
    ) OVER (PARTITION BY OPERATION_ID) AS total_business_days_per_op
FROM DATASOURCE
WHERE 
    CREATED_DT BETWEEN 
          @Prompt('输入开始日期','DT',,Mono,Free,Persistent,,User:1) 
      AND @Prompt('输入结束日期','DT',,Mono,Free,Persistent,,User:2)
ORDER BY OPERATION_ID, CREATED_DT, ACTIVITY_ID;

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