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

Question

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

Show the version of your query is the window clause. It should work. — Gordon Linoff
– Gordon Linoff, Commented Apr 15, 2018 at 15:07
postgresql.org/docs/10/static/sql-select.html : WINDOW should come after the GROUP BY ... HAVING ...` clauses (but before the ORDER BY ...) — wildplasser
– wildplasser, Commented Apr 15, 2018 at 15:17
@wildplasser: Thanks for this comment ! That was the issue - I had put it before the GROUP BY. — user7832194
– user7832194, Commented Apr 15, 2018 at 15:19

wildplasser · Accepted Answer · 2018-04-15 15:26:51Z

4

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
        ;

answered Apr 15, 2018 at 15:26

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related