主页 / dba / 问题 / 127303
Accepted
Kaishu
Asked: 2016-01-26 21:59:57 +0800 CST

如何将列传递给sql中的函数？

  • 772

我计算中位数为：

DECLARE @TEMP TABLE
(
    ID INT
)

Select 
(
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) + 
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

以上查询我想使用。columns但是，就我而言，我想计算的人太多了MEDIAN。但我认为重复上面的代码块会很糟糕column。所以，我试图定义单独的函数来接受column值、处理和返回中值。我应该为此定义table-value-funtion还是有另一种optimized方法？

这个问题与以下问题有关：

sql-server sql-server-2012

2 个回答

  1. Best Answer

    计算简单或分组中位数的方法比您的问题中显示的方法更有效：

    计算中位数最快的方法是什么？
    分组中位数的最佳方法

    2012 年的总冠军是 Peter Larsson 的方法。模式是：

    简单中位数

    SELECT
    Median = AVG(1.0 * SQ.YourColumn)
FROM 
(
    SELECT NumRows = COUNT_BIG(*) 
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
) AS C
CROSS APPLY 
(
    SELECT YT.ColumnName
    FROM dbo.YourTable AS YT
    WHERE YT.ColumnName IS NOT NULL
    ORDER BY YT.ColumnName ASC
    OFFSET (C.NumRows - 1) / 2 ROWS
    FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ;

    分组中位数

    SELECT
    SQ2.GroupingColumn,
    SQ2.Median
FROM 
(
    SELECT
        GroupingColumn,
        NumRows = COUNT_BIG(*) 
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
    GROUP BY
        GroupingColumn
) AS C
CROSS APPLY 
(
    SELECT 
        Median = AVG(1.0 * SQ1.YourColumn)
    FROM
    (
        SELECT YT.ColumnName
        FROM dbo.YourTable AS YT
        WHERE 
            YT.GroupingColumn = C.GroupingColumn
            AND YT.ColumnName IS NOT NULL
        ORDER BY 
            YT.ColumnName ASC
            OFFSET (C.NumRows - 1) / 2 ROWS
            FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
    ) AS SQ1
) AS SQ2;

    为了最大化上述OFFSET方法的性能，您可能需要添加锁定提示（高级主题）。当然，也需要适当的索引。

    代码重用

    这很难通过 T-SQL 函数直接实现，因为这些函数不允许执行动态 SQL（假设您正在考虑传入列名）。

    有几种方法可以解决这个问题，包括使用函数来生成动态 SQL 文本本身，然后可以由调用者执行。问题中没有足够的细节来说明哪种方法最适合您。

    • 11

  2. 如果你想计算一个中值，有几种方法可以做到这一点，具体取决于你（或其他人）使用的 SQL Server 版本。Dwain Camps 为 Simple Talk 写了两篇文章，其中他从不同的人那里收集了几个纯 T-SQL 选项，给出了每个选项的示例并比较了它们的性能：

    但是，我认为您不能将这些方法中的任何一个封装到一个函数中以便于重用。为此，您需要使用 SQLCLR 来创建用户定义的聚合(UDA)。几年前我写了一篇文章，展示了如何创建 UDA，以 Median 为例 :-)

    充分利用 SQL Server 2005 UDT 和 UDA

    关于那篇文章，请记住：

    • 这篇文章写于 2007 年，在 SQL Server 2008 出现之前，它引入了一项改进（即允许存储超过 8000 个字节），这使得文章中显示的压缩技术在某种程度上是不必要的。GZipStream但是，删除这些东西并MaxByteSize从更改为 应该相当8000容易-1。
    • 您无需编写任何代码，因为本文底部包含一个安装 UDA 的 SQL 脚本，因此您只需下载、运行和使用 Median 聚合 :-)

    也可以看看：

    • 5

相关问题