分析函数ROW_NUMBER应该是大家使用非常多的分析函数了。
常用来实现获取前n条,前1条记录、去除重复等需求。
下面根据官方文档的内容做下简单的介绍:
语法:
示例: ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn 按照department_id进行分组,组内根据salary字段进行降序排序,然后每行返回一个从1开始的唯一值
作用:
1.ROW_NUMBER()是个分析函数,为组内的每行返回一个从1开始的唯一值,order by 子句(不能省略)指定了排序的字段。
2.一般情况下,先使用ROW_NUMBER()将每组记录排序后作为子查询,在外层限制子查询ROW_NUMBER()返回的值,可以轻松实现top-N, bottom-N, and inner-N等需求。
3.top-1,也就是一般对记录进行去重,限制每组的ROW_NUMBER()=1,也就是保留按排序条件的第1条,这样就实现了去重的目的
例子:
1.返回每个部门工资排名前三的员工,如果部门内的员工数为n<3,那么就返回前n名员工
SELECT department_id, first_name, last_name, salary
FROM
(
SELECT
department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;
2.取1999年每个产品销售额排名前5的渠道,对比这些渠道在2000年的销售情况
SELECT sales_2000.channel_desc, sales_2000.prod_name,
sales_2000.amt amt_2000, top_5_prods_1999_year.amt amt_1999,
sales_2000.amt - top_5_prods_1999_year.amt amt_diff
FROM
/* The first subquery finds the 5 top-selling products per channel in year 1999. */
(SELECT channel_desc, prod_name, amt
FROM
(
SELECT channel_desc, prod_name, sum(amount_sold) amt,
ROW_NUMBER () OVER (PARTITION BY channel_desc
ORDER BY SUM(amount_sold) DESC) rn
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 1999
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
)
WHERE rn <= 5
) top_5_prods_1999_year,
/* The next subquery finds sales per product and per channel in 2000. */
(SELECT channel_desc, prod_name, sum(amount_sold) amt
FROM sales, times, channels, products
WHERE sales.time_id = times.time_id
AND times.calendar_year = 2000
AND channels.channel_id = sales.channel_id
AND products.prod_id = sales.prod_id
GROUP BY channel_desc, prod_name
) sales_2000
WHERE sales_2000.channel_desc = top_5_prods_1999_year.channel_desc
AND sales_2000.prod_name = top_5_prods_1999_year.prod_name
ORDER BY sales_2000.channel_desc, sales_2000.prod_name
;
CHANNEL_DESC PROD_NAME AMT_2000 AMT_1999 AMT_DIFF
--------------- --------------==-------------------------------- ---------- ---------- ----------
Direct Sales 17" LCD w/built-in HDTV Tuner 628855.7 1163645.78 -534790.08
Direct Sales Envoy 256MB - 40GB 502938.54 843377.88 -340439.34
Direct Sales Envoy Ambassador 2259566.96 1770349.25 489217.71
Direct Sales Home Theatre Package with DVD-Audio/Video Play 1235674.15 1260791.44 -25117.29
Direct Sales Mini DV Camcorder with 3.5" Swivel LCD 775851.87 1326302.51 -550450.64
Internet 17" LCD w/built-in HDTV Tuner 31707.48 160974.7 -129267.22
Internet 8.3 Minitower Speaker 404090.32 155235.25 248855.07
Internet Envoy 256MB - 40GB 28293.87 154072.02 -125778.15
Internet Home Theatre Package with DVD-Audio/Video Play 155405.54 153175.04 2230.5
Internet Mini DV Camcorder with 3.5" Swivel LCD 39726.23 189921.97 -150195.74
Partners 17" LCD w/built-in HDTV Tuner 269973.97 325504.75 -55530.78
Partners Envoy Ambassador 1213063.59 614857.93 598205.66
Partners Home Theatre Package with DVD-Audio/Video Play 700266.58 520166.26 180100.32
Partners Mini DV Camcorder with 3.5" Swivel LCD 404265.85 520544.11 -116278.26
Partners Unix/Windows 1-user pack 374002.51 340123.02 33879.49
15 rows selected.
总结:
包含ROW_NUMBER()的sql作为子查询,外面套一层,然后限制rn
以上内容来自Oracle官方文档:
Oracle® Database SQL Language Reference
11g Release 2 (11.2)
E26088-03