分配行号，不需任何顺序 [英] Assigning row number without any order

问题描述

如何不带任何顺序使用row_number()函数

How can I use row_number() function without any order

示例表:

COL A           COL B
42123345990000  0
42123345990000  0.33333334
42123345990000  0.6666667
42123345990000  1
42123345990000  0.86340976
42123345980000  0
42123345980000  0.1
42123345980000  0.2
42123345980000  0.3432426
42123345980000  0.5
42123345980000  0.53144264

Desired Output:

ROW     COL A           COL B
1   42123345990000  0
2   42123345990000  0.33333334
3   42123345990000  0.6666667
4   42123345990000  1
5   42123345990000  0.86340976
1   42123345980000  0
2   42123345980000  0.1
3   42123345980000  0.2
4   42123345980000  0.3432426
5   42123345980000  0.5
6   42123345980000  0.53144264

我希望分区在COL A上存在，但是没有排序.

I would like partition to be existing on COL A but no ordering.

推荐答案

对row_number问题的无序一般回答是对一个常数进行排序-row_number()对(按1排序)

The general answer to the question of a row_number without an ordering is to order over a constant - row_number() over (order by 1)

在您的情况下，预期输出显示行号实际上是基于col b值的排名，因此，您真正想要的是-density_rank()over(按COLB排序)

In your case the expected output shows that the row number is actually a rank based upon the col b values, so what you actually want is - dense_rank() over (order by COLB)

这篇关于分配行号，不需任何顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！