Grouping (MySQL)
Grouping, oftewel transponeerquery's, hebben betrekking op het samenvoegen van gegevens uit meerdere records in één veld. 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 niet toegepast (waarom niet???) ########################################################################## select group_concat("aap", "noot", "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
Zoals meestal, begint het onschuldig:
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;
Performance
Probleem
In de zomer van 2019 stuitte ik op een performance-probleem. Queries zoals
update tools_per_sku_combined_tmp set tools_li_02 = ( select group_concat( distinct trim( concat ( '<li>', ifnull(concat(brand, ' ' ), ''), # brand ifnull(concat(tool_main_id, ' ' ), ''), # main-id ifnull(concat(' (', tool_sub_id, ')' ), '') # sub-id ) ) separator ',' ) from tool_combined_tmp where tools_per_sku_combined_tmp.sku = tool_combined_tmp.sku group by tools_per_sku_combined_tmp.sku );
namen voor 500 producten, al 22 seconde in beslag. Naarmate het om meer items de primaire tabel gingen, nam de performance meer dan evenredig af.
Oplossingen
- Het concat-gedeelte verhuisd naar de secundaire tabel (dus als een complete kolom). Dat verbeterde de performance met zo'n 47% (van 22s naar 12s)
- Distinct weggelaten: Dat werkte juist averechts
- Uit de secundaire tabel, de kolom waarop select group_concat wordt toegepast, voorzien van een index. Dat scheelde 8% (van 12s naar 11s).
De grouping-query werd daarmee
update tools_per_sku_combined_tmp set tools_li_02 = ( select group_concat(distinct li_brand_main_sub separator ',' ) from tool_combined_tmp where tools_per_sku_combined_tmp.sku = tool_combined_tmp.sku group by tools_per_sku_combined_tmp.sku );
De echte oplossing: Combined PK in de juiste volgorde!
In de secundaire tabel was de PK gedefineerd als primary key (tool_id, sku)
, terwijl de match op sku gaat. Toen ik dit omdraaide, verbeterde de performance van 13s naar 0,2s. Dankzij deze posting.
Zie ook
Bronnen
- https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/
- http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
- http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
Performance