基于另一个表中的数据在 FOR LOOP 中执行 Snowflake SQL 查询

我会构建一个 SQL 块并在最后运行它。

使用简单的 SELECT 作为模式来显示构建过程的初始化，您可以从以下位置编写自己的动态 sql：

WITH table_list as (
    SELECT * FROM VALUES
    ('Table1', 'PLANTS', 'TEXT'),
    ('Table1', 'HEIGHT', 'FLOAT'),
    ('Table2', 'COLOR', 'TEXT'),
    ('Table2', 'SMELL', 'TEXT')
    v(tablename, column_name, data_type)
), to_rows as (
    SELECT
        tablename
        ,CASE data_type
            WHEN 'TEXT' THEN 'SELECT ''tablename'' as TABLENAME, ''column_name'' as COLUMN_NAME, ''data_type'' as DATA_TYPE, count(column_name) as count, MAX(LEN(column_name)) as max_length, null as max_value FROM tablename '
            WHEN 'FLOAT' THEN 'SELECT ''tablename'' as TABLENAME, ''column_name'' as COLUMN_NAME, ''data_type'' as DATA_TYPE, count(column_name) as count, null as max_length, MAX(column_name) as max_value FROM tablename '
        END as sql
        ,REPLACE(REPLACE(REPLACE(sql, 'data_type', data_type), 'column_name', column_name), 'tablename', tablename) as final_sql
    FROM table_list
)
SELECT
    listagg (final_sql, ' UNION ALL ') within group(order by tablename) as the_big_sql
FROM to_rows;

这使：

如果针对这些表运行：

create table table1(plants text, height float);
create table table2(color text, smell text);

insert into table1 values ('big plant', 10.1),('medium plant', 5.3),('tiny', 1.0);
insert into table2 values ('red', 'bold'), ('blue', 'weak');

给出：

但这是为您完全编写的动态答案：

首先制作要完成的工作的表格：

CREATE TABLE PROFILE_TABLE_LIST AS 
SELECT * FROM VALUES
    ('Table1', 'PLANTS', 'TEXT'),
    ('Table1', 'HEIGHT', 'FLOAT'),
    ('Table2', 'COLOR', 'TEXT'),
    ('Table2', 'SMELL', 'TEXT')
    v(tablename, column_name, data_type);

并使用之前创建的“真实数据表”，我们可以使用：

declare
    sql string;
    final_sql string;
    c1 cursor for (select * from PROFILE_TABLE_LIST);
    res resultset;
begin
  final_sql := '';
  
  for record in c1 do
    if(record.data_type = 'TEXT') THEN
        sql := 'SELECT '''||record.tablename||''' as TABLENAME, '''||record.column_name||''' as COLUMN_NAME, '''||record.data_type||''' as DATA_TYPE, count('||record.column_name||') as count, MAX(LEN('||record.column_name||')) as max_length, null as max_value FROM '||record.tablename||' ';
    else
        sql := 'SELECT '''||record.tablename||''' as TABLENAME, '''||record.column_name||''' as COLUMN_NAME, ''data_type'' as DATA_TYPE, count('||record.column_name||') as count, null as max_length, MAX('||record.column_name||') as max_value FROM '||record.tablename||' ';
    end if;
    if(final_sql<>'')then 
        final_sql := final_sql || ' UNION ALL ';
    end if;
    final_sql := final_sql || sql;
        
  end for;

  res := (execute immediate :final_sql);
  return table(res);
end;

给出：