gpt4 book ai didi

sql - 无法在 presto 中将 '' 转换为 bigint

转载 作者:行者123 更新时间:2023-12-03 17:04:10 24 4
gpt4 key购买 nike

我试图在我的查询中清除 'null',但运行时仍然出错,
一直告诉我 'cannot cast'' 到 bigint,有什么办法可以解决吗?
我的查询

select m.app, m.hour,m.user_id, 
m.avg_minutes_watched, n.userid, n.watched_mins, n.active FROM

(SELECT app,
substr(hour,1,8) hour,
CAST(COALESCE(json_extract_scalar(json, '$.user_id'), '-999999') as BigInt) user_id,
CAST(COALESCE(json_extract_scalar(json, '$.playback_time'), '-999999') as BigInt) /60000 avg_minutes_watched
FROM prod
WHERE event_type = 'user_session_complete' AND hour > '20180331' and hour < '20180501')m

left join

(select userid, watched/60000 watched_mins,
(case when watched/60000 >= 2 then 'active' else 'not_active' end) active from est where realdate > '2018-03-31' and realdate < '2018-05-01') n

on m.user_id = n.userid
order by m.hour, m.user_id;
错误
Query 20180510_220127_17857_bxg5s, FAILED, 72 nodes
Splits: 5,178 total, 644 done (12.44%)
0:04 [39.2M rows, 1.93GB] [9.32M rows/s, 469MB/s]
查询 20180510_220127_17857_bxg5s 失败:无法将 '' 转换为 BIGINT

最佳答案

如果转换失败,TRY_CAST 将返回 null:

TRY_CAST(json_extract_scalar(json, '$.user_id') as BigInt) user_id 

关于sql - 无法在 presto 中将 '' 转换为 bigint,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50282162/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com