Блог

Здесь есть два стола. В одной таблице представлены все мобильные действия (все страницы, посещенные пользователями на мобильных устройствах). В другой таблице перечислены все веб-действия (все страницы, посещенные пользователями в Интернете). Я пытаюсь вернуть процент пользователей, которые посещали только мобильные устройства, только Интернет и то и другое. То есть процент пользователей, которые находятся только в мобильной таблице, только в веб-таблице и в обеих таблицах. Сумма процентов должна возвращать 1. Я изо всех сил пытаюсь понять, почему мое решение неверно. Вот набор данных, если это необходимо. Заранее спасибо.

мобильная таблица

• идентификатор пользователя (идентификатор пользователя, посетившего данную страницу на мобильном устройстве, например: 128)
• страница (страница, посещенная этим пользователем на мобильном устройстве, например: page_5_mobile)

web_table

• идентификатор пользователя (идентификатор пользователя, посетившего данную веб-страницу, например: 1210)
• страница (страница, посещенная этим пользователем в Интернете, например: page_1_web)

Вот моя попытка решения:

 with t1 as
(select count(*) as t1_count
from mobile_table),

t2 as
(select count(*) as t2_count
from web_table),

t3 as 
(select count(*) as t3_count
from web_table
join mobile_table 
on q2_web.user_id = q2_mobile.user_id)

select round(cast(t1_count as numeric)*100/(t1_count t2_count  t3_count),2) as mobile_usage,
round(cast(t2_count as numeric)*100/(t1_count t2_count t3_count),2) as web_usage,
round(cast(t3_count as numeric)*100/(t1_count t2_count t3_count),2) as mobile_and_web_usage
from t1, t2, t3
 

Результат моего запроса:

• мобильное использование: 37.06
• web_usage: 25.14
• mobile_and_web_usage: 37,80

Единственная проблема заключается в том, что официальное решение отличается:

• мобильное использование: 16
• web_usage: 31
• mobile_and_web_usage: 52

Почему вы добавляете t3_count . его следует вычесть.

Попробуй вот так

 with t1 as
(select count(distinct user_id) as t1_count
from mobile_table),

t2 as
(select count(distinct user_id) as t2_count
from web_table),

t3 as 
(select count(distinct q2_web.user_id) as t3_count
from web_table q2_web
join mobile_table q2_mobile
on q2_web.user_id = q2_mobile.user_id)

select round(cast(t1_count-t3_count as numeric)*100/(t1_count t2_count- t3_count),0) as mobile_usage,
round(cast(t2_count-t3_count as numeric)*100/(t1_count t2_count-t3_count),0) as web_usage,
round(cast(t3_count as numeric)*100/(t1_count t2_count-t3_count),0) as mobile_and_web_usage
from t1, t2, t3
 

Более коротким способом вы можете написать свой запрос, как показано ниже:

 with ct as(
select 
coalesce(t1.user_id,t2.user_id),
count(t1.page) "mobile",count(t2.page) "web" from mobile_table t1 full outer join web_table t2 on t1.user_id=t2.user_id
group by 1)
select 
round(cast(count(*) filter (where mobile>0 and web=0) as numeric)/count(*),2)*100 "Mobile",
round(cast(count(*) filter (where mobile=0 and web>0) as numeric)/count(*),2)*100 "WEB",
round(cast(count(*) filter (where mobile>0 and web>0) as numeric)/count(*),2)*100 "BOTH"
from ct