sql - 检查 IF 子句中的前 X 行

Question

我有一个表格，其中包含给定单位的每日报告，可以简化为:

Unit | Status | Day1    | On     | 10 March 20162    | On     | 10 March 20163    | On     | 10 March 20164    | On     | 10 March 20161    | Off    | 11 March 20162    | On     | 11 March 20163    | On     | 11 March 20164    | On     | 11 March 2016...

I'm looking to query which units have reported 'Off' for the last 7 days but not really sure how to do it. I would like the query to be something like

SELECT 
    Unit, 
    Status,
    CASE WHEN([all 7 previous days were 'Off']) THEN 'Dead' ELSE 'Alive' END
FROM unit_table a
WHERE Day = '15 Sept 2016'

我该怎么做那个 IF 子句？有点像

CASE WHEN(SELECT COUNT(SELECT * FROM unit_table b WHERE DAY > '08 Sept 2016' AND Status = 'Off' AND b.Unit = a.Unit) > 7) 

也许吧？

我知道'DAY > '2016 年 9 月 8 日'' 无法在 native 运行，有一些用于比较和更新日期的内部函数。这仅用于解释目的。

Answer 1

嗯，我在想这样的事情:

select u.unit
from unit_table u
where date >= '2016-09-15' - interval '6 day'
group by u.unit
having count(distinct case when status = 'Off' then day end) = 7;

我对 Snowflake 不是很熟悉，但语法可能如下所示:

select u.unit
from unit_table u
where date >= dateadd(day, -6, CURRENT_DATE)
group by u.unit
having count(distinct case when u.status = 'Off' then day end) = 7;