CODEWITHSUNDEEP
sqlpostgresql
bah
bah

Reputation: 168

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

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)

Upvotes: 0

Views: 628

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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;

Upvotes: 1

Related Questions