SQL窗口函数依赖于？

Question

我遇到了一个非常奇怪的问题，我非常困惑，因为我无法理解。我使用redshift并编写了一个查询，该查询使用两个窗口函数计算正在运行的和。

SELECT
    SUM(window_function_1) wf1,
    SUM(window_function_2) wf2
FROM (
    SELECT      
        period_new,
        SUM(metric) OVER (PARTITION BY period_new_year,dim ORDER BY period_new ROWS UNBOUNDED PRECEDING) AS window_function_1,
        SUM(metric) OVER (PARTITION BY period_new_year,dim ORDER BY period_new ROWS UNBOUNDED PRECEDING) AS window_function_2
    FROM
        some_schema.sample_data_2)

当我通过按时间维度(即period_old)选择另一个顺序来更改列period_old的定义时，window_function_1和window_function_2的聚合值都会发生变化。

SELECT
        SUM(window_function_1) wf1,
        SUM(window_function_2) wf2
FROM (
        SELECT      
            period_new,
            SUM(metric) OVER (PARTITION BY period_new_year,dim ORDER BY period_new ROWS UNBOUNDED PRECEDING) AS window_function_1,
            SUM(metric) OVER (PARTITION BY period_new_year,dim ORDER BY period_old ROWS UNBOUNDED PRECEDING) AS window_function_2
        FROM
            some_schema.sample_data_2)

我的问题是:不同列的窗口功能是否相互影响？如果是的话:为什么？

period_old,period_new,period_new_year,dim,metric
202205,202202,2022,AIRNVG,-7285325.3
202507,202504,2025,AIRNVG,-7275293
202410,202407,2024,AIRNVG,-7275293
202506,202503,2025,AIRNVG,-7275293
202311,202308,2023,AIRNVG,-7275293
202206,202203,2022,AIRNVG,-7287894.58
202407,202404,2024,AIRNVG,-7275293
202507,202504,2025,AIRNVG,-7275293
202508,202505,2025,AIRNVG,-7275293
202510,202507,2025,AIRNVG,-7275293
202308,202305,2023,AIRNVG,-7275293
202411,202408,2024,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202407,202404,2024,AIRNVG,-7275293
202507,202504,2025,AIRNVG,-7275293
202504,202501,2025,AIRNVG,-7275293
202305,202302,2023,AIRNVG,-7275293
202410,202407,2024,AIRNVG,-7275293
202510,202507,2025,AIRNVG,-7275293
202408,202405,2024,AIRNVG,-7275293
202207,202204,2022,AIRNVG,-7353484.19
202201,202110,2021,AIRNVG,-7275293
202411,202408,2024,AIRNVG,-7275293
202406,202403,2024,AIRNVG,-7275293
202401,202310,2023,AIRNVG,-7275293
202402,202311,2023,AIRNVG,-7275293
202310,202307,2023,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202206,202203,2022,AIRNVG,-7275293
202407,202404,2024,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202505,202502,2025,AIRNVG,-7275293
202504,202501,2025,AIRNVG,-7275293
202507,202507,2025,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7287848.93
202303,202212,2022,AIRNVG,-7275293
202303,202212,2022,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202510,202507,2025,AIRNVG,-7275293
202201,202110,2021,AIRNVG,-7303133.41
202204,202201,2022,AIRNVG,-7275293
202204,202201,2022,AIRNVG,-7275412.24
202401,202310,2023,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202306,202303,2023,AIRNVG,-7275293
202302,202211,2022,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202410,202407,2024,AIRNVG,-7275293
202508,202505,2025,AIRNVG,-7275293
202201,202110,2021,AIRNVG,-7305948.84
202402,202311,2023,AIRNVG,-7275293
202401,202310,2023,AIRNVG,-7275293
202211,202211,2022,AIRNVG,-7275293
202504,202501,2025,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202407,202404,2024,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202303,202212,2022,AIRNVG,-7275293
202510,202507,2025,AIRNVG,-7275293
202508,202505,2025,AIRNVG,-7275293
202207,202204,2022,AIRNVG,-7275293
202201,202110,2021,AIRNVG,-7279411.62
202204,202201,2022,AIRNVG,-7304173.27
202311,202308,2023,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202206,202206,2022,AIRNVG,-7469554.64
202503,202412,2024,AIRNVG,-7275293
202505,202502,2025,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7278069.03
202111,202111,2021,AIRNVG,-7256019.96
202208,202205,2022,AIRNVG,-7299659.79
202203,202112,2021,AIRNVG,-7277659.25
202203,202112,2021,AIRNVG,-7274923.95
202506,202503,2025,AIRNVG,-7275293
202204,202201,2022,AIRNVG,-7270618.82
202501,202410,2024,AIRNVG,-7275293
202210,202207,2022,AIRNVG,-7275293
202404,202401,2024,AIRNVG,-7275293
202412,202409,2024,AIRNVG,-7275293
202309,202306,2023,AIRNVG,-7275293
202312,202309,2023,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202112,202109,2021,AIRNVG,-7279322.46
202504,202501,2025,AIRNVG,-7275293
202307,202304,2023,AIRNVG,-7275293
202407,202407,2024,AIRNVG,-7275293
202305,202302,2023,AIRNVG,-7275293
202510,202510,2025,AIRNVG,-7275293
202510,202507,2025,AIRNVG,-7275293
202203,202112,2021,AIRNVG,-7279117.48
202201,202110,2021,AIRNVG,-7299484.32
202401,202310,2023,AIRNVG,-7275293
202406,202406,2024,AIRNVG,-7275293
202211,202208,2022,AIRNVG,-7275293
202211,202208,2022,AIRNVG,-7275293
202310,202307,2023,AIRNVG,-7275293
202202,202111,2021,AIRNVG,-7277847.37
202504,202501,2025,AIRNVG,-7275293
202312,202309,2023,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7277588.41
202504,202501,2025,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202110,202107,2021,AIRNVG,-7286865.38
202211,202208,2022,AIRNVG,-7275293
202210,202207,2022,AIRNVG,-7288827.24
202304,202301,2023,AIRNVG,-7275293
202504,202501,2025,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202312,202309,2023,AIRNVG,-7275293
202411,202408,2024,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202211,202208,2022,AIRNVG,-7275293
202403,202312,2023,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7291294.14
202504,202501,2025,AIRNVG,-7275293
202410,202407,2024,AIRNVG,-7275293
202305,202302,2023,AIRNVG,-7275293
202111,202108,2021,AIRNVG,-7309237.44
202203,202112,2021,AIRNVG,-7276913.73
202207,202204,2022,AIRNVG,-7277087.14
202209,202206,2022,AIRNVG,-7391313.41
202201,202110,2021,AIRNVG,-7279229.62
202308,202305,2023,AIRNVG,-7275293
202506,202503,2025,AIRNVG,-7275293
202506,202503,2025,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202211,202208,2022,AIRNVG,-7275293
202211,202208,2022,AIRNVG,-7275293
202310,202307,2023,AIRNVG,-7275293
202502,202411,2024,AIRNVG,-7275293
202302,202211,2022,AIRNVG,-7275293
202507,202504,2025,AIRNVG,-7275293
202507,202504,2025,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7290859.5
202305,202302,2023,AIRNVG,-7275293
202305,202302,2023,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202408,202405,2024,AIRNVG,-7275293
202312,202309,2023,AIRNVG,-7275293
202308,202305,2023,AIRNVG,-7275293
202409,202406,2024,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202206,202203,2022,AIRNVG,-7311212.77
202503,202412,2024,AIRNVG,-7275293
202407,202404,2024,AIRNVG,-7275293
202305,202302,2023,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202408,202405,2024,AIRNVG,-7275293
202312,202309,2023,AIRNVG,-7275293
202506,202503,2025,AIRNVG,-7275293
202110,202107,2021,AIRNVG,-7293020.37
202202,202111,2021,AIRNVG,-7279368.99
202307,202304,2023,AIRNVG,-7275293
202302,202211,2022,AIRNVG,-7275293
202304,202301,2023,AIRNVG,-7275293
202502,202411,2024,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7272846.39
202509,202506,2025,AIRNVG,-7275293
202508,202505,2025,AIRNVG,-7275293
202209,202206,2022,AIRNVG,-7283409.87
202207,202204,2022,AIRNVG,-7277325.4
202409,202406,2024,AIRNVG,-7275293
202110,202107,2021,AIRNVG,-7278047.05
202402,202311,2023,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202503,202412,2024,AIRNVG,-7275293
202504,202501,2025,AIRNVG,-7275293
202502,202411,2024,AIRNVG,-7275293
202205,202202,2022,AIRNVG,-7271838.12
202410,202407,2024,AIRNVG,-7275293
202509,202509,2025,AIRNVG,-7275293
202412,202409,2024,AIRNVG,-7275293
202110,202107,2021,AIRNVG,-7281176.39
202411,202408,2024,AIRNVG,-7275293
202501,202410,2024,AIRNVG,-7275293
202210,202207,2022,AIRNVG,-7316151.61
202202,202111,2021,AIRNVG,-7274246.89
202510,202507,2025,AIRNVG,-7275293
202209,202206,2022,AIRNVG,-7278760.28
202201,202110,2021,AIRNVG,-7274201.3
202212,202209,2022,AIRNVG,-7275293
202503,202412,2024,AIRNVG,-7275293
202202,202111,2021,AIRNVG,-7280919.16
202505,202502,2025,AIRNVG,-7275293
202307,202307,2023,AIRNVG,-7275293
202111,202108,2021,AIRNVG,-7322364.86
202303,202212,2022,AIRNVG,-7275293
202509,202506,2025,AIRNVG,-7275293
202209,202206,2022,AIRNVG,-7307015.05
202201,202110,2021,AIRNVG,-7307029.73
202110,202107,2021,AIRNVG,-7282066.44
202506,202503,2025,AIRNVG,-7275293
202206,202203,2022,AIRNVG,-7798641.57
202310,202307,2023,AIRNVG,-7275293
202212,202209,2022,AIRNVG,-7275293
202405,202402,2024,AIRNVG,-7275293
202507,202504,2025,AIRNVG,-7275293

Answer 1

您的排序不是确定性的-- period_new有重复的值。滚动和将具有不同的值，取决于所选择的任意排序。( 202203的Period_new有两个不同的度量值，所以哪个是第一个就重要了。)

通过改变第一窗口函数的排序，影响第二窗口函数的初始状态。由于排序不是确定性的，这将创建一个与第一个查询不同的答案。

如果让数据库来做它想做的任何事情，那么它可以在查询之间更改它正在做的事情。