Гость
Войти | Профиль | Очистить
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как посчитать количество новых уникальных значений за временной период / 11 сообщений из 11, страница 1 из 1
26.07.2020, 17:17
    #39984104
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Добрый день!
Есть таблица состоящая из 2-х столбцов, id и timestamp.
Как посчитать количество новых уникальных id за период(например за неделю)?
Другими словами: за самую первую неделю пришло 10 уникальных id мы их посчитали выдало 10, за вторую неделю пришло 15 id (5 таких же как на первой неделе и 10 новых) мы их посчитали выдало 10. И так за каждый период вычислять только новые.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 18:25
    #39984112
Жук в муравейнике
Жук в муравейнике
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Как-то в этом духе
Код: sql
1.
2.
3.
4.
5.
6.
7.
select *
from (
          select row_number() over(partition by id) rn
          from tbl
        )
where rn = 1
    and timestamp > ADDATE(timestamp, -7)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 19:43
    #39984129
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Типа
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT COUNT(DISTINCT id)
FROM sourcetable t1
WHERE timestamp BETWEEN @start AND @end
  AND NOT EXISTS ( SELECT NULL
                   FROM sourcetable t2
                   WHERE t1.id = t2.id
                     AND t2.timestamp < @start )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 21:22
    #39984148
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Akina,
Большое спасибо за потраченное время.
Прошу пояснить еще один момент:
@start AND @end - Я правильно понимаю что это искомый интервал?
Например для месяца это будет выглядеть вот так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT COUNT(DISTINCT id)
FROM sourcetable t1
WHERE timestamp BETWEEN DATE_FORMAT(timestamp, '%Y-%m') and DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 1 month),  '%Y-%m')
  AND NOT EXISTS ( SELECT NULL
                   FROM sourcetable t2
                   WHERE t1.id = t2.id
                     AND t2.timestamp < DATE_FORMAT(timestamp, '%Y-%m') )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 21:33
    #39984151
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
gosIX
@start AND @end - Я правильно понимаю что это искомый интервал?
Да, это он. Границы - включительно. Если надо невключительно - замени на два неравенства.
gosIX
Например для месяца это будет выглядеть вот так
Не так. Период у тебя фиксированный же. Так что границы - это литералы (типа '2020-05-01'), а не функции от поля.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 21:45
    #39984156
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Akina,
А если они мне не известны? Ну то есть статистика нужна помесячная.
Точнее так:
Нужно посчитать статистику по каждому календарному месяцу из имеющихся.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 22:02
    #39984160
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
gosIX
А если они мне не известны? Ну то есть статистика нужна помесячная.
Тогда генерируй список этих месяцев (начало-конец для каждого месяца) и используй как ещё один источник данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 22:53
    #39984169
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Akina,
Честно сказать, мне не понятно как это реализовать.
Можете помочь, дать более развернутую наводку?
Как Я понимаю списки выглядят следующим образом:
Код: sql
1.
2.
3.
4.
SELECT DATE_FORMAT(timestamp, '%Y-%m') FROM sourcetable
GROUP BY 1
SELECT DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 1 month),  '%Y-%m') FROM sourcetable
GROUP BY 1


Полный запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT COUNT(DISTINCT id)
FROM sourcetable t1
WHERE timestamp >= (SELECT DATE_FORMAT(timestamp, '%Y-%m') FROM sourcetable GROUP BY 1) 
    AND timestamp < (SELECT DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 1 month),  '%Y-%m') FROM sourcetable GROUP BY 1)
    AND NOT EXISTS ( SELECT NULL
                   FROM sourcetable t2
                   WHERE t1.id = t2.id
                     AND t2.timestamp < (SELECT DATE_FORMAT(timestamp, '%Y-%m') FROM sourcetable GROUP BY 1) )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2020, 23:05
    #39984170
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Понимаю что ошибка на этапе:
Код: sql
1.
2.
WHERE timestamp >= (SELECT DATE_FORMAT(timestamp, '%Y-%m') FROM sourcetable GROUP BY 1) 
    AND timestamp < (SELECT DATE_FORMAT(DATE_ADD(timestamp, INTERVAL 1 month),  '%Y-%m') FROM sourcetable GROUP BY 1)


Так как там список из дат, но не хватает знаний верно реализовать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2020, 08:39
    #39984228
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
WITH RECURSIVE
cte AS ( SELECT CAST(DATE_FORMAT(MIN(ts), '%Y-%m-01') AS DATE) ts
         FROM test
       UNION ALL
         SELECT ts + INTERVAL 1 MONTH
         FROM cte
         WHERE ts < ( SELECT CAST(DATE_FORMAT(MAX(ts), '%Y-%m-01') AS DATE) ts
                      FROM test )
       )
SELECT DATE_FORMAT(cte.ts, '%Y-%m') year_and_month,
       COUNT(DISTINCT t1.id) new_id_count
FROM cte
LEFT JOIN test t1 ON t1.ts BETWEEN cte.ts AND LAST_DAY(cte.ts)
WHERE NOT EXISTS ( SELECT NULL
                   FROM test t2
                   WHERE t1.id = t2.id 
                     AND t2.ts < cte.ts )
GROUP BY year_and_month;

fiddle
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.07.2020, 11:21
    #39984592
gosIX
gosIX
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество новых уникальных значений за временной период
Akina,
Большое Вам спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как посчитать количество новых уникальных значений за временной период / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 167ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1828438]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 63ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 13ms
total:  167ms
x
x
Закрыть


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