Блог

У меня есть таблица Oracle DB, которая содержит некоторую информацию о пользователях игрового веб-сайта. Каждый игрок имеет уникальный идентификатор gamer_id и получает новый идентификатор session_id, назначенный для каждой игровой сессии. Игровая сессия обычно длится несколько недель, и в таблице содержится запись в день для игровой сессии на одного игрока. Игрок может или не может получать бонусные баллы ежедневно, и эта информация записывается в колонке bonus_points.

Я хочу идентифицировать игроков, которые собирали бонусные баллы каждые 7 дней, начиная с session_start_dt_tm и заканчивая session_end_dt_tm. Так, например, если session_start_dt_tm для игрока был JUL-02-2020, я сначала хотел бы определить, был ли у них какой-либо день с июля-02-2020 по ИЮЛЬ-08-2020 включительно, когда были собраны бонусные баллы. Допустим, у этого игрока были бонусные баллы 06 июля 2020 года, в этом случае их следующие 7 дней будут перезапущены с 06 июля 2020 года и закончатся 12 июля 2020 года и так далее, пока не будет достигнуто значение session_end_dt_tm или, если сеанс все еще активен, тогда значение session_end_dt_tm будет равно нулю.

Наконец, я хотел бы суммировать количество 7-дневных сегментов, сгруппированных для каждого игрока, где у них были бонусные очки.

Пример данных:

 gamer_id,session_id,session_start_dt_tm,session_calendar_day,bonus_points,session_end_dt_tm
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-02,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-03,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-04,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-05,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-06,5,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-07,11,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-08,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-09,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-10,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-11,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-12,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-13,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-14,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-15,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-16,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-17,4,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-18,1,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-19,1,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-20,5,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-21,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-22,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-23,8,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-24,6,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-25,0,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-26,3,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-27,1,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-28,1,2020-07-29 20:21:18
1h22-hd3u-77-gue8,7119,2020-07-02 17:30:21,2020-07-29,2,2020-07-29 20:21:18
77df-jgf9-47-q10b,819472,2020-09-24 03:00:11,2020-09-24,3,null
77df-jgf9-47-q10b,819472,2020-09-24 03:00:11,2020-09-25,7,null
77df-jgf9-47-q10b,819472,2020-09-24 03:00:11,2020-09-26,2,null
  

Я думаю, что хорошим местом для начала будут аналитические функции Oracle. Что вы пробовали до сих пор, и есть ли у вас какие-то конкретные вопросы, связанные с тем, что вы пробовали?