Как посчитать количество новых уникальных значений за временной период / MySQL

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как посчитать количество новых уникальных значений за временной период

11 сообщений из 11, страница 1 из 1

Как посчитать количество новых уникальных значений за временной период

#39984104

gosIX

Гость

Добрый день!
Есть таблица состоящая из 2-х столбцов, id и timestamp.
Как посчитать количество новых уникальных id за период(например за неделю)?
Другими словами: за самую первую неделю пришло 10 уникальных id мы их посчитали выдало 10, за вторую неделю пришло 15 id (5 таких же как на первой неделе и 10 новых) мы их посчитали выдало 10. И так за каждый период вычислять только новые.

...

Рейтинг:

0 / 0

26.07.2020, 17:17

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984112

Жук в муравейнике

Участник

Сообщения: 703

Рейтинг: 0 / 0

Как-то в этом духе

Код: 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, 18:25

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984129

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Типа

Код: 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, 19:43

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984148

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:22

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984151

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

gosIX

@start AND @end - Я правильно понимаю что это искомый интервал?

Да, это он. Границы - включительно. Если надо невключительно - замени на два неравенства.

gosIX

Например для месяца это будет выглядеть вот так

Не так. Период у тебя фиксированный же. Так что границы - это литералы (типа '2020-05-01'), а не функции от поля.

...

Рейтинг:

0 / 0

26.07.2020, 21:33

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984156

gosIX

Гость

Akina,
А если они мне не известны? Ну то есть статистика нужна помесячная.
Точнее так:
Нужно посчитать статистику по каждому календарному месяцу из имеющихся.

...

Рейтинг:

0 / 0

26.07.2020, 21:45

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984160

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

gosIX

А если они мне не известны? Ну то есть статистика нужна помесячная.

Тогда генерируй список этих месяцев (начало-конец для каждого месяца) и используй как ещё один источник данных.

...

Рейтинг:

0 / 0

26.07.2020, 22:02

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984169

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, 22:53

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984170

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

26.07.2020, 23:05

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984228

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Код: 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

27.07.2020, 08:39

| Ответить | Цитировать | Написать

Как посчитать количество новых уникальных значений за временной период

#39984592

gosIX

Гость

Akina,
Большое Вам спасибо.

...

Рейтинг:

0 / 0

28.07.2020, 11:21

| Ответить | Цитировать | Написать

11 сообщений из 11, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&fpage=18&tid=1828438]:	0ms
get settings:	10ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	51ms
get topic data:	13ms
get forum data:	3ms
get page messages:	53ms
get tp. blocked users:	2ms
others:	253ms

total:	408ms