代码之家 › 专栏 › 技术社区 › AaronLS

类似于z顺序问题的复杂SQL查询

sql-server sql

AaronLS · 技术社区 · 15 年前

我在MS SQL Server中遇到了一个复杂的SQL问题,在画一张纸的时候,我意识到我可以把它看作一个由矩形填充的单条,每个矩形都有不同Z顺序的段。实际上,它根本与z顺序或图形无关,而更多地与一些复杂的业务规则有关,这些规则很难解释。但是,如果有人对如何解决下面的问题有想法的话,我会给出我的解决方案。

我有以下数据:

ObjectID  |  PercentOfBar  |  ZOrder (where smaller is closer)
---------------------------------------------------------------
A         |  100           |  6
B         |  50            |  5
B         |  50            |  4
C         |  30            |  3
C         |  70            |  6

我需要的查询结果是,按任意顺序:

PercentOfBar  |  ZOrder
-------------------------
50            |  5
20            |  4
30            |  3

这样想吧,如果我画一个矩形A,它将填充100%的条,Z的顺序是6。

6666666666
AAAAAAAAAA

然后,如果我将由两个段组成的矩形B布局,两个段将覆盖矩形A,从而产生以下渲染:

4444455555
BBBBBBBBBB

根据经验法则,对于给定的矩形,其分段的布局应使最高Z阶位于较低Z阶的右侧。

最后,矩形C将只覆盖矩形B的一部分,其30%的段为z阶3,在左侧。希望您可以看到如何在上面列出的输出数据集中表示:

3334455555
CCCBBBBBBB

现在,为了使事情变得更复杂,我实际上有了第4列,这样每个键都会进行分组:

输入:

SomeKey, ObjectID, PercentOfBar, ZOrder (where smaller is closer)
X, A, 100, 6
X, B, 50, 5
X, B, 50, 4
X, C, 30, 3
X, C, 70, 6
Y, A, 100, 6
Z, B, 50, 2
Z, B, 50, 6
Z, C, 100, 5

输出:

SomeKey, PercentOfBar, ZOrder
X, 50, 5
X, 20, 4
X, 30, 3
Y, 100, 6
Z, 50, 2
Z, 50, 5

注意,在输出中,每个somekey的percentofbar加起来可达100%。

这是我知道我今晚睡觉时会想到的。

只是想明确地提出一个问题:

会产生上述结果的查询是什么?

1 回复 | 直到 15 年前

Mark Byers 15 年前

我做了以下假设:

您使用的是SQL Server 2005或更高版本。
somekey、objectid、zorder是唯一的。

其他注释:

我没有优化查询-我只是试图得到正确的结果。
我只在你的测试数据上测试过。

考虑到这一点,你可以尝试这样的方法:

WITH Bars AS (
    SELECT
        T1.SomeKey,
        T1.ObjectID,
        T1.ZOrder,
        SUM(T2.PercentOfBar) - T1.PercentOfBar AS PercentStart,
        SUM(T2.PercentOfBar) AS PercentEnd
    FROM Table1 T1
    JOIN Table1 T2
    ON T1.SomeKey = T2.SomeKey
        AND T1.ObjectID = T2.ObjectID
        AND T1.ZOrder >= T2.ZOrder
    GROUP BY T1.SomeKey, T1.ObjectID, T1.PercentOfBar, T1.ZOrder),
Boundaries AS (
    SELECT P, ROW_NUMBER() OVER (ORDER BY P) AS rn
    FROM (
        SELECT DISTINCT PercentStart AS P FROM Bars
        UNION
        SELECT DISTINCT PercentEnd FROM Bars
    ) T1),
Intervals AS (
    SELECT B1.P AS PercentStart, B2.P AS PercentEnd
    FROM Boundaries B1
    JOIN Boundaries B2
        ON B1.rn + 1 = B2.rn),
Bits AS (
    SELECT
        SomeKey,
        ObjectId,
        ZOrder,
        Intervals.PercentStart,
        Intervals.PercentEnd
    FROM Intervals
    JOIN Bars
        ON Bars.PercentStart <= Intervals.PercentStart
        AND Bars.PercentEnd >= Intervals.PercentEnd),
LowestZOrder AS (
    SELECT SomeKey, PercentStart, MIN(ZOrder) AS ZOrder
    FROM Bits
    GROUP BY SomeKey, PercentStart),
LowestBits AS (
    SELECT Bits.*
    FROM Bits
    JOIN LowestZOrder
        ON Bits.SomeKey = LowestZOrder.SomeKey
        AND Bits.PercentStart = LowestZOrder.PercentStart
        AND Bits.ZOrder = LowestZOrder.ZOrder)
SELECT
    SomeKey,
    MAX(PercentEnd) - MIN(PercentStart) AS PercentOfBar,
    ZOrder
FROM LowestBits
GROUP BY SomeKey, ObjectID, ZOrder
ORDER BY SomeKey, ObjectID, MIN(PercentStart) DESC

结果:

SomeKey PercentOfBar ZOrder
X       50           5
X       20           4
X       30           3
Y       100          6
Z       50           2
Z       50           5

测试数据:

CREATE TABLE Table1 (SomeKey NVARCHAR(100) NOT NULL, ObjectID NVARCHAR(100) NOT NULL, PercentOfBar INT NOT NULL, ZOrder INT NOT NULL);
INSERT INTO Table1 (SomeKey, ObjectID, PercentOfBar, ZOrder) VALUES
('X', 'A', 100, 6),
('X', 'B', 50, 5),
('X', 'B', 50, 4),
('X', 'C', 30, 3),
('X', 'C', 70, 6),
('Y', 'A', 100, 6),
('Z', 'B', 50, 2),
('Z', 'B', 50, 6),
('Z', 'C', 100, 5);