CODEWITHSUNDEEP
sqlpostgresqlpartitioningwindow-functions
user7832194
user7832194

Reputation:

PostgreSQL: Combine repeating window function (OVER / PARTITION BY) in one statement

I have a larger query in which I have to select either the first or the last value for multiple columns.

So far I have the below structure for my query which works as intended.
However, since I have around 10 columns where for each of them I have to apply the same window function, i.e. the OVER (PARTITION BY ...) part (like for the sample columns col4 to col7 below), I was wondering if there is a way to write this just once instead of having to repeat the same lines each time.

I tried WINDOW w AS (...) after the FROM clause and then refered to this via OVER w AS ... for each column but that didn't work.

Can anyone here help me with this ?

My query structure (shortened): 

SELECT DISTINCT
    c.col1
    , c.col2
    , c.col3
    , FIRST_VALUE(c.col4) OVER
        (
            PARTITION BY c.col1
            ORDER BY c.col1, c.col2, c.col3
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS col4
    , LAST_VALUE(c.col5) OVER
        (
            PARTITION BY c.col1
            ORDER BY c.col1, c.col2, c.col3
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS col5
    , LAST_VALUE(c.col6) OVER
        (
            PARTITION BY c.col1
            ORDER BY c.col1, c.col2, c.col3
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS col6
    , FIRST_VALUE(c.col7) OVER
        (
            PARTITION BY c.col1
            ORDER BY c.col1, c.col2, c.col3
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS col7
    /* ... */
FROM
    CUSTOMERS c
GROUP BY
    /* ... */
ORDER BY
    /* ... */

Many thanks in advance for any help with this

Upvotes: 1

Views: 1968

Answers (1)

wildplasser
wildplasser

Reputation: 44220

WINDOW should come after the GROUP BY ... HAVING ... clauses (but before the ORDER BY ...) , as described in The Fine Manual

This should probably work:

SELECT DISTINCT
    c.col1 , c.col2 , c.col3
    , FIRST_VALUE(c.col4) OVER www AS col4
    , LAST_VALUE(c.col5) OVER www AS col5
    , LAST_VALUE(c.col6) OVER www AS col6
    , FIRST_VALUE(c.col7) OVER www AS col7
    /* ... */
FROM
    CUSTOMERS c
GROUP BY 1,2,3
WINDOW www AS (PARTITION BY c.col1
            ORDER BY c.col1, c.col2, c.col3
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
ORDER BY 1,2,3
        ;

Upvotes: 4

Related Questions