sql - Teradata GROUPBY 的 Row_Number

Question

我正在尝试使用下面的结果集确定如何将 ROW_NUMBER() 函数与 TERADATA 结合使用。这是我尝试过的:

select col1, col2, ROW_NUMBER() OVER (ORDER by col2  ) 
    FROM
     (select col1, col2 
    from TABLE
    ORDER BY col2) A

结果集

38  11/14/2016  1
38  11/15/2016  2
38  11/16/2016  3
38  11/17/2016  4
38  11/18/2016  5
38  11/19/2016  6
39  11/20/2016  7
39  11/21/2016  8
39  11/22/2016  9
39  11/23/2016  10
39  11/24/2016  11
39  11/25/2016  12

预期:

Col1    Col2    Col3
38  11/14/2016  1
38  11/15/2016  1
38  11/16/2016  1
38  11/17/2016  1
38  11/18/2016  1
38  11/19/2016  1
39  11/20/2016  2
39  11/21/2016  2
39  11/22/2016  2
39  11/23/2016  2
39  11/24/2016  2
39  11/25/2016  2

Answer 1

您需要添加PARTITION BY:

SELECT col1, 
       col2,
       ROW_NUMBER() OVER (PARTITION BY col1 ORDER by col2) AS row_index 
FROM TABLE
ORDER BY col2

嗯，看来你确实需要:

SELECT col1, 
       col2,
       DENSE_RANK() OVER (ORDER by col1) AS row_index 
FROM TABLE
ORDER BY col2