《SQL语言艺术(PDF格式)》第22章

order　bya。timestamp；　a。statistic_id　
这个方法还是有缺陷：前后两次计算累计值的时间间隔，如果不介于　200　到　400　秒之间（例　
如取样频率改变了），如此之大的时间跨度就会引起风险。　
我们还有更安全的方法，就是使用基于“记录窗口（windows　of　rows）”的OLAP函数（OLAP　
function）。难以想象，这种本质上不太符合关系理论的技术可以显著提升性能，但应作为查询　
优化的最后手段使用。借助partition　子句，OLAP函数支持“分别处理结果集的不同子集”，比如　
…………………………………………………………Page　69……………………………………………………………
分别对它们进行排序、总计等处理。借助OLAP　函数row_number（），可以根据　statistic_id　建立　
子集，然后按时间戳增大的顺序为不同统计赋予连续整数编号，接下来，就可以连接statistic_id　
和两个序号了，如下例子所示：　
select　a。timestamp；　
a。statistic_id；　
（b。counter　…a。counter）*60/　
（b。timestamp　…a。timestamp）　
from　（select　timestamp；　
statistic_id；　
counter；　
row_number（　）over（partition　bystatistic_id　
order　bytimestamp）　rn　
from　hit_counter）　a；　
（select　timestamp；　
statistic_id；　
counter；　
row_number（　）over（partition　bystatistic_id　
order　bytimestamp）　rn　
from　hit_counter）　b　
where　b。rn=a。rn＋1　
anda。statistic_id　=b。statistic_id　
order　bya。timestamp；　a。statistic_id　
Oracle等DBMS支持OLAP　函数　lag（column_name；　n）。该函数借助分区（）和排序（），返回　
column_name之前的第n个值。如果使用lag（）函数，我们的查询甚至执行得更快——比先前的查　
询大约快25％。　
select　timestamp；　
statistic_id；　
（counter　…prev_counter）　*60/　
（timestamp　…prev_timestamp）　
from　（select　timestamp；　
statistic_id；　
counter；　
lag（counter；1）over（partition　bystatistic_id　
order　bytimestamp）　prev_counter；　
lag（timestamp；　1）over（partition　bystatistic_id　
order　bytimestamp）　prev_timestamp　
from　hit_counter）　a　
order　bya。timestamp；　a。statistic_id　
很多时候，我们的数据并不像航班案例中那样具有对称性。通常，当需要查找和最小、最大、　
最早、或最近的值相关联的数据时，首先必须找到这些值本身（此为第一遍扫描，需比较记录），　
…………………………………………………………Page　70……………………………………………………………
接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口（sliding　window）为基础的OLAP　
函数，可以将两遍扫描合而为一（至少表面上如此）。基于时间戳或日期的数据查询，非常特殊　
也非常重要，本章在稍后的“基于日期的简单搜索或范围搜索”中专门讨论。　
总结：当多个选取条件用于同一个表的不同记录时，可以使用基于滑动窗口工作的函数。　
基于一个表的自连接　
Self…Joins　on　OneTable　
利用卓越的、广为流行的范式（注2），有助于我们设计正确的关系数据库（至少满足3NF）。所　
有非键字段均与键相关、并完整依赖于键，非键字段之间没有任何依赖。每条记录具有逻辑一　
致性，同一个表中没有重复记录。于是，才能够建立同一个表之间的连接关系：使用同一查询　
从同一表中选择不同记录的集合（可以相交），然后连接它们，就好像它们来自不同表一样。本　
节将讨论简单的自连接。本节不讨论较复杂的嵌套层次结构，这一主题在第7章中讨论。　
自连接，指表与自身的连接，这种情况比分层查询更常见。自连接用于“从不同角度看　
待相同数据”的情况，例如，查询航班会两次用到airports　表，一次找到“出发机场”的名称，另　
一次找出“到达机场”的名称：　
select　f。flight_number；　
a。airport_name　departure_airport；　
b。airport_name　arrival_airport　
from　flights　f；　
airports　a；　
airports　b　
where　f。dep_iata_code　=a。iata_code　
andf。arr_iata_code　=b。iata_code　
此时，一般规则仍然适用：重点保证索引访问的高效。但是，如果此时索引访问不太高效怎么　
办呢？首当其冲地，应避免“第一轮处理丢弃了第二轮处理所需的记录”。应该通过一次处理收　
集所有感兴趣的记录，再使用诸如case　语句等结构分别显示记录，第11章将详细说明这种方法。　
非常微妙的是，有些情况看似与“机场的例子”很像，但其实不然。例如，如何利用一个保存“定　
期累计值”（注3）的表，显示每个时间段内累计值的增量？此时，该表内的两个不同记录间虽　
然有关联，但这种关联很弱：两个记录之所以相关，是因为它们的时间戳之间有前后关系。而　
连接两个flights表是通过airports表进行的，这种关联很强。　
例如，时间段为5分钟，时间戳以“距参照日期多少秒（seconds　elapsed　since　a　reference　date）”　
表示，则查询如下：　
select　a。timestamp；　
a。statistic_id；　
（b。counter　…a。counter）/5hits_per_minute　
…………………………………………………………Page　71……………………………………………………………
from　hit_counter　a；　
hit_counter　b　
where　b。timestamp　=a。timestamp＋300　
andb。statistic_id　=a。statistic_id　
order　bya。timestamp；　a。statistic_id　
上述脚本有重大缺陷：如果第二个累计值不是正好在第一个累计值之后5分钟取得的，那么就无　
法连接这两条记录。于是，我们改以“范围条件”定义连接。查询如下：　
select　a。timestamp；　
a。statistic_id；　
（b。counter　…a。counter）*60/　
（b。timestamp　…a。timestamp）　hits_per_minute　
from　hit_counter　a；　
hit_counter　b　
where　b。timestamp　between　a。timestamp＋200　
anda。timestamp　＋400　
andb。statistic_id　=a。statistic_id　
order　bya。timestamp；　a。statistic_id　
这个方法还是有缺陷：前后两次计算累计值的时间间隔，如果不介于　200　到　400　秒之间（例　
如取样频率改变了），如此之大的时间跨度就会引起风险。　
我们还有更安全的方法，就是使用基于“记录窗口（windows　of　rows）”的OLAP函数（OLAP　
function）。难以想象，这种本质上不太符合关系理论的技术可以显著提升性能，但应作为查询　
优化的最后手段使用。借助partition　子句，OLAP函数支持“分别处理结果集的不同子集”，比如　
分别对它们进行排序、总计等处理。借助OLAP　函数row_number（），可以根据　statistic_id　建立　
子集，然后按时间戳增大的顺序为不同统计赋予连续整数编号，接下来，就可以连接statistic_id　
和两个序号了，如下例子所示：　
select　a。timestamp；　
a。statistic_id；　
（b。counter　…a。counter）*60/　
（b。timestamp　…a。timestamp）　
from　（select　timestamp；　
statistic_id；　
counter；　
row_number（　）over（partition　bystatistic_id　
order　bytimestamp）　rn　
from　hit_counter）　a；　
（select　timestamp；　
statistic_id；　
counter；　
row_number（　）over（partition　bystatistic_id　
…………………………………………………………Page　72……………………………………………………………
order　bytimestamp）　rn　
from　hit_counter）　b　
where　b。rn=a。rn＋1　
anda。statistic_id　=b。statistic_id　
order　bya。timestamp；　a。statistic_id　
Oracle等DBMS支持OLAP　函数　lag（column_name；　n）。该函数借助分区（）和排序（），返回　
column_name之前的第n个值。如果使用lag（）函数，我们的查询甚至执行得更快——比先前的查　
询大约快25％。　
select　t