DB2 OLAP

sue

1.OLAP函数 ROW_NUMBER  提供行号

 
select row_number() over (order by 字段) as row_no  from tabname
 
   查询表的第N行到第M行的数据
 
select a.* from (     select row_number() over (order by 字段) as row_no,a.* from tabname a)Twhere t.row_no between 1 and 100 
 2.OLAP查询
 
    累计一个星期内每天的销售数字
 
sum(sum(?)) over (partition by week order by date rows unbounded preceding) as  
    OLAP分割：    partition by
    row n preceding 窗口帧
    n=2表示当前行加上前两行，计算的是第三行的数据
 
3.OLAP RANK函数
   
RANK() OVER (partition by dept order by dollors desc nulls last )AS RANK   
    RANK                      1,2,2,4              
    DENSE_RANK         1,2,2,3
    ROW_NUMBER       1,2,3,4
 
    按部门分割，降序，若有空值将被排到后面