Блог

Главная - Вопросы по программированию - выберите пользователей, появляющихся не реже одного раза в неделю в течение нескольких недель

выберите пользователей, появляющихся не реже одного раза в неделю в течение нескольких недель

#mysql #sql

#mysql #sql

Вопрос:

Предполагая, что у вас есть простая таблица, подобная следующей:

  --------- --------------------- 
| user_id | activity_date       |
 --------- --------------------- 
|   23672 | 2011-04-26 14:53:02 |
|   15021 | 2011-04-26 14:52:20 |
|   15021 | 2011-04-26 14:52:09 |
|   15021 | 2011-04-26 14:51:51 |
|   15021 | 2011-04-26 14:51:38 |
|    6241 | 2011-04-26 14:51:12 |
|     168 | 2011-04-26 14:51:12 |
...

Как вы можете выбрать набор идентификаторов пользователя, которые появляются не реже одного раза в неделю в течение последних 4 недель?

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

1. Поскольку большинство необходимых здесь фильтров в некоторой степени зависят от базы данных, было бы полезно, если бы вы сообщили нам, какой тип (и версию) SQL server вы используете.

2. Что для вас значит «раз в неделю»? Допустим, я вошел в систему во вторник, 2011-04-12, и снова в четверг, 2011-04-21. Если «неделя» означает с понедельника по воскресенье, я регистрировался раз в неделю в течение двух недель. Если «неделя» означает семь дней между входами в систему, у меня их нет.

Ответ №1:

 select user_id, 
sum(if(activity_date between now() - interval 1 week and now(),1,0)) as this_week,
sum(if(activity_date between now() - interval 2 week and now() - interval 1 week,1,0)) as last_week,
sum(if(activity_date between now() - interval 3 week and now() - interval 2 week,1,0)) as two_week_ago,
sum(if(activity_date between now() - interval 4 week and now() - interval 3 week,1,0)) as three_week_ago
from activities
where activity_date >= now() - interval 4 week 
group by user_id
having this_week > 0 and last_week > 0 and two_week_ago > 0 and three_week_ago > 0

Ответ №2:

Здесь используется синтаксис Oracle, поэтому для MySQL может потребоваться некоторая очистка, но вы можете использовать серию подзапросов, чтобы выбрать из таблицы, выполнялась ли активность в каждую из 4 недель, затем суммировать итоги по пользователям, а затем выбирать только пользователей, у которых активность > 0 за каждую неделю.

 select user_id from 
{
  select user_id, SUM(in_week_0) sum_week_0,SUM(in_week_1) sum_week_1,SUM(in_week_2) sum_week_2,SUM(in_week_3) sum_week_3 from
  (
    select user_id, 
      activity_date, 
      CASE 
        WHEN activity_date < '01-MAY-11' AND activity_date >=  '24-APR-11' THEN 1
        ELSE 0
      END in_week_0,
      CASE 
        WHEN activity_date < '24-APR-11' AND activity_date >=  '17-APR-11' THEN 1
        ELSE 0
      END in_week_1,
      CASE 
        WHEN activity_date < '17-APR-11' AND activity_date >=  '10-APR-11' THEN 1
        ELSE 0
      END in_week_2,
      CASE 
        WHEN activity_date < '10-APR-11' AND activity_date >=  '03-APR-11' THEN 1
        ELSE 0
      END in_week_3
        from table
  )
  group by user_id
)
where sum_week_0 > 0 and sum_week_1 > 0 and sum_week_2 > 0 and sum_week_3 > 0;