Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
 Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

Откуда:
Сообщений: 15
Добрый день!

У меня есть следующая таблица:

pageview - таблица просмотра страниц по пользователям
Поле Тип Описание
id int первичный ключ
created_at datetime время посещения страницы (dd.mm.yyyy)
page_id int id страницы
user_id int id пользователя который просматривал страницу


Пример данных в pageview:
id created_at page_id user_id
1 26.10.2010 3 1
2 27.10.2010 3 1
3 29.10.2010 3 1
4 30.10.2010 9 2
5 01.10.2010 2 4
6 02.10.2010 2 4
7 03.10.2010 4 4
8 05.10.2010 5 4
9 06.10.2010 8 9
10 07.10.2010 3 1
11 08.10.2010 7 5
12 09.10.2010 8 11
13 09.10.2010 4 16
14 14.10.2010 3 1


Я написал запрос, который выводит общее количество дней, которое пользователи просматривали страницу
(таблицу user здесь не привожу, т.к. из нее берется, только id пользователя).

SELECT pageview.user_id AS user_id,
pageview.page_id as page_id,
COUNT(pageview.creat_at) AS days_cnt
FROM [user] INNER JOIN pageview ON user.[id]=pageview.[user_id]
GROUP BY pageview.page_id, pageview.user_id
ORDER BY COUNT(pageview.created_at) DESC


По пользователю user_id=1
результат:
user_id page_id days_cnt
1 3 5
Всего 5 дней пользователь просматривал страницу.

Задача: Нужно вывести наибольшее количество дней, которое пользователь просматривал одну и туже страницу подряд.
В случае с user_id=1 он просматривал страницу максимально 3-и дня подряд 26.10.2010, 27.10.2010, 29.10.2010

Нужный результат (user_id=1):
user_id page_id days_cnt
1 3 3


Как проще переписать мой запрос, чтобы решить данную задачу?

Сообщение было отредактировано: 20 окт 21, 13:55
20 окт 21, 13:50    [22385858]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21483
andrewkochn
Как проще переписать мой запрос, чтобы решить данную задачу?
Полностью. Выбросить и написать с нуля.

В MS Access потребуется запрос, использующий как минимум 3 копии исходной таблицы.

PS. А за каким [censored] в источнике запроса мотается таблица user?

Сообщение было отредактировано: 20 окт 21, 14:51
20 окт 21, 14:49    [22385900]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 7584
andrewkochn

...
Задача: Нужно вывести наибольшее количество дней, которое пользователь просматривал одну и туже страницу подряд.
В случае с user_id=1 он просматривал страницу максимально 3-и дня подряд 26.10.2010, 27.10.2010, 29.10.2010
....
А вот это не понял:
26.10.2010, 27.10.2010-это подряд
27.10.2010, 29.10.2010-а это не подряд(нет 28.10.2010-получается подряд только 2 дня-26.10.2010, 27.10.2010)
Поясните
По моему термин "подряд" подразумевает непрерывный ряд

Сообщение было отредактировано: 20 окт 21, 15:18
20 окт 21, 15:14    [22385911]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

автор
PS. А за каким [censored] в источнике запроса мотается таблица user?


Из таблицы users, бралось еще одно поле - email.
В данной задаче оно конечно не требуется и таблицу user из запроса можно убрать.

автор
В MS Access потребуется запрос, использующий как минимум 3 копии исходной таблицы.

Какова методика? Джойнить копии таблиц к друг другу?
20 окт 21, 15:18    [22385914]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

sdku
А вот это не понял:
26.10.2010, 27.10.2010-это подряд
27.10.2010, 29.10.2010-а это не подряд(нет 28.10.2010-получается подряд только 2 дня-26.10.2010, 27.10.2010)
Поясните


Прошу прощения моя ошибка. Неудачно скопировал. Конечно 26.10.2010, 27.10.2010 и 28.10.2010
20 окт 21, 15:21    [22385916]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21483
Если в таблице нет дублирующихся записей, можно обойтись и 2 копиями.

Будет что-то вроде

SELECT MAX(t2.date - t1.date)
FROM ( SELECT t1.date, t2.date
       FROM table t1
       JOIN table t2 ON t1.date < t2.date
       GROUP BY t1.date, t2.date
       HAVING t2.date - t1.date = COUNT(*) - 1 ) AS subquery


Т.к. сперва составляем все пары дат. Потом отбираем только те, меж которых количество записей на 1 больше разности дат, т.е. нет пропущенных дат. Ну и во внешнем запросе находим максимальную длительность.

Условия соответствия и группировки добавь самостоятельно.
20 окт 21, 15:37    [22385929]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 7584
как вариант: с использованием функции:
для проверки добавлял\изменял записи таблицы-результат не такой как в Вашем сообщении,но верный

К сообщению приложен файл (tmp.rar - 19Kb) cкачать
20 окт 21, 23:19    [22386139]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

Попробовал выполнить ваш запрос. JOIN заменил на INNER JOIN, т.к. Access выводит ошибку ссылаясь на неправильный синтаксис FROM. Также не добавлял пока никакие условия и поля.

SELECT MAX(t2.created_at - t1.created_at), t2.created_at, t1.created_at
FROM ( SELECT t1.created_at, t2.created_at
FROM pageview t1
INNER JOIN pageview t2 ON t1.created_at < t2.created_at
GROUP BY t1.created_at, t2.created_at
HAVING t2.created_at - t1.created_at = COUNT(*) - 1 ) AS subquery
GROUP BY t1.created_at, t2.created_at

Взял такие данные для проверки:
id created_at page_id user_id
1 07.10.2021 3 2
2 08.10.2021 4 2
3 22.10.2021 1 1
4 24.10.2021 2 5
5 18.10.2021 10 1
6 28.10.2021 10 1
7 31.10.2021 5 7
8 11.10.2021 1 3
9 07.10.2021 6 1
10 02.10.2021 4 6
11 23.10.2021 10 1
12 01.10.2021 3 2
13 26.10.2021 4 5
14 29.09.2021 2 4
15 24.10.2021 10 1
16 12.10.2021 10 2
17 27.09.2021 4 3
18 20.10.2021 4 3
19 03.10.2021 1 4



Выводится следующий результат:

Expr1000 t2.created_at t1.created_at
1 08.10.2021 07.10.2021
1 24.10.2021 23.10.2021


Следующие даты не попадают в результат:
1) 11.10.2021,12.10.2021
2) 02.10.202, 03.10.2021
3) 22.10.2021,23.10.2021
4) 23.10.2021,24.10.2021

У всех пар максимальное количество дней равно 1-му.
Хотя должно быть, если например диапазон 22.10.2021, 23.10.2021, 24.10.2021 - 3 дня.
21 окт 21, 01:49    [22386172]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

Спасибо за вариант. Но требуется реализация именно на sql. Для меня это не типичная задача, поэтому возникли трудности.
21 окт 21, 02:04    [22386173]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21483
andrewkochn
Попробовал выполнить ваш запрос. JOIN заменил на INNER JOIN, т.к. Access выводит ошибку ссылаясь на неправильный синтаксис FROM. Также не добавлял пока никакие условия и поля.
У Вас во внешнем запросе 3 выходных поля и группировка, которых у меня нет. Как это согласуется с последним предложением - я даже не представляю.
21 окт 21, 07:49    [22386191]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21483
Выложите уже наконец нормальный пример БД. А то в воздухе пальцами вертеть можно долго - и безуспешно.

Скопируйте в отдельную БД таблицу с данными (~100 записей для ~3-4 пользователей и стольких же страниц). Зазипуйте и приложите к сообщению. Добавьте требуемые результаты для 2-3 различных условий отбора (например, отбор по пользователям или по диапазону дат) с соотв. пояснениями.
21 окт 21, 09:11    [22386215]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

Откуда:
Сообщений: 15
Akina
andrewkochn
Попробовал выполнить ваш запрос. JOIN заменил на INNER JOIN, т.к. Access выводит ошибку ссылаясь на неправильный синтаксис FROM. Также не добавлял пока никакие условия и поля.
У Вас во внешнем запросе 3 выходных поля и группировка, которых у меня нет. Как это согласуется с последним предложением - я даже не представляю.



Если сделать запрос в точности как у вас (изменив наименование полей), будет следующий результат:

Expr1000
1
1


Поэтому это особо ничего не меняет. Даты вывел для наглядности, чтобы понять к каким из них относится агрегация.
21 окт 21, 10:31    [22386251]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

Откуда:
Сообщений: 15
Akina
Выложите уже наконец нормальный пример БД. А то в воздухе пальцами вертеть можно долго - и безуспешно.

Скопируйте в отдельную БД таблицу с данными (~100 записей для ~3-4 пользователей и стольких же страниц). Зазипуйте и приложите к сообщению. Добавьте требуемые результаты для 2-3 различных условий отбора (например, отбор по пользователям или по диапазону дат) с соотв. пояснениями.



Нужно вывести наибольшее количество дней, которое пользователь просматривал одну и туже страницу подряд.
То есть человек смотрел страницу три дня подряд, потом перерыв, затем 5 дней подряд, то максимальным для него будет 5, не 8. Форма вывода user_id, page_id, days_cnt


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

К сообщению приложен файл (pageview.7z - 12Kb) cкачать
21 окт 21, 11:24    [22386273]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
court
Member

Откуда:
Сообщений: 2438
andrewkochn
Нужно вывести наибольшее количество дней, которое пользователь просматривал одну и туже страницу подряд.

нехватает, конешно, в Аксе аналит.функций ... (
select
  t.user_id
  ,t.page_id
  ,min(t.created_at) as from_dt
  ,max(t.created_at) as to_dt
  ,count(*) as cnt_dt
from
  (SELECT t1.Код, t1.id, t1.created_at, t1.page_id, t1.user_id
      ,t1.created_at - (select count(*) from pageview t2 where t1.user_id=t2.user_id and t1.page_id=t2.page_id and t2.created_at<t1.created_at) as inv
  FROM pageview t1) t
group by
  t.user_id
  ,t.page_id  
  ,t.inv

user_idpage_idfrom_dtto_dtcnt_dt
1218.11.202118.11.20211
1227.11.202127.11.20211
1317.11.202117.11.20211
1412.09.202112.09.20211
1530.11.202130.11.20211
2101.09.202103.09.20213
2130.09.202130.09.20211
2201.10.202101.10.20211
2222.10.202122.10.20211
2203.11.202103.11.20211
2315.10.202118.10.20214
2403.10.202103.10.20211
2425.11.202125.11.20211
3209.09.202109.09.20211
3219.10.202120.10.20212
3224.10.202124.10.20211
3411.09.202111.09.20211
3404.10.202104.10.20211
3426.10.202126.10.20211
3523.11.202123.11.20211
4105.09.202107.09.20213
4131.10.202131.10.20211
4205.11.202106.11.20212
5413.09.202115.09.20213
5422.09.202122.09.20211
21 окт 21, 12:03    [22386300]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
vmag
Member

Откуда: MP
Сообщений: 4229
andrewkochn
Нужно вывести наибольшее количество дней, которое пользователь просматривал одну и туже страницу подряд.
То есть человек смотрел страницу три дня подряд, потом перерыв, затем 5 дней подряд, то максимальным для него будет 5, не 8. Форма вывода user_id, page_id, days_cnt


надеюсь это абстрактный пример...
или вы делаете инструмент для вычисления даунов ?
... смотреть на страницу три дня подряд... это ж как обкуриться нужно...
21 окт 21, 14:37    [22386383]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

Спасибо, данный запрос в принципе подойдет!

Akina, sdku благодарю вас также.
21 окт 21, 14:45    [22386391]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
andrewkochn
Member

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

Пример конечно абстрактный.
Но ничего не мешает применять его в реальной аналитике, сделав отдельно таблицу календаря и переписав таблицу pageview.
Возьмем например страницу товара, если пользователь заходил на нее 10 дней подряд, вероятно он хочет его купить, соответственно ему можно предложить скидку на этот товар.
Речь идет про посещение страницы, а не нахождение на ней целые сутки подряд.
21 окт 21, 14:54    [22386400]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
vmag
Member

Откуда: MP
Сообщений: 4229
andrewkochn,

andrewkochn
Возьмем например страницу товара, если пользователь заходил на нее 10 дней подряд, вероятно он хочет его купить, соответственно ему можно предложить скидку на этот товар.


так то да, только наоборот, если страницу затирают глазами, то скидку делать не нужно - и так купят...
а вот если товаром даже не интересуются, то тут нужно уже думать...
21 окт 21, 20:11    [22386624]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать максимальную последовательность дат  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 7584
Я бы просто добавил в тбл. еще одно поле дата\время с заполнением при сохранении записи со значением: если разность между датой создания записи и датой последней (по дате или мах счетчику/если он последовательный/=1 день и page=page новой записи)задать значение дата предшествующей записи,иначе писать текущую дату.Имея в таблице это поле Вы извлечете любую интересующую информацию при помощи простых запросов на выборку(при необходимости с параметрами)
22 окт 21, 12:22    [22386819]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить