sql - 单个 SQL 查询中的多个 Row_Number() 调用

Question

我正在尝试设置一些数据来计算 SQL Server 2008 中的多个中位数，但遇到性能问题。现在，我正在使用这个pattern ([另一个例子 bottom )。是的，我没有使用 CTE，但使用 CTE 无论如何都无法解决我遇到的问题，而且性能很差，因为 row_number 子查询以串行方式运行，而不是并行运行。

这是一个完整的示例。下面的 SQL 我详细解释了这个问题。

-- build the example table    

CREATE TABLE #TestMedian (
    StateID INT,
    TimeDimID INT,
    ConstructionStatusID INT,

    PopulationSize BIGINT,
    SquareMiles BIGINT
);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 100000, 200000);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 200000, 300000);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 300000, 400000);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 100000, 200000);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 250000, 300000);

INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)
VALUES (1, 1, 1, 350000, 400000);

--TruNCATE TABLE TestMedian

    SELECT
        StateID
        ,TimeDimID
        ,ConstructionStatusID
        ,NumberOfRows = COUNT(*) OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID)
        ,PopulationSizeRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY PopulationSize)
        ,SquareMilesRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY SquareMiles)
        ,PopulationSize
        ,SquareMiles
    INTO #MedianData
    FROM #TestMedian

    SELECT MinRowNum = MIN(PopulationSizeRowNum), MaxRowNum = MAX(PopulationSizeRowNum), StateID, TimeDimID, ConstructionStatusID, MedianPopulationSize= AVG(PopulationSize) 
    FROM #MedianData T
    WHERE PopulationSizeRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2)
    GROUP BY StateID, TimeDimID, ConstructionStatusID

    SELECT MinRowNum = MIN(SquareMilesRowNum), MaxRowNum = MAX(SquareMilesRowNum), StateID, TimeDimID, ConstructionStatusID, MedianSquareMiles= AVG(SquareMiles) 
    FROM #MedianData T
    WHERE SquareMilesRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2)
    GROUP BY StateID, TimeDimID, ConstructionStatusID


    DROP TABLE #MedianData
    DROP TABLE #TestMedian

此查询的问题在于 SQL Server 以串行方式而非并行方式执行两个“ROW__NUMBER() OVER...”子查询。因此，如果我有 10 个这样的 ROW__NUMBER 计算，它会一个接一个地计算它们，我会得到线性增长，这很糟糕。我有一个 8 路 32GB 系统，正在运行这个查询，我希望有一些并行性。我正在尝试在 5,000,000 行表上运行这种类型的查询。

我可以通过查看查询计划并查看同一执行路径中的排序来判断它是这样做的(显示查询计划的 XML 在 SO 上效果不佳)。

所以我的问题是:如何更改此查询以便并行执行 ROW_NUMBER 查询？是否有一种完全不同的技术可以用来为多个中值计算准备数据？

Answer 1

每个 ROW_NUMBER 都需要首先对行进行排序。由于您的两个 RN 具有不同的 ORDER BY 条件，因此查询必须生成结果，然后为第一个 RN 排序(可能已经排序)，生成 RN，然后为第二个 RN 排序并生成第二个 RN 结果。根本没有任何神奇的仙尘可以在不计算行在所需顺序中的位置的情况下具体化行数值。