SQL Server 2008中的用户定义的排名/分析函数

Question

我正在计划将数据仓库迁移到SQL Server 2008，并尝试想出在SQL Server 2008中复制Oracle的LAG、LEAD、FIRST_VALUE和LAST_VALUE分析函数的方法。虽然窗口分析函数的基本机制已经包含在SQL Server 2008中（例如，ROW_NUMBER、RANK和DENSE_RANK都存在），但不包括这些函数。

对于这些函数，可以通过创建一个子查询并使用ROW_NUMBER为每行分配一个数字，然后自连接该查询以查找具有相邻行号（对于LAG和LEAD）或行号为1（对于FIRST_VALUE）的相关行来实现相同的功能。

我预计进行自连接会降低操作的效率：但是我还没有SQL Server进行测试。因此，在实际评估性能之前，我想知道是否有更好的解决方法可以避免自连接。

查看用户定义的聚合函数文档后，可以想象可以使用相同的代码结构来提供用户定义的分析函数。

所以我的问题是：您是否可以在用户定义的聚合函数之后添加OVER()子句，以使其作为分析函数调用？

如果可以，每行是否会调用一次Terminate()方法？是否需要特殊处理以确保按照OVER()子句中指定的顺序将行发送到您的UDF中？

- William Rose

Answer 1

我会使用自连接而不是UDF。

您正在查看使用表访问的标量UDFS，这几乎总是会导致性能不佳（它是一个游标）。否则，您可能可以使用APPLY，但这也是逐行进行的。

另外，Oracle函数不是聚合函数。用户定义的聚合仍然必须对结果集执行相同的处理。

请记住，内部上，Oracle仍然必须逐行处理才能计算出值。

因此，以下是SQL Server 2005+示例FIRST_VALUE（未经测试），使用自连接。

请注意，交叉连接用于解耦FIRST_VALUE和其余2个，因为结果集之间没有关系。如果使用UDF或用户定义的聚合，则很可能需要针对第一个结果集中的每一行重复计算FIRST_VALUE。

;WITH CTE AS
(
    SELECT
        department_id, last_name, salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS ranking
    FROM employees
    WHERE department_id = 90
)
SELECT
    c1.department_id, c1.last_name, c1.salary,
    c2.last_name as Poorest
FROM
    CTE c1
    CROSS JOIN
    (SELECT last_name FROM CTE WHERE Ranking = 1) c2
ORDER BY
    c1.employee_id

- gbn

---

很好：我希望从聚合UDF示例的设计中能够发现更多可供优化器使用的选项，以便比直接使用游标获得更好的性能。Oracle可能确实会对分析函数进行逐行处理，但它能够在数据上进行单次遍历（在排序后），这通常比等效的自连接更好。如果每行有多个函数，这将导致许多自连接，您认为UDF是否会获得任何优势？ - William Rose

可能你可以在一个自连接中处理多个输出。在我的例子中，你可以添加一个额外的CTE列来按工资降序排列，并且也可以使用LAST_VALUE函数。相比多个用户定义函数，一个单一的用户定义函数会更好。 - gbn

Answer 2

在SQL Server中，分析是SSAS的一部分；您将在那里找到FirstNonEmpty、LastNonEmpty、FirstChild、LastChild等函数。它包含在SQL Server的标准版和企业版中；请点击这里。也就是说，如果您想要构建多维数据集（cubes）。

- Damir Sudarevic