Complex SQL Query similar to a z order problem
I have a complex SQL problem in MS SQL Server, and in drawing on a piece of paper I realized that I could think of it as a single bar filled with rectangles, each rectangle having segments with different Z orders. In reality it has nothing to do with z order or graphics at all, but more to do with some complex business rules that would be difficult to explain. Howoever, if anyone has ideas on how to solve the below that will give me my solution.
I have the following data:
ObjectID | PercentOfBar | ZOrder (where smaller is closer)
---------------------------------------------------------------
A | 100 | 6
B | 50 | 5
B | 50 | 4
C | 30 | 3
C | 70 开发者_高级运维 | 6
The result of my query that I want is this, in any order:
PercentOfBar | ZOrder
-------------------------
50 | 5
20 | 4
30 | 3
Think of it like this, if I drew rectangle A, it would fill 100% of the bar and have a z order of 6.
6666666666
AAAAAAAAAA
If I then layed out rectangle B, consisting of two segments, both segments would cover up rectangle A resulting in the following rendering:
4444455555
BBBBBBBBBB
As a rule of thumb, for a given rectangle, it's segments should be layed out such that the highest z order is to the right of the lower Z orders.
Finally rectangle C would cover up only portions of Rectangle B with it's 30% segment that is z order 3, which would be on the left. You can hopefully see how the is represented in the output dataset I listed above:
3334455555
CCCBBBBBBB
Now to make things more complicated I actually have a 4th column such that this grouping occurs for each key:
Input:
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
Output:
SomeKey, PercentOfBar, ZOrder
X, 50, 5
X, 20, 4
X, 30, 3
Y, 100, 6
Z, 50, 2
Z, 50, 5
Notice in the output, the PercentOfBar for each SomeKey would add up to 100%.
This is one I know I'm going to be thinking about when I go to bed tonight.
Just to be explicit and have a question:
What would be a query that would produce the results described above?
I'm making the following assumptions:
- You're using SQL Server 2005 or newer.
- SomeKey, ObjectID, ZOrder is unique.
Other notes:
- I have not optimized the query - I just tried to get the correct result.
- I've only tested it on your test data.
With that in mind you could try something like this:
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
Result:
SomeKey PercentOfBar ZOrder
X 50 5
X 20 4
X 30 3
Y 100 6
Z 50 2
Z 50 5
Test data:
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);
精彩评论