#sql #data-analysis
Вопрос:
У меня есть таблица с более чем 500 тысячами строк и столбцов, как показано ниже;
user_id | event_date | event_name | version| =============================================== 1543435 | 18092021 | first_open | 113 5476523 | 18092021 | session_start| 111 7418529 | 18092021 | first_open | 112 1754821 | 18092021 | first_open | 113 9820011 | 18092021 | session_start| 114 4568714 | 18092021 | session_start| 120 Пользователи, у которых есть имя события с first_open, означают, что пользователь установил и открыл приложение в первый раз, в то время как session_start означает, что пользователь установил и открыл его раньше, это не в первый раз.
идентификатор пользователя уникален для каждого пользователя и не меняется при каждом входе в систему.
Мы имеем дело с пользователями, у которых есть только версия 113.
Мне нужно найти долю пользователей, которые установили приложение 18-го числа (18.09.2021) и снова открыли его 1-го (19.09.2021) и 3-го (21.09.2021) дней.
Дорожная карта:
- Шаг 1: Поиск уникальных идентификаторов пользователей с датой события=18092021 и именем события=first_open и получение их количества
- Шаг 2: Поиск количества совпадений с датой события=19092021 и именем события=начало сеанса, которые также соответствуют идентификаторам пользователей из шага 1
- Шаг 3: Поиск количества совпадений с датой события=21092021 и именем события=session_start, которые также соответствуют идентификаторам пользователей из шага 1
- Шаг 4: Деление числа подсчета на шаге 2 и числа подсчета на шаге 1 даст нам задержку на 1 день
- Шаг 5: Деление числа подсчета на шаге 3 и числа подсчета на шаге 1 даст нам удержание на 3-й день
После недели исследований и мозгового штурма я написал запрос ниже:
SELECT (SELECT COUNT(DISTINCT our_data.user_id) FROM our_data WHERE our_data.event_date = '20210918' AND our_data.event_name ='first_open' AND our_data.version = '113') AS DAY_ZERO, (SELECT COUNT(DISTINCT dayone.user_id) FROM our_data LEFT JOIN our_data AS dayone ON our_data.user_id = dayone.user_id) WHERE our_data.event_date = '20210918' AND dayone.event_date = our_data.event_date 1 AND our_data.event_name ='first_open' AND dayone.event_name ='session_start' AND our_data.version = '113' AND dayone.version = '113') AS DAY_ONE, (SELECT COUNT(DISTINCT our_data.user_id) FROM our_data LEFT JOIN our_data as daythree ON our_data.user_id = daythree.user_id WHERE our_data.event_date = '20210918' AND daythree.event_date = our_data.event_date 3 AND our_data.event_name ='first_open' AND daythree.event_name ='session_start' AND our_data.version = '113' AND daythree.version = '113') AS DAY_THREE Этот запрос дал мне следующие результаты:
DAY_ZERO | DAY_ONE | DAY_THREE | ======================================== 14879 | 7850 | 949 | Среди этих результатов я не смог выполнить ни одной операции в одном и том же запросе. Мне нужно достичь Day_ONE/DAY_ZERO= УДЕРЖАНИЕ на 1-й ДЕНЬ и DAY_THREE/DAY_ZERO= УДЕРЖАНИЕ НА 3-Й ДЕНЬ. Мне нужно выполнить эти операции в течение других первых дней в той же таблице, поэтому я должен сделать это в одном запросе. Как ты думаешь, что я могу сделать?
Комментарии:
1. Я удалил конфликтующие теги СУБД. Просто отметьте тот, который вы действительно используете.
Ответ №1:
В настоящее время у меня нет доступной базы данных sql, но я думаю, что решение должно выглядеть так:
select DAY_ONE/DAY_ZERO as DAY_1_RETENTION, DAY_THREE/DAY_ZERO as DAY_3_RETENTION from (... your query ...) Комментарии:
1. Я попытался сделать это сейчас, но получил предупреждения «Недопустимое имя столбца» для каждого DAY_ZERO, DAY_ONE и DAY_THREE.
Ответ №2:
Я бы опубликовал это в комментарии, но запрос кажется слишком длинным для комментария. Короче говоря, лучший ответ будет немного зависеть от того, какой тип SQL вы на самом деле используете (T-SQL? PL/SQL? PL/pgSQL?), но общий подход будет таким же.
Вы уже рассчитали необходимые значения в подзапросах. Я повторно использовал эти подзапросы, но вместо значений строк превратил их в таблицы с уникальными именами столбцов.
Теперь у вас есть три таблицы, каждая из которых имеет одну строку. Скрестите их, чтобы получить «одну строку» с тремя разными столбцами… и выполните необходимые вычисления по столбцам
select DAY_ONE.cnt1/DAY_ZERO.cnt0 as DAY_1_RETENTION, DAY_THREE.cnt3/DAY_ZERO.cnt0 as DAY_3_RETENTION FROM (SELECT COUNT(DISTINCT our_data.user_id) AS cnt0 FROM our_data WHERE our_data.event_date = '20210918' AND our_data.event_name ='first_open' AND our_data.version = '113') AS DAY_ZERO, (SELECT COUNT(DISTINCT dayone.user_id) cnt1 FROM our_data LEFT JOIN our_data AS dayone ON our_data.user_id = dayone.user_id) WHERE our_data.event_date = '20210918' AND dayone.event_date = our_data.event_date 1 AND our_data.event_name ='first_open' AND dayone.event_name ='session_start' AND our_data.version = '113' AND dayone.version = '113') AS DAY_ONE, (SELECT COUNT(DISTINCT our_data.user_id) cnt3 FROM our_data LEFT JOIN our_data as daythree ON our_data.user_id = daythree.user_id WHERE our_data.event_date = '20210918' AND daythree.event_date = our_data.event_date 3 AND our_data.event_name ='first_open' AND daythree.event_name ='session_start' AND our_data.version = '113' AND daythree.version = '113') AS DAY_THREE Комментарии:
1. это работает как заклинание! большое вам спасибо за потраченное время.