雪花 查询过去 7 天中的每一天是否存在 Id

Question

我们每天都会将新记录插入到带有 id 和created_on列的表中。

如何识别过去 7 天内是否每天都存在具有特定标识符的记录？

Answer 1

这可以通过存储过程完成：

CREATE OR REPLACE PROCEDURE TIME_TRAVEL(QUERY TEXT, DAYS FLOAT)
RETURNS VARIANT LANGUAGE JAVASCRIPT AS
$$
function run_query(query, offset) {
try {
var sqlText = query.replace('"at"', " AT(OFFSET => " + (offset + 0) + ") ");
return (snowflake.execute({sqlText: sqlText})).next();
}
catch(e) { return false }
}
var days, result = [];
for (days = 0; days < DAYS; days++)
if (run_query(QUERY, -days * 86400)) result.push(days);
return result;
$$;
CALL TIME_TRAVEL('SELECT * FROM TASK_HISTORY "at" WHERE QUERY_ID = ''019024ef-002e-8f71-0000-05e10030a782''', 7);

要使时间旅行查询替换正常工作，请输入一个"at"作为表别名。返回值
是查询返回任何值时的日偏移量数组。
这只有在您拥有雪花企业版的情况下才能超出DAYS=2。

Answer 2

我用下面的查询做到了

select id,  sum(present) as total_count 
from
(select id,feed_date, count(1) as present 
from catalog_rca 
where feed_date between '2019-11-19' and '2019-11-25'  
group by 1,2) as temp
group by 1 having total_count = 7;