DB2 OLAP

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
)T
where 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

 

    按部门分割，降序，若有空值将被排到后面