We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3
Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE
Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP
My view : The below will be my notes as I solved. Hope it will help!!
Q1 : Normal Group by which will produce a * b * c .
In our case
(2, 2, 2, 6, 18, 8) => a=2 b=2 c=2 => 2*2*2 =8 which is not satisfied with d in this case. We can ignore this
(2, 2, 2, 8, 64, 15) => 8 satisfied with dth place
(5, 10, 10, 500, 1000, 550) => a=5 b=10 c=10 => 5*10*10 =500 satisfied with dth place
(4, 7, 3, 84, 160, 117) => 4*7*3=84 satisfied with d th place.
Q2 : group by with CUBE
Explanation: Because of WITH CUBE, we need to consider all 3 dimensions as well and every possible cases.
so the result would be a*b*c +((a*b) +(b*c) +(c*a)) + (a + b + c) + 1
remaining options left ,
(2, 2, 2, 8, 64, 15) => 8 + (4 + 4 + 4 )+( 2 + 2 +2) +1 = 27 Not satisfied with eth place
(5, 10, 10, 500, 1000, 550) => 500 + (50 + 100 + 50) +(5+10+10) + 1 = 726 satisfied with dth place
(4, 7, 3, 84, 160, 117) => 84+ (28+21+12) + (4+7+3) +1 =160 satisfied with e th place.
Q3 : Group by with ROLL UP
Explanation : Roll up will Reduce the dimensions.
The formula will be a*b*c + (a*b) + a + 1
remaining options left ,
(5, 10, 10, 500, 1000, 550) => 500 + (50 ) +(5) + 1 = 556 Not satisfied with fth place
(4, 7, 3, 84, 160, 117) => 84+ (28) + (4) +1 =117 satisfied with f th place.
****So the ANSWER will be (4, 7, 3, 84, 160, 117) satisfied all the three queries based on the OLAP operations.
OLAP Operation Types
You are viewing a single comment's thread. Return to all comments →
Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3
Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE
Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP
My view : The below will be my notes as I solved. Hope it will help!!
Q1 : Normal Group by which will produce a * b * c .
In our case
(2, 2, 2, 6, 18, 8) => a=2 b=2 c=2 => 2*2*2 =8 which is not satisfied with d in this case. We can ignore this (2, 2, 2, 8, 64, 15) => 8 satisfied with dth place (5, 10, 10, 500, 1000, 550) => a=5 b=10 c=10 => 5*10*10 =500 satisfied with dth place (4, 7, 3, 84, 160, 117) => 4*7*3=84 satisfied with d th place.
Q2 : group by with CUBE
Explanation: Because of WITH CUBE, we need to consider all 3 dimensions as well and every possible cases.
remaining options left , (2, 2, 2, 8, 64, 15) => 8 + (4 + 4 + 4 )+( 2 + 2 +2) +1 = 27 Not satisfied with eth place (5, 10, 10, 500, 1000, 550) => 500 + (50 + 100 + 50) +(5+10+10) + 1 = 726 satisfied with dth place (4, 7, 3, 84, 160, 117) => 84+ (28+21+12) + (4+7+3) +1 =160 satisfied with e th place.
Q3 : Group by with ROLL UP
Explanation : Roll up will Reduce the dimensions.
****So the ANSWER will be (4, 7, 3, 84, 160, 117) satisfied all the three queries based on the OLAP operations.