Azure / U-SQL - ORDER BY in ROW_NUMBER()

Question

我正在构建一个具有多个中间表的 U-SQL 脚本。 我想根据其中一列的顺序分配 ROW_NUMBER((，但不知道如何这样做。

@t = SELECT *
     FROM(
        VALUES
        ( 1, "2010-01-01","TagName1", 30 ),
        ( 2, "2010-01-01","TagName2", 5 ),
        ( 2, "2010-01-02","TagName2", 7 ),
        ( 2, "2010-01-02","TagName3", 6 ),
        ( 3, "2010-01-03","TagName2", 15 ),
        ( 1, "2010-01-01","TagName2", 2 ),
        ( 1, "2010-01-01","TagName3", 1),
        ( 3, "2010-01-04","TagName1", 2 ),
        ( 3, "2010-01-04","TagName2", 4 )
     ) AS T(DeviceID, Date, TagName, dv);
@t2 = SELECT DISTINCT(dv) as dv
             , ROW_NUMBER() OVER() as LevelNumber
      FROM @t
      ORDER BY dv;

如果我运行上面的代码，它会告诉我将ORDER BY移动到 OUTPUT 语句，但是当表内容在后续操作中发生变化时，这不起作用。

我尝试将列名放在OVER()部分中，但这也不起作用。

我想要这样的东西：

  dv          LevelNumber
  1                1
  2                2      
  4                3
  5                4

有什么建议吗？

Answer 1

您可以在OVER子句中包含ORDER BY和/或PARTITION BY子句。 您可以将ORDER BY子句移动到OUTPUT语句中，例如

@t = SELECT *
     FROM(
        VALUES
        ( 1, "2010-01-01","TagName1", 30 ),
        ( 2, "2010-01-01","TagName2", 5 ),
        ( 2, "2010-01-02","TagName2", 7 ),
        ( 2, "2010-01-02","TagName3", 6 ),
        ( 3, "2010-01-03","TagName2", 15 ),
        ( 1, "2010-01-01","TagName2", 2 ),
        ( 1, "2010-01-01","TagName3", 1),
        ( 3, "2010-01-04","TagName1", 2 ),
        ( 3, "2010-01-04","TagName2", 4 )
     ) AS T(DeviceID, Date, TagName, dv);

//@t2 = SELECT DISTINCT(dv) as dv
//             , ROW_NUMBER() OVER() as LevelNumber
//      FROM @t
//      ORDER BY dv;

@output =
    SELECT DeviceID,
           Date,
           TagName,
           dv,
           ROW_NUMBER() OVER( ORDER BY dv ) AS LevelNumber
    FROM @t;

OUTPUT @output TO "/output/output.csv"
ORDER BY dv
USING Outputters.Csv();

我能得到的最接近你的结果的是这个查询，但你需要的逻辑对我来说不是 100% 清楚：

@output =
    SELECT dv,
           ROW_NUMBER() OVER(ORDER BY dv) AS LevelNumber
    FROM
    (
        SELECT DISTINCT dv
        FROM @t
    ) AS x;