您的位置：程序门 -> db2 -> 数据管理



大数据量分类汇总


[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]


大数据量分类汇总
发表于：2007-12-14 20:20:11 楼主
系统中有张表二千多万的数据，要进行一些类型分类汇总
本人不才，写出语句运行无法成功......望高手指点指点.急.
表结构如下
主键     部门     状态       类型       进入时间           发出时间
id       dept   state   type         indate         outdate

本人汇总的语句如下：
select   dept,type     ,sum（t.autousecount)   autousecount,sum（t.handusecount)   handusecount,sum（t.cancellusecount)   cancellusecount,sum（t.blankusecount)   blankusecount,sum（t.lossusecount)   lossusecount,sum（t.indatecount)   indatecount,sum（t.outdatecount)   outdatecount,sum（t.fdatecount)   fdatecount,sum（t.bdatecount)   bdatecount   from
  （select   dept,type     ,count（state)   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,0   lossusecount,0   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo     where   state=   '2'     group   by   dept,type    
    union   all   select   dept,type     ,0   autousecount,   count（state)   handusecount,0   cancellusecount,0   blankusecount,0   lossusecount,0   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo     where   state=   '3'     group   by   dept,type    
    union   all   select   dept,type     ,   0   autousecount,   0   handusecount,count（state)   cancellusecount,0     blankusecount,0   lossusecount,0   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo   where   state=   '4'     group   by   dept,type    
    union   all   select   dept     ,type     ,0   autousecount,   0   handusecount,0   cancellusecount,count（state)   blankusecount,0   lossusecount,0   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo   where   state=   '5'     group   by   dept,type    
    union   all   select   dept     ,type     ,   0   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,count（state)   lossusecount,0   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo   where   state=   '6'     group   by   dept     ,type    
    union   all   select   dept     ,type   ,   0   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,   0   lossusecount,   count（outdate)   indatecount,0   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo   state=   '1'   and   outdate   is     null   group   by   dept     ,type    
    union   all   select   dept     ,type   ,   0   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,   0   lossusecount,   0   indatecount,0   outdatecount,0   fdatecount   ,count（*)   bdatecount   from   tk_dzinfo   where   state=   '1'   and   indate   is   null   group   by   dept,   type    
    union   all   select   dept     ,type   ,   0   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,   0   lossusecount,   0   indatecount,count（outdate)   outdatecount,0   fdatecount,0   bdatecount   from   tk_dzinfo   where   state=   '1'   and   boutdate   is   not   null   group   by   dept     ,type    
    union   all   select   dept     ,type   ,   0   autousecount,   0   handusecount,0   cancellusecount,0   blankusecount,   0   lossusecount,   0   indatecount,0   outdatecount   ,count（*)   fdatecount,0   bdatecount   from   tk_dzinfo   where   state=   '1'   and   indate   is   not   null   group   by   dept     ,type
该汇总条件区分主要是状态（state)与进入时间（indate)与发出时间（outdate)   几个条件的不同汇总不同的结果,而且sate='1'时有多个,所以一时只能写出如此语句,但由于该表数据量过大（两千万条以上）所以一执行就等于死机,   我想有什么方法可替换union   all方法的占用表资源少的其他方法，或有其他更好的方法汇总，希望有高手能帮下，急求解决方案！谢谢大家帮忙看下  
发表于：2007-12-17 19:40:211楼 得分:0
如果   indate\bdate\outdate\fdate   中任意两个不同时为   null   则:
select   dept,   type,   sum（case   when   sta1   =   '2'   then   1   else   0   end)   autousecount,
                                      sum（case   when   sta1   =   '3'   then   1   else   0   end)   handusecount,
                                      sum（case   when   sta1   =   '4'   then   1   else   0   end)   cancellusecount,
                                      sum（case   when   sta1   =   '5'   then   1   else   0   end)   blankusecount,
                                      sum（case   when   sta1   =   '6'   then   1   else   0   end)   lossusecount,
                                      sum（case   when   sta1   =   '7'   then   1   else   0   end)   indatecount,
                                      sum（case   when   sta1   =   '8'   then   1   else   0   end)   bdatecount,
                                      sum（case   when   sta1   =   '9'   then   1   else   0   end)   outdatecount,
                                      sum（case   when   sta1   =   '10'   then   1   else   0   end)   fdatecount
from   （select   dept,   type,   （case   when   state   =   '2'   then   '2'
                                                    case   when   state   =   '3'   then   '3'
                                                    case   when   state   =   '4'   then   '4'
                                                    case   when   state   =   '5'   then   '5'
                                                    case   when   state   =   '6'   then   '6'
                                                    case   when   state   =   '1'   and   indate   is   null   then   '7'
                                                    case   when   state   =   '1'   and   bdate   is   null   then   '8'
                                                    case   when   state   =   '1'   and   outdate   is   null   then   '9'
                                                    case   when   state   =   '1'   and   fdate   is   null   then   '10')   sta1
              from   tk_dzinfo   td)   group   by   dept,   type,   sta1

相关文章
其他资讯

快速检索
分类导航标签a

最新资讯
热门点击