MySql wraps multiple rows per column - most optimized way for big data?
I want to wrap multiple rows per column.
This is my data table (about 20 mil.rows)
PHONE SERVICE
0000000 service1
0000000 service2
0000000 service3
1111111 service1
1111111 service4
2222222 service5
and I would like to get the following output:
PHONE SC1 SC2 SC3 SC4 SC5
0000000 service1 service2 service3 NULL NULL
1111111 service1 service4 NULL NULL NULL
2222222 service5 NULL NULL NULL NULL
etc..
Does anyone know how to do this faster (about 20 million entries)? Many thanks!
+3
source to share
2 answers
This should work well, make sure you have an index on phone
.
SELECT phone,
SUBSTRING_INDEX(services, ',', 1) SC1,
if(service_count >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 2), ',', -1), NULL) SC2,
if(service_count >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 3), ',', -1), NULL) SC3,
if(service_count >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 4), ',', -1), NULL) SC4,
if(service_count >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 5), ',', -1), NULL) SC5
FROM (SELECT phone, GROUP_CONCAT(service) AS services, COUNT(*) as service_count
FROM phones
GROUP BY phone) AS x
+2
source to share
You can use variables for the service number within each group and use conditional aggregation to rotate rows into columns.
select phone,
max(case when rn = 1 then service end) sc1,
max(case when rn = 2 then service end) sc2,
max(case when rn = 3 then service end) sc3,
max(case when rn = 4 then service end) sc4,
max(case when rn = 5 then service end) sc5
from (
select phone,service,
@rowNum := if(@prevPhone = phone,@rowNum+1,1) rn,
@prevPhone := phone
from mytable
cross join (select @prevPhone := null, @rowNum := 1) c
order by phone, service
) t1 group by phone
0
source to share