Блог

Вот мой текущий запрос:

 SELECT DATEDIFF(created_at, '2020-07-01') DIV 6 period, 
       user_id FROM transactions
WHERE DATE(created_at) >= '2020-07-01' 
GROUP BY user_id, DATEDIFF(created_at, '2020-07-01') DIV 6
ORDER BY period
 

Он возвращает список пользователей, у которых была хотя бы одна транзакция за период (период === 6 дней). Вот упрощенное представление текущего выходного сигнала:

 // res_table
 -------- --------- 
| period | user_id |
 -------- --------- 
| 0      | 1111    |
| 0      | 2222    |
| 0      | 3333    |
| 1      | 7777    |
| 1      | 1111    |
| 2      | 2222    |
| 2      | 1111    |
| 2      | 8888    |
| 2      | 3333    |
 -------- --------- 
 

Теперь мне нужно знать, за какой период у скольких пользователей снова была хотя бы одна транзакция (с точки зрения маркетинга я пытаюсь представить коэффициент удержания с помощью когортной диаграммы). Поэтому вычисления должны выполняться в декартовом алгоритме; Например self-join ,!

Вот ожидаемый результат:

  --------- --------- ------------ 
| periodX | periodY | percentage |
 --------- --------- ------------ 
| 0       | 0       | 100%       | -- it means 3 users exist in period 0 and logically all of them exist in period 0. So 3/3=100%
| 0       | 1       | 33%        | -- It means 3 users exist in period 0, and just 1 of them exist in period 1. So  1/3=33%
| 0       | 2       | 66%        | -- It means 3 user exists in period 0, and just 2 of them exist in period 2. So 2/3=66%
| 1       | 1       | 100%       | -- it means 1 user (only #777, actually #111 is ignored because it's duplicated in pervious periods) exists in period 1 and logically it exists in period 1. So 1/1=100%
| 1       | 2       | 0%         |
| 2       | 2       | 100%       |
 --------- --------- ------------ 
 

Можно ли это сделать чисто с помощью MySQL?

Вы можете использовать оконные функции:

 SELECT first_period, period, COUNT(*),
       COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY first_period) as ratio
FROM (SELECT DATEDIFF(created_at, '2020-07-01') DIV 6 period, 
            user_id,
            MIN(MIN(DATEDIFF(created_at, '2020-07-01') DIV 6) OVER (PARTITION BY user_id)) as first_period
     FROM transactions
     WHERE DATE(created_at) >= '2020-07-01' 
     GROUP BY user_id, DATEDIFF(created_at, '2020-07-01') DIV 6
    ) u
GROUP BY first_period, period
ORDER BY first_period, period;
 

Это не включает пропущенные периоды. Это немного хитростей, потому что вам нужно перечислить их все:

 with periods as (
      select 0 as period union all
      select 1 as period union all
      select 2 as period
     )
select p1.period, p2.period, COUNT(u.user_id)
from periods p1 join
     periods p2
     on p1.period <= p2.period left join
     (SELECT DATEDIFF(created_at, '2020-07-01') DIV 6 period, 
             user_id,
             MIN(MIN(DATEDIFF(created_at, '2020-07-01') DIV 6) OVER (PARTITION BY user_id)) as first_period
      FROM transactions
      WHERE DATE(created_at) >= '2020-07-01' 
      GROUP BY user_id, DATEDIFF(created_at, '2020-07-01') DIV 6
     ) u
     ON p1.period = u.first_period AND p2.period = u.period
GROUP BY p1.period, p2.period;