Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Вопрос-Ответ Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
Есть таблица событий, в которой собирается вся активность пользователя в продукте.
Колонки:
- user_id
- event_timestamp
- event_name
Нужно составить запрос, который вернёт таблицу вида:
- год и месяц появления пользователя в системе
- количество новых пользователей (пришедших в этом месяцу)
- вероятность возврата пользователя на второй месяц (30-60 день после первого появления в системе).

Любой диалект SQL
20 ноя 19, 20:59    [22021294]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 65894
Блог
Krew21
Нужно составить запрос

Составляйте.

Krew21
вероятность возврата пользователя на второй месяц

Н-да. При таких преподах трудно надеяться на учеников.
20 ноя 19, 22:35    [22021347]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
982183
Member

Откуда: VL
Сообщений: 3386
Krew21

Нужно составить запрос, который вернёт таблицу вида:
- год и месяц появления пользователя в системе
- количество новых пользователей (пришедших в этом месяцу)
- вероятность возврата пользователя на второй месяц (30-60 день после первого появления в системе).

1.Одну таблицУ, или три таблицЫ ?
2. месяцУ или месяцЕ?
3. Методика расчета вероятности возврата какая?
21 ноя 19, 01:04    [22021391]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
1. Одну таблицу
2. Месяце
3. Методика расчета любая - с этим условием основная сложность.

К сообщению приложен файл. Размер - 88Kb
21 ноя 19, 08:23    [22021450]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
982183
Member

Откуда: VL
Сообщений: 3386
3. Методика расчета вероятности возврата какая?
"Любая" - это не вариант.

Как мне кажется, невозможно посчитать вероятность возврата, имея только дату регистрации.

Сообщение было отредактировано: 21 ноя 19, 08:42
21 ноя 19, 08:36    [22021452]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Ivan_Pisarevsky
Member

Откуда: НН
Сообщений: 8780
Krew21
Нужно составить запрос
begin 
  if <ЕСТЬ СВОИ ПОПЫТКИ> 
  then $0; 
  else $200; 
end
Картинка с другого сайта.
21 ноя 19, 11:31    [22021582]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21178
Krew21
- год и месяц появления пользователя в системе

Взять минимальный штамп и выделить год-месяц
Krew21
- количество новых пользователей (пришедших в этом месяцу)

В предыдущем запросе - оставить заданный год-месяц и посчитать
Krew21
- вероятность возврата пользователя на второй месяц (30-60 день после первого появления в системе).

К первому запросу добавить запрос появления того же пользователя (наличие записи) за второй от посчитанного месяц.
21 ноя 19, 12:37    [22021653]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
exp98
Member

Откуда:
Сообщений: 3008
982183
Как мне кажется, невозможно посчитать вероятность возврата, имея только дату регистрации.
Наверное стоит применить оценку априорной вероятности этого события.
Как вариант, предполагаем, что в БД есть некоторое кол-во "повторников на 2-й мес". Учебке не важно, 2-й это приход или 10-й.
Для всех юзеров, появившихся от 30 дней до сегодня, берём долю "повторников". Это будет апр-й оценкой на сегоня для каждого нового сегодн-го юзера.
Как вариант - попроще: ~на 1/10 от всей выборки считаем эту оценку ипользуем её всегда.

Если нет "эталонных" юзеров, то ходим с бубей, напр. р= 0.5
21 ноя 19, 13:35    [22021715]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 53400
Krew21
3. Методика расчета любая - с этим условием основная сложность.

А в чём сложность-то? Поделить количество пользователей, вернувшихся на второй месяц на общее их количество и умножить на сто в уме не получается? Возьми калькулятор.
21 ноя 19, 15:01    [22021841]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
Dimitry Sibiryakov,

Да, пока это лучшее, что из предложенных вводных можно сделать
Но, по сути, это будет показатель retention
retention = (количество пользователей на конец периода - количество пользователей за новый период)/количество пользователей на начало периода

Не уверен, что это можно интерпретировать как вероятность
21 ноя 19, 18:44    [22022097]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
exp98,

Вероятность 0,5% - это для одного конкретного: вернулся или нет
Для всей выборки, думаю, этот способ не подходит
21 ноя 19, 18:48    [22022098]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
Akina,

Не понял, что ты имеешь ввиду
21 ноя 19, 18:50    [22022100]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
Dimitry Sibiryakov,

Можешь подсказать, как полный sql запрос будет выглядеть с учетом предыдущих условий?
21 ноя 19, 18:52    [22022104]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21178
Krew21
Не понял, что ты имеешь ввиду
Первый - понятен? напиши и покажи.
Второй - понятен? напиши и покажи.

+ offtop
Лично я, пока не увижу, что ты вменяемо что-то сделал сам - ни строки кода не напишу.
21 ноя 19, 20:20    [22022171]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
exp98
Member

Откуда:
Сообщений: 3008
Krew21
Вероятность 0,5% - это для одного конкретного: вернулся или нет
Нет! р=0.5 - это 50%, оценка априорной в-сти. Повторяю, в этом случае ты лично можешь назвать любое своё любимое число, ибо мы все не ясновидцы, а разумных данных нэма. Но только в случае отсутствия аналогичных пользователей. Апостериорная будет, когда юзеры повторится, т.е. по факту. Иначе не бывает.

ИМенно так делают на практике: берут похожие случаи и предполагают, что так и повторится в недалёком будущем. Это называется "марковскими моделями". А когда похожих случаев нэма, их надо выдумать. А потом можно развести руками, типа: А кто ж знал? Вот как ты мне докажешь, что у твоих юзеров эта вероятность не 1/2 ? только по факту, а даже по прецеденту - это уже предположение.

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

И вообще, в задании надо было написать не "какова вероятность", а оценить шансы для конторы, что новый юзер скоро повторится.
22 ноя 19, 19:03    [22023229]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
exp98,

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

По поводу формулировки задачи, согласен, она сформулирована очень обтекаемо (постановщик задачи отказался ее уточнять)
23 ноя 19, 10:20    [22023450]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
Получился такой запрос:
select event_timestamp from Table
union
select count (user_id) from Table where month(event_timestamp) = month(now()) and year(event_timestamp) = year(now()) as A
union
select count (user_id) from Table where event_name='registration' and date_format(event_timestamp, '%Y%m') = date_format(date_add(now(), interval +1 month), '%Y%m') as b
union
select B/A * 100% from Table as return probability
1 дек 19, 18:28    [22029825]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21178
Krew21
Получился такой запрос
Который гарантированно свалится по ошибке несоответствия типов.
1 дек 19, 21:16    [22029891]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
exp98
Member

Откуда:
Сообщений: 3008
Ну да,
автор не понимает "union" как минимум.
union - это не последовательность вычислений.
union объединяет в единую таблицу одним длинным запросом несколько таблиц с одинаковой структурой.
Типовой приём, если надо "заселектить" последовательность действий -- снаружи обернуть другим запросом, а его ещё другим и т.д.

"as A" и т.п.
Делить таблицы А/B для меня новость.
много ещё чего ...

Сообщение было отредактировано: 2 дек 19, 14:35
2 дек 19, 14:35    [22030362]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
exp98, подскажи, пожалуйста, как задать все условия для каждого столбца и вывести последний столбец деления?

select event_timestamp, count (user_id) as A, count (user_id) as B from Table
where
(count (user_id)= ‘date month(event_timestamp) = month(now()) and year(event_timestamp) = year(now())’)
And
(count (user_id)= ‘date_format(event_timestamp, '%Y%m') = date_format(date_add(now(), interval +1 month), '%Y%m'’ and event_name='registration')
group by event_timestamp
having B/A * 100%
3 дек 19, 13:34    [22031187]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
exp98
Member

Откуда:
Сообщений: 3008
А почему я? )) Сперва надо работать над синтаксисом.

Почему боишься использовать вложенные подзапросы?

автор
count (user_id) as A, count (user_id) as B from Table
Как идея - НЕправильная. Селект выдёргивает из декартового произведения строчку за строчкой. Оставляет потом только те строчки, к-рые удовлетв-т условиям. Все столбы тянутся из одной и той же строки. Если нужно из разных строк, то столбы используй из разных имен таблиц, свяжи таблицы условием.

Найди в конце-концов в книге, в инете похожий по смыслу пример, потренируйся. Наконец, попроси товарищей пообъяснять.

автор
group by .....
having B/A * 100%
Как идея - правильная, реализация подкачала. Вопрос в том, кто этот некто, который "having", и что и когда он "having"? А дальше видно будет ...
3 дек 19, 15:08    [22031287]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
exp98
Member

Откуда:
Сообщений: 3008
Кстати, можно воспользоваться запросопостроителем.
3 дек 19, 15:09    [22031288]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 29805
exp98,

imho, тут оконные функции надо.
http://www.sql-tutorial.ru/ru/book_rank_dense_rank_functions.html
3 дек 19, 22:14    [22031616]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
exp98,

Ты имеешь в виду использовать вложенные подзапросы после where для фильтрации?
Это же не решит вопрос с последним столбцом, где надо произвести деление

Про разные таблицы не понял, что ты имеешь в виду - здесь же в условии всего 1 таблица с 3 столбцами

P.S. Если бы рядом были люди, которые разбираются в sql, я бы не писал на форуме)
4 дек 19, 11:28    [22031882]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос с условием вероятности  [new]
Krew21
Member

Откуда:
Сообщений: 12
kdv,

Можешь уточнить: оконные функции использовать, чтобы проводить фильтрацию по первым 3 столбцам или чтобы сделать деление для четвертого?

Если для деления, то не понимаю, как оконная функция поможет делить, ведь она будет применяться последней, и нужно делить 2 и 3 столбец с уже примененными к ним условиями фильтрации
4 дек 19, 11:32    [22031887]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Вопрос-Ответ Ответить