Snowflake仅用SELECT查询列唯一值计数的语法错误问题

需要仅通过SELECT语句查询INFORMATION_SCHEMA，获取指定schema（abc）和表（xyz）中所有列的唯一值计数，输出格式要求如下：

table_name | column_name | no_of_unique_elements
-----------|-------------|-----------------------
xyz        | col1        | 100
xyz        | col2        | 50
...

尝试的SQL语句存在语法错误：

select 'xyz' as table_name,'{1}', 
count(0) from 
  (select {0} 
   from {1} 
   group by {0} 
   having count(0) > 1 
   from (
     select ARRAY_TO_STRING(array_agg(column_name),',')
     from first_db.information_schema.columns 
     where table_schema='abc' 
     and TABLE_NAME='xyz'));

报错信息：

SQL compilation error: syntax error line 1 at position 8 unexpected '0'. syntax error line 1 at position 17 unexpected '1'. syntax error line 1 at position 30 unexpected '0'. syntax error line 1 at position 53 unexpected 'from'.

限制条件：仅拥有SELECT权限，无法创建存储过程、视图或临时表。

由于无法使用动态SQL或存储过程，我们通过拼接UNION ALL查询的方式，为每一列单独统计唯一值数量，分两步实现：

1. 生成单列统计的SQL片段 #

先从INFORMATION_SCHEMA中提取目标表的所有列，生成对应列的统计语句片段：

SELECT 
  CONCAT(
    'SELECT ''xyz'' AS table_name, ''', column_name, ''' AS column_name, COUNT(DISTINCT ', column_name, ') AS no_of_unique_elements FROM abc.xyz'
  ) AS sql_fragment
FROM first_db.information_schema.columns
WHERE table_schema = 'abc'
  AND table_name = 'xyz';

该查询会输出多条类似如下的语句片段：

SELECT 'xyz' AS table_name, 'col1' AS column_name, COUNT(DISTINCT col1) AS no_of_unique_elements FROM abc.xyz
SELECT 'xyz' AS table_name, 'col2' AS column_name, COUNT(DISTINCT col2) AS no_of_unique_elements FROM abc.xyz
...

2. 拼接完整查询并执行 #

将第一步生成的所有SQL片段用UNION ALL连接，得到最终可执行的查询语句，示例如下：

SELECT 'xyz' AS table_name, 'col1' AS column_name, COUNT(DISTINCT col1) AS no_of_unique_elements FROM abc.xyz
UNION ALL
SELECT 'xyz' AS table_name, 'col2' AS column_name, COUNT(DISTINCT col2) AS no_of_unique_elements FROM abc.xyz
UNION ALL
SELECT 'xyz' AS table_name, 'col3' AS column_name, COUNT(DISTINCT col3) AS no_of_unique_elements FROM abc.xyz;

执行该语句即可得到符合需求的输出结果。

1. 错误使用{0}、{1}这类非SQL原生占位符，SQL本身不支持该语法，需通过字符串函数生成合法语句
2. 子查询嵌套结构错误，HAVING子句后不能直接跟FROM关键字
3. 统计逻辑错误：COUNT(0)无法统计唯一值数量，需用COUNT(DISTINCT column)直接计算列的唯一值个数

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