Как посчитать рекурсивно кол-во рефералов?

Question

[Дмитрий Тарасов]

Всем привет!

Mysql 8

Задача такая: посчитать кол-во не всего рефералов, а кол-во рефералов у юзера на каждом уровне, до 3 уровня.

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

CREATE INDEX fk_tree_tree ON tb_users (id_referer);

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

Сделал вот такой запрос

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

Сейчас я получаю такой вывод

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

Мне надо получить вот такой ответ

id amount_ref_lvl_1 amount_ref_lvl_2 amount_ref_lvl_3
1 333 400 5999

То есть просто вывести кол-во рефералов у пользователя на каждом уровне.

В конце меняю select * from cte
на

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

Правильно делаю ? Или что-то можно проще и лучше ?

Comments

[Максим]

1. не видно данных что бы сказать правильно или нет, можно лучше или нельзя ...
2. не понятно что такое id и как Вы будете с ней работать (группировать)
3. если это MySQL можно использовать IF, запись будет короче, но case универсален
4. amount часто используется для суммы, а count для кол-ва

[Дмитрий Тарасов]

Максим,
1. Данные тут
2. id это идентификатор пользователя в таблице.
3. mysql
4. Хорошо, буду иметь ввиду. Но нужно кол-во.

Answer 1

[Максим]

Я бы писал вот так:

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

Обратите внимание на id_referer is null как точка входа и первый левел
main_id - как id первого уровня
Ну в общем разберетесь, я надеюсь ;-)

Comments

[Дмитрий Тарасов]

Максим,
1. Не совсем понятно как в вашем запросе посчитать рефералов для одного конкретно юзера.
2. id_referer is null это не правильно, потому что я хочу посчитать даже для того человек у которого id_referer is not null.
3. cnt_lvl1 выводит всегда 1, cnt_lvl2 считает кол-во рефералов 1 уровня, а cnt_lvl3 2 уровня.

[Максим]

Дмитрий Тарасов,
1. Первый запрос нужно делать по id или чемуто более четкому ... если Вы не считаете его первым уровнем, то поставте его нулевым. Но это точка отсчета и с него берется имя и начало цепочки. Вот Вам вариант с именем и левелом 0
2. Почитайте про рекурсию, id_referer is null это точка отсчета, в текущем примере она id = 1, но это ограничивает запрос до одной цепочки. Если же поставить id_referer is null то запрос к таблице cte будет выводить все цепочки стартонувшие из полей id_referer is null.
3. Это Ваше право решать как происходит номерация уровней, ведь это Вы архитектор ;-)

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

P.S. если подойдет, поменяйте старт с id = 1 на id_referer is null и посмотрите что выйдет

[Дмитрий Тарасов]

Максим,
c where id = 1 считает правильно для одного юзера, но если я заменяю на id_referer is null, то юзеров с id_referer is not null там нет, но я тоже хочу чтобы они были.

Я понимаю что на что-то надо опереться в запросе, так скажем конечный узел у которого id_referer is null, но а если такого у него не null, а его уровни рефералов тоже надо посчитать.

Просто это не логично, с where id = 1 считает рефералов для юзера с id = 1, а если id_referer is null ставлю то этого юзера там нет, потому как у него id_referer is not null.

Еще у меня есть такой вот запрос.

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`

Делаю EXPLAIN format=json

Он выполняется гораздно быстрее, но read_cost у него "query_cost": "111412.05", а у вашего "query_cost": "5.65".
И на t5 join таблица, вообще пишет "data_read_per_join": "3G" как эти цифры понимать не совсем понятно.

А на cte таблицы "data_read_per_join": "111K"

Ну то есть у меня какая-то не состыковка, ваш запрос быстро выполняется но медленнее чуть и при это стоимость запроса невероятна низка, а мой left join'нах стоит дороже, но выполняется чуть быстрее.
И какой в данном случае использовать вариант лучше ?

[Максим]

Дмитрий Тарасов,
У моего условие where lvl <= 3, а если только до 3 то надо where lvl < 3
А по скорости, надо на данные смотреть, мой универсален и лвл у него в переменной, Ваш рассматривает часный случай.
В общем оптимизация это отдельная скилуха и наlо видеть, а лучше понимать, данные =)