AnySQL.net

    Oracle的分析函数功能强大, 可以用于SQL的优化, 往往用普通的SQL需要好几次表扫描的, 用了分析函数后可以一句话轻松搞定. 我们来看一下统计学生成绩的例子吧.

    我不想专门建一个成绩表, 就用DBA_OBJECTS来表示吧, 用OBJECT_TYPE来表示班级或年级, 用OBJECT_ID余100的值来作成绩, 为了减少记录显示, 我只查了"TABLE"和"INDEX"两个班级, 通过使用WIDTH_BUCKET函数可以很容易地实现这一要求, 如下所示:

ASQL> SELECT CLASS_ID,SCORE_LEVEL,COUNT(*) NROWS
    2 FROM(
    3 SELECT
    4    OBJECT_TYPE CLASS_ID,
    5    WIDTH_BUCKET(MOD(OBJECT_ID,100),60,100,4) SCORE_LEVEL
    6 FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','INDEX'))
    7 GROUP BY CLASS_ID,SCORE_LEVEL
    8 ORDER BY 1,2
    9 /

CLASS_ID SCORE_LEVEL NROWS
-------- ----------- -----
INDEX              0   826
INDEX              1   140
INDEX              2   143
INDEX              3   144
INDEX              4   158
TABLE              0   613
TABLE              1   129
TABLE              2   144
TABLE              3   124
TABLE              4   115

10 rows returned.

ASQL>

    WIDTH_BUCKET这个函数有四个参数, 第一个参数列名, 第二个为起始值, 第三个为结束值, 第四个为在这个范围内将值平均划分的份数. 拿这个例子来说, 在60和100之间分四份, 那么60-70时这个函数返回1, 70-80时返回2, 80-90时返回3, 90-100时返回4, 而小于60时则返回0, 大于100时返回5(分数加一). 这个结果可以说是一目了然.

    分析函数比较全面的介绍在Data Warehouse Guide这个文档中, 我大致地看过两次, 还是记不住, 用时总得临时查阅.