按行编号排序

Question

更新：感谢大家，话题已关闭，睡醒后我已经理解了一切=）

我在理解OVER子句和ROW_NUMBER函数方面遇到了问题。有一个简单的表格，包含姓名和成绩。我想为每个姓名计算平均分。

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name)

它将显示类似于这样的内容，我明白其中的原因——这就是ROW_NUMBER()的作用。

name|number
Pete 1
Pete 2

但是如果我写

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

它将显示

name|number
Pete 1

这次我不明白如何在ROW_NUMBER()函数中使用ORDER BY。有人能给我解释一下吗？

- Alecs

---

也许如果您在SELECT子句中添加AVG(mark)（并且这是结果），它会有所帮助。那么很明显了。（我打赌Pete和John的平均分相同。） - beach

源数据类似于（Pete，10），（Pete，15），（Pete，10），（John，15），（John，20）。这并不重要。 - Alecs

这是一个查询链接，可用于在Stack Overflow网站上检索有关标记使用情况的信息。 - t-clausen.dk

不确定你在这里尝试做什么：窗口函数毫无意义。 - gbn

是的，可以不用它们来完成，关键是要理解它们的工作原理。 - Alecs

注意：OP的“它将显示”部分包含不正确的结果。请参阅我的答案以获取详细信息。 - beach

Answer 1

你可以按照ROW_NUMBER列排序，因为SELECT子句在ORDER BY子句之前进行评估。你可以按任何列或列别名进行排序。这就是为什么没有抛出错误消息的原因（因为它是有效的）。

SELECT name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY number

评估结果为

name       number
---------- --------------------
John       1
pete       1
pete       2
John       2
pete       3

OP的第二个row_number示例是不正确的。

SELECT AVG(mark) OVER(PARTITION BY name), name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

按预期返回，因为AVG是第一个排序列，其后是数字。

            name       number
----------- ---------- --------------------
11          pete       1
11          pete       2
11          pete       3
17          John       1
17          John       2

将查询改为number DESC，Pete仍然排在第一位，但行号是按降序排列的。

            name       number
----------- ---------- --------------------
11          pete       3
11          pete       2
11          pete       1
17          John       2
17          John       1

SQL 运算顺序

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

- beach

---

是的，你说得对。我在写这个问题时很累，把这个查询和另一个查询搞混了。我错过了一个带有ties的top 1查询。 - Alecs

Answer 2

你不能直接按ROW_NUMBER排序：我不知道为什么你在这种情况下没有得到错误，但通常会出错。因此需要使用派生表或CTE。

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) as number,
       AVG(mark) OVER (PARTITION BY name) AS nameavg
    FROM table
    ) foo
ORDER BY
   nameavg, number

然而，按照名称分区并按名称排序是没有意义的。每个分区都有随机顺序，因为排序就是分区。

我怀疑你想要像这样的东西，其中ROW_NUMBER是基于AVG计算的。

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY nameavg) AS number
    FROM
        (
        SELECT
           name,
           AVG(mark) OVER (PARTITION BY name) AS nameavg
        FROM table
        ) foo
    ) bar
ORDER BY
    number

或者更传统的方式（但名称对于普通人来说有点复杂）

SELECT
    name, number
FROM
    (
    SELECT
       name,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY nameavg) AS number
    FROM
        (
        SELECT
           name,
           AVG(mark) AS nameavg
        FROM
           table
        GROUP BY
           name
        ) foo
    ) bar
ORDER BY
    number

你可以使用以下方法，将派生的foo和bar合并为一个：

ROW_NUMBER() OVER (PARTITION BY name ORDER BY AVG(mark))

但是这些都没有意义：我知道你的问题是关于它如何工作的抽象问题，但是问题不清楚。如果你用简单的英语描述，并提供示例输入和输出，那么会更有意义。

- gbn

---

有个好心人为我的查询做了一个例子http://data.stackexchange.com/stackoverflow/q/107997/。他只添加了一些字段，但是查询可以被编辑。在那里也不会出现任何错误。 - Alecs

请注意，语句“您不能直接按ROW_NUMBER排序”并不完全正确。您可以通过使用列别名而无需使用子查询进行排序。 - beach