Snowflake UDTF子查询类型不支持问题的解决方法问询

问题背景 #

在Snowflake中尝试创建UDTF（用户定义表函数），用于根据表x或y中符合特定模式的行是否存在，为表v的每行生成标记，以此减少代码量和维护成本，但遇到错误：
SQL Error [2031] [42601]: SQL compilation error: Unsupported subquery type cannot be evaluated

最小复现代码 #

DROP TABLE IF EXISTS v;
CREATE TABLE v (CHARTGUID NUMBER(16));
INSERT INTO v VALUES(1410522400170);
INSERT INTO v VALUES(1548089000170);

DROP TABLE IF EXISTS x;
CREATE TABLE x (CHARTGUID NUMBER(16), FOO INT, BAR VARCHAR(1));
INSERT INTO x(CHARTGUID, FOO, BAR) VALUES (1410522400170, 1, 'a');
INSERT INTO x(CHARTGUID, FOO, BAR) VALUES (1548089000170, 0, 'a');

DROP TABLE IF EXISTS y;
CREATE TABLE y (CHARTGUID NUMBER(16), FOO INT, BAR VARCHAR(1));
INSERT INTO y(CHARTGUID, FOO, BAR) VALUES (1410522400170, 0, 'a');
INSERT INTO y(CHARTGUID, FOO, BAR) VALUES (1548089000170, 1, 'a');
INSERT INTO y(CHARTGUID, FOO, BAR) VALUES (1548089000170, 1, 'b');

CREATE OR REPLACE FUNCTION TEST_C(CHART_GUID NUMBER, p VARCHAR)
   RETURNS TABLE(A INT, B INT)
AS
$$
   SELECT
      CASE WHEN EXISTS (
         SELECT x.CHARTGUID
         FROM x
         WHERE x.CHARTGUID = CHART_GUID
            AND x.FOO = 1
            AND x.BAR = p
      ) THEN 1 ELSE 0 END,
      CASE WHEN EXISTS (
         SELECT y.CHARTGUID
         FROM y
         WHERE y.CHARTGUID = CHART_GUID
            AND y.FOO = 1
            AND y.BAR = p
      ) THEN 1 ELSE 0 END
$$
;

SELECT
   v.CHARTGUID,
   x.*
FROM v,
   TABLE(TEST_C(v.CHARTGUID, 'a')) x

实际场景痛点 #

实际需要多次调用TABLE(TEST_C(v.CHARTGUID, 'a'))（其中'a'为正则模式），希望避免大量重复子查询的写法：

SELECT 
   CASE WHEN EXISTS (
      SELECT x.CHARTGUID
      FROM x
      WHERE x.CHARTGUID = v.CHARTGUID
         AND x.BAR LIKE 'a%'
   ) THEN 1 ELSE 0 END AS "Ax",
   CASE WHEN EXISTS (
      SELECT y.CHARTGUID
      FROM y
      WHERE y.CHARTGUID = v.CHARTGUID
         AND y.BAR LIKE 'a%'
   ) THEN 1 ELSE 0 END AS "Ay",
   CASE WHEN EXISTS (
      SELECT x.CHARTGUID
      FROM x
      WHERE x.CHARTGUID = v.CHARTGUID
         AND x.BAR LIKE 'b%'
   ) THEN 1 ELSE 0 END AS "Bx",
   CASE WHEN EXISTS (
      SELECT y.CHARTGUID
      FROM y
      WHERE y.CHARTGUID = v.CHARTGUID
         AND y.BAR LIKE 'b%'
   ) THEN 1 ELSE 0 END AS "By",
   CASE WHEN EXISTS (
      SELECT x.CHARTGUID
      FROM x
      WHERE x.CHARTGUID = v.CHARTGUID
         AND x.BAR LIKE 'c%'
   ) THEN 1 ELSE 0 END AS "Cx",
   CASE WHEN EXISTS (
      SELECT y.CHARTGUID
      FROM y
      WHERE y.CHARTGUID = v.CHARTGUID
         AND y.BAR LIKE 'c%'
   ) THEN 1 ELSE 0 END AS "Cy",
   -- etc...
FROM v

解决方案 #

方法1：改用聚合函数替代EXISTS子查询 #

Snowflake的UDTF中不支持关联EXISTS子查询，可以将EXISTS替换为COUNT_IF()或MAX()实现相同逻辑：

CREATE OR REPLACE FUNCTION TEST_C(CHART_GUID NUMBER, p VARCHAR)
   RETURNS TABLE(A INT, B INT)
AS
$$
   SELECT
      -- 用COUNT_IF判断是否存在符合条件的行，存在返回1，否则0
      COUNT_IF(x.CHARTGUID = CHART_GUID AND x.FOO = 1 AND x.BAR = p) > 0::INT,
      COUNT_IF(y.CHARTGUID = CHART_GUID AND y.FOO = 1 AND y.BAR = p) > 0::INT
   FROM x, y
$$
;

或使用MAX函数：

CREATE OR REPLACE FUNCTION TEST_C(CHART_GUID NUMBER, p VARCHAR)
   RETURNS TABLE(A INT, B INT)
AS
$$
   SELECT
      COALESCE(MAX(CASE WHEN x.CHARTGUID = CHART_GUID AND x.FOO = 1 AND x.BAR = p THEN 1 ELSE 0 END), 0),
      COALESCE(MAX(CASE WHEN y.CHARTGUID = CHART_GUID AND y.FOO = 1 AND y.BAR = p THEN 1 ELSE 0 END), 0)
   FROM x, y
$$
;

方法2：预聚合数据后关联查询 #

如果UDTF方式存在性能问题，可以预先聚合x和y表的数据，再与v表关联，避免重复子查询：

-- 预聚合x表标记
WITH x_agg AS (
   SELECT
      CHARTGUID,
      BAR,
      MAX(CASE WHEN FOO = 1 THEN 1 ELSE 0 END) AS has_match
   FROM x
   GROUP BY CHARTGUID, BAR
),
-- 预聚合y表标记
y_agg AS (
   SELECT
      CHARTGUID,
      BAR,
      MAX(CASE WHEN FOO = 1 THEN 1 ELSE 0 END) AS has_match
   FROM y
   GROUP BY CHARTGUID, BAR
),
-- 维护需要匹配的模式列表
patterns AS (
   SELECT 'a' AS pattern UNION ALL SELECT 'b' UNION ALL SELECT 'c'
)
SELECT
   v.CHARTGUID,
   MAX(CASE WHEN p.pattern = 'a' THEN x.has_match ELSE 0 END) AS Ax,
   MAX(CASE WHEN p.pattern = 'a' THEN y.has_match ELSE 0 END) AS Ay,
   MAX(CASE WHEN p.pattern = 'b' THEN x.has_match ELSE 0 END) AS Bx,
   MAX(CASE WHEN p.pattern = 'b' THEN y.has_match ELSE 0 END) AS By,
   MAX(CASE WHEN p.pattern = 'c' THEN x.has_match ELSE 0 END) AS Cx,
   MAX(CASE WHEN p.pattern = 'c' THEN y.has_match ELSE 0 END) AS Cy
FROM v
CROSS JOIN patterns p
LEFT JOIN x_agg x ON v.CHARTGUID = x.CHARTGUID AND x.BAR LIKE p.pattern || '%'
LEFT JOIN y_agg y ON v.CHARTGUID = y.CHARTGUID AND y.BAR LIKE p.pattern || '%'
GROUP BY v.CHARTGUID;

这种方式只需维护patterns中的模式列表，扩展性更强。

方法3：使用标量函数替代UDTF #

如果仅需返回单行标记，可创建独立标量函数分别处理x和y表的判断，再组合调用：

CREATE OR REPLACE FUNCTION CHECK_X(CHART_GUID NUMBER, p VARCHAR)
RETURNS INT
AS
$$
   SELECT COUNT_IF(CHARTGUID = CHART_GUID AND FOO = 1 AND BAR = p) > 0::INT
   FROM x
$$
;

CREATE OR REPLACE FUNCTION CHECK_Y(CHART_GUID NUMBER, p VARCHAR)
RETURNS INT
AS
$$
   SELECT COUNT_IF(CHARTGUID = CHART_GUID AND FOO = 1 AND BAR = p) > 0::INT
   FROM y
$$
;

-- 查询调用
SELECT
   v.CHARTGUID,
   CHECK_X(v.CHARTGUID, 'a') AS Ax,
   CHECK_Y(v.CHARTGUID, 'a') AS Ay,
   CHECK_X(v.CHARTGUID, 'b') AS Bx,
   CHECK_Y(v.CHARTGUID, 'b') AS By,
   CHECK_X(v.CHARTGUID, 'c') AS Cx,
   CHECK_Y(v.CHARTGUID, 'c') AS Cy
FROM v;

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