SQL Window Functions dependend?-CodePudding

I came across a pretty weird problem and I am super confused because I cannot make sense of that. I use redshift and wrote a query that uses two window functions to calculate a running sum.

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)

When I change the definition of the column window_function_1 by choosing another ORDER BY time dimension (i.e. period_old) the aggregated values for window_function_1 and window_function_2 both change.

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)

My Question: Is there a way that window functions for different columns influence each other? If so: why?

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

CodePudding user response：

Your ordering is not deterministic - there are repeated values for period_new. The rolling sum will have different values depending on which arbitrary ordering is chosen. (Period_new of 202203 has two different metric values so which one is first will matter.)

By changing the ordering of the first window function is influencing the initial state for the second window function’s ordering. Since the ordering is not deterministic this creates a different answer than in the first query.

If you leave it up to the database to do whatever it wants then it can change what it is doing between queries.