使用Snowflake CTE查询报错error line 0 at position -1，求排查

你的查询出现error line 0 at position -1是因为存在多处语法和逻辑问题，具体如下：

• 连接语法不规范：from ll join acc, prod混合了显式JOIN和隐式逗号连接的写法，Snowflake不支持这种混用方式。由于ll和acc都是单一行的聚合结果，prod是多行数据，应该使用显式的CROSS JOIN来关联这三个CTE，明确笛卡尔积的逻辑。

• 同级列别名引用错误：select子句中直接用ll_count / total_num_acc引用同一级定义的别名，Snowflake不允许在同一个SELECT层级中引用刚定义的别名，必须使用原始的表别名+列名，比如ll.count / acc.total。

• GROUP BY子句不完整：SELECT中的ll_count和total_num_acc虽然是单值聚合结果，但在严格模式下，非聚合列、非分组列必须被聚合函数包裹或加入GROUP BY。不过因为这两个值是全局唯一的，可以用MAX()或MIN()包裹，或者调整分组逻辑。

修正后的查询语句 #

with 
ll as (
   select 
     count(*) as count
   from a.view
   where s_type = 'LL'
),
acc as (
   select 
      sum(num_acc) as total
   from b.view
),
prod as (
   select
      prod_version as prod
   from c.view
)
select
  prod.prod as product_type,
  ll.count as ll_count,
  acc.total as total_num_acc,
  ll.count / acc.total as ch
from ll
cross join acc
cross join prod
group by product_type, ll_count, total_num_acc;

如果ll和acc的聚合结果唯一，也可以简化写法（无需GROUP BY）：

with 
ll as (
   select 
     count(*) as count
   from a.view
   where s_type = 'LL'
),
acc as (
   select 
      sum(num_acc) as total
   from b.view
),
prod as (
   select
      prod_version as prod
   from c.view
)
select
  prod.prod as product_type,
  (select count from ll) as ll_count,
  (select total from acc) as total_num_acc,
  (select count from ll) / (select total from acc) as ch
from prod;

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