CODEWITHSUNDEEP
postgresqlwindow-functionsgaps-and-islandspalantir-foundryfoundry-contour
Daniel
Daniel

Reputation: 57

Fill gaps in preceeding rows

I'm working with the expression builder of Palantir Contour.

The following table is given, and I need to fill the gaps in the counter column.

Problem: The rows before the first value shall be filled with first counter -1

Date Counter
1.10.2022
2.10.2022
3.10.2022 3
5.10.2022
6.10.2022
8.10.2022 4
10.10.2022
12.10.2022 5

I'm now here:

First, I created a 'group' column. Each new Counter is the starting point of a new group:

sum(case when "Counter" NOT NULL then 1 else 0 end) OVER (
ORDER BY "Date" ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

And the added another column 'fillgap' with a second window function that fills the group with the first counter value of a group. I assume that I could combine the two:

first("Counter") OVER (
PARTITION BY "group"
ORDER BY "Date" ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Intermediate result:

Date Counter group fillgap
1.10.2022 0
2.10.2022 0
3.10.2022 3 1 3
5.10.2022 1 3
6.10.2022 1 3
8.10.2022 4 2 4
10.10.2022 2 4
12.10.2022 5 3 5

Now I'm stuck to fill the first two rows in group 0 with first counter (3) -1, e.g. 2

How can I do this?

Idea in words:
If group = 0 then fillgap = Counter(of next group)-1

Upvotes: 3

Views: 256

Answers (2)

jian
jian

Reputation: 4877

use count and first value. count is for get the correct group/partition. first value to get the first non-null value. If the partition first value is null then it's NULL.

CREATE temp TABLE t_foo (
    date date,
    counter int
);

INSERT INTO t_foo
    VALUES ('2022-10-01', NULL),
    ('2022-10-02', NULL),
    ('2022-10-03', 3),
    ('2022-10-05', NULL),
    ('2022-10-06', NULL),
    ('2022-10-08', 4),
    ('2022-10-10', NULL),
    ('2022-10-12', 5);

SELECT
    *,
    first_value(counter) OVER (PARTITION BY grp ORDER BY date)
FROM (
    SELECT
        date,
        counter,
        count(counter) OVER (ORDER BY date) AS grp
    FROM
        t_foo) sub;

Upvotes: 0

Arvind
Arvind

Reputation: 301

added another expression as suggested by Matija Herceg to arrive at the desired output

    case WHEN ("fillgap" IS NULL) 
         THEN (min("fillgap") 
              OVER (
              ORDER BY "Date" ASC
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))-1 
         ELSE "fillgap" end

Upvotes: 0

Related Questions