我们的数据集从根本上将一组日期(从当前周到过去的几周)加入到一组节中,具体取决于这些节是在该周开始或之前,然后在该周或之后结束。虽然此查询最初为我们提供了预期的结果,但本周开始为我们提供了错误的结果。经过一堆修补后,我们发现如果将查询更改为a LEFT JOIN
,然后使用WHERE
子句过滤查询,它将再次为我们提供正确的结果。
有什么不同?为什么一个起作用而另一个不起作用?(要点:为什么原始查询在突然遇到此错误之前要工作几周?)在Redshift上执行相同的内部联接会产生正确的结果,因此这似乎是我们不了解的Snowflake细微差别。
原始查询:
WITH week_list AS
(
SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
UNION ALL
SELECT DATEADD(week, 1, week_value)
FROM week_list
WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
SELECT
wl.week_value, s.id section_id
FROM week_list wl
JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
)
SELECT
aspw.week_value,
COUNT(DISTINCT aspw.section_id) count_sections
FROM
active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC
结果:一排,日期为2019-12-30(4周前)。过去三周没有数据。
注意:如果您DATEADD
在第一个CTE中进行了调整,则返回的第一个日期无论是什么,似乎总是可以成功加入。此行为仅在最后一周内开始-以前,此查询提供了预期的行数(换句话说,该行指定了第一个星期DATEADD
)。
“固定”查询:
WITH week_list AS
(
SELECT DATEADD(week, -4, DATE_TRUNC(week, CURRENT_DATE())) AS week_value
UNION ALL
SELECT DATEADD(week, 1, week_value)
FROM week_list
WHERE DATEADD(week, 1, week_value) < CURRENT_DATE()
),
active_sections_per_week AS
(
SELECT wl.week_value, s.id section_id
FROM week_list wl
LEFT JOIN schema.sections s ON wl.week_value >= DATE_TRUNC(week, s.starts_at)
AND wl.week_value <= DATE_TRUNC(week, s.ends_at)
WHERE s.id IS NOT NULL
)
SELECT aspw.week_value, COUNT(DISTINCT aspw.section_id) count_sections
FROM active_sections_per_week aspw
GROUP BY 1
ORDER BY 1 DESC
结果:返回四行,日期为2019-12-30至2020-01-20的周,并带有适当的节计数。
感谢您的所有反馈!好消息是,你们所有人都帮助我找到了令我满意的解决方案。我还跟踪了Snowflake,以便他们可以调查此行为,并查看这是否是我的用户错误,原因是不了解递归CTE的处理方式,或者它是否可能是最近发行版中引入的错误。
这是我发现的内容:虽然递归适用于我将其应用到的用例(基于生成日期列表CURRENT_DATE
),但并非绝对必要。由于我们需要日期列表,因此我可以轻松地生成一个表格并使用行号来进行DATEADD
调整。
看起来像这样:
SELECT DATEADD(week, '-' || ROW_NUMBER() OVER (ORDER BY NULL),
DATEADD(week, 1, DATE_TRUNC(week, CURRENT_DATE()))) AS week_value
FROM table (generator(rowcount => 200))
这种方法的最大好处之一是,我不再受限MAX_RECURSIONS
于Snowflake中的设置(默认情况下设置为100)。由于我使用这些数据来创建随时间变化的活动图,因此拥有200个值使我拥有超过三年的历史,而不仅仅是两年的历史。如果我想扩大我的Snowflake代表,也不必联系它。
将week_list
CTE更改为这种非递归方法似乎可以解决导致该CTE无法INNER JOIN
正确执行的任何问题。我们仍然不明白为什么递归CTE似乎工作了好几周然后突然开始出现异常,但是如果Snowflake可以通过我们的支持票证来阐明这一点,我将在这里加倍提供更新。谢谢大家的帮助和指导!
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句