分析函数也称为窗口函数。
1 概览
使用分析函数的查询，基于对数据行的分组来计算总量值。分析函数为每个分组返回多行数据。
不同于每个分组只得到一行数据，分析函数可以同时得到所有行的详细数据。区分分析函数和聚合函数的一种方法就是，在提及分析函数中使用的一组数据时，使用术语：窗口。
窗口是通过分析子句定义的。
2 示例数据
非标准化的sales_fact表
CREATE table sales_fact as
select country_name country,
country_subRegion region,
prod_name product,
calendar_year year,
calendar_week_number week,
sum(amount_sold) sale,
sum(amount_sold * (case
when mod(rownum, 10) = 0 then
1.4
when mod(rownum, 5) = 0 then
0.6
when mod(rownum, 2) = 0 then
0.9
when mod(rownum, 2) = 1 then
1.2
else
1
end)) recipts
from sales, times, customers, countries, products
where sales.time_id = times.time_id
and sales.prod_id = products.prod_id
and sales.cust_id = customers.cust_id
and customers.country_id = countries.country_id
group by country_name,
country_subRegion,
prod_name,
calendar_year,
calendar_week_number;sh@orclpdb1:orclcdb> CREATE table sales_fact as
2 select country_name country,
country_subRegion region,
4 prod_name product,
5 calendar_year year,
6 calendar_week_number week,
7 sum(amount_sold) sale,
8 sum(amount_sold * (case
9 when mod(rownum, 10) = 0 then
10 1.4
11 when mod(rownum, 5) = 0 then
12 0.6
when mod(rownum, 2) = 0 then
14 0.9
15 when mod(rownum, 2) = 1 then
16 1.2
17 else
18 1
19 end)) recipts
20 from sales, times, customers, countries, products
21 where sales.time_id = times.time_id
22 and sales.prod_id = products.prod_id
and sales.cust_id = customers.cust_id
24 and customers.country_id = countries.country_id
25 group by country_name,
26 country_subRegion,
27 prod_name,
28 calendar_year,
29 calendar_week_number;
Table created.
sh@orclpdb1:orclcdb> 3 分析函数剖析
分析函数由3部分组成：分区子句、排序子句以及开窗子句。
分析函数的基本语法：
function1（argument1，argument2,..argumentN）
over([partition-by-clause][order-by-clause][windowing-clause])
4.函数列表
5.聚合函数
聚合函数可以在分析模式或传统的非分析模式下执行运算。
sales列的动态求和
select year,
week,
sale,
sum(sale) over(partition by product, country, region, year order by week rows between unbounded preceding and current row) running_sum_ytd
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;
running_sum_ytd列是分析模式下求和函数的输出。列值在新的一年（2001年）时进行了重置，因为年份也是分区列，所以每年都会有一个新的分区。
5.1 跨越整个分区的聚合函数
可能需要在某个给定的分区的所有数据行上应用分析函数。例如：在一整年中计算sale列的最大值将需要包含数据分区中每一行数据的窗口。
使用SQL子句rows between unbounded preceding and unbounded following指定将MAX函数应用于数据分区的每一行上。
sale列的最大值
select year,
week,
sale,
sum(sale) over(partition by product, country, region, year order by week rows between unbounded preceding and unbounded following) Max_sale
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;
5.2 细粒度窗口声明
窗口的声明也可以更细化。假设你想要计算本周之前两周到本周之后两周共5周的时间窗口内sale列的最大值，可以使用子句rows between 2 preceding and 2 following 实现这一点。
5周时间跨度窗口内sale列的最大值
select year,
week,
sale,
sum(sale) over(partition by product, country, region, year order by week rows between 2 preceding and 2 following) Max_weeks_5
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;5.3 默认窗口声明
默认的窗口子句是rows between unbounded preceding and current row.
6.lead 和lag
lag和lead函数能够实现跨行引用。lag能够访问结果集中前面的行，lead函数支持访问结果集中后面的行。
6.1 语法和排序
lag(expression,offset,default) over(partition-clause order-by-clause)
lead和lag函数不支持开窗子句，这两个函数仅支持partition by子句和order by 子句。
6.2 从前一行中返回一个值
假设你需要在一行中取出当前周和前一周的销售量。你的需求表明需要进行跨行引用，如果在非分析型SQL语句中这就需要进行自联结。lag函数可以实现跨行引用而不用进行自联结。
使用lag(sale,1,sale)来在结果集中从sale列取出前一行的值。order by year,week 子句声明了每个数据分区中列值的排序方式。
sh@orclpdb1:orclcdb>
sh@orclpdb1:orclcdb> col product format a30
sh@orclpdb1:orclcdb> col country format a10
sh@orclpdb1:orclcdb> col region format a10
sh@orclpdb1:orclcdb> col year format 9999
sh@orclpdb1:orclcdb> col week format 99
sh@orclpdb1:orclcdb> col sale format 999999.99
sh@orclpdb1:orclcdb> col receipts format 999999.99
sh@orclpdb1:orclcdb> set lines 120 pages 100
sh@orclpdb1:orclcdb>
sh@orclpdb1:orclcdb> select year,week,sale,
2 lag(sale,1,sale) over(partition by product,country,region
3 order by year,week
4 ) prior_wk_sales
5 from sales_fact
6 where country in ('Australia') and product = 'Xtend Memory'
7 order by product,country,year,week;
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- ---------- --------------
1998 1 232.60 232.6
1998 2 117.56 232.6
1998 3 117.96 117.56
1998 4 117.96 117.96
1998 5 119.20 117.96
1998 6 235.12 119.2
1998 9 235.12 235.12
1998 10 471.04 235.12
1998 12 238.40 471.04
1998 14 235.12 238.4
1998 15 235.12 235.12
1998 17 235.12 235.12
1998 18 470.24 235.12
1998 19 235.92 470.24
1998 21 238.40 235.92
1998 23 470.24 238.4
1998 26 470.24 470.24
1998 27 230.08 470.24
1998 28 230.88 230.08
1998 29 230.88 230.88
1998 34 461.76 230.88
1998 35 230.08 461.76
1998 38 463.36 230.08
1998 39 463.36 463.36
1998 40 230.08 463.36
1998 41 233.28 230.08
1998 42 463.36 233.28
1998 43 230.08 463.36
1998 44 230.08 230.08
1998 45 230.08 230.08
1998 46 230.08 230.08
1998 47 230.88 230.08
1998 48 690.24 230.88
1998 50 115.04 690.24
1998 51 233.28 115.04
1998 52 345.52 233.28
1999 1 214.08 345.52
1999 3 378.40 214.08
1999 4 162.00 378.4
1999 5 320.04 162
1999 6 162.00 320.04
1999 8 412.44 162
1999 9 213.36 412.44
1999 10 288.00 213.36
1999 11 187.68 288
1999 12 236.64 187.68
1999 13 108.20 236.64
1999 14 108.20 108.2
1999 15 540.40 108.2
1999 16 378.40 540.4
1999 17 592.48 378.4
1999 18 288.72 592.48
1999 20 349.68 288.72
1999 21 162.00 349.68
1999 22 429.76 162
1999 25 429.76 429.76
1999 26 211.60 429.76
1999 27 212.32 211.6
1999 28 105.80 212.32
1999 31 163.48 105.8
1999 33 105.80 163.48
1999 34 423.20 105.8
1999 37 423.20 423.2
1999 38 289.20 423.2
1999 39 373.60 289.2
1999 40 162.00 373.6
1999 42 482.36 162
1999 43 211.60 482.36
1999 44 522.88 211.6
1999 45 105.80 522.88
1999 46 105.80 105.8
1999 47 591.12 105.8
1999 48 81.00 591.12
1999 53 108.20 81
2000 1 186.80 108.2
2000 3 373.64 186.8
2000 4 186.16 373.64
2000 5 186.80 186.16
2000 7 283.20 186.8
2000 8 186.16 283.2
2000 11 374.96 186.16
2000 12 186.16 374.96
2000 13 470.00 186.16
2000 14 470.68 470
2000 15 281.88 470.68
2000 16 377.60 281.88
2000 18 374.96 377.6
2000 19 186.16 374.96
2000 20 188.80 186.16
2000 21 749.92 188.8
2000 22 466.04 749.92
2000 23 94.40 466.04
2000 24 561.12 94.4
2000 27 180.32 561.12
2000 28 355.84 180.32
2000 29 266.88 355.84
2000 30 266.88 266.88
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- ---------- --------------
2000 31 179.12 266.88
2000 33 536.44 179.12
2000 34 714.08 536.44
2000 35 315.28 714.08
2000 36 473.64 315.28
2000 37 471.84 473.64
2000 38 317.44 471.84
2000 39 79.36 317.44
2000 40 358.24 79.36
2000 43 716.48 358.24
2000 44 540.96 716.48
2000 45 270.48 540.96
2000 46 986.96 270.48
2000 48 418.20 986.96
2000 49 169.52 418.2
2000 50 84.76 169.52
2000 52 269.80 84.76
2001 1 369.04 269.8
2001 2 473.52 369.04
2001 3 188.96 473.52
2001 4 1026.80 188.96
2001 5 373.76 1026.8
2001 6 89.76 373.76
2001 7 279.84 89.76
2001 8 184.24 279.84
2001 9 370.68 184.24
2001 10 276.20 370.68
2001 11 286.28 276.2
2001 12 467.24 286.28
2001 13 467.24 467.24
2001 14 651.64 467.24
2001 15 367.92 651.64
2001 16 1113.76 367.92
2001 18 89.48 1113.76
2001 20 472.12 89.48
2001 21 934.80 472.12
2001 22 567.12 934.8
2001 23 89.52 567.12
2001 24 547.68 89.52
2001 25 557.12 547.68
2001 27 377.92 557.12
2001 29 467.40 377.92
2001 30 651.64 467.4
2001 31 368.84 651.64
2001 32 275.60 368.84
2001 33 462.08 275.6
2001 34 275.60 462.08
2001 36 364.48 275.6
2001 37 372.64 364.48
2001 38 556.00 372.64
2001 39 462.28 556
2001 40 180.72 462.28
2001 41 268.76 180.72
2001 42 547.92 268.76
2001 43 558.32 547.92
2001 44 93.16 558.32
2001 46 374.32 93.16
2001 48 731.84 374.32
2001 49 181.04 731.84
2001 50 92.56 181.04
2001 51 459.28 92.56
2001 52 92.56 459.28
159 rows selected.
sh@orclpdb1:orclcdb> 6.3 理解数据行的位移
通过指定不同的位移可以来访问一个数据分区中的所有行。
lag函数使用了位移量10访问往前第10行的数据。输出还显示了在year=2001,week=52那一行，lag函数访问结果集中往前第10 行，也就是第40周的数据。注意lag(sale,10,sale)并不是访问将当前周的值52减去10得到的第42周的数据，而是访问分区中往前推10行的数据。
移位值为10的lag函数
select year,
week,
sale,
lag(sale,10,sale) over(partition by product, country, region order by year, week ) prior_wk_sales_10
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;6.4 从下一行中返回一个值
lead函数与lag函数类似，只是它可以访问排序后的结果集中当前值后面的数据行。lead(sale,1,sale)子句访问了排序后的结果集中当前值后面一行的数据值。
select year,
week,
sale,
lead(sale,1,sale) over(partition by product, country, region order by year, week ) prior_wk_sales
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;sh@orclpdb1:orclcdb> select year,
2 week,
sale,
4 lead(sale,1,sale) over(partition by product, country, region order by year, week ) prior_wk_sales
5 from sales_fact
6 where country in ('Australia')
7 and product = 'Xtend Memory'
8 order by product, country, year, week;
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- ---------- --------------
1998 1 232.60 117.56
1998 2 117.56 117.96
1998 3 117.96 117.96
1998 4 117.96 119.2
1998 5 119.20 235.12
1998 6 235.12 235.12
1998 9 235.12 471.04
1998 10 471.04 238.4
1998 12 238.40 235.12
1998 14 235.12 235.12
1998 15 235.12 235.12
1998 17 235.12 470.24
1998 18 470.24 235.92
1998 19 235.92 238.4
1998 21 238.40 470.24
1998 23 470.24 470.24
1998 26 470.24 230.08
1998 27 230.08 230.88
1998 28 230.88 230.88
1998 29 230.88 461.76
1998 34 461.76 230.08
1998 35 230.08 463.36
1998 38 463.36 463.36
1998 39 463.36 230.08
1998 40 230.08 233.28
1998 41 233.28 463.36
1998 42 463.36 230.08
1998 43 230.08 230.08
1998 44 230.08 230.08
1998 45 230.08 230.08
1998 46 230.08 230.88
1998 47 230.88 690.24
1998 48 690.24 115.04
1998 50 115.04 233.28
1998 51 233.28 345.52
1998 52 345.52 214.08
1999 1 214.08 378.4
1999 3 378.40 162
1999 4 162.00 320.04
1999 5 320.04 162
1999 6 162.00 412.44
1999 8 412.44 213.36
1999 9 213.36 288
1999 10 288.00 187.68
1999 11 187.68 236.64
1999 12 236.64 108.2
1999 13 108.20 108.2
1999 14 108.20 540.4
1999 15 540.40 378.4
1999 16 378.40 592.48
1999 17 592.48 288.72
1999 18 288.72 349.68
1999 20 349.68 162
1999 21 162.00 429.76
1999 22 429.76 429.76
1999 25 429.76 211.6
1999 26 211.60 212.32
1999 27 212.32 105.8
1999 28 105.80 163.48
1999 31 163.48 105.8
1999 33 105.80 423.2
1999 34 423.20 423.2
1999 37 423.20 289.2
1999 38 289.20 373.6
1999 39 373.60 162
1999 40 162.00 482.36
1999 42 482.36 211.6
1999 43 211.60 522.88
1999 44 522.88 105.8
1999 45 105.80 105.8
1999 46 105.80 591.12
1999 47 591.12 81
1999 48 81.00 108.2
1999 53 108.20 186.8
2000 1 186.80 373.64
2000 3 373.64 186.16
2000 4 186.16 186.8
2000 5 186.80 283.2
2000 7 283.20 186.16
2000 8 186.16 374.96
2000 11 374.96 186.16
2000 12 186.16 470
2000 13 470.00 470.68
2000 14 470.68 281.88
2000 15 281.88 377.6
2000 16 377.60 374.96
2000 18 374.96 186.16
2000 19 186.16 188.8
2000 20 188.80 749.92
2000 21 749.92 466.04
2000 22 466.04 94.4
2000 23 94.40 561.12
2000 24 561.12 180.32
2000 27 180.32 355.84
2000 28 355.84 266.88
2000 29 266.88 266.88
2000 30 266.88 179.12
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- ---------- --------------
2000 31 179.12 536.44
2000 33 536.44 714.08
2000 34 714.08 315.28
2000 35 315.28 473.64
2000 36 473.64 471.84
2000 37 471.84 317.44
2000 38 317.44 79.36
2000 39 79.36 358.24
2000 40 358.24 716.48
2000 43 716.48 540.96
2000 44 540.96 270.48
2000 45 270.48 986.96
2000 46 986.96 418.2
2000 48 418.20 169.52
2000 49 169.52 84.76
2000 50 84.76 269.8
2000 52 269.80 369.04
2001 1 369.04 473.52
2001 2 473.52 188.96
2001 3 188.96 1026.8
2001 4 1026.80 373.76
2001 5 373.76 89.76
2001 6 89.76 279.84
2001 7 279.84 184.24
2001 8 184.24 370.68
2001 9 370.68 276.2
2001 10 276.20 286.28
2001 11 286.28 467.24
2001 12 467.24 467.24
2001 13 467.24 651.64
2001 14 651.64 367.92
2001 15 367.92 1113.76
2001 16 1113.76 89.48
2001 18 89.48 472.12
2001 20 472.12 934.8
2001 21 934.80 567.12
2001 22 567.12 89.52
2001 23 89.52 547.68
2001 24 547.68 557.12
2001 25 557.12 377.92
2001 27 377.92 467.4
2001 29 467.40 651.64
2001 30 651.64 368.84
2001 31 368.84 275.6
2001 32 275.60 462.08
2001 33 462.08 275.6
2001 34 275.60 364.48
2001 36 364.48 372.64
2001 37 372.64 556
2001 38 556.00 462.28
2001 39 462.28 180.72
2001 40 180.72 268.76
2001 41 268.76 547.92
2001 42 547.92 558.32
2001 43 558.32 93.16
2001 44 93.16 374.32
2001 46 374.32 731.84
2001 48 731.84 181.04
2001 49 181.04 92.56
2001 50 92.56 459.28
2001 51 459.28 92.56
2001 52 92.56 92.56
159 rows selected.
sh@orclpdb1:orclcdb> partition by子句可以用来指定不同的分区边界，而order by子句可以改变分区内的排序顺序。有效地选择分区和排序列可以访问一个结果集中的任意一行。
7 first_value 和 last_value
first_value和last_value 函数常用在计算排过序的结果集中的最大值和最小值。
first_value函数从数据行窗口中第一行获取列值，而last_value 函数从该窗口中最后一行数据获取列值。
first_value 函数的语法
first_value(expression) over(partition-clause order-by-clause windowing-clause)
7.1 使用first_value计算最大值
first_value函数
select year,
week,
sale,
first_value(sale) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) top_sale_value,
first_value(week) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) top_sale_week
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;
7.2 使用last_value计算最小值
可以使用last_value函数计算最小值或最大值。last_value函数在数据行窗口中获取最后一行的列值。
如果你要计算sale列的最小值，那么可以使用Last_value(sale)子句及order by sale desc子句的组合来排序。
select year,
week,
sale,
last_value(sale) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) low_sale
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;可以有效地控制来使用窗口声明的粒度生成复杂报表。例如：rows between 10 preceding and 10 following子句指定了一个21行数据的窗口中求最大值或最小值。
8 其他分析函数
8.1 nth_value
使用nth_value函数，你可以获取排过序的结果集中的任意一行，而不仅是第一行或最后一行。 first_value函数可以写为nth_value(column_name,1)
nth_value函数的语法如下：
NTH_VALUE（measure, n） [FROM FIRST|FROM LAST][RESPECT NULLS|IGNORE NULLS]
OVER(partitioning-clause order-by-clause windowing-clause)
nth_value函数的第1个参数是列名，第2个参数为窗口位移量。
nth_value函数
SQL语句在product、country、region和year层级上取week列的值和sale列值第2高的值。因为数据是按照sale列降序进行排列的，结果集中的第2行就是sale列中第2高的值。
select year,
week,
sale,
nth_value(sale, 2) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) sale_2nd_top
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week;sh@orclpdb1:orclcdb> select year,
2 week,
3 sale,
4 nth_value(sale, 2) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) sale_2nd_top
5 from sales_fact
6 where country in ('Australia')
7 and product = 'Xtend Memory'
8 order by product, country, year, week;
YEAR WEEK SALE SALE_2ND_TOP
----- ---- ---------- ------------
1998 1 232.60 471.04
1998 2 117.56 471.04
1998 3 117.96 471.04
1998 4 117.96 471.04
1998 5 119.20 471.04
1998 6 235.12 471.04
1998 9 235.12 471.04
1998 10 471.04 471.04
1998 12 238.40 471.04
1998 14 235.12 471.04
1998 15 235.12 471.04
1998 17 235.12 471.04
1998 18 470.24 471.04
1998 19 235.92 471.04
1998 21 238.40 471.04
1998 23 470.24 471.04
1998 26 470.24 471.04
1998 27 230.08 471.04
1998 28 230.88 471.04
1998 29 230.88 471.04
1998 34 461.76 471.04
1998 35 230.08 471.04
1998 38 463.36 471.04
1998 39 463.36 471.04
1998 40 230.08 471.04
1998 41 233.28 471.04
1998 42 463.36 471.04
1998 43 230.08 471.04
1998 44 230.08 471.04
1998 45 230.08 471.04
1998 46 230.08 471.04
1998 47 230.88 471.04
1998 48 690.24 471.04
1998 50 115.04 471.04
1998 51 233.28 471.04
1998 52 345.52 471.04
1999 1 214.08 591.12
1999 3 378.40 591.12
1999 4 162.00 591.12
1999 5 320.04 591.12
1999 6 162.00 591.12
1999 8 412.44 591.12
1999 9 213.36 591.12
1999 10 288.00 591.12
1999 11 187.68 591.12
1999 12 236.64 591.12
1999 13 108.20 591.12
1999 14 108.20 591.12
1999 15 540.40 591.12
1999 16 378.40 591.12
1999 17 592.48 591.12
1999 18 288.72 591.12
1999 20 349.68 591.12
1999 21 162.00 591.12
1999 22 429.76 591.12
1999 25 429.76 591.12
1999 26 211.60 591.12
1999 27 212.32 591.12
1999 28 105.80 591.12
1999 31 163.48 591.12
1999 33 105.80 591.12
1999 34 423.20 591.12
1999 37 423.20 591.12
1999 38 289.20 591.12
1999 39 373.60 591.12
1999 40 162.00 591.12
1999 42 482.36 591.12
1999 43 211.60 591.12
1999 44 522.88 591.12
1999 45 105.80 591.12
1999 46 105.80 591.12
1999 47 591.12 591.12
1999 48 81.00 591.12
1999 53 108.20 591.12
2000 1 186.80 749.92
2000 3 373.64 749.92
2000 4 186.16 749.92
2000 5 186.80 749.92
2000 7 283.20 749.92
2000 8 186.16 749.92
2000 11 374.96 749.92
2000 12 186.16 749.92
2000 13 470.00 749.92
2000 14 470.68 749.92
2000 15 281.88 749.92
2000 16 377.60 749.92
2000 18 374.96 749.92
2000 19 186.16 749.92
2000 20 188.80 749.92
2000 21 749.92 749.92
2000 22 466.04 749.92
2000 23 94.40 749.92
2000 24 561.12 749.92
2000 27 180.32 749.92
2000 28 355.84 749.92
2000 29 266.88 749.92
2000 30 266.88 749.92
YEAR WEEK SALE SALE_2ND_TOP
----- ---- ---------- ------------
2000 31 179.12 749.92
2000 33 536.44 749.92
2000 34 714.08 749.92
2000 35 315.28 749.92
2000 36 473.64 749.92
2000 37 471.84 749.92
2000 38 317.44 749.92
2000 39 79.36 749.92
2000 40 358.24 749.92
2000 43 716.48 749.92
2000 44 540.96 749.92
2000 45 270.48 749.92
2000 46 986.96 749.92
2000 48 418.20 749.92
2000 49 169.52 749.92
2000 50 84.76 749.92
2000 52 269.80 749.92
2001 1 369.04 1026.8
2001 2 473.52 1026.8
2001 3 188.96 1026.8
2001 4 1026.80 1026.8
2001 5 373.76 1026.8
2001 6 89.76 1026.8
2001 7 279.84 1026.8
2001 8 184.24 1026.8
2001 9 370.68 1026.8
2001 10 276.20 1026.8
2001 11 286.28 1026.8
2001 12 467.24 1026.8
2001 13 467.24 1026.8
2001 14 651.64 1026.8
2001 15 367.92 1026.8
2001 16 1113.76 1026.8
2001 18 89.48 1026.8
2001 20 472.12 1026.8
2001 21 934.80 1026.8
2001 22 567.12 1026.8
2001 23 89.52 1026.8
2001 24 547.68 1026.8
2001 25 557.12 1026.8
2001 27 377.92 1026.8
2001 29 467.40 1026.8
2001 30 651.64 1026.8
2001 31 368.84 1026.8
2001 32 275.60 1026.8
2001 33 462.08 1026.8
2001 34 275.60 1026.8
2001 36 364.48 1026.8
2001 37 372.64 1026.8
2001 38 556.00 1026.8
2001 39 462.28 1026.8
2001 40 180.72 1026.8
2001 41 268.76 1026.8
2001 42 547.92 1026.8
2001 43 558.32 1026.8
2001 44 93.16 1026.8
2001 46 374.32 1026.8
2001 48 731.84 1026.8
2001 49 181.04 1026.8
2001 50 92.56 1026.8
2001 51 459.28 1026.8
2001 52 92.56 1026.8
159 rows selected.
sh@orclpdb1:orclcdb> 8.2 rank
rank函数以数值形式返回一个数据行在排序后的结果集中的位置。
rank函数对于计算最上面和最下面N行是非常有用的。
rank函数能够找出中间的N行数据。
rank函数的语法如下：
rank（） over(partition-clause order-by-clause)
rank函数的使用，销售额前10位的周
select *
from (select year,
week,
sale,
rank() over(partition by product, country, region, year order by sale desc) sales_rank
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week)
where sales_rank <= 10
order by 1, 4;
rank 函数为并列排名分配了同样的排名值。
8.3 dense_rank
dense_rank是rank函数的变体。rank和dense_rank函数的区别在于当存在并列的时候dense_rank函数不会跳过排名值。
dense_rank函数对于查询结果集中顶部、底部或中间N行的数据是非常有用的。
select *
from (select year,
week,
sale,
dense_rank() over(partition by product, country, region, year order by sale desc) sales_rank
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, week)
where sales_rank <= 10
order by 1, 4;
8.4 row_number
row_number函数为有序结果集中的每一行分配唯一的行编号。如果声明了分区子句，则为每一行分配一个基于其在该有序分区中位置的唯一编号。
row_number 函数可用于获取顶部、底部或中间N行数据的查询。
row_number() over(partition-clause order-by-clause)
select year,
week,
sale,
row_number() over(partition by product, country, region, year order by sale desc) sales_rn,
rank() over(partition by product, country, region, year order by sale desc) sales_rank
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by product, country, year, sales_rank;
8.5 stddev
stddev函数可以用来在一个数据分区中的某些数据行上计算标准偏差，或者如果没有声明分区子句则在整个结果集上计算标准偏差。
stddev函数
select year,
week,
sale,
stddev(sale) over(partition by product, country, region, year order by sale desc rows between unbounded preceding and unbounded following) stddv
from sales_fact
where country in ('Australia')
and product = 'Xtend Memory'
order by year, week;
8.6 listagg
listagg(string, separator) within group(order-by-clause) Over(partition-by-clause)
select listagg(country,',')
within group(order by country desc)
from (
select distinct country from sales_fact
order by country
)
从12c开始，VARCHAR2数据类型的最大长度从4000字节增加32767字节。但是，看上去这个增长并没有影响listagg函数结果字符串的长度。
listagg结果字符串的长度限制
select length(acol)
from (SELECT LISTAGG(object_name) WITHIN GROUP(ORDER BY NULL) acol
FROM all_objects
where rownum < 359);
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
