Skip to content

Instantly share code, notes, and snippets.

@diraneyya

diraneyya/upcoming_previous_window_functions.sql

Last active May 5, 2025 10:16
Show Gist options
  • Save diraneyya/4455b69e034651151983228cd8b2114b to your computer and use it in GitHub Desktop.
Save diraneyya/4455b69e034651151983228cd8b2114b to your computer and use it in GitHub Desktop.
Download ZIP
previous() and upcoming() are window functions that retrieve the previous or the upcoming non-null value within a column in pgsql.
Raw
upcoming_previous_window_functions.sql
-- "s" stands for state, "v" stands for value, "sf" stands for state (transition) function
create or replace function sf_previous(s anyelement, v anyelement)
returns anyelement language sql immutable strict as $$
select v $$;
create or replace function sf_upcoming(s anyelement, v anyelement)
returns anyelement language sql immutable strict as $$
select s $$;
drop aggregate if exists previous(anyelement);
drop aggregate if exists upcoming(anyelement);
create aggregate previous(anyelement) (
sfunc = sf_previous,
msfunc = sf_previous,
minvfunc = sf_upcoming,
mstype = anyelement,
stype = anyelement
);
create aggregate upcoming(anyelement) (
sfunc = sf_upcoming,
msfunc = sf_upcoming,
minvfunc = sf_previous,
mstype = anyelement,
stype = anyelement
);
Raw
usage.md

previous() and upcoming() are custom window and aggregate functions that retrieve the previous or the upcoming non-null value within a column (in an order determined by the user).

This would be possible using built-in window functions if PostgreSQL supported the IGNORE NULLS directive in window function calls, which it currently does not.

Basic usage

Depending on what you are trying to do, you may want to exclude, or not to exclude, the current row when performing these look-back and look-ahead operations.

Not excluding current row

select 
    seq, 
    contract,
    previous(contract) over (order by seq asc
        -- since this is the default frame, it can be omitted in the prev case
        rows between unbounded preceding and current row) as prev_contract,
    upcoming(contract) over (order by seq asc
        rows between current row and unbounded following) as next_contract
from jobs
order by seq desc;

Result

seq contract prev_contract next_contract
45 NULL "13635-f5376618_JB4753026-S" NULL
44 NULL "13635-f5376618_JB4753026-S" NULL
43 NULL "13635-f5376618_JB4753026-S" NULL
42 "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
41 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
40 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
39 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
38 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
37 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
36 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
35 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
34 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
33 "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S" "16223-k15327.5415-S"
32 NULL "16223-k15327.5415-S" "16223-k15327.5415-S"
31 NULL "16223-k15327.5415-S" "16223-k15327.5415-S"
30 "16223-k15327.5415-S" "16223-k15327.5415-S" "13635-10376_JB4753190-S"
29 NULL "13635-10376_JB4753190-S" "13635-10376_JB4753190-S"
28 NULL "13635-10376_JB4753190-S" "13635-10376_JB4753190-S"
27 NULL "13635-10376_JB4753190-S" "13635-10376_JB4753190-S"
26 "13635-10376_JB4753190-S" "13635-10376_JB4753190-S" "10001-1001326364-S"
25 "10001-1001326364-S" "10001-1001326364-S" "10001-1001326364-S"
24 NULL "10001-1001326364-S" "10001-1001326364-S"
23 NULL "10001-1001326364-S" "10001-1001326364-S"
22 NULL "10001-1001326364-S" "10001-1001326364-S"
21 NULL "10001-1001326364-S" "10001-1001326364-S"
20 NULL "10001-1001326364-S" "10001-1001326364-S"
19 NULL "10001-1001326364-S" "10001-1001326364-S"
18 NULL "10001-1001326364-S" "10001-1001326364-S"
17 NULL "10001-1001326364-S" "10001-1001326364-S"
16 NULL "10001-1001326364-S" "10001-1001326364-S"
15 NULL "10001-1001326364-S" "10001-1001326364-S"
14 NULL "10001-1001326364-S" "10001-1001326364-S"
13 NULL "10001-1001326364-S" "10001-1001326364-S"
12 NULL "10001-1001326364-S" "10001-1001326364-S"
11 NULL "10001-1001326364-S" "10001-1001326364-S"
10 NULL "10001-1001326364-S" "10001-1001326364-S"
9 NULL "10001-1001326364-S" "10001-1001326364-S"
8 NULL "10001-1001326364-S" "10001-1001326364-S"
7 NULL "10001-1001326364-S" "10001-1001326364-S"
6 NULL "10001-1001326364-S" "10001-1001326364-S"
5 NULL "10001-1001326364-S" "10001-1001326364-S"
4 "10001-1001326364-S" "10001-1001326364-S" "10001-1001326364-S"
3 NULL NULL "10001-1001326364-S"
2 NULL NULL "10001-1001326364-S"

Excluding current row

select 
    seq, 
    contract,
    previous(contract) over (order by seq asc
        rows between unbounded preceding and current row exclude current row
        ) as prev_contract,
    upcoming(contract) over (order by seq asc
        rows between current row and unbounded following exclude current row
        ) as next_contract
from jobs
order by seq desc;

Result

seq contract prev_contract next_contract
45 NULL "13635-f5376618_JB4753026-S" NULL
44 NULL "13635-f5376618_JB4753026-S" NULL
43 NULL "13635-f5376618_JB4753026-S" NULL
42 "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S" NULL
41 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
40 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
39 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
38 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
37 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
36 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
35 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
34 NULL "13635-f5376618_JB4753026-S" "13635-f5376618_JB4753026-S"
33 "13635-f5376618_JB4753026-S" "16223-k15327.5415-S" "13635-f5376618_JB4753026-S"
32 NULL "16223-k15327.5415-S" "13635-f5376618_JB4753026-S"
31 NULL "16223-k15327.5415-S" "13635-f5376618_JB4753026-S"
30 "16223-k15327.5415-S" "13635-10376_JB4753190-S" "13635-f5376618_JB4753026-S"
29 NULL "13635-10376_JB4753190-S" "16223-k15327.5415-S"
28 NULL "13635-10376_JB4753190-S" "16223-k15327.5415-S"
27 NULL "13635-10376_JB4753190-S" "16223-k15327.5415-S"
26 "13635-10376_JB4753190-S" "10001-1001326364-S" "16223-k15327.5415-S"
25 "10001-1001326364-S" "10001-1001326364-S" "13635-10376_JB4753190-S"
24 NULL "10001-1001326364-S" "10001-1001326364-S"
23 NULL "10001-1001326364-S" "10001-1001326364-S"
22 NULL "10001-1001326364-S" "10001-1001326364-S"
21 NULL "10001-1001326364-S" "10001-1001326364-S"
20 NULL "10001-1001326364-S" "10001-1001326364-S"
19 NULL "10001-1001326364-S" "10001-1001326364-S"
18 NULL "10001-1001326364-S" "10001-1001326364-S"
17 NULL "10001-1001326364-S" "10001-1001326364-S"
16 NULL "10001-1001326364-S" "10001-1001326364-S"
15 NULL "10001-1001326364-S" "10001-1001326364-S"
14 NULL "10001-1001326364-S" "10001-1001326364-S"
13 NULL "10001-1001326364-S" "10001-1001326364-S"
12 NULL "10001-1001326364-S" "10001-1001326364-S"
11 NULL "10001-1001326364-S" "10001-1001326364-S"
10 NULL "10001-1001326364-S" "10001-1001326364-S"
9 NULL "10001-1001326364-S" "10001-1001326364-S"
8 NULL "10001-1001326364-S" "10001-1001326364-S"
7 NULL "10001-1001326364-S" "10001-1001326364-S"
6 NULL "10001-1001326364-S" "10001-1001326364-S"
5 NULL "10001-1001326364-S" "10001-1001326364-S"
4 "10001-1001326364-S" NULL "10001-1001326364-S"
3 NULL NULL "10001-1001326364-S"
2 NULL NULL "10001-1001326364-S"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment