How to recursively count number of referrals?

Hello!

Mysql 8

Task: count number not only referrals and number of referrals the user at each level, up to level 3. 

СREATE TABLE tb_users (
id int unsigned not null primary key,
username varchar(20),
id_referer unsigned int
);

CREATE INDEX fk_tree_tree ON tb_users (id_referer);

ALTER TABLE ADD CONSTRAINT al_tree fk_tree_tree
 FOREIGN KEY (id_referer) REFERENCES tb_users (id) ON UPDATE CASCADE ON DELETE CASCADE;

Made here such request 

with recursive cte (id, id_referer, lvl) as (
 select id,
id_referer,
 1 lvl
 from tb_users
 where id_referer = 1
 union DISTINCT
 select p.id
p.id_referer,
 lvl + 1
 from tb_users p
 inner join cte
 on p.id_referer = cte.id
 where lvl < 3
)
select * from cte


Now I get this output

id id_referer lvl
2 1 1
3 1 1
.......

I need to get an answer here

id amount_ref_lvl_1 amount_ref_lvl_2 amount_ref_lvl_3
400 1 333 5999

That is just to display the number of referrals the user has at each level.

At the end change select * from cte
on 
select COUNT(CASE WHEN `lvl` = 1 THEN 1 END) `lvl_1`, COUNT(CASE WHEN `lvl` = 2 THEN 1 END) `lvl_2`, COUNT(CASE WHEN `lvl` = 3 THEN 1 END) `lvl_3` from cte


Doing the right thing ? Or something easier and better ?
April 19th 20 at 12:24

Related questions

More answers about "How to recursively count number of referrals?"
1 answer
April 19th 20 at 12:26
I would write it like this:
with recursive cte (main_id, id, id_referer, lvl) as (
 select id, 
id
id_referer,
 1 lvl
 from tb_users
 where id_referer is null
 union all
 select main_id, 
p.id
p.id_referer,
 lvl + 1
 from tb_users p
 inner join cte
 on p.id_referer = cte.id
 where lvl < 3
)
select main_id, 
 count(case lvl when 1 then 1 end) cnt_lvl1,
 count(case lvl when 2 then 1 end) cnt_lvl2,
 count(case lvl when 3 then 1 end) cnt_lvl3
 from cte
 group by main_id


Note id_referer is null as the entry point and the first level
main_id - id as the first level
Well, in General, will understand, I hope ;-)
@naomie_Metz,
1. Not clear in your query to count referrals specifically for one user.
2. id_referer is null is not correct, because I want to count even the man who id_referer is not null.
3. cnt_lvl1 displays always 1, cnt_lvl2 count the number of referrals level 1 and level 2 cnt_lvl3. - alene commented on April 19th 20 at 12:29
@'alene,
1. The first query you need to do by id or chemuto more clear ... if You do not think it is the first level, then drop it to zero. But it's a starting point and he is taken to the name and the beginning of a chain. Here's an option with the given name and level 0
2. Read about recursion, id_referer is null this is the starting point, in this example it is id = 1, but this limits the query to a single chain. If we put id_referer is null then query the cte table will display all the chains startmovie id_referer of the fields is null.
3. It is Your right to decide how the numbering of the levels, because You're the architect ;-)
with recursive cte (main_username, id, id_referer, lvl) as (
 select username, 
id
id_referer,
 0 lvl
 from tb_users
 where id = 1
 union all
 select main_username, 
p.id
p.id_referer,
 lvl + 1
 from tb_users p
 inner join cte
 on p.id_referer = cte.id
 where lvl <= 3
)
select main_username, 
 count(case lvl when 1 then 1 end) cnt_lvl1,
 count(case lvl when 2 then 1 end) cnt_lvl2,
 count(case lvl when 3 then 1 end) cnt_lvl3
 from cte
 group by main_username

PS if you come, change the start id = 1 for id_referer is null and see what happens - naomie_Metz commented on April 19th 20 at 12:32
@naomie_Metz,
c where id = 1 considers correctly for one user, but if I replace id_referer is null, users with id_referer is not null there, but I also want them to be.

I understand that it is necessary to rely in the request, so for example the end node which id_referer is null, but if so it is not null, and the levels of referrals also needs to be calculated.

It's just not logical, from a where id = 1 considers referrals for the user with id = 1 and if id_referer is null bet that this user is not there, because he id_referer is not null.

I have such a request.
SELECT `tb1`.`id`, COUNT(`t3`.`id`) as `one_referals`, count( `t4`.`id`) as `two_referals`, count( `t5`.`id`) as `three_referals`
FROM `tb_users` `tb1`
LEFT JOIN `tb_users` `t3` ON `t3`.`id_referer` = `tb1`.`id`
LEFT JOIN `tb_users` `t4` ON `t4`.`id_referer` = `t3`.`id`
LEFT JOIN `tb_users` `t5` ON `t5`.`id_referer` = `t4`.`id`
WHERE `tb1`.`id` = 1
GROUP BY `tb1`.`id`


Do EXPLAIN format=json

It runs much faster, but he read_cost "query_cost": "111412.05", and your "query_cost": "5.65".
And t5 in the join table, it says "data_read_per_join": "3G" as these figures to understand is not entirely clear.

And on cte table "data_read_per_join": "111K"

I mean I have some not matchmaking, your request is done quickly but a little slower and the query cost is incredible low and my left joins tries is more expensive, but runs a little faster.
And what in this case to use a better option ? - alene commented on April 19th 20 at 12:35
@'alene,
My where condition lvl <= 3, but if only 3 then you have to lvl where < 3
As for speed, it is necessary for the data to look at, my versatile and LVL it in a variable, consider Your private event.
In General, optimization is a separate skyluke and наlо to see and better understand the data =) - naomie_Metz commented on April 19th 20 at 12:38

Find more questions by tags MySQL