Query
;WITH Mable AS(
SELECT 1 Mable
UNION ALL
SELECT Mable + 1
FROM Mable
WHERE Mable < 100
), GatesA AS(
SELECT
*, HopperA = CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END
FROM Mable
), GatesB AS(
SELECT *, HopperB = ROW_NUMBER() OVER (ORDER BY Mable) % 4
FROM GatesA
WHERE HopperA <> 1)
SELECT
SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END)
, SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
, COUNT(1)
- SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END) - SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
FROM GatesA A
LEFT JOIN GatesB B
ON A.Mable = B.Mable
|
Tuned Query by khtan
;WITH Marble AS(
SELECT 1 Marble
UNION ALL
SELECT Marble + 1
FROM Marble
WHERE Marble < 100
)
SELECT
SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
, SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
, COUNT(1)
- SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
- SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
FROM Marble
|
How it work?
First step, understand and list down the marble flow.
Second Step, Identify which marble should enter red bucket. This can easily achieve by using modulo 3, any number that does not have any remainder should enter red bucket. In this case, marble 3, 6, 9, 12, 15... should enter red bucket.
Third Step, Identify which marble should enter aqua bucket. After removing all the marble that enter red bucket, we had to identify the FOURTH marble that should enter aqua bucket. This can be achieve by re-numbering the marble and modulo 4.
However, there were much simple solution by using maths. Notice that every gap of marble that fall into Aqua Bucket is 6. For example, range 5 to 0 is 6, range 11 to 6 is 6, range 17 to 12 is 6.
We could utilize modulo 6 for this solution. But if we blindly utilize modulo 6, the result will be incorrect as there were no marble named 0; therefore we had to add 1 into original numbering to achieve the required result.
Therefore, adding 1 to the original numbering (referring to Number + 1 column) shows that multiplication of six (later modulo 6) will be the marble that should enter red bucket.
Therefore, adding 1 to the original numbering (referring to Number + 1 column) shows that multiplication of six (later modulo 6) will be the marble that should enter red bucket.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189443&SearchTerms=marble
No comments:
Post a Comment