ROW_NUMBER [analytic]

为 窗口分区中的每一行分配一系列唯一编号，从 1 开始。通常情况下，ROW_NUMBER 和 RANK 可以互换，但有以下区别：

  • ROW_NUMBER 为有序集中的每一行分配一个唯一的序号，从 1 开始。

  • ROW_NUMBER() 为 Vertica 扩展，而 RANK 符合 SQL-99 标准。

行为类型

不可变

语法

ROW_NUMBER () OVER (
    [ window-partition-clause ]
    [ window-order-clause ] )

参数

OVER()
请参阅分析函数

示例

以下 ROW_NUMBER 查询按 customer_region 对 VMart 表 customer_dimension 中的客户进行分区。在每个分区中，该函数按其窗口顺序子句指定的资历顺序对这些客户进行排名：

=> SELECT * FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY customer_region ORDER BY customer_since) AS most_senior,
     customer_region, customer_name, customer_since FROM public.customer_dimension WHERE customer_type = 'Individual') sq
   WHERE most_senior <= 5;
 most_senior | customer_region |    customer_name     | customer_since
-------------+-----------------+----------------------+----------------
           1 | West            | Jack Y. Perkins      | 1965-01-01
           2 | West            | Linda Q. Winkler     | 1965-01-02
           3 | West            | Marcus K. Li         | 1965-01-03
           4 | West            | Carla R. Jones       | 1965-01-07
           5 | West            | Seth P. Young        | 1965-01-09
           1 | East            | Kim O. Vu            | 1965-01-01
           2 | East            | Alexandra L. Weaver  | 1965-01-02
           3 | East            | Steve L. Webber      | 1965-01-04
           4 | East            | Thom Y. Li           | 1965-01-05
           5 | East            | Martha B. Farmer     | 1965-01-07
           1 | SouthWest       | Martha V. Gauthier   | 1965-01-01
           2 | SouthWest       | Jessica U. Goldberg  | 1965-01-07
           3 | SouthWest       | Robert O. Stein      | 1965-01-07
           4 | SouthWest       | Emily I. McCabe      | 1965-01-18
           5 | SouthWest       | Jack E. Miller       | 1965-01-25
           1 | NorthWest       | Julie O. Greenwood   | 1965-01-08
           2 | NorthWest       | Amy X. McNulty       | 1965-01-25
           3 | NorthWest       | Kevin S. Carcetti    | 1965-02-09
           4 | NorthWest       | Sam K. Carcetti      | 1965-03-16
           5 | NorthWest       | Alexandra X. Winkler | 1965-04-05
           1 | MidWest         | Michael Y. Meyer     | 1965-01-01
           2 | MidWest         | Joanna W. Bauer      | 1965-01-06
           3 | MidWest         | Amy E. Harris        | 1965-01-08
           4 | MidWest         | Julie W. McCabe      | 1965-01-09
           5 | MidWest         | William . Peterson   | 1965-01-09
           1 | South           | Dean . Martin        | 1965-01-01
           2 | South           | Ruth U. Williams     | 1965-01-02
           3 | South           | Steve Y. Farmer      | 1965-01-03
           4 | South           | Mark V. King         | 1965-01-08
           5 | South           | Lucas Y. Young       | 1965-01-10
(30 rows)

另请参阅