Oracle SQL分析函数row_number()获取前n条记录、去除重复记录

最新推荐文章于 2024-01-18 17:39:45 发布

SeanData

最新推荐文章于 2024-01-18 17:39:45 发布

阅读量3.6k

点赞数

分类专栏： Oracle 开发文章标签： row_number 分析函数 sql oracle 去除重复

本文链接：https://blog.csdn.net/seandba/article/details/72725200

版权

Oracle 开发专栏收录该内容

25 篇文章 0 订阅

订阅专栏

分析函数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