Redshift中的溢出错误
Redshift中的溢出错误
提问于 2018-06-06 04:01:06
我在Amazon Redshift上运行以下非常简单的查询
SELECT member_id,
COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE member_id in (select memberid from auth.members)
group by member_id但这给了我以下错误：
An error occurred when executing the SQL command:
SELECT member_id,
COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE member_id IN (SELEC...
[Amazon](500310) Invalid operation: Overflow (Integer valid range -2147483648 to 2147483647)
Details:
-----------------------------------------------
error: Overflow (Integer valid range -2147483648 to 2147483647)
code: 1207
context: Value: '2952163128494839000', Character: '8'
query: 2748804
location: :0
process: query5_95_2748804 [pid=0]
-----------------------------------------------;
Execution time: 2.11s
1 statement failed.我检查了一下，表members的memberid不是'2952163128494839000‘。我还尝试使用CONVERT和CAST函数将memberid转换为integer，但得到了相同的错误。
我为什么要面对这个错误，我该如何着手修复它？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-06 04:03:26
大概，您在成员id比较时遇到了问题。我发现你不太可能有足够的日期来溢出count(distinct)。
将它们转换为字符串：
SELECT member_id,
COUNT(DISTINCT TO_CHAR(created_at,'YYYY-MM-DD')) AS days
FROM ads.fbs_modality_staging
WHERE cast(member_id as varchar(255)) in (select cast(memberid as varchar(255)) from auth.members)
group by member_id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50708052
复制相关文章
相似问题
腾讯云开发者
