szhswl
宋针还的个人空间
create materialized view v_acct_view refresh complete on demand
start with sysdate
next trunc(sysdate+1)+2/24   
enable query rewrite
as
select area.areaname                   areaname,
       latn.Latnname                   latnname,
       count(cust.custid)              custid,
       to_char(vMain.updatedate,'yyyy-mm-dd')             Dday,
       to_char(vMain.updatedate,'yyyy-mm')                Ymonth,
       to_char(vMain.updatedate,'q')                      Qquater,
       count(vMain.acctid)             sum_acctid,
       sum(vMain.Jftotal)              sum_jftotal,
       sum(vMain.Jfusable)             sum_jfusable,
       sum(vMain.Jfconsume)            sum_jfconsume,
       sum(vMain.Jfinline)             sum_jfinline,
       sum(vMain.Jfsales)              sum_jfsales,
       sum(vMain.Jfencourage)          sum_Jfencourage,
       round(avg(vMain.Jftotal))       avg_jftotal,
       round(avg(vMain.Jfusable))      avg_jfusable,
       round(avg(vMain.Jfconsume))     avg_jfconsume,
       round(avg(vMain.Jfinline))      avg_jfinline,
       round(avg(vMain.Jfsales))       avg_jfsales,
       round(avg(vMain.Jfencourage))   avg_Jfencourage
from acct vMain
left join latn latn on latn.latnid = vMain.latnid
left join area area on area.areaid = vMain.areaid
group by
    latn.latnname,
    area.areaname,
    to_char(vMain.updatedate,'q') ,
    to_char(vMain.updatedate,'yyyy-mm'),
    to_char(vMain.updatedate,'yyyy-mm-dd')


---------------------------------------------------------------------------------------------------------------------------------
说人之短,乃护己之短。夸己之长,乃忌人之长。皆由存心不厚,识量太狭耳。能去此弊,可以进德,可以远怨。
http://www.blogjava.net/szhswl
------------------------------------------------------------------------------------------------------ ----------------- ---------
posted on 2007-12-03 16:38 宋针还 阅读(479) 评论(0)  编辑  收藏 所属分类: SQL

只有注册用户登录后才能发表评论。


网站导航: