row_number()row_number()

返回序列化行集中当前行的索引。Returns the current row's index in a serialized row set. 默认情况下,第一行的行索引从 1 开始,每增加一行就增加 1The row index starts by default at 1 for the first row, and is incremented by 1 for each additional row. 可以选择让行索引从不同于 1 的值开始。Optionally, the row index can start at a different value than 1. 此外,可以根据提供的谓词重置行索引。Additionally, the row index may be reset according to some provided predicate.

语法Syntax

row_number ( [StartingIndex [, Restart]] )row_number ( [StartingIndex [, Restart]] )

  • StartingIndex 是 long 类型的常量表达式,表示要从其开始(或重新开始)的行索引的值。StartingIndex is a constant expression of type long indicating the value of the row index to start at (or to restart to). 默认值为 1The default value is 1.
  • Restart 是 bool 类型的可选参数,表示何时将编号重新设置为 StartingIndex 值。Restart is an optional argument of type bool that indicates when the numbering is to be restarted to the StartingIndex value. 如果未提供,则使用默认值 falseIf not provided, the default value of false is used.

返回Returns

函数将当前行的行索引作为 long 类型的值返回。The function returns the row index of the current row as a value of type long.

示例Examples

以下示例返回一个包含两列的表,第一列 (a) 包含从 101 的数字,第二列 (rn) 包含从 110 的数字:The following example returns a table with two columns, the first column (a) with numbers from 10 down to 1, and the second column (rn) with numbers from 1 up to 10:

range a from 1 to 10 step 1
| sort by a desc
| extend rn=row_number()

下面的示例与上面的示例类似,只是第二列 (rn) 从 7 开始:The following example is similar to the above, only the second column (rn) starts at 7:

range a from 1 to 10 step 1
| sort by a desc
| extend rn=row_number(7)

最后一个示例展示了如何对数据进行分区,以及如何为每个分区的行编号。The last example shows how one can partition the data and number the rows per each partition. 此处,我们按 Airport 对数据进行分区:Here, we partition the data by Airport:

datatable (Airport:string, Airline:string, Departures:long)
[
  "TLV", "LH", 1,
  "TLV", "LY", 100,
  "SEA", "LH", 1,
  "SEA", "BA", 2,
  "SEA", "LY", 0
]
| sort by Airport asc, Departures desc
| extend Rank=row_number(1, prev(Airport) != Airport)

运行此查询会生成以下结果:Running this query produces the following result:

AirportAirport 航空公司Airline DeparturesDepartures 排名Rank
SEASEA BABA 22 11
SEASEA LHLH 11 22
SEASEA LYLY 00 33
TLVTLV LYLY 100100 11
TLVTLV LHLH 11 22