本文详细介绍了Oracle数据库中的分析函数，包括评级、反百分位、窗口、报表、延迟与领先等函数的使用，以及MODEL子句进行行间计算的方法。通过实例展示了如何利用分析函数执行复杂计算，如计算等级、百分位数、移动平均等，以及使用PIVOT和UNPIVOT进行数据转换。此外，文章还探讨了如何执行Top-N查询，包括FETCH FIRST子句、OFFSET和PERCENT子句的应用。
本文主要包括：
· 使用分析函数执行复杂的计算
· 使用 MODEL 子句执行行间计算
· 使用 PIVOT 和 UNPIVOT 子句，它们对于了解大量数据的总体趋势非常有用
· 执行 top-N 查询，从结果集返回顶部或底部的 N 行
一、 使用分析函数
分析函数能够执行复杂的计算，例如查找每月销售额最高的商品类型、业绩最佳的销售员，等等，
分析函数可以分为以下几类：
· 评级函数 可计算等级、百分位数、n分片（三分片、四分片等）
· 反百分位函数 可计算对应于百分位数的数值
· 窗口函数 可计算累积和移动总计
· 报表函数 可计算诸如市场占有率之类的结果
· 延迟与领先函数 可获得与当前行的距离为若干行的那一行中的值
· 首函数与末函数 可获得某个与排序分组中的第一个值与最后一个值
· 线性回归函数 可用一条普通最小平方回归曲线来拟合一组数值对
· 假想评级与分布函数 可以计算在将一个新行插入到表中之后，它在表中的等级和百分位数
1. 使用评级函数
评级函数（ranking function）用于计算等级、百分位数、n分片等，评级函数如下表所示
1.1 使用 RANK() 和 DENSE_RANK() 函数
范例：使用 RANK() 和 DENSE_RANK() 获得2003年不同产品类型的销售评级
SELECT
prd_type_id,SUM(amount),
RANK()OVER(ORDER BY SUM(amount) DESC)AS rank,
DENSE_RANK()OVER(ORDER BY SUM(amount) DESC)AS dense_rank
FROM all_sales
WHERE year=2003
--AND amount IS NOT NUL -- 在评级函数中，空值被指定为最高的排名1
GROUP BY prd_type_id
ORDER BY prd_type_id;
使用 NULLS FIRST 和 NULLS LAST 子句控制空值的排名
范例：使用 NULLS LAST 将空值指定为最低
SELECT
prd_type_id,SUM(amount),
RANK()OVER(ORDER BY SUM(amount) DESC NULLS LAST)AS rank,
DENSE_RANK()OVER(ORDER BY SUM(amount) DESC NULLS LAST)AS dense_rank
FROM all_sales
WHERE year=2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
分析函数与 PARTITION BY 子句结合使用
在需要将分组划分为子分组时，可以将 PARTITION BY 子句和分析函数结合起来使用
范例：根据月份划分销售额
SELECT
prd_type_id,month,SUM(amount),
RANK()OVER(PARTITION BY month ORDER BY SUM(amount) DESC)AS rank
FROM all_sales
WHERE year=2003
AND amount IS NOT NULL
GROUP BY prd_type_id,month
ORDER BY prd_type_id,month;
分析函数与 ROLLUP、CUBE、GROUPING SETS 操作符结合使用范例：使用 ROLLUP 和 RANK() 获得不同产品类型 ID 的销售排名
SELECT
prd_type_id,sum(amount),
RANK()OVER(ORDER BY SUM(amount) DESC) AS rank
FROM all_sales
WHERE year=2003
GROUP BY ROLLUP(prd_type_id) -- 标准分组、合计
ORDER BY prd_type_id;
范例：使用 CUBE 和 RANK() 根据产品类型 ID 和员工 ID 获得销售排名SELECT
prd_type_id,emp_id,SUM(amount),
RANK()OVER(ORDER BY SUM(amount) DESC)AS rank
FROM all_sales
WHERE year=2003
GROUP BY CUBE(prd_type_id,emp_id)
ORDER BY prd_type_id,emp_id;
范例：使用 GROUPING SETS 和 RANK() 获得销售额小计的排名SELECT
prd_type_id,emp_id,SUM(amount),
RANK()OVER(ORDER BY SUM(amount) DESC)AS rank
FROM all_sales
WHERE year=2003
GROUP BY GROUPING SETS(prd_type_id,emp_id) -- 两个分组小计
ORDER BY prd_type_id,emp_id;
1.2 使用 CUME_DIST() 和 PERCENT_RANK() 函数CUME_DIST() 函数计算一行在组中的相对位置，CUME_DIST() 总是返回大于 0、小于或等于
1 的数，该数表示该行在 N 行中的位置（N为组中所有的行数）；例如，在一个 3 行的组中，
返回的累计分布值分别为 1/3、2/3、3/3；
PERCENT_RANK() 函数对于一个组中给定的行来说，在计算那行的序号时，先减 1，然后除以
N-1（N为组中所有的行数）；该函数总是返回 0~1（包括1）之间的数
范例：计算销售额的累计分布和百分比排名
SELECT
prd_type_id,SUM(amount),
CUME_DIST()OVER(ORDER BY SUM(amount) DESC) CUME_DIST,
PERCENT_RANK()OVER(ORDER BY SUM(amount) DESC) PERCENT_RANK
FROM all_sales
WHERE year=2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
如：prd_type_id=5 的那行为第一行，则 CUME_DIST()=1/5、PERCENT_RANK()=(1-1)/(5-1)
1.3 使用 NTILE() 函数
NTILE(buckets) 函数可以计算 n 分片的值，如三分片、四分片等，Bucket参数指定了分片的片数，
行数将被分组为 buckets 个片，例如：
· NTILE(3) 表示指定片数为2，行将分组为两片
· NTILE(5) 表示指定片数为5，行将分组为五片
范例：使用 NTILE(4) 将行分组为四片
SELECT
prd_type_id,SUM(amount),
NTILE(4)OVER(ORDER BY SUM(amount) DESC) AS NTILE
FROM all_sales
WHERE year=2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
1.4 使用 ROW_NUMBER() 函数
ROE_NUMBER() 函数从 1 开始，为分组中的每行返回一个数字（行号）
SELECT
prd_type_id,SUM(amount),
ROW_NUMBER()OVER(ORDER BY SUM(amount) DESC) ROW_NUMBER
FROM all_sales
WHERE year=2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
2. 使用反百分位函数
反百分位函数的作用与 CUME_DIST() 和 PERCENT_RANK() 相反
· PERCENTILE_DISC(x) 在每一个分组中检查累计分布的数值，直到找到大于或等于 x 的值
· PERCENTILE_CONT(x) 在每一个分组中检查百分比排名的数值，直到找到大于或等于 x 的值
范例：使用 PERCENTILE_DISC() 和 PERCENTILE() 获取百分位数大于或等于 0.6 的销售总额
SELECT
PERCENTILE_CONT(0.6)WITHIN GROUP(ORDER BY SUM(amount) DESC)
AS PERCENTIL_CONT,
PERCENTILE_DISC(0.6)WITHIN GROUP(ORDER BY SUM(amount) DESC)
AS PERCENTILE_DISC
FROM all_sales
WHERE year=2003
GROUP BY prd_type_id;
结果与“使用 CUME_DIST() 和 PERCENT_RANK() 函数”部分的结果相比较，分别与那里的
累计分布和百分比排名各为 0.6 和 0.75 的行对应
3. 使用窗口函数（详见分析函数窗口子句 RANGE/ROWS 差别）
窗口函数可以计算一定范围内、一定值域内或一段时间内的累积和以及移动平均值
查询返回一组行，它们称为结果集；“窗口”这个术语用来描述结果集中行的子集，然后
由窗口函数来处理通过窗口”看“到的行子集，并返回一个值。
可以定义窗口的起点和终点
3.1 计算累积和
范例：通过执行累积和操作计算出从2003年1月到12月的累积销售额
SELECT
month,SUM(amount) AS MONTH_AMOUNT,
SUM(SUM(amount))OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS CUMULATIVE_AMOUNT -- 当前分组中的第一行到本行
FROM all_sales
WHERE year=2003
GROUP BY month;
范例：计算2003年6月到12月的累积销售额
SELECT
month,SUM(amount) AS MONTH_AMOUNT,
SUM(SUM(amount))OVER(ORDER BY month) AS CUMULATIVE_AMOUNT
FROM all_sales
WHERE year=2003
AND month BETWEEN 6 AND 12
GROUP BY month;
3.2 计算移动平均值
范例：计算本月与前3个月之间（共4个月）销售额的移动平均值
SELECT
month,SUM(amount) AS MONTH_AMOUNT,
AVG(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
AS MOVING_AVERAGE
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
3.3 计算中心平均值
范例：计算当前月份前后各一个月内（共三个月）销售额的中心移动平均值
SELECT
month,SUM(amount) AS MONTH_AMOUNT,
AVG(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS MOVING_AVERAGE
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
3.4 用 FIRST_VALUE() 和 LAST_VALUE() 获取第一行和最后一行
FIRST_VALUE() 和 LAST_VALUE() 函数可以获取窗口中的第一行和最后一行数据
范例：用FIRST_VALUE() 和 LAST_VALUE() 获得前一个月和后一个月的销售额
SELECT
month,SUM(amount) AS month_amount,
FIRST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS PREVIOUS_MONTH_AMOUNT,
LAST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS NEXT_MONTH_AMOUNT
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
范例：将当前月份的销售额除以前一个月的销售额（保存在curr_div_prev列中）
将当前月份的销售额除以下一个月的销售额（保存在curr_div_next列中）
SELECT
month,SUM(amount) AS month_amount,
SUM(amount)/FIRST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS CURR_DIV_PREV,
SUM(amount)/LAST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS CURR_DIV_NEXT
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
3.5 用 NTH_VALUE() 函数获取第 n 行NTH_VALUE() 函数返回窗口中的第 n 行
范例：使用 NTH_VALUE() 函数获得第2个月的销售额
SELECT
month,SUM(amount) AS MONTH_AMOUNT,
NTH_VALUE(SUM(amount),2)OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)AS NTH_VALUE
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
范例：使用 NTH_VALUE() 函数获得第24名员工销售产品类型#1、#2、#3的最大销售额
注：第24名员工的最大销售额包含在窗口的第4个位置
SELECT
prd_type_id,emp_id,MAX(amount),
NTH_VALUE(MAX(amount),4)OVER(PARTITION BY prd_type_id ORDER BY emp_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) NTH_VALUE
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 3
GROUP BY prd_type_id,emp_id
ORDER BY prd_type_id,emp_id;
4. 使用报表函数
报表函数可用于执行跨越分组和组内分区的计算
4.1 总计报表
范例：查询报告2003年前三个月的：
· 每月销售额的总和（total_month_amount 列）
· 所有产品类型销售额的总和（total_product_type_amount 列）
SELECT
month,prd_type_id,SUM(amount),
SUM(SUM(amount))OVER(PARTITION BY month) AS total_month_amount,
SUM(SUM(amount))OVER(PARTITION BY prd_type_id)AS total_product_type_id
FROM all_sales
WHERE year=2003
AND month <=3
GROUP BY month,prd_type_id
ORDER BY month,prd_type_id;
4.2 使用 RATIO_TO_REPORT() 函数
RATIO_TO_REPORT() 函数可以用来计算某个值在一组值得总和中所占的比率
范例：查询报告2003年前三个月的：
· 每种产品类型每个月的销售额总计（prd_type_amount 列）
· 该产品类型的销售额占整月销售额的比率（prd_type_ratio 列）
SELECT
month,prd_type_id,
SUM(amount) AS prd_type_amount,
RATIO_TO_REPORT(SUM(amount))OVER(PARTITION BY month) AS prd_type_ratio
FROM all_sales
WHERE year=2003
AND month <= 3
GROUP BY month,prd_type_id
ORDER BY month,prd_type_id;
4.3 使用 LISTAGG() 函数
LISTAGG() 函数对分组内的各行排序并且将分组的值集连接起来
范例：从 products 表检索产品 #1 到 #5，按照价格和产品名排序，并返回最贵的产品
SELECT
LISTAGG(name,',')WITHIN GROUP (ORDER BY price,name) AS "Product List",
MAX(price) AS "Most Expensive"
FROM products
WHERE product_id <= 5;
范例：从 products 表检索产品 #1 到 #5，对于每种产品，使用 LISTAGG() 函数显示具有
相同 product_type_id 值的产品
SELECT
product_id,product_type_id,name,
LISTAGG(name,',')WITHIN GROUP (ORDER BY name)OVER(PARTITION BY product_type_id)
AS "Product List"
FROM products
WHERE product_id <= 5
ORDER BY product_id,product_type_id;
5. 使用 LAG() 和 LEAD() 函数
LAG() 和 LEAD() 函数可获得位于距当前行指定距离的那一行中的数据
范例：用 LAG() 和 LEAD() 函数获得前一个月和后一个月的销售额
SELECT
month,SUM(amount) AS month_amount,
LAG(SUM(amount),1)OVER(ORDER BY month) AS previous_month_amount,
LEAD(SUM(amount),1)OVER(ORDER BY month) AS next_month_amount
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
LAG(SUM(amount),1) 可获取前一行的销售额总计
6. 使用 FIRST 和 LAST 函数
FIRST 函数和 LAST 函数可获得排序分组中的第一个值和最后一个值
范例：用 FIRST 和 LAST 函数获得2003年中销售额最高和最低的月份
SELECT
SUM(month)KEEP(DENSE_RANK FIRST ORDER BY SUM(amount))
AS highest_sales_month,
SUM(month)KEEP(DENSE_RANK LAST ORDER BY SUM(amounT))
AS lowest_sales_month
FROM all_sales
WHERE year=2003
GROUP BY month
ORDER BY month;
7. 使用假想评级与分布函数
假想评级与分布函数可以计算在将一个新行插入表中之后，它在表中的排名和百分比
下面这些函数可以与假想计算结合使用：
· RANK()
· DENSE_RANK()
· PERCENT_RANK()
· CUME_DIST()
范例：使用 RANK() 和 PERCENT_RANK() 函数获得2003年每一种产品类型销售额的排名和百分比排名
SELECT
prd_type_id,SUM(amount),
RANK()OVER(ORDER BY SUM(amount) DESC) AS rank,
PERCENT_RANK()OVER(ORDER BY SUM(amount) DESC) AS percent_rank
FROM all_sales
WHERE year=2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;
范例：计算销售额 $500000 的假想排名和百分比排名
SELECT
RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS rank,
PERCENT_RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS percent_rank
FROM all_sales
WHERE year=2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;
二、 使用 MODEL 子句
Oracle Database 10g 中引入的 MODEL 子句可以用来进行行间计算
MODEL 子句允许访问数组中的元素那样访问行中的某个列
1. MODEL 子句示例
范例：查询获取2003年内每个月由#21完成的产品类型为#21完成的产品类型为#1、#2的销售额，
并根据2003年的销售数据预测出2004年1月、2月和3月的销售额
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY (prd_type_id)
DIMENSION BY (month,year)
MEASURES(amount sales_amount)
(
sales_amount[1,2004]=sales_amount[1,2003],
sales_amount[2,2004]=sales_amount[2,2003]+sales_amount[3,2003],
sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)
)
ORDER BY prd_type_id,year,month;
注：
· PARTITION BY(prd_type_id) 指定结果是根据 prd_type_id 分区的
· DIMENSION BY(month,year) 定义数组的维度是 month 和 year，这意味着
必须提供月份和年份才能访问数组中的单元
· MEASURE(amount sales_amount) 表明数组中的每个单元包含销售额，同时
表明数组名为 sales_amount
2. 用 BETWEEN 和 AND 返回特定范围内的数据单元
BETWEEN 和 AND 关键字可用于访问某个范围内的数据单元
范例：将2004年1月的销售额设置为2003年1月至3月销售额的平均值再四舍五入为保留两位小数
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[1,2004]=ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3,2003],2)
)
ORDER BY prd_type_id,year,month;
3. 用 ANY 和 IS ANY 访问所有的数据单元
可以用 ANY 和 IS ANY 谓词访问数组中所有的数据单元，ANY 和位置标记合用，
IS ANY 和符号合用
范例：将2004年1月的销售额设置为所有年份月份的销售额之和再四舍五入为保留两位小数
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,ANY],2)
)
ORDER BY prd_type_id,year,month;
4. 用 CURRENTV() 函数获取某个维度的当前值
CURRENTV() 函数用于获得某个维度的当前值
范例：将2004年第一个月的销售额设置为2003同月销售额的1.25倍
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[1,2004]=ROUND(sales_amount[currentv(),2003]*1.25,2)
)
ORDER BY prd_type_id,year,month;
5. 用 FOR 循环访问数据单元
可通过 FOR 循环访问数据单元
范例：将2004年前3个月的销售额设置为2003年相应月份销售额的1.25倍
注：其中使用了 FOR 循环，还通过 INCREMENT 关键字定义每一次循环迭代中 month 的增量
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2) -- CURRNETV() 获取某个维度的当前值
)
ORDER BY prd_type_id,year,month;
6. 处理空值和缺失值
6.1 使用 IS PRESENT
如果数据单元指定的行在 MODEL 子句执行之前存在，那么 IS PRESENT 返回 TRUE
例如，sales_amount[CURRNTV(),2003] IS PRESENT
范例：将2004年前3个月的销售额设置为2003年同期销售额的1.25倍
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=
CASE WHEN sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
ORDER BY prd_type_id,year,month;
6.2 使用 PRESENTV() 函数
如果 cell 引用的行在 MODEL 子句执行之前就存在，那么 PRESENTV(cell,expr1,expr2)
返回表达式 expr1，如果这行不存在，返回表达式 expr2
例如，
PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENT(),2003]*1.25,2),0)
范例：将2004年前3个月的销售额设置为2003年同期销售额的1.25倍
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
ORDER BY prd_type_id,year,month;
6.3 使用 PRESENTNNV() 函数
如果 cell 引用的单元在 MODEL 子句执行之前已经存在，并且该单元的值不为空，那么
PRESENTNNV(cell,expr1,expr2) 返回表达式 expr1，如果行不存在或单元值为空值，那么
返回表达式 expr2
例如，
PRESENTNNV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果 sales_amount[CURRENT(),2003] 存在且为非空值，那么上面的表达式将四舍五入为
保留两位小数后的总小数额；否则返回0
6.4 使用 IGNORE NAV 和 KEEP NAV
默认情况下，MODEL 子句将缺失值的单元视为该单元拥有值 null，具有 null 值的单元也同样
视为空值，可以使用 IGNORE NAV 改变这种默认行为，它返回如下值之一：
· 单元为数值空值或缺失值时返回 0
· 单元为字符串空值或缺失值时返回空字符串
· 单元为日期空值或缺失值时返回 01-JAN-2000
· 单元为其他所有数据库类型的空值或缺失值时返回空值
也可以使用 KEEP NAV，这是默认行为，KEEP NAV 对空值或缺失数字值返回空值
范例：IGNORE NAV 的用法
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL IGNORE NAV
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
(
sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
ORDER BY prd_type_id,year,month;
7. 更新已有单元
默认情况下，如果表达式左端的引用单元存在，就更新单元，如果该单元不存在，就在组中创建新的行
可以用 RULES UPDATE 改变这种默认行为，指出在单元不存在的情况下，不创建新的行
范例： 使用 RULES UPDATE
SELECT
prd_type_id,year,month,sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id=21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY(month,year)
MEASURES(amount sales_amount)
RULES UPDATE
(
sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
ORDER BY prd_type_id,year,month;
注：由于2004年的单元不存在，同时又指定了 RULES UPDATE，因此不会再数组中为2004年创建新行
三、 使用 PIVOT 和 UNPIVOT 子句
PIVOT 子句和 UNPIVOT 子句是在 Oracle 11g 中引入的
PIVOT 可以在查询输出中将行旋转为列，同时针对数据作为聚合函数使用；
UNPIVOT 是 PIVOT 的相反操作，它可以在查询输出中将列旋转为行
1. PIVOT 子句的简单示例
范例：查询2003年前4个月的产品类型#1、#2 和 #3 的销售额，注意查询输出的数据展示了
每种产品类型在每个月的销售额的总和
SELECT *
FROM
(
SELECT month,prd_type_id,amount
FROM all_sales
WHERE year=2003
AND prd_type_id IN (1,2,3)
)
PIVOT
(
SUM(amount) FOR month IN (1 AS JAN,2 AS FEB,3 AS MAR,4 AS APR)
)
ORDER BY prd_type_id;
注：上面的这条 SELECT 语句具有以下结构
SELECT *
FROM
(
inner_query
)
PIVOT
(
aggregate_function FOR pivot_column IN (list_of_values)
)
ORDER BY ...;
2. 转换多个列
范例：转换 month 和 prd_type_id 两列，它们在 FOR 部分被引用，注意 PIVOT 的 IN 部分
中的值列表包含 month 和prd_type_id 这两列的一个值：
SELECT *
FROM
(
SELECT month,prd_type_id,amount
FROM all_sales
WHERE year=2003
AND prd_type_id IN (1,2,3)
)
PIVOT
(
SUM(amount) FOR (month,prd_type_id) IN
(
(1,2) AS JAN_PRDTYPE2,
(2,3) AS FEB_PRDTYPE3,
(3,1) AS MAR_PRDTYPE1,
(4,2) AS APR_PRDTYPE2
)
);
可以将任何值放在 IN 部分中，以便得到自己感兴趣的值
范例：将放到 IN 部分的产品类型值更改了，以得到这些产品类型在指定月份的销售额
SELECT *
FROM
(
SELECT month,prd_type_id,amount
FROM all_sales
WHERE year=2003
AND prd_type_id IN (1,2,3)
)
PIVOT
(
SUM(amount) FOR (month,prd_type_id) IN
(
(1,1) AS JAN_PRDTYPE1,
(2,2) AS FEB_PRDTYPE2,
(3,3) AS MAR_PRDTYPE3,
(4,1) AS APR_PRDTYPE1
)
);
结果：
· 产品类型 #1 在 1 月份的销售额是 $38909.04
...
3. 在转换中使用多个聚合函数
范例：使用 SUM() 函数得到产品类型在 1 月份和 2 月份的销售额，用 AVG() 函数得到销售额的平均值
SELECT *
FROM
(
SELECT month,prd_type_id,amount
FROM all_sales
WHERE year=2003
AND prd_type_id IN (1,2,3)
)
PIVOT
(
SUM(amount) AS sum_amount,
AVG(amount) AS avg_amount
FOR (month) IN (1 AS JAN,2 AS FEB
)
)
ORDER BY prd_type_id;
4. 使用 UNPIVOT 函数
UNPIVOT 函数用于将列旋转为行，它执行与 PIVOT 相反的操作
范例：使用 UNPIVOT 从 pivot_sales_data 表得到将列旋转为行的销售数据
SELECT *
FROM pivot_sales_data
UNPIVOT
(
amount FOR month IN (JAN,FEB,MAR,APR)
)
ORDER BY prd_type_id;
四、 执行 Top-N 查询
Oracle 12c 的一项新功能是对执行 Top-N 查询的原生支持，Top-N查询包含行限制子句，
行限制子句允许通过指定以下方面的内容来对检索的行加以限制：
· 返回的行数，使用 FETCH FIRST 子句
· 一个偏移量，在限制开始之前，指定行跳过的行数，使用 OFFSET 子句
· 返回所选行的总数的百分比，使用 PERCENT 子句
使用行限制子句时，还可以添加下列操作之一：
· ONLY，它在行限制子句中指定返回确切数目的行，可以是行数或百分比
· WITH TIES，它包括额外的具有相同排序关键字值的行，并作为检索出的最后一行
（排序关键字是在 ORDER BY 子句中指定的列）
1. 使用 FETCH FIRST 子句
范例：使用 FETCH FIRST 从 more_employees 表检索employee_id 值最小的 5 名员工
SELECT employee_id,first_name,last_name
FROM more_employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;
2. 使用 OFFSET 子句
OFFSET 子句用来指定行限制开始前跳过的行数
范例：从 more_employees 表检索 employee_id 值在 6 和 10 之间的员工
SELECT employee_id,first_name,last_name
FROM more_employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
3. 使用 PERCENT 子句
可以使用 PERCENT 子句指定返回所选的总行数的百分比
范例：从 products 表检索前 20% 价格最高的产品
SELECT product_id,name,price
FROM products
ORDER BY price DESC
FETCH FIRST 20 PERCENT ROWS ONLY;
4. 使用 WITH TIES 子句
可以使用 WITH TIES 包括其他具有相同排序关键字的行作为最后获取的一行
排序关键字是在 ORDER BY 子句中指定的列
范例：从 more_employees 表中使用 WITH TIES 检索前 10% 工资最低的员工，排序关键字是 salary 列
SELECT employee_id,first_name,last_name,salary
FROM more_employees
ORDER BY salary
FETCH FIRST 10 PERCENT ROWS WITH TIES;
本文使用的表：http://pan.baidu.com/s/1zuHRK
扫一扫
471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
