Início / dba / Perguntas / 127303
Accepted
Kaishu
Asked: 2016-01-26 21:59:57 +0800 CST

Como posso passar coluna para função no sql?

  • 772

Estou calculando mediana como:

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

Consulta acima que eu quero usar. Mas, no meu caso, há tantos columnspara aqueles que quero calcular MEDIAN. Mas acho que seria ruim repetir o bloco de código acima para cada arquivo column. Então, estou tentando definir uma função separada que aceite columnvalores, processe e retorne a mediana. Devo definir table-value-funtionisso ou há outra optimizedmaneira de fazer isso?

Esta questão está relacionada com as seguintes questões:

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Existem maneiras muito mais eficientes de calcular uma mediana simples ou agrupada do que a mostrada em sua pergunta:

    Qual é a maneira mais rápida de calcular a mediana?
    Melhores abordagens para mediana agrupada

    O vencedor geral de 2012 é um método de Peter Larsson. O padrão é:

    Mediana Simples

    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;

    Mediana Agrupada

    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;

    Para maximizar o desempenho do OFFSETmétodo acima, pode ser necessário adicionar uma dica de bloqueio (tópico avançado). A indexação adequada também será necessária, é claro.

    Reutilização de código

    Isso é difícil de conseguir diretamente com uma função T-SQL, pois elas não permitem a execução de SQL dinâmico (supondo que você esteja pensando em passar o nome da coluna).

    Existem várias maneiras de contornar isso, incluindo o uso de uma função para gerar o próprio texto SQL dinâmico, que pode ser executado pelo chamador. Não há detalhes suficientes na pergunta para dizer qual abordagem seria mais adequada para você.

    • 11

  2. Se você deseja calcular um valor mediano, existem algumas maneiras de fazer isso, dependendo da versão do SQL Server em que você (ou outra pessoa, talvez) esteja. Dwain Camps escreveu dois artigos para o Simple Talk nos quais coletou várias opções T-SQL puras de várias pessoas, deu exemplos de cada uma e comparou seu desempenho:

    No entanto, não acho que você possa encapsular nenhum desses métodos em uma função para fácil reutilização. Para isso, você precisaria usar o SQLCLR para criar um agregado definido pelo usuário (UDA). Escrevi um artigo há vários anos mostrando como criar um UDA, usando Median como exemplo :-)

    Obtendo o máximo dos UDTs e UDAs do SQL Server 2005

    Em relação a esse artigo, lembre-se:

    • O artigo foi escrito em 2007, antes do lançamento do SQL Server 2008, que introduziu uma melhoria (ou seja, permitindo armazenar mais de 8000 bytes) que torna a técnica de compactação mostrada no artigo um tanto desnecessária. Mas, deve ser bastante fácil remover o GZipStreammaterial e mudar MaxByteSizede 8000para -1.
    • Você não precisa codificar nada, pois o artigo inclui, na parte inferior, um script SQL que instala o UDA para que você possa baixar, executar e usar o agregado Median :-)

    Veja também:

    • 5

relate perguntas