Snowflake调用FLATTEN函数打平数组返回Unknown function错误问询

报错原因 #

FLATTEN是非标准SQL函数，仅少数数据库（如Snowflake、BigQuery）原生支持，你当前使用的数据库不识别该函数；且即便在支持该函数的数据库中，FLATTEN属于表生成函数，不能直接放在SELECT后的字段列表中使用，需要放在FROM子句中做关联查询。

解决方案 #

场景1：使用Snowflake数据库（从你的语法风格判断概率最高） #

改用侧连接关联FLATTEN返回的行集，同时用TO_CHAR转换日期格式即可，修改后SQL如下：

with cte as (
    SELECT 
        array_construct('2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date) AS array
        ,'2021-09-11'::date AS max_date
        ,date_filter(
            '2021-09-12'::date,
            array_construct('2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date)
        ) as dates 
)
SELECT TO_CHAR(f.value, 'yyyy-mm-dd') AS format_date 
FROM cte, LATERAL FLATTEN(input => cte.dates) f;

优化建议：你当前的自定义JS函数逻辑可以直接用原生SQL函数实现，性能更高，可替换为：

ARRAY_FILTER(
    array_construct('2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date), 
    d -> d < '2021-09-12'::date
) as dates

场景2：使用其他数据库 #

不同数据库的数组打平语法不同，对应替换即可：

• PostgreSQL：用UNNEST函数替代

with cte as (
    SELECT 
        array['2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date] AS array
        ,'2021-09-11'::date AS max_date
        ,date_filter(
            '2021-09-12'::date,
            array['2021-09-10'::date,'2021-09-11'::date,'2021-09-12'::date]
        ) as dates 
)
SELECT TO_CHAR(unnest(dates), 'yyyy-mm-dd') AS format_date FROM cte;

• MySQL 8.0+：用JSON_TABLE配合JSON数组处理

with cte as (
    SELECT 
        JSON_ARRAY('2021-09-10','2021-09-11','2021-09-12') AS array
        ,'2021-09-11' AS max_date
        ,date_filter(
            '2021-09-12',
            JSON_ARRAY('2021-09-10','2021-09-11','2021-09-12')
        ) as dates 
)
SELECT DATE_FORMAT(jt.date_val, '%Y-%m-%d') AS format_date 
FROM cte
JOIN JSON_TABLE(
    cte.dates,
    '$[*]' COLUMNS (date_val DATE PATH '$')
) jt;

内容的提问来源于stack exchange，提问作者RagePwn