请问下面的物化视图有什么问题?用内层的sql可以建,但是加了外层的sql后就无法创建视图了
错误“不能从复杂查询中创建一个可快速刷新的实体化视图”create materialized view mv_channel_hour_1
refresh fast
start with sysdate next (sysdate+1/24)
as
select vtime, eid, count(sessionid) as visiter_num, sum(vtimes) as visit_times
from
(
select
to_char(enter_time,'yyyymmdd hh24') as vtime,
eid,
sessionid,
count(id) as vtimes
from
WAP_T_USER_CHANNEL_ACCESS
group by
to_char(enter_time,'yyyymmdd hh24'),
eid,
sessionid
)b
group by
vtime,eid
错误“不能从复杂查询中创建一个可快速刷新的实体化视图”create materialized view mv_channel_hour_1
refresh fast
start with sysdate next (sysdate+1/24)
as
select vtime, eid, count(sessionid) as visiter_num, sum(vtimes) as visit_times
from
(
select
to_char(enter_time,'yyyymmdd hh24') as vtime,
eid,
sessionid,
count(id) as vtimes
from
WAP_T_USER_CHANNEL_ACCESS
group by
to_char(enter_time,'yyyymmdd hh24'),
eid,
sessionid
)b
group by
vtime,eid
有可能是 对eid进行了2次group by的缘故吧!
固也是group by 问题.
http://hi.baidu.com/mars_mars/blog/item/f744d10e721a5ae5aa6457b2.html