代码之家 › 专栏 › 技术社区 › Umar.H

循环游标和动态SQL-雪花

snowflake-cloud-data-platform sql

Umar.H · 技术社区 · 2 年前

我正试图编写一个过程,该过程接受表列表和date_column,以按日历日期创建一些row_counts,用于对账。

SELECT t.*
FROM (
  VALUES ('tbl1', 'created_date')
       , ('tbl2', 'modify_date')
       , ('tbl3', 'last_seen_date')
  ) t(tbl, dt)

+----+--------------+
|TBL |DT            |
+----+--------------+
|tbl1|created_date  |
|tbl2|modify_date   |
|tbl3|last_seen_date|
+----+--------------+

我使用Datagrip通过JDBC连接连接到Snowflake,所以我认为我需要遵循文档中的经典SnowSQL部分:

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/loops.html#cursor-based-for-loops

EXECUTE IMMEDIATE $$

  DECLARE
    dt text
    , tbl text;
    c1 CURSOR FOR SELECT dt, tbl from t;
    BEGIN
      FOR record in c1 DO
        dt := record.dt
        tbl := record.tbl
        stmt =: 'SELECT COUNT(*)' ||
           CONCAT(', DAYOFMONTH(', $dt, ')') ||
           CONCAT('\n FROM ', $tbl) ||
           CONCAT('\n WHERE YEAR(', $dt, ')', ' = YEAR(CURRENT_DATE)') ||
           CONCAT('\n AND MONTH(', $dt, ')', ' = MONTH(CURRENT_DATE)') ||
           '\n GROUP BY' ||
           CONCAT('\n DAYOFMONTH(', $dt, ')')
      EXECUTE IMMEDIATE stmt -- will adapt this to be an update statement eventually. 
      END FOR
      end;
$$

这返回了一个SQL编译错误,我已经尝试了一些不同的变体,但我不知道如何继续。

1 回复 | 直到 2 年前

Lukasz Szozda 2 年前

可以使用绑定变量重写查询字符串,而不是连接查询字符串,这会使其几乎无法读取:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    
    stmt := 'INSERT INTO result(cnt, day_of_month)
             SELECT COUNT(*), DAYOFMONTH(IDENTIFIER(?)) AS day_of_month
             FROM TABLE(?)
             WHERE YEAR(IDENTIFIER(?)) = YEAR(CURRENT_DATE)
               AND MONTH(IDENTIFIER(?)) = MONTH(CURRENT_DATE)
             GROUP BY day_of_month';
             
     EXECUTE IMMEDIATE :stmt USING (dt, tbl, dt, dt); 
     
     RETURN stmt;
  END FOR;
END;

如果列或表是参数,则应将其包装为 IDENTIFIER / TABLE 功能。

对于示例数据:

CREATE OR REPLACE TABLE t AS
SELECT  'col1' AS dt, 'tab1' AS tbl UNION ALL 
SELECT  'col2' AS dt, 'tab1' ;

CREATE TABLE tab1(col1 DATE, col2 DATE) AS
SELECT CURRENT_DATE(), CURRENT_DATE()-40;

CREATE TABLE result(cnt INT, day_of_month INT);

SELECT * FROM result;

Gokhan Atil 2 年前

有很多小问题,比如缺少分号等。以下是修复的脚本:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    stmt := 'SELECT COUNT(*)' ||
       CONCAT(', DAYOFMONTH(', dt, ')') ||
       CONCAT('\n FROM ', tbl) ||
       CONCAT('\n WHERE YEAR(', dt, ')', ' = YEAR(CURRENT_DATE)') ||
       CONCAT('\n AND MONTH(', dt, ')', ' = MONTH(CURRENT_DATE)') ||
       '\n GROUP BY' ||
       CONCAT('\n DAYOFMONTH(', dt, ')');
   -- EXECUTE IMMEDIATE :stmt; 
     RETURN stmt;
  END FOR;
END;