ParseException: SQL

Question

result = aml_identity_g.connectedComponents()
conn_comps = result.select("id", "component",'type') \
  .createOrReplaceTempView("components") 
display(result)

这创造了

​

​

%sql
create table temptable 
as with dupes as (
   select component, count(case when type = 'Person' then 1 end) person_ct 
   from components 
   group by component 
   having person_ct > 1
)

将我抛出一个错误

Error in SQL statement: ParseException: 
mismatched input '<EOF>' expecting {'(', 'DESC', 'DESCRIBE', 'FROM', 'MAP', 'REDUCE', 'SELECT', 'TABLE', 'VALUES'}(line 6, pos 21)
   
== SQL ==
create table temptable 
as with dupes as (
    select component, count(case when type = 'Person' then 1 end) 
person_ct 
    from components 
    group by component 
    having person_ct > 1
)
---------------------^^^

不明白这里的错误。

Answer 1

在这个查询中，您真的不需要CTE --应该足够有正常的SELECT了。所以应该是这样的：

create table temptable as (
   select component, count(case when type = 'Person' then 1 end) person_ct 
   from components 
   group by component 
   having person_ct > 1
)

根据关于拥有条款的文件，您可以在HAVING表达式中使用别名。