友情提示:如果本网页打开太慢或显示不完整,请尝试鼠标右键“刷新”本网页!阅读过程发现任何错误请告诉我们,谢谢!! 报告错误
一世书城 返回本书目录 我的书架 我的书签 TXT全本下载 进入书吧 加入书签

SQL语言艺术(PDF格式)-第17章

按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!




接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口(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 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 



很多时候,我们的数据并不像航班案例中那样具有对称性。通常,当需要查找和最小、最大、 

最早、或最近的值相关联的数据时,首先必须找到这些值本身(此为第一遍扫描,需比较记录), 

接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口(sliding window)为基础的OLAP 

函数,可以将两遍扫描合而为一(至少表面上如此)。基于时间戳或日期的数据查询,非常特殊 

也非常重要,本章在稍后的“基于日期的简单搜索或范围搜索”中专门讨论。 



总结:当多个选取条件用于同一个表的不同记录时,可以使用基于滑动窗口工作的函数。 



基于日期的简单搜索或范围搜索 



Simple orRange Searchingon Dates 



搜索条件有多种,其中日期(和时间)占有特殊地位。日期极为常见,而且比其他数据类型更 

可能成为范围搜索的条件,范围搜索可以是有界的(如“在某两天之间”),也可以是部分有界 

(“在某天之前”)。通常,为了获得这种结果集,查询需要使用当前日期(如“前六个月”)。 



上一节“通过聚合获得结果集”所举的例子,用到了sales_history 表。当时,条件位于amount 上, 

其实对于sales_history这种表更常见的是日期条件,尤其是读取特定日期的数据、或读取两个日 

期之间的数据。在保存历史数据的表中查找特定日期(或其对应值)时,必须特别注意确定当 

前日期的方法,它可能成为聚合条件的基础。 


…………………………………………………………Page 73……………………………………………………………

第1章已指出,设计保存历史数据的表颇为困难,而且没有现成的简单解决方案。无论你对当前 

数据、还是历史数据感兴趣,设计历史数据的存储方案都要根据如何使用数据决定,同时还要 

看数据多快会过时。例如,零售系统中价格的变动速度比较慢(除非正在经受严重的通货膨胀), 

而网络流量或财务设备的价格改变速度比较快,甚至快很多。 

从宏观角度来看,关键是各项历史数据的数量:是“少量数据项、大量历史数据”,还是“大量数 

据项、少量历史数据”,或是介于两者之间?其重点是:数据项的可选择性取决于数据项的总数、 

取样频率(“每天一次”还是“每次改变时”)、时间长短(“永久”还是“一年”等)。因此,本节将首 

先讨论“大量数据项、少量历史数据”的情况,接着讨论“少量数据项、大量历史数据”的情况, 

最后讨论当前值问题。 



大量数据项、少量历史数据 



ManyItems; Few HistoricalValues 



既然没有为每个数据项保留大量历史数据,那么各项的ID可选择性很高。说明要查询哪些项, 

限定参与查询的少数历史记录,就可确定特定日期(当前日期或以前日期)对应的值。这种情 

况需要我们再次处理聚合值(aggregate value)。 



除非建立了代理键(本情况不需要代理键),否则主键通常是复合键,由item_id和record_date组 

成。为了查询特定日期的值,可采用两种方法:子查询和 OLAP 函数。 

使用子查询 

查找某数据项在特定日期的值相对简单,但实际上,这种简单只是假象。通常你会遇到这样的 

代码: 

select whatever 

from hist_data asouter 

where outer。item_id =somevalue 

andouter。record_date =(select max(inner。record_date) 

from hist_data asinner 

where inner。item_id =outer。item_id 

andinner。record_date 
返回目录 上一页 下一页 回到顶部 0 0
未阅读完?加入书签已便下次继续阅读!
温馨提示: 温看小说的同时发表评论,说出自己的看法和其它小伙伴们分享也不错哦!发表书评还可以获得积分和经验奖励,认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦!