主页 / dba / 问题 / 127473
Accepted
Kaishu
Asked: 2016-01-28 05:44:07 +0800 CST

计算多个中位数

  • 772

我已经根据传递给它的值定义function了table value parameterwhich 。定义为：returns medianFunction

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

Table Type TypeMedian定义为：

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

现在我有一个table Listing带有填充值的 as 和一个table RESULT要根据以下内容填充的值table Listing：

Tables structure作为

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

列表表有超过 1000 行数据。

column两者都tables属于 type int。现在我想填写columnsofRESULT table并且column可以计算为：

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

我这样做是为了：

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);

-- 我为剩余的列重复此更新查询。我怎么能在单个查询中做到这一点？

sql-server sql-server-2012

2 个回答

  1. Best Answer

    对于两个总和和三个中位数，在一张表上，老实说，我看不出使用复杂的动态或基于函数的解决方案有什么好处。

    使用我之前向您展示的 Peter Larsson 中位数方法构造单个查询非常容易：

    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)

    解决方案

    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;

    预期的执行计划形状：

    计划

    • 3

  2. 我为剩余的列重复此更新查询。我怎么能在单个查询中做到这一点？

    暂时搁置性能影响，在单个查询中完成此操作的最简单方法**是通过 SQLCLR 创建用户定义的聚合 (UDA)。这就是我在回答你的相关问题时所描述的：

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

    如果您有这样的功能，您可以执行以下操作：

    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;

    话虽如此，性能仍然是需要考虑的问题。上面显示的方法并不适用于所有情况。如果LISTING表有数百万行（每个分组至少数百万行），那么这可能行不通。但是，如果每个分组有 5000 行，或者甚至可能有 10,000 行或类似的行，并且该过程不是每分钟运行多次，那么您应该没问题。当然，与任何事情一样，应该根据您的实际数据对其进行测试，以确定是否存在性能问题。

    **通过“单个查询中最简单的方法”，我假设所需的方法是可以在多种情况下轻松应用的方法，尤其是临时查询。

    • 2

相关问题