大佬教程收集整理的这篇文章主要介绍了postgresql – 从查询更新Redshift表,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
update mr_usage_au au inner join(SELEct mr.UserID,date(mr.ActionDatE) as ActionDate,count(case when mr.EventID in (32) then mr.UserID end) as Moods,count(case when mr.EventID in (33) then mr.UserID end) as ActivitIEs,sum(case when mr.EventID in (10) then mr.Duration end) as Duration from mr_session_log mr where mr.EventTime >= current_date - interval '1 days' and mr.EventTime < current_date Group By mr.UserID,date(mr.ActionDatE)) slog on slog.UserID=au.UserID and slog.ActionDate=au.Date set au.Moods = slog.Moods,au.ActivitIEs=slog.ActivitIEs,au.Durarion=slog.Duration
但是我收到以下错误:
应该这样工作(至少在目前的POSTGRes上):
updatE mr_usage_au SET Moods = slog.Moods,ActivitIEs = slog.ActivitIEs,Durarion = slog.Duration FROM ( SELEct UserID,ActionDate::date,count(CASE WHEN EventID = 32 THEN UserID END) AS Moods,count(CASE WHEN EventID = 33 THEN UserID END) AS ActivitIEs,sum(cASE WHEN EventID = 10 THEN Duration END) AS Duration FROM mr_session_log WHERE EventTime >= current_date - 1 -- just subtract Integer from a date AND EventTime < current_date GROUP BY UserID,ActionDate::date ) slog WHERE slog.UserID = mr_usage_au.UserID AND slog.ActionDate = mr_usage_au.Date;
POSTGRes和Redshift通常就是这种情况:
>使用FROM子句连接其他表.
>您不能在SET子句中对目标列进行表限定.
还有,Redshift was forked from POSTGReSQL 8.0.2,这是很久以前的事了.只应用了POSTGRes的一些后续更新.
>例如,POSTGRes 8.0 did not allow a table alias in an updatE
statement,yet – 这就是你看到的错误背后的原因.
我简化了其他一些细节.
以上是大佬教程为你收集整理的postgresql – 从查询更新Redshift表全部内容,希望文章能够帮你解决postgresql – 从查询更新Redshift表所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。