在sql笔记本中创建表时出现未知错误

qyzbxkaa  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(462)

我试图在sql notebook中使用以下语法创建一个表,但不断出现如下错误:“expected list item:”my code:

create table
   player_info as 
( 
select distinct id,
         name,
         sex,
         age,
         case(when height is NULL then floor(avg(height)) else height end) as Hght,
         case(when weight is NULL then floor(avg(weight)) else weight end ) as Wght,
         team as country,
         city
from athlete_events
group by id,
         name,
         sex,
         age
)

例如,我想从主数据集中使用select子句中提到的字段创建一个表,以便删除空值。预期的输出应该是一个表,其中age、height和weight列中没有空值。
编辑:我找到了解决这个问题的方法,实际上是括号,它是case语句和select子句的问题,它不应该包含在括号中,它认为它是子查询的一部分。考虑到条件,我使用“na”作为该列的数据类型的还有文本。代码如下所示:

create table
   player_info as 

select distinct id,
         name,
         sex,
         age,
         (case when height ='NA' then floor(avg(height)) else height end) as Hght,
         (case when weight ='NA' then floor(avg(weight)) else weight end ) as Wght,
         team as country,
         city
from athlete_events
group by id,
         name,
         sex,
         age
o4hqfura

o4hqfura1#

程序正在抱怨最外面的括号:

case(when height is NULL then floor(avg(height)) else height end) as Hght,

下一行也需要更正。 CASE 是表达式,不是函数。您可以查看文档中的正确语法。
要注意,这样做并不能达到既定的目标。子查询基本上只在一个 id 一次。如果特定行中的高度或重量为空, floor(avg(height)) 将为0。

3duebb1j

3duebb1j2#

括号之间不允许有括号 case 以及 when . 而不是:

case(when height is NULL then floor(avg(height)) else height end) as Hght,
     case(when weight is NULL then floor(avg(weight)) else weight end ) as Wght,

用途:

(case when height is NULL then floor(avg(height)) else height end) as Hght,
     (case when weight is NULL then floor(avg(weight)) else weight end ) as Wght,

我真诚地怀疑这是否是你真正想要的。 height 以及 weight 不在 group by . 事实上,我怀疑 group by 你真的想要:

select id, name, sex, age,
       coalesce(height, floor(avg(height) over ()),
       coalesce(weight, floor(avg(weight) over ())
       team as country, city
from athlete_events

相关问题