{"id":538,"date":"2023-10-20T10:43:14","date_gmt":"2023-10-20T08:43:14","guid":{"rendered":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/?page_id=538"},"modified":"2025-06-13T10:45:17","modified_gmt":"2025-06-13T08:45:17","slug":"1-9-consultes-avancades-agrupacions","status":"publish","type":"page","link":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/1-9-consultes-avancades-agrupacions\/","title":{"rendered":"1.9. Consultes avan\u00e7ades: agrupacions"},"content":{"rendered":"<p>Mitjan\u00e7ant el desglossament de dades d\u2019una taula, en funci\u00f3 d\u2019algun camp concret, podem calcular estad\u00edstiques sobre cada categoria. L\u2019ordre <code>GROUP BY<\/code> permet realitzar agrupacions de les dades de les taules segons els criteris que establim, i \u00e9s possible combinar aquestes classificacions amb operadors d\u2019agregaci\u00f3 com <code>COUNT<\/code><strong>, \u00a0<\/strong><code>MAX<\/code><strong>, \u00a0<\/strong><code>MIN<\/code><strong>, \u00a0<\/strong><code>AVG<\/code><strong> o \u00a0<\/strong><code>SUM<\/code><strong>.<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"raw\" data-enlighter-theme=\"droide\">SELECT camp1, camp2,..., campn FROM taula GROUP BY atribut;<\/pre>\n<p>Per exemple, demanem les esp\u00e8cies presents a la nostra base de dades:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">mysql&gt; SELECT especie FROM gens GROUP BY especie;\r\n\r\n+-----------------+\r\n| especie         |\r\n+-----------------+\r\n| D. melanogaster |\r\n| H. sapiens      |\r\n+-----------------+\r\n2 rows in set (0.00 sec)<\/pre>\n<p>Posteriorment, podem comptar el nombre exacte d\u2019exemples de cada esp\u00e8cie:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">mysql&gt; SELECT especie,COUNT(*) FROM gens GROUP BY especie;\r\n\r\n+-----------------+----------+\r\n| especie         | COUNT(*) |\r\n+-----------------+----------+\r\n| D. melanogaster |       2  |\r\n| H. sapiens      |       2  |\r\n+-----------------+----------+\r\n2 rows in set (0.00 sec)<\/pre>\n<p>Ara obtenim les estad\u00edstiques b\u00e0siques sobre la longitud dels gens:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">mysql&gt; SELECT especie, cromosoma, inici, final, final-inici\r\n    -&gt; FROM gens;\r\n\r\n+-----------------+-----------+-----------+-----------+-------------+\r\n| especie         | cromosoma |   inici   |   final   | final-inici |\r\n+-----------------+-----------+-----------+-----------+-------------+\r\n| D. melanogaster | chr3R     |  20477248 |  20479098 |        1850 |\r\n| D. melanogaster | chr2L     |    476437 |    479046 |        2609 |\r\n| H. sapiens      | chr12     | 121416548 | 121440312 |       23764 |\r\n| H. sapiens      | chr8      | 128748314 | 128753678 |        5364 |\r\n+-----------------+-----------+-----------+-----------+-------------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql&gt; SELECT especie, AVG(final-inici), MIN(final-inici),\r\n    -&gt; MAX(final-inici) FROM gens GROUP BY especie;\r\n\r\n+-----------------+------------------+------------------+------------------+\r\n| especie         | AVG(final-inici) | MIN(final-inici) | MAX(final-inici) |\r\n+-----------------+------------------+------------------+------------------+\r\n| D. melanogaster |        2229.5000 |             1850 |             2609 |\r\n| H. sapiens      |       14564.0000 |             5364 |            23764 |\r\n+-----------------+------------------+------------------+------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Mitjan\u00e7ant el desglossament de dades d\u2019una taula, en funci\u00f3 d\u2019algun camp concret, podem calcular estad\u00edstiques sobre cada categoria. L\u2019ordre GROUP BY permet realitzar agrupacions de les dades de les taules segons els criteris que establim, i \u00e9s possible combinar aquestes classificacions amb operadors d\u2019agregaci\u00f3 com COUNT, \u00a0MAX, \u00a0MIN, \u00a0AVG o \u00a0SUM. SELECT camp1, camp2,&#8230;, campn [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":[],"acf":[],"_links":{"self":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/538"}],"collection":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/comments?post=538"}],"version-history":[{"count":10,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/538\/revisions"}],"predecessor-version":[{"id":951,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/538\/revisions\/951"}],"wp:attachment":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/media?parent=538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}