postgresql – 从查询更新Redshift表
发布时间:2020-12-13 18:09:45 所属栏目:百科 来源:网络整理
导读:我正在尝试从查询中更新Redshift中的表: update mr_usage_au auinner 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
我正在尝试从查询中更新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 但是我收到以下错误:
对于Redshift(或Postgres),这是完全无效的语法.让我想起了SQL Server ……
应该这样工作(至少在目前的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子句连接其他表. 还有,Redshift was forked from PostgreSQL 8.0.2,这是很久以前的事了.只应用了Postgres的一些后续更新. >例如,Postgres 8.0 did not allow a table alias in an 我简化了其他一些细节. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |