英文:
Why I get a "Syntax error:L compilation error: (line ..." when trying to create a SNOWFLAKE UDTF which use IDENTIFIER function?
问题
我尝试创建一个Snowflake UDTF时,为什么会出现“Syntax error: L compilation error: (line ...)”错误?如果我注释掉这行:“AND IDENTIFIER(baseline) = I.COMP”,函数就能正确编译,但不会生成预期的结果。
SQL是该函数的处理程序。
CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)
RETURNS TABLE(name varchar, value varchar)
LANGUAGE SQL
AS
$$
SELECT V.name, V.value
FROM (
SELECT name, value
FROM (
SELECT
Q1.GRP AS NAME,
Q1.AGG AS VALUE,
ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK
FROM (
-- demographics
(
SELECT
D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX
FROM store_pro_demos_long D
INNER JOIN index_dim A USING(DX_ID)
INNER JOIN index_demos_pct I
ON D.MVS_DESC = I.MVS_DESC
AND IDENTIFIER(baseline) = I.COMP
AND I.BASELINE = baseline
WHERE D.DX_ID = dx_id
)
) Q1
) WHERE RNK = 1
) V
$$;
英文:
Why I get a "Syntax error:L compilation error: (line ..." when trying to create a Snowflake UDTF? If I comment the line: "AND IDENTIFIER(baseline) = I.COMP" the function compiles correctly but do not generate the expected results.
SQL is the handler for the function.
CREATE OR REPLACE FUNCTION fn_Shopper_Insights(dx_id varchar, business varchar, baseline varchar)
RETURNS TABLE(name varchar, value varchar)
LANGUAGE SQL
AS
$$
SELECT V.name, V.value
FROM (
SELECT name, value
FROM (
SELECT
Q1.GRP AS NAME,
Q1.AGG AS VALUE,
ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK
FROM (
-- demographics
(
SELECT
D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX
FROM store_pro_demos_long D
INNER JOIN index_dim A USING(DX_ID)
INNER JOIN index_demos_pct I
ON D.MVS_DESC = I.MVS_DESC
AND IDENTIFIER(baseline) = I.COMP
AND I.BASELINE = baseline
WHERE D.DX_ID = dx_id
)
) Q1
) WHERE RNK = 1
) V
$$;
答案1
得分: 1
三级嵌套是不必要的,可以简化为:
SELECT
D.PROJECT_TYPE AS GRP,
D.MVS_DESC AS AGG
FROM store_pro_demos_long AS D
JOIN index_dim AS A USING(DX_ID)
JOIN index_demos_pct AS I
ON D.MVS_DESC = I.MVS_DESC
AND IDENTIFIER(baseline) = I.COMP
AND I.BASELINE = baseline
WHERE D.DX_ID = dx_id
QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;
第二个 IDENTIFIER(baseline)
可以替换为 CASE
表达式:
AND IDENTIFIER(baseline) = I.COMP
<=>
AND I.COMP = CASE WHEN baseline = 'col_name1' THEN D.col_name1
WHEN baseline = 'col_name2' THEN D.col_name2
ELSE D.col_name_default
END
注意:代码部分未被翻译。
英文:
Three level nesting is unnecessary and could be simplified:
SELECT V.name, V.value
FROM (
SELECT name, value
FROM (
SELECT
Q1.GRP AS NAME,
Q1.AGG AS VALUE,
ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY INDEX DESC) AS RNK
FROM (
-- demographics
(
SELECT
D.DX_ID, D.PROJECT_TYPE AS GRP, D.MVS_DESC AS AGG, D.PCT, DIV0NULL(D.PCT, I.PCT) AS INDEX
FROM store_pro_demos_long D
INNER JOIN index_dim A USING(DX_ID)
INNER JOIN index_demos_pct I
ON D.MVS_DESC = I.MVS_DESC
AND IDENTIFIER(baseline) = I.COMP
AND I.BASELINE = baseline
WHERE D.DX_ID = dx_id
)
) Q1
) WHERE RNK = 1
) V
To:
SELECT
D.PROJECT_TYPE AS GRP,
D.MVS_DESC AS AGG
FROM store_pro_demos_long AS D
JOIN index_dim AS A USING(DX_ID)
JOIN index_demos_pct AS I
ON D.MVS_DESC = I.MVS_DESC
AND IDENTIFIER(baseline) = I.COMP
AND I.BASELINE = baseline
WHERE D.DX_ID = dx_id
QUALIFY ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY DIV0NULL(D.PCT, I.PCT) DESC)=1;
Second IDENTIFIER(baseline)
could be replaced with CASE
expression:
AND IDENTIFIER(baseline) = I.COMP
<=>
AND I.COMP = CASE WHEN baseline = 'col_name1' THEN D.col_name1
WHEN baseline = 'col_name2' THEN D.col_name2
ELSE D.col_name_default
END
答案2
得分: 0
我在考虑使用您的建议,将 IDENTIFIER 函数替换为 dbt 和 Jinja。我将创建UDTF作为dbt模型,然后使用pre_hook
或macro
来查询不同的基线,并使用jinja动态生成CASE语句:
AND IDENTIFIER(baseline) = I.COMP
<=>
AND I.COMP = CASE {%- for b in baselines -%}
WHEN baseline = {{b}} THEN D.{{b}}
{%- endfor -%}
ELSE D.col_name_default
END
英文:
I am thinking in using your recommendation of replacing IDENTIFIER function with dbt and Jinja. I will create the UDTF as a dbt model, and then use a pre_hook
or a macro
to query the different baselines and generate the case dynamically with jinja:
AND IDENTIFIER(baseline) = I.COMP
<=>
AND I.COMP = CASE {%- for b in baselines -%}
WHEN baseline = {{b}} THEN D.{{b}}
{%- endfor -%}
ELSE D.col_name_default
END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论