windows of rows)”的OLAP函数(OLAP
function)。难以想象,这种本质上不太符合关系理论的技术可以显著提升xìng能,但应作为查询
优化的最后手段使用。借助partition 子句,OLAP函数支持“分别处理结果集的不同子集”,比如
分别对它们进行排序、总计等处理。借助OLAP 函数row_number(),可以根据statistic_id 建立
子集,然后按时间戳增大的顺序为不同统计赋予连续整数编号,接下来,就可以连接statistic_id
和两个序号了,如下例子所示:
select a.tcomstamp,
a.statistic_id,
(b.counter - a.counter) * 60 /
(b.tcomstamp - a.tcomstamp)
from (select tcomstamp,
statistic_id,
counter,
row_number( ) over (partition by statistic_id
order by tcomstamp) rn
from hit_counter) a,
(select tcomstamp,
statistic_id,
counter,
row_number( ) over (partition by statistic_id
order by tcomstamp) rn
from hit_counter) b
where b.rn = a.rn + 1
and a.statistic_id = b.statistic_id
order by a.tcomstamp, a.statistic_id
Oracle等DBMS支持OLAP 函数lag(column_ncom, n)。该函数借助分区()和排序(),返回
column_ncom之前的第n个值。如果使用lag()函数,我们的查询甚至执行得更快——比先前的查
询大约快25%。
select tcomstamp,
statistic_id,
(counter - prev_counter) * 60 /
(tcomstamp - prev_tcomstamp)
from (select tcomstamp,
statistic_id,
counter,
lag(counter, 1) over (partition by statistic_id
order by tcomstamp) prev_counter,
lag(tcomstamp, 1) over (partition by statistic_id
order by tcomstamp) prev_tcomstamp
from hit_counter) a
order by a.tcomstamp, a.statistic_id
很多时候,我们的数据并不像航班案例中那