英文:
Recursive CTE, "cte must be qualified with a dataset (e.g dataset.table)"
问题
使用BigQuery，编写一个递归CTE以遍历日期，以确定是否已经过去了一周或更短的时间。CTE在BigQuery中未找到。在测试每个CTE块之后，在UNION ALL的第二部分出现了错误。
错误信息:
表格“cte_test”必须用数据集来标识（例如，数据集.表格）。
查询:
sql
WITH cte_lag AS (
id,
code,
LAG(start_date) OVER (PARTITION BY id, code ORDER BY start_date ASC) AS previous_start_date
FROM main_table
),
cte_test AS (
SELECT
x.*,
1 AS lvl
FROM cte_lag AS x
WHERE DATE_DIFF(start_date, previous_start_date, DAY) <= 7
UNION ALL
-- 错误发生在这里
SELECT
y.*, z.lvl + 1
FROM cte_test AS y
INNER JOIN cte_lag AS z ON
y.id = z.id
AND
y.code = z.code
)
SELECT * FROM cte_test
英文:
Using BigQuery, write a recursive cte to iterate over dates to determine if a week or less has passed. CTE isn't found by BigQuery. After testing each cte block, error is occurring at the second part of the recursive cte after the UNION ALL.
Error:
Table "cte_test" must be qualified with a dataset (e.g. dataset.table).
Query:
sql
WITH cte_lag AS(
id
, code
, LAG(start_date) OVER (PARTITION BY id, code ORDER BY start_date ASC) AS previous_start_date
FROM main_table,
cte_test AS(
SELECT
x.*
, 1 AS lvl
FROM cte_lag AS x
WHERE DATE_DIFF(start_date, previous_start_date, DAY) <= 7
UNION ALL
-- Error occurs here
SELECT
y.*, z.lvl +1
FROM cte_test AS y
INNER JOIN cte_lag AS z ON
y.id = z.id
AND
y.code = z.code
)
SELECT * FROM cte_test
答案1
得分: 1
如@Jaytiger所提到的，查询应以WITH RECURSIVE开头，因为这是一个递归查询。
WITH RECURSIVE cte_lag AS (
id,
code,
LAG(start_date) OVER (PARTITION BY id, code ORDER BY start_date ASC) AS previous_start_date
)
由于这是正确的解决方法，为了将来可能遇到这种情况的社区成员受益，将此答案发布为社区维基。
英文:
As mentioned by @Jaytiger, the query should start with WITH RECURSIVE since it is a recursive query.
WITH RECURSIVE cte_lag AS(
id
, code
, LAG(start_date) OVER (PARTITION BY id, code ORDER BY start_date ASC) AS previous_start_date)
Posting this answer as a Community Wiki since this is the right workaround and for the benefit of the community that might encounter this use case in the future.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论