OLAP函数介绍

最新推荐文章于 2024-04-14 11:30:00 发布

DBFocus

最新推荐文章于 2024-04-14 11:30:00 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

文章标签： null teradata 商业智能 oracle db2 sql

本文链接：https://blog.youkuaiyun.com/DBFocus/article/details/5352651

本文深入探讨了OLAP（在线分析处理）技术，介绍了其在商业智能中的关键作用。内容涉及OLAP的基本概念，以及如何在Teradata、Oracle和DB2等数据库系统中使用OLAP函数进行复杂的数据分析。通过学习，读者将掌握如何利用SQL进行多维数据查询，提升数据分析效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;