主页 / dba / 问题 / 230328
Accepted
youcantryreachingme
Asked: 2019-02-21 14:52:41 +0800 CST

解释 OVER 子句

  • 772

我看到了一个简洁的 TSQL 语句，它有效地将字符串拆分为其组成字符，每行一个，用于评估ascii每个字符的值。

如果我正确、有效地读取查询，则使用 3 个 CTE 来准备一个包含 10,000 行的 1 列表，每列的值为“0”。

第四个 CTE 定义如下：

cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n 
    FROM E4
)

随后，将此 CTE 连接到一个表，该表包含一个包含感兴趣字符串的列，其中包含以下内容select：

SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))

即行号 n，然后是 LastName 中的第 n 个字符，然后是该字符的 ascii 值。

我的问题与over上述 CTE 中的条款有关。

本质上，它到底在做什么？

如果我们从 10,000 个相同的行中查询 row_number，为什么我们需要一个order by子句？为什么order by放入一个over子句而不是作为语句的order by子句select- 特别是因为该over子句甚至没有指定任何分区？（我认为这意味着row_number操作的窗口是完整的 10,000 行？）排序是什么意思select null？

sql-server t-sql

2 个回答

  1. Best Answer

    ROW_NUMBER() 是一个排名窗口函数，排名窗口函数需要一个强制的 ORDER BY 子句。如果您尝试在没有 ORDER BY 的情况下编写它，您将收到语法错误。

    SELECT ROW_NUMBER() OVER()
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 4112, Level 15, State 1, Line 1 
-- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

    子查询的诀窍是有人在博客中发现的，作为一种性能优化。SQL Server 总是执行排序操作，因为 ORDER BY 子句不允许使用常量：

    SELECT ROW_NUMBER() OVER(ORDER BY NULL)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5309, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support constants as ORDER BY clause expressions.

    也不是被视为索引的整数：

    SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5308, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support integer indices as ORDER BY clause expressions.

    事实证明，由于代码中的一些故障，您可以通过使用子查询来规避这个限制，由于某种原因，这是允许的，并消除了排序运算符。

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);

    无排序运算符

    您可以在子查询中使用任何常量，NULL 可能让人想起在 EXISTS 谓词中使用 SELECT NULL 的习惯，在 SQL Server 的早期历史中，与 * 或任何其他列表达式相比，这对性能有影响，如优化器不够聪明，无法忽略它。

    高温高压

    更新 @Erik-Darling 评论说，您还可以通过使用计算表达式来规避它：

    You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);
    • 8

  2. OVER 子句在应用选定的窗口函数之前设置行集的排序（如果包括 PARTITION BY，则进行分区）。由于您可以在单个查询中使用多个 Window 函数，因此每个函数都需要自己的分区和排序，以确保按需要返回数据。

    在您的示例中， ROW_NUMBER() 用于为 CTE 中的每一行生成一个连续的行号。使用 SELECT NULL 是因为不需要特定的顺序，但窗口函数需要 ORDER BY 子句。

    实现相同目的的另一种方法是使用 IDENTITY 列，但是，它具有其他含义，需要更改现有表或创建临时表。CTE 中的 ROW_NUMBER 窗口函数允许动态生成此标识。

    要回答您的具体问题：

    本质上，它到底在做什么？

    在将 ROW_NUMBER() 窗口函数应用于该结果集以生成等于 E4 中的行数的行号列表之前，它“随机”对 E4 中的行进行排序。OVER 可以翻译为“使用此排序和分区获取我的结果集，并将此窗口函数应用于（OVER）该结果集，而与主 SELECT 语句中的排序无关。”

    更多信息：OVER 子句

    • 4

相关问题