{"id":12866,"date":"2022-10-02T05:45:37","date_gmt":"2022-10-02T00:15:37","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/"},"modified":"2022-10-02T05:45:37","modified_gmt":"2022-10-02T00:15:37","slug":"solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/","title":{"rendered":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-21987367\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"21987367\" data-parentid=\"21940392\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Thanks to the <code>MODEL<\/code> syntax feature <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/community.oracle.com\/message\/12278407#12278407\">hint from <em>GregV<\/em> in the Oracle forum<\/a> I could write this query really very short and precise without the need for <em>porno<\/em>. Cool!<\/p>\n<p>So to easily check out the difference with my sample code and an at least <em>10g<\/em> Oracle db you just need to modify the original script linked above the following way:<\/p>\n<pre><code>        \/**************************\n         * the original sample query base data\n         ***************************\/\n        ...  -- all content before the last select of the original example-SQL\n\n        \/**************************\n         * the original sample porno-query\n         ***************************\/\n\n        ,agg_porno as (\n            select\n                descr,\n\n                ...  -- all the porno-query details\n\n            from sum_data_lvl1\n            \/*\n             DESCR                              SUM        AGG_LVL SUM_ID\n             ---------------------------------- ---------- ------- ------\n             money available in 2013            33233235.3       1 MA\n             money spent in 2013                 4253235.3       1 MS\n             money bound to contracts in 2013     34333500       1 MB\n             money spent 2013 in % of available         12       2 MSP\n             money bound 2013 in % of available        103       2 MBP\n            *\/\n        )\n\n        \/**************************\n         * the new nice model-based query instead\n         ***************************\/\n\n        ,agg_model as (\n            select\n                descr,\n                trunc(s,1) as sum,\n                agg_lvl,\n                sum_id \n            from sum_data_lvl1\n            model\n                dimension by (sum_id)\n                measures (descr, sum as s, agg_lvl)\n                rules (\n                    s['MSP'] = s['MS'] \/ s['MA'] * 100,\n                    s['MBP'] = s['MB'] \/ s['MA'] * 100\n                )\n        )\n        \/*\n         DESCR                              SUM        AGG_LVL SUM_ID\n         ---------------------------------- ---------- ------- ------\n         money available in 2013            33233235.3       1 MA\n         money spent in 2013                 4253235.3       1 MS\n         money bound to contracts in 2013     34333500       1 MB\n         money spent 2013 in % of available       12.7       2 MSP\n         money bound 2013 in % of available      103.3       2 MBP\n         *\/\n\nselect * from agg_porno where 1=0  -- change to 1=1 to see these results\nunion all select * from agg_model where 1=1  -- change to 1=0 to hide these results\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Thanks to the MODEL syntax feature hint from GregV in the Oracle forum I could write this query really very short and precise without the need for porno. Cool! So to easily check out the difference with my sample code and an at least 10g Oracle db you just need to modify the original &#8230; <a title=\"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/\" aria-label=\"More on [Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[3482,3483,838],"class_list":["post-12866","post","type-post","status-publish","format-standard","hentry","category-solved","tag-aggregate-functions","tag-analytic-functions","tag-oracle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Thanks to the MODEL syntax feature hint from GregV in the Oracle forum I could write this query really very short and precise without the need for porno. Cool! So to easily check out the difference with my sample code and an at least 10g Oracle db you just need to modify the original ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-02T00:15:37+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)\",\"datePublished\":\"2022-10-02T00:15:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/\"},\"wordCount\":89,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"aggregate-functions\",\"analytic-functions\",\"oracle\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/\",\"name\":\"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-10-02T00:15:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb","og_description":"[ad_1] Thanks to the MODEL syntax feature hint from GregV in the Oracle forum I could write this query really very short and precise without the need for porno. Cool! So to easily check out the difference with my sample code and an at least 10g Oracle db you just need to modify the original ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/","og_site_name":"JassWeb","article_published_time":"2022-10-02T00:15:37+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)","datePublished":"2022-10-02T00:15:37+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/"},"wordCount":89,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["aggregate-functions","analytic-functions","oracle"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/","url":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/","name":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle) - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-02T00:15:37+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-best-calculate-n-level-aggregation-data-based-on-n-1-level-data-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How to best calculate n-level aggregation data based on (n-1)-level data (Oracle)"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777613206","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/12866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=12866"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/12866\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=12866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=12866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=12866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}