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
很多时候,我们的数据并不像航班案例中那样具有对称xìng。通常,当需要查找和最小、最大、
最早、或最近的值相关联的数据时,首
松语文学免费小说阅读_www.16sy.com