Question 18257 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

has_been window function? – Amazon Web Sevices

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    You can use sub-query, and check it yourself:

    SELECT 
    id,
    case when exists (select 1 from test t2 where t2.id<t.id and t2.is_true = TRUE) then TRUE else FALSE end wind
FROM test t
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search