CODEWITHSUNDEEP
sqlsubquerywindow-functionsamazon-athena
user
user

Reputation: 592

Remove the time series before sequence of zero using sql

I have a time series as follows :

Day.          Data 

1/1/2020.     0
2/1/2020      .2
3/1/2020      0
......       ...
1/2/2020      0
2/2/2020.     0
3/2/2020.     .2
4/2/2020.     .3
5/2/2020.     0
6/2/2020      0
7/2/2020.     0
8/2/2020      2
9/2/2020      2.4
10/2/2020     3

So I want filter data only show after final sequence of zeros that we have in time series in this case I want to get only data after 8/2/202. I have tried this

SELECT * FROM table where Data> 0

here is the result :

Day.          Data 
    
    2/1/2020      .2
    ......       ...
    3/2/2020.     .2
    4/2/2020.     .3
    8/2/2020      2
    9/2/2020      2.4
    10/2/2020     3

However this does not find the lates 0 and remove everything before that.

I want also show the result 2 days after the final zero in sequence in the table.

Day           Data
10/2/2020     3
11/2/2020.    3.5
.....        ....

Upvotes: 0

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

One method is:

select t.*
from t
where t.day > (select max(t2.day) from t t2 where t2.value = 0);

You can offset this:

where t.day > (select max(t2.day) + interval '2' day from t t2 where t2.value = 0);

The above assumes that at least one row has zeros. Here are two easy fixes:

where t.day > all (select max(t2.day) from t t2 where t2.value = 0);

or:

where t.day > (select coalesce(max(t2.day), '2000-01-01') from t t2 where t2.value = 0);

Upvotes: 1

GMB
GMB

Reputation: 222652

You can use window functions:

select day, data
from (
    select t.*, max(case when data = 0 then day end) over() day0
    from mytable t
) t
where day > day0 or day0 is null
order by day0

This is easily adapted if you want to start two days after the last 0:

select day, data
from (
    select t.*, max(case when data = 0 then day end) over() day0
    from mytable t
) t
where day > day0 + interval '2 day' or day0 is null
order by day0

Upvotes: 0

Related Questions