Postgres查询，如果满足条件，则有条件地从记录子集返回值。

Question

给定一个具有时间戳和值的数据集，我想运行一个查询，对于给定的一天，如果有值为0的记录存在，则返回当天的0；如果当天只有非零的值，则返回0。如果当天没有记录，也可以返回0。

例如，使用给定的数据集。

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

我希望把这个还给你：

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

我知道我可以编写一个存储过程来完成这个任务，但是我希望我可以用一个查询(可能是CTE)来完成这个任务。

Answer 1

可以将聚合与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;

这假设值为非负值(如您的示例中所示)。如果您可以具有负值：

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;