Блог

Главная - Вопросы по программированию - Убедитесь, что столбец временной метки (событие) происходит X раз в течение периода Y

Убедитесь, что столбец временной метки (событие) происходит X раз в течение периода Y

#postgresql

#postgresql

Вопрос:

Скрипка по этой ссылке.

У меня есть таблица с идентификатором пользователя и action_time: Schema (PostgreSQL v13)

 CREATE TABLE actions(
  action_time timestamp,
  user_id int
  );
  
insert into actions values ('2015-01-20 01:00',1);
insert into actions values ('2015-01-01 01:00',1);
insert into actions values ('2015-01-10 01:00',1);
insert into actions values ('2015-01-12 01:00',1);
insert into actions values ('2015-01-16 01:00',1);
insert into actions values ('2015-01-23 01:00',1);
insert into actions values ('2015-02-20 01:00',1);
insert into actions values ('2015-03-20 01:00',1);
insert into actions values ('2015-05-20 01:00',1);
insert into actions values ('2015-06-20 01:00',1);
insert into actions values ('2015-01-20 01:00',2);
insert into actions values ('2015-03-20 01:00',2);
insert into actions values ('2015-04-20 01:00',2);
insert into actions values ('2015-05-20 01:00',2);
insert into actions values ('2015-05-21 01:00',2);
insert into actions values ('2015-05-21 01:00',2);
insert into actions values ('2015-05-23 01:00',2);

Я хочу проверить, выполнял ли пользователь хотя бы одно действие каждый месяц в течение 6 месяцев.

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

 SELECT date_trunc('month',action_time) AS month_parth, user_id,COUNT(*) AS 
monthly_actions FROM actions
GROUP BY month_parth,user_id
ORDER BY user_id,month_parth ASC;

И получите следующее:

Запрос # 1

 SELECT date_trunc('month',action_time) AS month_parth, user_id,COUNT(*) AS monthly_actions FROM actions
GROUP BY month_parth,user_id
ORDER BY user_id,month_parth ASC;
month_parth user_id monthly_actions
2015-01-01T00:00:00.000Z 1 6
2015-02-01T00:00:00.000Z 1 1
2015-03-01T00:00:00.000Z 1 1
2015-05-01T00:00:00.000Z 1 1
2015-06-01T00:00:00.000Z 1 1
2015-01-01T00:00:00.000Z 2 1
2015-03-01T00:00:00.000Z 2 1
2015-04-01T00:00:00.000Z 2 1
2015-05-01T00:00:00.000Z 2 4

User_id 1 явно выполняет по крайней мере одно действие в месяц (достигая максимума в 6 января), но у 2 есть некоторые пробелы.

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

Желаемым результатом может быть простой фиктивный столбец с TRUE / FALSE для пользователей, которые выполняют хотя бы одно действие в месяц в течение 6 месяцев.

Ответ №1:

Вот запрос, в котором one_action_per_month_during_next_6_months является логическим значением для каждого идентификатора пользователя и для каждого месяца, что является истинным, если пользователь выполнил хотя бы одно действие в ближайшие месяцы :

 WITH list AS
(
SELECT date_trunc('month',action_time) AS month_parth
     , user_id, COUNT(*) AS monthly_actions
  FROM actions
 GROUP BY month_parth,user_id
)
SELECT user_id
     , month_parth
     , monthly_actions
     , count(*) FILTER (WHERE monthly_actions >=1) OVER (PARTITION BY user_id ORDER BY month_parth RANGE BETWEEN CURRENT ROW AND '6 months' FOLLOWING) = 6 AS one_action_per_month_during_next_6_months
  FROM list AS l
 ORDER BY user_id,month_parth ASC

результаты теста в dbfiddle

Комментарии:

1. Спасибо! Все еще немного сбит с толку, похоже, что предложенный вами запрос возвращает true только в том месяце, в котором у него было => 6 действий, в идеале, мне все равно, сколько действий в месяц, если есть хотя бы одно действие, тогда я хочу пометить этого пользователя, вывод может быть простоодна строка на идентификатор пользователя, не уверен, почему это так = 6

2. Вы неправильно поняли запрос, который я вам предлагаю. WITH list AS (...) Начиная с вашего запроса, который находится в инструкции, для каждой результирующей строки для каждого идентификатора пользователя и за месяц я подсчитываю количество строк (a), которые являются частью окна этой строки (b), для которых monthly_actions > = 1 (c), чей month_parth находится в диапазоне между month_parth изстрока и 6 следующих месяцев . Вы можете протестировать мой запрос на какой-нибудь более большой выборке данных в dbfiddle и сообщить мне, не соответствует ли он вашим ожиданиям, если да.

3. Большое спасибо, что нашли время объяснить это @Eduoard

Ответ №2:

 select user_id, count(cnt) >= 6 six_months from
(
 select user_id, date_trunc('month', action_time) monthperiod, count(*) cnt
 from actions
 group by user_id, monthperiod
) t
group by user_id;

Кстати, у пользователя 1 нет 6 месяцев хотя бы с одним действием, поскольку он пропускает апрель.

Метки: ,