{"id":3207,"date":"2019-12-31T11:01:04","date_gmt":"2019-12-31T10:01:04","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3207"},"modified":"2019-12-31T11:01:04","modified_gmt":"2019-12-31T10:01:04","slug":"cubes-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3207","title":{"rendered":"Cubes in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The issue about roll ups in Oracle is that roll up only calculate sub totals in one direction. So if you have a country, city combination, subtotals are calculated per city in a country, but not in a country per city.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In case of a hierarchy, this is ok. One is only interested in having the subtotals in a certain natural order. First totals per city, then totals per country and finally totals per region.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It is different if not such natural order exists. Think of a combination of department and location. A department may be spread over several locations and a location may have several departments. One could be interested to have to subtotals per department (aggregating over locations) and subtotals per location (aggregating over departments). <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For that situation, we have the concept of a cube.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select department_id ,job_id, count(employee_id) emp_count \nFROM\nemployees\ngroup by cube (department_id ,job_id) ;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">So instead of &#8220;rollup&#8221;, the keyword &#8220;cube&#8221; is used.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Like the rollup, we may add a grouping clause to indicate how the subtotals are calculated.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select  department_id ,  job_id , count(*) ,\n GROUPING ( department_id ) g1,\n GROUPING ( job_id ) g2\n from employees\n group by  cube( department_id ,  job_id );<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">or<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select  department_id ,  job_id , count(*) ,\nGROUPING_id (department_id,job_id) group_id\nfrom employees\ngroup by  cube( department_id ,  job_id );<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">or<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select  department_id ,  job_id , count(*) ,\nGROUPING_id (department_id,job_id) group_id\nfrom employees\ngroup by\nGROUPING SETS  ((department_id,job_id ) , () );<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note that the latter SQL does not contain a cube clause. Instead, the GROUPING SETS is used. This allows us to explicitly mention all combinations for which a sub total is required. Here, the grand total is required (therefore ()), with the subtotals on low level (department_id, job_id).<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/01\/Capture-17-1-17.png\" alt=\"\" class=\"wp-image-3218\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>The issue about roll ups in Oracle is that roll up only calculate sub totals in one direction. So if you have a country, city combination, subtotals are calculated per city in a country, but not in a country per city. In case of a hierarchy, this is ok. One is only interested in having [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3207","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3207"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3207\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3207"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}