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

Откуда:
Сообщений: 2
Картинка с другого сайта.
Всем привет! Кто может подсказать, как посчитать сколько в среднем транзакций делает каждый пользователь в течение 72 часов после первой совершенной транзакции?

То есть тут нужно для начала видимо разбить на партиции относительно user_id

Потом найти в каждой такой партиции самую первую транзакцию (отсортировава по возрастанию поле transaction_ts и взяв первую строку в каждой партиции)

Потом необходимо пробежаться по всей партиции и найти: какие транзации были в интервале от "самая первая транзакция + 1 секунда" до "самая первая транзакция + 72 часа"

Затем в каждой партиции посчитать сколько было таких транзакций

И, наконец, взять из этих двух чисел (так как пользователя всего два в данном примере) среднее

Чисто технически не могу понять как такое реализовать, может быть есть кто знает, как такое можно сделать или может быть вообще есть логика попроще той, что я обозначил. Заранее спасибо 🙏

Если необходимо могу сбросить скрипт для создания и наполнения такой таблицы:
CREATE TABLE Transactions
(
transaction_ts TIMESTAMP,
user_id int,
transaction_id int,
item varchar (30)
);

INSERT INTO Transactions VALUES ('06/18/2010 10:00:00',1, 11, 'oil-1');
INSERT INTO Transactions VALUES ('06/19/2010 10:00:00',1, 12, 'oil-2');
INSERT INTO Transactions VALUES ('06/20/2010 10:00:00',1, 13, 'oil-3');
INSERT INTO Transactions VALUES ('06/21/2010 10:10:00',1, 14, 'oil-4');

INSERT INTO Transactions VALUES ('06/1/2010 10:00:00',2, 21, 'beer-1');
INSERT INTO Transactions VALUES ('06/2/2010 10:00:00',2, 22, 'beer-2');
INSERT INTO Transactions VALUES ('06/2/2010 10:30:00',2, 23, 'beer-3');
INSERT INTO Transactions VALUES ('06/3/2010 10:00:00',2, 24, 'beer-4');
INSERT INTO Transactions VALUES ('06/4/2010 10:10:00',2, 25, 'beer-5');
5 июн 21, 15:40    [22331766]     Ответить | Цитировать Сообщить модератору
 Re: Не сложная, но тяжелая задачка с партициями  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
Схематично:

SELECT t1.*, SUM(t2.ts <= t1.ts +INTERVAL 72 HOUR) cnt
FROM table t1 
JOIN table t2 USING (userid)
WHERE t2.ts>t1.ts
GROUP BY t2.*


Вместо звёзд подставить список нужных полей.

Сообщение было отредактировано: 5 июн 21, 19:38
5 июн 21, 19:46    [22331802]     Ответить | Цитировать Сообщить модератору
 Re: Не сложная, но тяжелая задачка с партициями  [new]
ab.tmn
Member

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

а какие таблицы тут предполагалось соединять с помощью JOIN? По сути все данные лежат в одной таблице
5 июн 21, 21:23    [22331823]     Ответить | Цитировать Сообщить модератору
 Re: Не сложная, но тяжелая задачка с партициями  [new]
Gluck99
Member

Откуда: Оттуда
Сообщений: 1155
ab.tmn
а какие таблицы тут предполагалось соединять с помощью JOIN? По сути все данные лежат в одной таблице
Если требуется, таблицу можно джойнить саму к себе, это не запрещено.
6 июн 21, 10:30    [22331865]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить