{"id":19806,"date":"2024-02-29T18:21:03","date_gmt":"2024-02-29T16:21:03","guid":{"rendered":"https:\/\/www.dase-analytics.com\/blog\/?p=19806\/"},"modified":"2024-03-05T08:34:48","modified_gmt":"2024-03-05T06:34:48","slug":"funkcia-group-by-v-bigquery","status":"publish","type":"post","link":"https:\/\/www.dase-analytics.com\/blog\/sk\/funkcia-group-by-v-bigquery\/","title":{"rendered":"Funkcia GROUP BY v BigQuery"},"content":{"rendered":"

Ak pracujete s BigQuery, SQL funkciu GROUP BY<\/strong> v\u00e1m netreba podrobnej\u0161ie predstavova\u0165. Pri anal\u00fdze d\u00e1t patr\u00ed medzi jednu z najpou\u017e\u00edvanej\u0161\u00edch. Funguje tak, \u017ee v ur\u010den\u00fdch st\u013apcoch zoskupuje riadky, ktor\u00e9 maj\u00fa rovnak\u00e9 hodnoty. Na tieto riadky aplikuje agregovan\u00e9 funkcie ako s\u00fa SUM, AVG, COUNT<\/strong> a \u010fal\u0161ie.<\/p>\n

\u010co ste mo\u017eno nevedeli, funkcia GROUP BY m\u00e1 viacero vari\u00e1nt, ktor\u00e9 roz\u0161iruj\u00fa jej mo\u017enosti. Pozrieme sa na ne v nasleduj\u00facich riadkoch.<\/p>\n

GROUP BY groupable items<\/strong><\/h3>\n

Alebo teda zoskupovanie pod\u013ea polo\u017eiek prestavuje jednoduch\u00fd GROUP BY, ako ho pou\u017e\u00edvame zvy\u010dajne. Dopyt zdru\u017e\u00ed riadky v tabu\u013eke, ktor\u00e9 maj\u00fa spolo\u010dn\u00e9 hodnoty pre ur\u010dit\u00e9 st\u013apce.<\/p>\n

\"group<\/a><\/p>\n

\"group<\/a><\/p>\n

GROUP BY GROUPING SETS<\/strong><\/h3>\n

Je u\u017eito\u010dn\u00fd, ak chcete agregovan\u00e9 hodnoty pre r\u00f4zne st\u013apce, ale nechcete vykon\u00e1va\u0165 viacero samostatn\u00fdch dopytov. V\u00fdsledky sa zobrazia v jednej tabu\u013eke, \u010do zjednodu\u0161uje anal\u00fdzu \u00fadajov.<\/p>\n

Napr\u00edklad dopyt:<\/strong><\/p>\n

GROUP BY x UNION ALL GROUP BY y UNION ALL GROUP BY z<\/p>\n

m\u00f4\u017eete nahradi\u0165 jednoduch\u00fdm<\/p>\n

GROUPING SETS(x,y,z)<\/p>\n

Poh\u013ead na rovnak\u00e9 d\u00e1ta ako v prvom pr\u00edklade, ale z inej perspekt\u00edvy.<\/p>\n

\"group<\/a><\/p>\n

\"group<\/a><\/p>\n

* null hodnoty znamenaj\u00fa, \u017ee dan\u00fd st\u013apec sa pri agreg\u00e1cii nepou\u017eil. Neplat\u00ed to v\u0161ak pre prv\u00fd riadok. D\u00e1ta nie s\u00fa vy\u010disten\u00e9, st\u013apec medium<\/em> obsahoval hodnotu null<\/em>.<\/p>\n

GROUP BY ROLLUP a GROUP BY CUBE<\/strong><\/h3>\n

Maj\u00fa podobn\u00fa funkcionalitu. Vytv\u00e1raj\u00fa medzis\u00fa\u010dty pre ka\u017ed\u00fa skupinu aj podskupinu \u00fadajov. To je ide\u00e1lne pre hierarchick\u00fa agreg\u00e1ciu d\u00e1t. Rozdiel je v tom, \u017ee GROUP BY CUBE generuje riadky medzis\u00fa\u010dtu pre v\u0161etky permut\u00e1cie zoskupen\u00ed, \u010do sa hod\u00ed pre komplexn\u00e9 anal\u00fdzy, odha\u013eovanie vz\u0165ahov v r\u00f4znych dimenzi\u00e1ch \u00fadajov a hlb\u0161ie pochopenie d\u00e1t.<\/p>\n

Pr\u00edklad sme zjednodu\u0161ili, aby bol v\u00fdstup dopytu lep\u0161ie \u010ditate\u013en\u00fd.<\/p>\n

\"group<\/a><\/p>\n

\"group<\/a><\/p>\n

* riadky s null hodnotami ozna\u010duj\u00fa medzis\u00fa\u010dty. Prv\u00fd riadok je celkov\u00fd s\u00fa\u010det.<\/p>\n

Ver\u00edme, \u017ee v\u00e1s tento stru\u010dn\u00fd preh\u013ead mo\u017enost\u00ed GROUP BY funkcie in\u0161piroval presk\u00fama\u0165 ju podrobnej\u0161ie.<\/strong> Kompletn\u00fa dokument\u00e1ciu m\u00f4\u017eete n\u00e1js\u0165 na tomto linku<\/a>.<\/p>\n

Budeme ve\u013emi radi, ak sa s nami podel\u00edte o to, ako vyu\u017e\u00edvate funkciu GROUP BY vy a nap\u00ed\u0161ete n\u00e1m va\u0161e tipy a triky. ✌<\/p>\n

* UPDATE: V BigQuery m\u00e1te mo\u017enos\u0165 vyu\u017ei\u0165 nov\u00fa klauzulu, v ktorej namiesto GROUP BY 1,2,3,…n sta\u010d\u00ed nap\u00edsa\u0165 GROUP BY ALL.<\/p>\n","protected":false},"excerpt":{"rendered":"

Ak pracujete s BigQuery, SQL funkciu GROUP BY v\u00e1m netreba podrobnej\u0161ie predstavova\u0165. Pri anal\u00fdze d\u00e1t patr\u00ed medzi jednu…<\/p>\n","protected":false},"author":78,"featured_media":19810,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[639,200],"tags":[673,958],"_links":{"self":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/19806"}],"collection":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/users\/78"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/comments?post=19806"}],"version-history":[{"count":7,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/19806\/revisions"}],"predecessor-version":[{"id":19815,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/19806\/revisions\/19815"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/media\/19810"}],"wp:attachment":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/media?parent=19806"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/categories?post=19806"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/tags?post=19806"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}