Início / dba / Perguntas / 127473
Accepted
Kaishu
Asked: 2016-01-28 05:44:07 +0800 CST

Calculando várias medianas

  • 772

Eu defini functioncom table value parameterqual returns medianconforme os valores passados ​​para ele. Functiondefinido como:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

E Table Type TypeMediandefinição como:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Agora eu tenho um table Listingcom valores preenchidos nele como e table RESULTa ser preenchido de acordo com table Listing:

Tables structureComo

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

A tabela de listagem tem mais de 1.000 linhas de dados.

Todos columnde ambos tablessão do tipo int. Agora eu quero preencher columnse RESULT tableisso columnpode ser calculado como:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

E estou fazendo assim:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian

INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing

UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)

-- For next column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing

UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--E esta consulta de atualização é repetida para as colunas restantes. Como eu poderia fazer isso em uma única consulta?

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Para duas somas e três medianas, em uma única tabela, honestamente não consigo ver o benefício de usar uma solução dinâmica ou baseada em função complicada.

    É muito fácil construir uma única consulta usando o método mediano de Peter Larsson que mostrei antes :

    CREATE TABLE dbo.Listing
(
    ListingCol1 integer NULL,
    ListingCol2 integer NULL,
    ListingCol3 integer NULL,
    ListingCol4 integer NULL,
    ListingCol5 integer NULL
);

CREATE TABLE dbo.Result
(
    Col1 integer NULL,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

-- Just to show indexes are helpful for the median calculations
CREATE INDEX i ON dbo.Listing (ListingCol3)
CREATE INDEX j ON dbo.Listing (ListingCol4)
CREATE INDEX k ON dbo.Listing (ListingCol5)

    Solução

    INSERT dbo.Result
(
    Col1,
    Col2,
    Col3,
    Col4,
    Col5
)
SELECT
    SC.SumCol1,
    SC.SumCol2,
    SQ3.MedianCol3,
    SQ4.MedianCol4,
    SQ5.MedianCol5
FROM 
(
    -- Sums + counts needed for the median calculations
    SELECT 
        SumCol1 = SUM(L.ListingCol1),
        SumCol2 = SUM(L.ListingCol2),
        CountCol3 = COUNT_BIG(L.ListingCol3),
        CountCol4 = COUNT_BIG(L.ListingCol4),
        CountCol5 = COUNT_BIG(L.ListingCol5)
    FROM dbo.Listing AS L
) AS SC
CROSS APPLY 
(
    -- Median for column 3
    SELECT
        MedianCol3 = AVG(1.0 * SQ.ListingCol3)
    FROM
    (
        SELECT 
            L3.ListingCol3
        FROM dbo.Listing AS L3
        WHERE 
            L3.ListingCol3 IS NOT NULL
        ORDER BY 
            L3.ListingCol3 ASC
        OFFSET (SC.CountCol3 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol3 % 2) ROWS ONLY
    ) AS SQ
) AS SQ3
CROSS APPLY 
(
    -- Median for column 4
    SELECT
        MedianCol4 = AVG(1.0 * SQ.ListingCol4)
    FROM
    (
        SELECT 
            L4.ListingCol4
        FROM dbo.Listing AS L4
        WHERE 
            L4.ListingCol4 IS NOT NULL
        ORDER BY 
            L4.ListingCol4 ASC
        OFFSET (SC.CountCol4 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol4 % 2) ROWS ONLY
    ) AS SQ
) AS SQ4
CROSS APPLY 
(
    -- Median for column 5
    SELECT
        MedianCol5 = AVG(1.0 * SQ.ListingCol5)
    FROM
    (
        SELECT 
            L5.ListingCol5
        FROM dbo.Listing AS L5
        WHERE 
            L5.ListingCol5 IS NOT NULL
        ORDER BY 
            L5.ListingCol5 ASC
        OFFSET (SC.CountCol5 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol5 % 2) ROWS ONLY
    ) AS SQ
) AS SQ5;

    Formato do plano de execução esperado:

    Plano

    • 3

  2. E esta consulta de atualização é repetida para as colunas restantes. Como eu poderia fazer isso em uma única consulta?

    Deixando de lado as implicações de desempenho por um momento, a maneira mais fácil de fazer isso em uma única consulta ** é criando um agregado definido pelo usuário (UDA) via SQLCLR. Isto é o que eu descrevi em minha resposta à sua pergunta relacionada:

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

    Se você tivesse essa função, poderia fazer o seguinte:

    INSERT INTO RESULT(Col1, Col2, Col3, Col4, Col5)
  SELECT SUM(ListingCol1) AS [Col1],
         SUM(ListingCol2) AS [Col2],
         dbo.AggMedian(ListingCol3) AS [Col3],
         dbo.AggMedian(ListingCol4) AS [Col4],
         dbo.AggMedian(ListingCol5) AS [Col5]
  FROM   LISTING;

    Com isso dito, o desempenho ainda é algo que precisa ser considerado. A abordagem mostrada acima não funciona em todas as situações. Se a LISTINGtabela tiver milhões de linhas (pelo menos milhões por cada agrupamento), isso pode não funcionar. Mas se cada agrupamento tiver 5.000 linhas, ou talvez até 10.000 ou algo assim, e o processo não for executado várias vezes por minuto, você ficará bem. Claro, como com qualquer coisa, deve ser testado contra seus dados reais para determinar se há um problema de desempenho ou não.

    ** Por "maneira mais fácil em uma única consulta", estou assumindo que o método desejado é aquele que pode ser facilmente aplicado em várias situações, principalmente em consultas ad hoc.

    • 2

relate perguntas