用netezza中的新值更新列

vatpfxk5 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(249)

我对解决这个问题有疑问。我的表有model\u name、device\u type、count\u device\u type和new\u device\u type。我想为每个型号更新当前设备类型列，使其只有一个设备类型（请参见表中的示例）


**MODEL               DEVICE_TYPE    COUNT_DEVICE_TYPE          NEW_DEVICE_TYPE**

SAMUNG GALAXY S5      SMARTPHONE             100                SMARTPHONE
SAMUNG GALAXY S5      PORTABLE PDA           30                 SMARTPHONE
SAMUNG GALAXY S5      HANDHELD               10                 SMARTPHONE

我已尝试此代码，但无法捕获新值：

update tmp_BI_device_table a
a.device_type = b.new_device_type
from (
     select
     model
     ,device_type
     ,case when count(model)<40 then 
     (select distinct device_type from tmp_BI_dim_device_ref a group by model, device_type having count(model)>10 ) else device_type end as new_device_type
from tmp_BI_device_table
group by 1,2
 )

我收到这个错误：

ERROR:  12 : More than one tuple returned by a subselect used as an expression

sql case netezza

来源：https://stackoverflow.com/questions/61951219/update-column-with-new-value-in-netezza

1条答案

按热度按时间

fhg3lkii1#

如果您想要具有最高计数的设备类型，可以使用 first_value() :

select model, device_type, count(*),
       first_value(device_type) over (partition by model order by count(*) desc) as imputed_device_type
from tmp_BI_device_table
group by 1, 2

赞(0）回复(0）举报 2021-08-13

我来回答

用netezza中的新值更新列

1条答案

相关问题

热门标签

最新问答