如何让MySQL的WINDOW函数仅在窗口框架完整时返回值，否则返回NULL？

需要计算每个日期过去7天（包含当天）的移动总和与移动平均值，使用WINDOW函数通过ROWS BETWEEN定义窗口框架后计算结果正确，但前6天也显示了总和与平均值。要求仅在完整的7天窗口框架可用时显示数值，否则返回NULL。

表结构 #

CREATE TABLE Customer (
    customer_id INT NOT NULL,
    visited_on DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO Customer (customer_id, visited_on, amount) VALUES
(1, '2019-01-01', 100.00),
(2, '2019-01-02', 110.00),
(3, '2019-01-03', 120.00),
(4, '2019-01-04', 130.00),
(5, '2019-01-05', 110.00),
(6, '2019-01-06', 140.00),
(7, '2019-01-07', 150.00),
(8, '2019-01-08', 80.00),
(9, '2019-01-09', 110.00),
(1, '2019-01-10', 130.00),
(3, '2019-01-10', 150.00);

原查询语句 #

WITH DAILY_REVENUE AS
    (SELECT
         visited_on,
         SUM(amount) AS amount
    FROM
        Customer
    GROUP BY
        visited_on
    ORDER BY
        visited_on ASC
)
, MOVING_AVG AS(
    SELECT
        visited_on,
        SUM(amount) OVER(ORDER BY visited_on ASC
                        ROWS BETWEEN 6 PRECEDING
                        AND
                        CURRENT ROW) AS amount,
        CAST(AVG(amount) OVER(ORDER BY visited_on ASC
                        ROWS BETWEEN 6 PRECEDING
                        AND
                        CURRENT ROW) AS DECIMAL(5,2)) AS average_amount
    FROM
        DAILY_REVENUE
)
SELECT * FROM MOVING_AVG

当前输出 #

预期输出 #

核心思路是判断当前行是否拥有完整的7天窗口：由于DAILY_REVENUE是按日期升序排列的每日数据，我们可以用ROW_NUMBER()为每行标记序号，当序号≥7时，说明当前行及之前已有6行数据，满足完整窗口要求，此时返回计算结果；否则返回NULL。

修改后的查询语句：

WITH DAILY_REVENUE AS
    (SELECT
         visited_on,
         SUM(amount) AS amount
    FROM
        Customer
    GROUP BY
        visited_on
    ORDER BY
        visited_on ASC
)
, MOVING_AVG AS(
    SELECT
        visited_on,
        ROW_NUMBER() OVER(ORDER BY visited_on ASC) AS row_num,
        SUM(amount) OVER(ORDER BY visited_on ASC
                        ROWS BETWEEN 6 PRECEDING
                        AND
                        CURRENT ROW) AS moving_sum,
        CAST(AVG(amount) OVER(ORDER BY visited_on ASC
                        ROWS BETWEEN 6 PRECEDING
                        AND
                        CURRENT ROW) AS DECIMAL(5,2)) AS moving_avg
    FROM
        DAILY_REVENUE
)
SELECT
    visited_on,
    CASE WHEN row_num >=7 THEN moving_sum ELSE NULL END AS amount,
    CASE WHEN row_num >=7 THEN moving_avg ELSE NULL END AS average_amount
FROM MOVING_AVG

验证结果 #

执行上述查询后，输出将与预期结果完全一致：前6行的amount和average_amount为NULL，从第7行（2019-01-07）开始显示正确的移动总和与平均值。

内容的提问来源于stack exchange，提问作者Syed Talha Tariq