OLAP函数介绍，

OLAP函数介绍，

   

n随着商业智能的普及，OLAP得到了越来越多的使用。在此简单介绍一些常用的OLAP函数。（DB2，Oracle,teradata支持） 例子1：找出每个部门中奖金排在前3位的员工   nSQL92的实现：自连接子查询 SELECT S0.workdept,S0.empno,S0.bonus FROM emp S0 WHERE EXISTS (SELECT S1.workdept FROM emp AS S1 WHERE S0.workdept = S1.workdept AND S1.bonus >= S0.bonus GROUP BY S1.workdept HAVING COUNT(DISTINCT S1.bonus) <= 3) ORDER BY S0.workdept; nSQL99的实现：OLAP分组排序函数 WITH temp AS (SELECT DENSERANK() OVER(PARTITION BY workdept ORDER BY bonus DESC) AS denserank,workdept,empno,bonus FROM emp) SELECT * FROM temp WHERE denserank<=3; OLAP函数：FUNC(子句) OVER(PARTITION BY 子句 ORDER BY 子句 ROWS或RANGE子句)   例子2：外汇报盘每隔2秒，红色数据为取上一次不为空数据，如何得到？  

时间戳	买价	卖价
2008-03-14-22.00.01.572000	0.9986	1.0018
2008-03-14-22.00.03.581000	0.9986	1.0015
2008-03-14-22.00.05.584000	0.9984	1.0015
2008-03-14-22.00.07.584000	0.9985	1.0015
2008-03-14-22.00.09.980000	0.9985	1.0015
2008-03-14-22.00.11.916000	0.9985	1.0015
2008-03-14-22.00.13.912000	0.9985	1.0014
2008-03-14-22.00.15.921000	0.9985	1.0014
2008-03-14-22.00.17.927000	0.9985	1.0014
2008-03-14-22.00.19.927000	0.9985	1.0014
2008-03-14-22.00.21.928000	0.9985	1.0014
2008-03-14-22.00.23.928000	0.9985	1.0014
2008-03-14-22.00.25.931000	0.9985	1.0009
2008-03-14-22.00.27.931000	0.9969	1.0006

  位移函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE nLAG：LAG(表达式或字段, 偏移量, 默认值, IGNORE NULLS或RESPECT NULLS) SELECT timestamp,(CASE WHEN r1 IS NULL THEN LAG(r1,1,0,'IGNORE NULLS') OVER(ORDER BY timestamp) ELSE r1 END) AS buyrate,(CASE WHEN r2 IS NULL THEN LAG(r2,1,0,'IGNORE NULLS') OVER(ORDER BY timestamp) ELSE r2 END) AS sellrate FROM quotation; LAG函数等同于SQL92的实现： SELECT timestamp,(CASE WHEN r1 IS NULL THEN (select b.r1 from quotation b where b.timestamp<=a. timestamp and b.r1 is not null order by b. timestamp desc fetch first 1 rows only) ELSE r1 END) AS buyrate,(CASE WHEN r2 IS NULL THEN (select b.r1 from quotation b where b.timestamp<=a. timestamp and b.r1 is not null order by b. timestamp desc fetch first 1 rows only) ELSE r2 END) AS sellrate FROM quotation a;