ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / уникальные вхождения за период
15 сообщений из 15, страница 1 из 1
уникальные вхождения за период
    #39159904
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
Уважаемые гуру, прошу совета.

Есть таблица:

Код: sql
1.
2.
3.
4.
CREATE TABLE atbl (
  uname TEXT,
  edate DATE
);


Нужно посчитать кол-во уникальных uname за предыдущую неделю для каждой даты.

Вот запрос который считает кол-во уников за сутки:

Код: sql
1.
2.
3.
4.
5.
SELECT
  edate,
  count(DISTINCT uname)
FROM atbl
GROUP BY edate;


А нужно:

Код: sql
1.
2.
3.
4.
SELECT count(DISTINCT uname)
FROM atbl
WHERE edate >= '2016-01-31'::date - 7
      AND edate <= '2016-01-31'::date;


и так для каждой edate в таблице.

Могу реализовать подобное с помощью внешнего скрипта, но хочется сделать на чистом SQL.
Предполагаю, что нужны оконные ф-и, но не соображу как их применить.
...
Рейтинг: 0 / 0
01.02.2016, 11:17
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39159911
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
ПётрГриг,

посмотрите в сторону generate_series
...
Рейтинг: 0 / 0
01.02.2016, 11:23
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39159934
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
Lonepsychoпосмотрите в сторону generate_series

Извините, но не понимаю как generate_series может здесь помочь.
...
Рейтинг: 0 / 0
01.02.2016, 11:47
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39159957
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
ПётрГриг,

Код: sql
1.
2.
3.
4.
5.
6.
SELECT count(DISTINCT a.uname)
FROM atbl AS a
INNER JOIN
generate_series(date_trunc('day', now()) - INTERVAL '21 days', date_trunc('day', now()), INTERVAL '1 week') AS d
ON a.edate >= d AND
     a.edate < (d + INTERVAL '1 week');


чтото на подобии этого...
...
Рейтинг: 0 / 0
01.02.2016, 12:10
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160104
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
ПётрГригУважаемые гуру, прошу совета.

Есть таблица:

Код: sql
1.
2.
3.
4.
CREATE TABLE atbl (
  uname TEXT,
  edate DATE
);


Нужно посчитать кол-во уникальных uname за предыдущую неделю для каждой даты.

Вот запрос который считает кол-во уников за сутки:

Код: sql
1.
2.
3.
4.
5.
SELECT
  edate,
  count(DISTINCT uname)
FROM atbl
GROUP BY edate;


А нужно:

Код: sql
1.
2.
3.
4.
SELECT count(DISTINCT uname)
FROM atbl
WHERE edate >= '2016-01-31'::date - 7
      AND edate <= '2016-01-31'::date;


и так для каждой edate в таблице.

Могу реализовать подобное с помощью внешнего скрипта, но хочется сделать на чистом SQL.
Предполагаю, что нужны оконные ф-и, но не соображу как их применить.

не понял, чем то "что нужно" отличается от того, что получено (групбаем).

вы не могли бы попытаться донести вашу проблему немного более развёрнуто, своими словами, а не кодом ?

если нужно вывести 0 для отсутствующих напрочь в таблице дат -- то как рекомендовал предыдущий оратор , но RIGHT JOIN--ом результата группировки (запрос 1 вашей темы, но с недельным ограничением) с серией.
...
Рейтинг: 0 / 0
01.02.2016, 13:33
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160236
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
Lonepsycho,

если я правильно понял идею, то вы хотите разбить таблицу на недельные интервалы и внутри них посчитать кол-во уникальных uname.

Но это не то, что нужно.

Нужно ДЛЯ КАЖДОЙ даты узнать кол-во уникальных uname за предыдущую неделю.

Тупо в лоб это можно сделать следующим образом:

Получить все даты:
Код: sql
1.
2.
SELECT DISTINCT (edate)
FROM atbl;



А потом для каждой даты посчитать уников за неделю:

Код: sql
1.
2.
3.
SELECT count(DISTINCT uname)
FROM atbl
WHERE edate BETWEEN ? - 7 AND ?;



Только это криво.
Хотелось бы найти более правильный способ решения на чистом SQL.
Наверняка это возможно.
...
Рейтинг: 0 / 0
01.02.2016, 14:38
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160237
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
qwwqне понял, чем то "что нужно" отличается от того, что получено (групбаем).

вы не могли бы попытаться донести вашу проблему немного более развёрнуто, своими словами, а не кодом ?


Это архивная таблица в неё заносится активности пользователей.
Нужно для каждых суток посчитать сколько пользователей было активно за неделю.

GROUP BY показывает кол-во уникальных пользователей за сутки, а нужны пользователи ЗА НЕДЕЛЮ, но для каждых суток.

Т.е. в результате нужно получить примерно следующее:

.....
2016-01-28 - 1100 // уникальных uname с 01.21 по 01.28
2016-01-29 - 2500 // уникальных uname с 01.22 по 01.29
2016-01-30 - 3500 // уникальных uname с 01.23 по 01.30
2016-01-31 - 2200 // уникальных uname с 01.24 по 01.31
.....
...
Рейтинг: 0 / 0
01.02.2016, 14:40
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160244
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Используй подзапрос.
...
Рейтинг: 0 / 0
01.02.2016, 14:43
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160282
big-trot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-trot
Гость
ПётрГригqwwqне понял, чем то "что нужно" отличается от того, что получено (групбаем).

вы не могли бы попытаться донести вашу проблему немного более развёрнуто, своими словами, а не кодом ?


Это архивная таблица в неё заносится активности пользователей.
Нужно для каждых суток посчитать сколько пользователей было активно за неделю.

GROUP BY показывает кол-во уникальных пользователей за сутки, а нужны пользователи ЗА НЕДЕЛЮ, но для каждых суток.

Т.е. в результате нужно получить примерно следующее:

.....
2016-01-28 - 1100 // уникальных uname с 01.21 по 01.28
2016-01-29 - 2500 // уникальных uname с 01.22 по 01.29
2016-01-30 - 3500 // уникальных uname с 01.23 по 01.30
2016-01-31 - 2200 // уникальных uname с 01.24 по 01.31
.....

А если так
Код: sql
1.
2.
SELECT count(DISTINCT uname) OVER (PARTITION BY BETWEEN edate AND edate - interval'7 day' )
FROM atbl
...
Рейтинг: 0 / 0
01.02.2016, 15:13
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160324
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
big-trot,

во-первых
Код: plaintext
ERROR:  DISTINCT is not implemented for window functions

во-вторых
для скользящих нужно что--то типа range. или научиться эмулировать его чем то ещё. 18121603
т.е. дайте мне хотя бы умение сослаться на ТЕКУЩЕЕ значение поля в выражении для описания окна -- и я сэмулирую вам range.
...
Рейтинг: 0 / 0
01.02.2016, 15:49
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160327
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
пока же -- Dimitry Sibiryakov выдал наиболее просто реализуемое предложение.
...
Рейтинг: 0 / 0
01.02.2016, 15:51
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160336
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
Dimitry SibiryakovИспользуй подзапрос.
Глупый вопрос - а как его использовать?

qwwqпока же -- Dimitry Sibiryakov выдал наиболее просто реализуемое предложение.
К сожалению не хватает знаний, что бы реализовать данное предложение.
...
Рейтинг: 0 / 0
01.02.2016, 16:01
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160366
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
ПётрГриг,

если комбинировать ваши запросы, то как-то так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
WITH unique_dates as (
SELECT DISTINCT (edate)
FROM atbl)

SELECT edate,
(SELECT count(DISTINCT uname) FROM atbl
WHERE edate BETWEEN unique_dates.edate  - 7 AND unique_dates.edate) as cnt
FROM unique_dates;



если таблица большая, то даты быстрее будет сгенерировать, используя generate_series и max/min из таблицы.
...
Рейтинг: 0 / 0
01.02.2016, 16:20
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160376
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
LonepsychoПётрГриг,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
	(date'2016-01-01' + d)  AS date_for
	,(	SELECT count(DISTINCT a.uname)
		FROM atbl	AS a
		WHERE 
				a.edate >= (date'2016-01-01' + d) -7
			AND	a.edate < (date'2016-01-01' + d )
	)	AS cnt_dist_7
FROM 
	generate_series(0, 21, 1) AS d


чтото на подобии этого...
...
Рейтинг: 0 / 0
01.02.2016, 16:28
| Ответить | Цитировать | Написать  
уникальные вхождения за период
    #39160389
ПётрГриг
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПётрГриг
Гость
AlexiusПётрГриг,

если комбинировать ваши запросы, то как-то так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
WITH unique_dates as (
SELECT DISTINCT (edate)
FROM atbl)

SELECT edate,
(SELECT count(DISTINCT uname) FROM atbl
WHERE edate BETWEEN unique_dates.edate  - 7 AND unique_dates.edate) as cnt
FROM unique_dates;




Спасибо, коллега!
То, что нужно!
...
Рейтинг: 0 / 0
01.02.2016, 16:35
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / уникальные вхождения за период
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&tid=1997479]:
 0ms
get settings:
 7ms
get forum list:
 19ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 57ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 233ms
total:  408ms
созд. / загр.: 408ms
x
x
Закрыть


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