Postgres query to conditionally return a value from a subset of records if it meets a criteria

Question

Given a dataset with a timestamp and a value, I would like to run a query that for a given day, would return a 0 for that day if a record exists with value 0 or 1 if only non-zero values exist for that day. If there are no records for that day, also return 0.

As an example, with the given data set.

2019-06-20 23.1
2019-06-20 22.4
2019-06-20 23.1
2019-06-18 23.2
2019-06-18 0
2019-06-18 22.8

I would like to have this returned:

2019-06-20 1 -- only non-zero values for 6/20
2019-06-19 0 -- no data for 06/19
2019-06-18 0 -- 06/18 has a zero value

I know I can write a stored procedure to do this, but was hoping I could do it with a query (possibly CTE)

Gordon Linoff · Accepted Answer · 2019-06-20 20:09:42Z

1

You can use aggregation with generate_series():

select d.day,
       (case when min(t.value) > 0 then 1 else 0 end) as flag
from (select generate_series(min(day), max(day), interval '1 day') as day
      from t
     ) d left join
     t
     on t.day = d.day
group by d.day
order by d.date;

This assumes that the values are non-negative (as in your example). If you can have negative values:

select d.day,
       (case when count(*) filter (where t.value = 0) = 0 and
                  count(t.value) > 0
             then 1 else 0 
        end) as flag
from (select generate_series(min(day), max(day), interval '1 day') as day
      from t
     ) d left join
     t
     on t.day = d.day
group by d.day
order by d.date;

answered Jun 20, 2019 at 20:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres query to conditionally return a value from a subset of records if it meets a criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related