Grouping (MySQL)

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken

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.

Een 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;

Bronnen