Блог

У меня есть таблицы, в которых хранится тип учетной записи, которую пользователи создают для продукта. Есть 3 возможных пути.

1. Они могут либо создать пробную учетную запись, затем создать полную учетную запись.
2. Они могут создать только полную учетную запись.
3. Они могут создать пробную учетную запись только.

В первом случае будет создано 2 записи, в то время как в других случаях будет создана 1 запись. пример исходной таблицы ниже:

  ------ -------------- 
| user | account_type |
 ------ -------------- 
| 1    | trial        |
 ------ -------------- 
| 1    | full         |
 ------ -------------- 
| 2    | full         |
 ------ -------------- 
| 3    | full         |
 ------ -------------- 
| 4    | trial        |
 ------ -------------- 
| 4    | full         |
 ------ -------------- 
| 5    | trial        |
 ------ -------------- 
| 5    | full         |
 ------ -------------- 
| 6    | trial        |
 ------ -------------- 
| 7    | full         |
 ------ -------------- 
  

Я хотел бы подсчитать количество пользователей, которые создали пробную учетную запись, за которой следует полная учетная запись, а также тех, кто сразу же создал полную учетную запись. предполагаемая итоговая таблица ниже:

  ------------------- ------------------------ 
| full_account_only | trial_and_full_account |
 ------------------- ------------------------ 
| 124               | 256                    |
 ------------------- ------------------------ 
  

Мой запрос до сих пор :

 select sum(case
             when account_type_cnt = 1 then
              1
           end) as "full_account_only",
       sum(case
             when account_type_cnt = 2 then
              1
           end) as "trial_and_full_account "
  from (select user, count(distinct(account_type)) as account_type_cnt
          from tbl
         group by user)
  

но я понимаю, что это не будет соответствовать критериям подсчета пользователей, которые создали только полную учетную запись. Может кто-нибудь любезно помочь решить эту проблему?

Вы можете использовать два уровня агрегирования. Я бы разместил подсчеты в отдельных строках, вот так:

 select num_trial, num_full, count(*) as num_users
from (select user,
             sum(case when account_type = 'trial' then 1 else 0 end) as num_trial,
             sum(case when account_type = 'full' then 1 else 0 end) as num_full
      from t
      group by user
     ) u
group by num_trial, num_full;
  

Вы можете легко изменить это, чтобы вместо этого поместить значения в столбцы:

 select sum(case when num_trial > 0 and num_full = 0 then 1 else 0 end) as only_trial,
       sum(case when num_full > 0 and num_trial = 0 then 1 else 0 end) as only_full,
       sum(case when num_full > 0 and num_trial > 0 then 1 else 0 end) as both
from (select user,
             sum(case when account_type = 'trial' then 1 else 0 end) as num_trial,
             sum(case when account_type = 'full' then 1 else 0 end) as num_full
      from t
      group by user
     ) u
group by num_trial, num_full;
  

Аналогичный подход к Gordon, но немного другая реализация, отчасти потому, что я предпочитаю использовать count при подсчете, а не sum. Вы можете получить подсчеты в виде флагов, для которых типы учетных записей существуют для каждого пользователя:

 select usr,
  count(case when account_type = 'trial' then account_type end) as trial_cnt,
  count(case when account_type = 'full' then account_type end) as full_cnt
from tbl
group by usr
  

 USR | TRIAL_CNT | FULL_CNT
--: | --------: | -------:
  6 |         1 |        0
  2 |         0 |        1
  4 |         1 |        1
  3 |         0 |        1
  5 |         1 |        1
  7 |         0 |        1
  1 |         1 |        1
  

а затем используйте это как внутренний запрос с другим уровнем условной агрегации:

 select
  count(case when trial_cnt > 0 then usr end) as trial,
  count(case when full_cnt > 0 then usr end) as full,
  count(case when full_cnt > 0 and trial_cnt = 0 then usr end) as full_only,
  count(case when full_cnt > 0 and trial_cnt > 0 then usr end) as trial_and_full
from (
  select usr,
    count(case when account_type = 'trial' then usr end) as trial_cnt,
    count(case when account_type = 'full' then usr end) as full_cnt
  from tbl
  group by usr
)
  

 TRIAL | FULL | FULL_ONLY | TRIAL_AND_FULL
----: | ---: | --------: | -------------:
    4 |    6 |         3 |              3
  

db<>скрипка