{"id":310,"date":"2015-11-20T16:46:06","date_gmt":"2015-11-20T16:46:06","guid":{"rendered":"http:\/\/192.168.200.82\/?page_id=310"},"modified":"2020-07-13T19:25:32","modified_gmt":"2020-07-13T19:25:32","slug":"sql-basics","status":"publish","type":"page","link":"https:\/\/help.bwtech.com\/?page_id=310","title":{"rendered":"SQL Basics"},"content":{"rendered":"<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">Contents<\/p><ul class=\"toc_list\"><li><a href=\"#SQL_BASICS\"><span class=\"toc_number toc_depth_1\">1<\/span> SQL BASICS<\/a><ul><li><a href=\"#OVERVIEW\"><span class=\"toc_number toc_depth_2\">1.1<\/span> OVERVIEW<\/a><\/li><li><a href=\"#AGGREGATION_FUNCTIONS\"><span class=\"toc_number toc_depth_2\">1.2<\/span> AGGREGATION FUNCTIONS<\/a><ul><li><a href=\"#Aggregation_functions_and_KPI_formulas\"><span class=\"toc_number toc_depth_3\">1.2.1<\/span> Aggregation functions and KPI formulas<\/a><\/li><li><a href=\"#KPI_calculations_for_different_elements\"><span class=\"toc_number toc_depth_3\">1.2.2<\/span> KPI calculations for different elements<\/a><\/li><\/ul><\/li><li><a href=\"#REFERENCES\"><span class=\"toc_number toc_depth_2\">1.3<\/span> REFERENCES<\/a><\/li><\/ul><\/li><\/ul><\/div>\n<h1 style=\"text-align: center;\"><span id=\"SQL_BASICS\"><strong>SQL BASICS<\/strong><\/span><\/h1>\n<h2><span id=\"OVERVIEW\"><strong>OVERVIEW<\/strong><\/span><\/h2>\n<p style=\"text-align: justify;\">NetChart uses the MySQL database (DB) which uses the SQL language to query data from the DB. You must observe the syntax of formulas to understand the result returned by the database.<\/p>\n<h2 style=\"text-align: justify;\"><span id=\"AGGREGATION_FUNCTIONS\"><strong>AGGREGATION FUNCTIONS<\/strong><\/span><\/h2>\n<p style=\"text-align: justify;\">Aggregation functions are functions that work in a group. A group is defined by the SQL GROUP BY clause. In the example below, we would have one group if we group by rnc_name and two groups if we group by cell_name. One line per group is returned per MySQL.<\/p>\n<p><a href=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_1_SQL_Basics.jpg\" rel=\"attachment wp-att-367\"><img loading=\"lazy\" class=\"size-full wp-image-367 aligncenter\" src=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_1_SQL_Basics.jpg\" alt=\"[SCM]actwin,0,0,0,0;WINWORD 23\/11\/2015 , 15:42:32 SQL Basics.docx - Microsoft Word\" width=\"631\" height=\"173\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">If we group by RNC_NAME we would have one line with the following results, according to each aggregation function:<\/p>\n<ul style=\"text-align: justify;\">\n<li><strong>sum<\/strong>(counter_1) = 120: return the sum of all values of column counter_1<\/li>\n<li><strong>avg<\/strong>(counter_1) = 30: return the average of all values of column counter_1<\/li>\n<li><strong>min<\/strong>(counter_1) = 20: return the minimum value of all values of column counter_1<\/li>\n<li><strong>max<\/strong>(counter_1) = 40: return the maximum value of all values of column counter_1<\/li>\n<li><strong>count<\/strong>(counter_1) = 4: return the number of values, or the number of lines, of column counter_1<\/li>\n<li><strong>count<\/strong>(<strong>distinct<\/strong> counter_1) = 3: return the number of distinct values of column counter_1<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">If no aggregation function is specified, then querying the database for counter_1 will return a random value. In this case, if we query only for counter_1 in the GROUP BY rnc_name we will have a result like:<\/p>\n<ul style=\"text-align: justify;\">\n<li><strong>counter_1<\/strong> = random value = 20 or 30 or 40: return a random value corresponding to one of the values in the group.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">If we GROUP BY cell_name we would have two lines, one per group, with the following results, according to each aggregation function:<\/p>\n<p><a href=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_2_SQL_Basics.jpg\" rel=\"attachment wp-att-368\"><img loading=\"lazy\" class=\"size-full wp-image-368 aligncenter\" src=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_2_SQL_Basics.jpg\" alt=\"SQL_2\" width=\"647\" height=\"121\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #ff0000;\"><strong>NOTE:<\/strong><\/span> not using an aggregation function will result in a random result.<\/p>\n<p style=\"text-align: justify;\">\n<h3 style=\"text-align: justify;\"><span id=\"Aggregation_functions_and_KPI_formulas\"><span style=\"color: #808080;\"><strong>Aggregation functions and KPI formulas<\/strong><\/span><\/span><\/h3>\n<p style=\"text-align: justify;\">The aggregation functions affect directly the KPI results. Based on it you can have a sum or an average based on the element selected.<\/p>\n<p style=\"text-align: justify;\">Suppose you have the &#8220;counter_table&#8221; below and that we are grouping by rnc_name:<\/p>\n<p><a href=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_3_SQL_Basics.jpg\" rel=\"attachment wp-att-369\"><img loading=\"lazy\" class=\"size-full wp-image-369 aligncenter\" src=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_3_SQL_Basics.jpg\" alt=\"IF\" width=\"812\" height=\"232\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Now consider the result of the following formulas for throughput:<\/p>\n<p style=\"text-align: justify;\"><strong><span style=\"color: #0000ff;\">Formula 1:<\/span> sum<\/strong>(traffic_erl) \/ <strong>sum<\/strong>(measurement_duration) = (5 + 6 + 7 + 8 + 9 + 10) \/ (60 + 60 + 60 + 55 + 54 + 60) = 45 \/ 349 = 0.13 Erl\/s<\/p>\n<p style=\"text-align: justify;\"><strong><span style=\"color: #0000ff;\">Formula 2:<\/span> sum<\/strong>(traffic_erl) \/ <strong>avg<\/strong>(measurement_duration)\u00a0 = (5 + 6 + 7 + 8 + 9 + 10) \/ ((60 + 60 + 60 + 55 + 54 + 60) \/ 6) = 45 \/ 58.16 = 0.77 Erl\/s<\/p>\n<p style=\"text-align: justify;\">The formula 1 will give the average throughput of all cells of the RNC.<\/p>\n<p style=\"text-align: justify;\">The formula 2 will give the total throughput, or the sum of throughputs, of all cells of the RNC, which can mean the total throughput handled by the RNC at the moment.<\/p>\n<p style=\"text-align: justify;\">Both formulas 1 and 2 are correct, although they represent distinct quantities, distinct KPIs.<\/p>\n<p style=\"text-align: justify;\">\n<h3 style=\"text-align: justify;\"><span id=\"KPI_calculations_for_different_elements\"><strong><span style=\"color: #808080;\">KPI calculations for different elements<\/span><\/strong><\/span><\/h3>\n<p style=\"text-align: justify;\">The query results made to the database will always return one line per GROUP selected. Suppose we have the formula below which represents the percentage of dropped calls in an element:<\/p>\n<ul style=\"text-align: justify;\">\n<li>100 * sum(call_drops) \/ sum(call_attempts)<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Considering the counter_table below we will have the following results, depending on the used aggregation:<\/p>\n<figure id=\"attachment_370\" aria-describedby=\"caption-attachment-370\" style=\"width: 786px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_4_SQL_Basics.jpg\" rel=\"attachment wp-att-370\"><img loading=\"lazy\" class=\"size-full wp-image-370\" src=\"https:\/\/help.bwtech.com\/wp-content\/uploads\/2016\/01\/Tabela_4_SQL_Basics.jpg\" alt=\"[SCM]actwin,0,0,0,0;WINWORD 23\/11\/2015 , 15:46:00 SQL Basics.docx - Microsoft Word\" width=\"786\" height=\"295\" \/><\/a><figcaption id=\"caption-attachment-370\" class=\"wp-caption-text\">For RNC level we will have the percentage of a drop considering all attempts and drops of that RNC, as shown in the table above. The same concept applies to all elements, observing the used formula.<\/figcaption><\/figure>\n<h2><span id=\"REFERENCES\"><strong>REFERENCES<\/strong><\/span><\/h2>\n<p style=\"text-align: justify;\">\u00a0The online help of MySQL: http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/index.html<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Contents1 SQL BASICS1.1 OVERVIEW1.2 AGGREGATION FUNCTIONS1.2.1 Aggregation functions and KPI formulas1.2.2 KPI calculations for different elements1.3 REFERENCES SQL BASICS OVERVIEW NetChart uses the MySQL database (DB) which uses the SQL language to query data from the DB. You must observe the syntax of formulas to understand the result returned by the database. AGGREGATION FUNCTIONS Aggregation [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":106,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/pages\/310"}],"collection":[{"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/help.bwtech.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=310"}],"version-history":[{"count":8,"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/pages\/310\/revisions"}],"predecessor-version":[{"id":1596,"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/pages\/310\/revisions\/1596"}],"up":[{"embeddable":true,"href":"https:\/\/help.bwtech.com\/index.php?rest_route=\/wp\/v2\/pages\/106"}],"wp:attachment":[{"href":"https:\/\/help.bwtech.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}