Grouping (MySQL)
Grouping, oftewel transponeerquery's, hebben betrekking op het samenvoegen van gegevens uit meerdere records. In MySQL is de optie group_concat vaak het eerste gereedschap om hierop los te laten, maar het heeft zijn beperkingen. Dit is niet hetzelfde als pivoting zoals in pivot tables: Bij pivoting wordt geaggregeerde data gegenereerd.
Een andere manier om dit uit te leggen:
Je hebt twee tabellen met een 1-op-∞-relatie. Je wilt alle records van de "1"-kant tonen, met alle bijbehorende records van de "∞"-kant samengevoegd in één veld.
Opwarmertje
select group_concat("aap", "noot", "mies"); aapnootmies
Separator
Met het keyword separator kun je gegevens uit meerdere records scheiden. Voorbeelden:
-- Output: aapnootmies - De separator wordt helaas niet toegepast: -- select group_concat("aap","nooi","mies" separator ", ") -- Output: Wél gescheiden: -- select sku, group_concat(brand, " ", orgbrush separator ", ") from orgbrush group by sku;
LET OP: De volgorde is van belang:
-- SEPARATOR moet NA eventuele sorteervolgorde: -- group_concat(distinct brush_tool.tool_sub_id order by brush_tool.tool_sub_id asc separator ", ") as tools_sub
GROUP BY
In bovenstaand voorbeeld heb je group by nodig om output per sku te krijgen:
select SKU, group_concat(" ",brand_lowercase, " ", kind_de_lowercase, " ", type) from tbl_tools group by sku -- Deze regel zorgt ervoor dat je per SKU een rij krijgt
- Zie [1] voor een aardige intro
- group by Kan meerdere argumenten verwerken. Zie [2] (onderaan de pagina).
Row ... was cut by GROUP_CONCAT()
GROUP_CONCAT() kent een stringlimiet waar ik nogal 's mee in botsing kom. Deze regel aan het begin van de query, lost dat op:
SET @@group_concat_max_len = 30000;
Een grondigere oplossing:
group_concat_max_len=30000
toevoegen aan het [mysqld]-gedeelte van my.ini
.
Eindelijk een echt voorbeeld
Het begint met deze query:
select distinct tbl_root.SKU, tbl_powertools_brand_kind_type.Brand from tbl_root join tbl_powertools_brand_kind_type on tbl_powertools_brand_kind_type.SKU = tbl_root.SKU where KindOfProduct='Koolborstels voor handgereedschap' order by SKU
Voorbeeld van de output:
'0100', 'BOSCH' '0101', 'BOSCH' '0102', 'BOSCH' '0103', 'BOSCH' '0104', 'BOSCH' '0104', 'CASALS'
En ik wil zoiets als
'0100', 'BOSCH' '0101', 'BOSCH' '0102', 'BOSCH' '0103', 'BOSCH' '0104', 'BOSCH','CASALS'
De oplossing:
select distinct tbl_root.SKU, group_concat(distinct tbl_powertools_brand_kind_type.Brand order by Brand) as brand -- Distinct nodig, anders eindeloze herhaling from tbl_root join tbl_powertools_brand_kind_type on tbl_powertools_brand_kind_type.SKU = tbl_root.SKU where KindOfProduct='Koolborstels voor handgereedschap' group by tbl_root.SKU -- Niet vergeten! Anders belandt alles op één regel order by SKU
Selecteer de hoogste prijs per SKU
In de lijst komen bepaalde SKU's meerdere keren voor. Ik wil alleen die met de hoogste prijs weten. Tjakka:
select distinct model, max(sell_price) as sell_price from kbo.uc_products group by model order by model asc;
Complexer voorbeeld
Dit speelde in januari 2017:
-- Iteratie 1 - Test met select-query -- ================================== -- -- select -- -- brush_tool_main.sku, -- brush_tool_main.tool_id_main, -- brush_tool_sub.tool_id_sub -- -- from brush_tool_main -- join brush_tool_sub -- -- on -- -- brush_tool_main.tool_id_main = brush_tool_sub.tool_id_main -- and -- brush_tool_main.sku = brush_tool_sub.sku -- -- order by brush_tool_main.tool_id_main asc, brush_tool_main.sku asc; -- Iteratie 2 -- ==================================== -- * Onduidelijk of alle subtools per main inderdaad dezelfde koolborstel gebruiken -- drop table if exists brush_tool_group; create table brush_tool_group select distinct brush_tool_main.sku, brush_tool_main.tool_id_main, group_concat(distinct brush_tool_sub.tool_id_sub) as subtools from brush_tool_main join brush_tool_sub on brush_tool_main.tool_id_main = brush_tool_sub.tool_id_main and brush_tool_main.sku = brush_tool_sub.sku group by brush_tool_main.sku, brush_tool_main.tool_id_main -- KUN JE group by GEBRUIKEN MET TWEE ARGUMENTEN? > Ja order by brush_tool_main.tool_id_main asc, brush_tool_main.sku asc;
Nog zo'n complex voorbeeld
Wat dit voorbeeld toont:
- Je kunt meerdere group_concats in één query hebben
- Separator-symbool mag met " of ' worden omsloten
- Sorteervolgorde vóór separator
drop table if exists subtools_per_subsku_tmp; create table subtools_per_subsku_tmp select distinct root_child_metabo.sku, group_concat(distinct brush_tool.tool_sub_id order by brush_tool.tool_sub_id asc separator ', ' ) as tools_sub_comma_separated, group_concat(distinct brush_tool.tool_sub_id order by brush_tool.tool_sub_id asc separator " <li> " ) as tools_sub_li_separated from root_child_metabo join brush_tool on brush_tool.tool_main_id = substring_index(root_child_metabo.tool_id,"_",2) where root_child_metabo.sku = ean_id group by root_child_metabo.sku order by sku asc;