has_been window function?

Question

With this following schema

CREATE TABLE test (
  id INT
  , is_true BOOL
);
INSERT INTO test (id,is_true) VALUES (1,FALSE);
INSERT INTO test (id,is_true) VALUES (2,FALSE);
INSERT INTO test (id,is_true) VALUES (3,TRUE);
INSERT INTO test (id,is_true) VALUES (4,FALSE); 

I make this query:

SELECT 
    is_true
FROM test; 

which of course gives

FALSE
FALSE
TRUE
FALSE

what I want is some kind of window? function that does HAS_BEEN_TRUE rather than IS_TRUE and that respects the id ordering. This is why I think it may be a window function - because it has to do with a calculation over all rows that are like this.

It should return

FALSE -- never been TRUE
FALSE -- never been TRUE
FALSE -- even though it IS TRUE, it has never been so previously
TRUE  -- now it HAS BEEN true. 

I would also accept

FALSE
FALSE
TRUE
TRUE 

which I guess changes it to HAS_BEEN_OR_IS_TRUE. I really want to make this happen in redshift but Im happy to hear about any database where a window function does this.

Answer 1

A window max() of the boolean column should do what you want:

select t.*,
    max(is_true) over(
        order by id
        rows unbounded preceding
    ) has_been_or_is_true
from test t

Or, if you want to look only at previous row, ignoring the current row, we can refine the row frame:

select t.*,
    max(is_true) over(
        order by id
        rows between unbounded preceding and 1 preceding
    ) has_been_true
from test