ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Небольшой запроc в bigquery
25 сообщений из 30, страница 1 из 2
  1  все
Небольшой запроc в bigquery
    #39725135
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Добрый день ! Только начинаю в sql, возникла проблема с небольшим запросом в bq, буду рад любым советам.
Есть две таблицы : installs и logins, в них есть колонки uid - уникальный ID пользователя, _PARTITION(timestamp) - дата

Мне необходимо посчитать количество пользователей из таблицы installs в нужную мне дату и разделить на количество пользователей в другую дату из таблицы logins. (показатель retention, или удержания)

Код :
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  `installs`
LEFT JOIN
  `installs` AS `logins`
ON
  `installs`.uid = `logins`.uid
  where `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') and `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')



Результат запроса : Error: division by zero: 0 / 0
В чём может быть проблема? Заранее благодарю!
...
Рейтинг: 0 / 0
30.10.2018, 17:24
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725158
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

В запросе всего одна таблица, которая соединяется сама с собой.
Соответственно условие в where ни для одной из строк не может быть истинным.
count определяет что получилось 0 строк и именно это значение в знаменателе дроби приводит к ошибке.
...
Рейтинг: 0 / 0
30.10.2018, 17:40
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725164
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел Лузанов,

Вы имеете в виду :
Код: plsql
1.
2.
LEFT JOIN
  `installs` AS `logins`

? Как я могу это исправить ?
...
Рейтинг: 0 / 0
30.10.2018, 17:46
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725174
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

Да, именно это.
Замените installs на logins.
...
Рейтинг: 0 / 0
30.10.2018, 18:00
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725176
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Попробовал по-другому :
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
  COUNT(DISTINCT`installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  `installs`
LEFT JOIN
  `ogins` ON `installs`.uid = `logins`.uid
WHERE
  `nstalls`._PARTITIONTIME=TIMESTAMP('2018-09-02')
  AND `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')



Выдало :
Row 1
New_users 323
Retained_users 323
Retention 1

Что не может быть верным результатом, т.к. 2018-09-02 было в районе 3000 установок
...
Рейтинг: 0 / 0
30.10.2018, 18:02
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725180
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел Лузанов,

Заменил
Код: plsql
1.
2.
LEFT JOIN
  `logins` AS `logins`


Правда, не понял, зачем.

Результат, как и выше : 323 установлено и 323 залогинились на следующей день, что явно неверно :(
...
Рейтинг: 0 / 0
30.10.2018, 18:05
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725181
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Павел Лузанов,

вангую, там null-ы в уидах, или исходное число записей -- 0.
т.к. каунт от _внутренней_ таблицы внешнего соединения -- 0.
...
Рейтинг: 0 / 0
30.10.2018, 18:06
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725183
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Павел ЛузановVedash,

В запросе всего одна таблица, которая соединяется сама с собой.
Соответственно условие в where ни для одной из строк не может быть истинным.
count определяет что получилось 0 строк и именно это значение в знаменателе дроби приводит к ошибке.

ага , понял

1.там левое соединение. но :
2. выделенное превращает его во внутреннее.
по взаимоисключающему
...
Рейтинг: 0 / 0
30.10.2018, 18:12
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725184
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
qwwq,

Если я понял, о чем Вы :
Код: plsql
1.
2.
3.
4.
5.
SELECT
  COUNT(DISTINCT uid)
FROM
  `nstalls`
where _PARTITIONTIME=TIMESTAMP('2018-09-02')

Выдает 3172

Во второй таблице по аналогичном запросу очень много (около 21к)
...
Рейтинг: 0 / 0
30.10.2018, 18:14
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725187
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

Ну уж покажите тогда что возвращает:
Код: sql
1.
SELECT  COUNT(*), COUNT(DISTINCT uid) FROM `installs` WHERE `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02');


и
Код: sql
1.
SELECT  COUNT(*), COUNT(DISTINCT uid) FROM `logins` WHERE `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03');
...
Рейтинг: 0 / 0
30.10.2018, 18:17
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725190
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

соврал. не по взаимоискл. те же гуиды в другой день.
но во внутреннее точно.
т.е. почему 0 -- надо смотреть данные. скорее всего зря date кастится в таймстамп. надо наоборот.
надо на типы в табле смотреть.
...
Рейтинг: 0 / 0
30.10.2018, 18:20
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725191
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел Лузанов,

На первое выдает :
Row f0_ f1_
1 3172 3172

На второе выдает :

Row f0_ f1_
1 79150 27625
...
Рейтинг: 0 / 0
30.10.2018, 18:20
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725193
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
qwwq1.там левое соединение. но :
2. выделенное превращает его во внутреннее.
по взаимоисключающему

Согласен, условие из WHERE нужно в ON перенести.
В терминах исходного запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  `installs`
LEFT JOIN
  `logins` AS `logins`
ON
  `installs`.uid = `logins`.uid AND 
  `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') and `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')
...
Рейтинг: 0 / 0
30.10.2018, 18:21
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725194
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
qwwq,
...
Рейтинг: 0 / 0
30.10.2018, 18:21
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725196
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел Лузановqwwq1.там левое соединение. но :
2. выделенное превращает его во внутреннее.
по взаимоисключающему

Согласен, условие из WHERE нужно в ON перенести.
В терминах исходного запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  `installs`
LEFT JOIN
  `logins` AS `logins`
ON
  `installs`.uid = `logins`.uid AND 
  `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') and `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')



Результат :(
...
Рейтинг: 0 / 0
30.10.2018, 18:24
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725210
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

Ох, что-то уже совсем голова не варит.
Ну давайте так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  (select * from `installs` where `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') ) AS `installs`
LEFT JOIN
  (select * from `logins` where `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')) AS `logins`
ON
  `installs`.uid = `logins`.uid
...
Рейтинг: 0 / 0
30.10.2018, 18:47
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725214
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

А одинаковых uid в logins наверное может быть несколько?
Тогда видимо требуется оставить только уникальные в последнем запросе:
Код: sql
1.
(select distinct uid from `logins` where `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')) AS `logins`
...
Рейтинг: 0 / 0
30.10.2018, 18:53
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725314
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

Переборщил с включением в ON условий. Должно быть так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  `installs`
LEFT JOIN
  `logins` 
ON
  `installs`.uid = `logins`.uid AND `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')
WHERE
  `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02')



или так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  (select uid from `installs` where `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') ) AS `installs`
LEFT JOIN
  (select uid from `logins` where `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')) AS `logins`
ON
  `installs`.uid = `logins`.uid
...
Рейтинг: 0 / 0
30.10.2018, 22:02
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725678
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел ЛузановVedash,
или так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
  COUNT(DISTINCT `installs`.uid) AS New_users,
  COUNT(DISTINCT `logins`.uid) AS Retained_users,
  COUNT(DISTINCT `logins`.uid) / COUNT(DISTINCT `installs`.uid) AS Retention
FROM
  (select uid from `installs` where `installs`._PARTITIONTIME=TIMESTAMP('2018-09-02') ) AS `installs`
LEFT JOIN
  (select uid from `logins` where `logins`._PARTITIONTIME=TIMESTAMP('2018-09-03')) AS `logins`
ON
  `installs`.uid = `logins`.uid



Супер! Всё получилось, как надо:) Спасибо огромное, даже не думал, что так подробно поможете)
Я, наверное, совсем борзею, но у меня опять ступор : теперь аналогичный запрос нужно сделать по дням (с отображением даты) на месяц. Также умножил Retention на 100 - для отображения в процентах. Пробую мудрить с _PARTITIONTIME, пока не очень выходит :
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT
  _PARTITIONTIME AS Day,
  COUNT(DISTINCT `ciliz_bottle_fb.installs`.uid) AS New_users,
  COUNT(DISTINCT `ciliz_bottle_fb.logins`.uid) AS Retained_users,
  COUNT(DISTINCT `ciliz_bottle_fb.logins`.uid) / COUNT(DISTINCT `ciliz_bottle_fb.installs`.uid) * 100 AS Retention
FROM (
  SELECT
    _PARTITIONTIME, uid
  FROM
    `ciliz_bottle_fb.installs`
  WHERE
    `ciliz_bottle_fb.installs`._PARTITIONTIME=TIMESTAMP('2018-09-01') ) AS `ciliz_bottle_fb.installs`
LEFT JOIN (
  SELECT
     uid
  FROM
    `ciliz_bottle_fb.logins`
  WHERE
    `ciliz_bottle_fb.logins`._PARTITIONTIME=TIMESTAMP('2018-09-02') ) AS `ciliz_bottle_fb.logins`
ON
  `ciliz_bottle_fb.installs`.uid = `ciliz_bottle_fb.logins`.uid
GROUP BY
  1


Хочется запросить таблицу с каждым днём, в котором будет указано число и % Retained users
Результат :
...
Рейтинг: 0 / 0
31.10.2018, 11:57
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725685
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Vedash,
Насколько я понимаю, нужно где-то впихнуть interval '1 day' (если такая функция вообще работает в standart SQL) и limit 30.
Если с лимитом всё понятно - можно в конце всего запроса дописать, то с синтаксисом интервала совсем беда.
...
Рейтинг: 0 / 0
31.10.2018, 12:01
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725848
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

Ну что-то типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT dates.day,
       data.New_users,
       data.Retained_users,
       data.Retention
FROM
    generate_series(
        TIMESTAMP '2018-09-01', TIMESTAMP '2018-09-30', INTERVAL '1 Day'
    ) as dates(day),
LATERAL (
    SELECT
      COUNT(DISTINCT i.uid) AS New_users,
      COUNT(DISTINCT l.uid) AS Retained_users,
      (COUNT(DISTINCT l.uid)::numeric / COUNT(DISTINCT i.uid)::numeric) AS Retention
    FROM
      `installs` AS i
    LEFT JOIN
      `logins` AS l
    ON
      i.id = l.id AND l._PARTITIONTIME = dates.day + INTERVAL '1 Day'
    WHERE
      i._PARTITIONTIME = dates.day
) data
...
Рейтинг: 0 / 0
31.10.2018, 14:33
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725928
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел ЛузановVedash,

Ну что-то типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT dates.day,
       data.New_users,
       data.Retained_users,
       data.Retention
FROM
    generate_series(
        TIMESTAMP '2018-09-01', TIMESTAMP '2018-09-30', INTERVAL '1 Day'
    ) as dates(day),
LATERAL (
    SELECT
      COUNT(DISTINCT i.uid) AS New_users,
      COUNT(DISTINCT l.uid) AS Retained_users,
      (COUNT(DISTINCT l.uid)::numeric / COUNT(DISTINCT i.uid)::numeric) AS Retention
    FROM
      `installs` AS i
    LEFT JOIN
      `logins` AS l
    ON
      i.id = l.id AND l._PARTITIONTIME = dates.day + INTERVAL '1 Day'
    WHERE
      i._PARTITIONTIME = dates.day
) data



почему-то ругается на синтаксис с interval : Error: Syntax error: Unexpected keyword INTERVAL
...
Рейтинг: 0 / 0
31.10.2018, 15:47
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725948
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Vedash,

А какая база данных, кстати?

Что выдает: SELECT now() + INTERVAL '1 Day';
...
Рейтинг: 0 / 0
31.10.2018, 15:59
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725952
Vedash
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vedash
Гость
Павел ЛузановVedash,

А какая база данных, кстати?

Что выдает: SELECT now() + INTERVAL '1 Day';
Аналогично : Query Failed
Error: Syntax error: Unexpected keyword INTERVAL at [1:16]
...
Рейтинг: 0 / 0
31.10.2018, 16:02
| Ответить | Цитировать | Написать  
Небольшой запроc в bigquery
    #39725956
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Павел ЛузановVedash,

А какая база данных , кстати?

Что выдает: SELECT now() + INTERVAL '1 Day';
где есть тип "стринг" и иденты квотят апострофами ?
...
Рейтинг: 0 / 0
31.10.2018, 16:06
| Ответить | Цитировать | Написать  
25 сообщений из 30, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Небольшой запроc в bigquery
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=48&tid=1995517]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 38ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 2ms
others: 275ms
total:  426ms
созд. / загр.: 426ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]