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
################################################################

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

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.

Bronnen

Performance