Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
 
Фильтр авторов:    показать 
  скрыть
  Ответ в темуСоздание новой темыСоздание опроса

> Посчитать активность в игре по неделям
kaww  
 ۩  [x] Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1787
Пользователь №: 20757
На форуме: 7 лет, 5 месяцев, 18 дней
Карма: 187




Всем привет.
Встала задача выяснить играл ли игрок в течении последних недель и в каких неделях играл, т.е. важен сам факт, что играл в течении недели, и запомнить результат.
Дано: таблица с играми game (id, ended_stamp) и таблица с участниками participation (player_id, game_id).

Решил, что буду получать результат в виде битовой последовательности, где установленный бит означает, что игрок в эту неделю играл.
1. Нужно сгруппировать игры по игроку и по номеру недели
SELECT
p.player_id,
WEEK(g.ended_stamp) AS week_num
FROM games AS g
INNER JOIN participation AS p ON p.game_id=g.id
GROUP BY p.player_id, week_num

2. Но при таком подходе на гранце года возникнет проблема. Год не всегда заканчивается в последний день недели. И в таком случае, чтобы неделя была всегда длиной 7 дней. Нужно взять дни из первой (нулевой) недели следующего года и добавить к предыдущему
IF(WEEK(g.ended_stamp) = 0, WEEK(CONCAT(YEAR(g.ended_stamp) - 1, '-12-31')), WEEK(g.ended_stamp))

Получим запрос
SELECT
p.player_id,
IF(WEEK(g.ended_stamp) = 0, WEEK(CONCAT(YEAR(g.ended_stamp) - 1, '-12-31')), WEEK(g.ended_stamp)) AS week_num
FROM games AS g
INNER JOIN participation AS p ON p.game_id=g.id
GROUP BY p.player_id, week_num

3. Чтобы определить позицию бита для каждой из недели, сгенерируем последовательность из интересующих нас недель и позиции, запросом вида:
SQL
SELECT IF(
WEEK(NOW())=0,
WEEK(CONCAT(YEAR(NOW()) - 1), '-12-31'),
WEEK(NOW()))AS week_num,
0 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 1 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 1 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 1 WEEK)) AS week_num,
1 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 2 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 2 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 2 WEEK)) AS week_num,
2 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
3 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
4 AS pos

Этот запрос так же учитывает обстоятельство вывленное в предыдущем пункте касаемо границ года.
4. Сложив эти 2 запроса вместе, а так же приняв во внимание тот факт, что нас интересуют только последние 5 недель из истории игр получим запрос вида:
SQL
SELECT
weeks.week_num,
weeks.pos,
_p.player_id,
FROM (
SELECT IF(
WEEK(NOW())=0,
WEEK(CONCAT(YEAR(NOW()) - 1), '-12-31'),
WEEK(NOW()))AS week_num,
0 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 1 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 1 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 1 WEEK)) AS week_num,
1 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 2 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 2 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 2 WEEK)) AS week_num,
2 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
3 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
4 AS pos) AS weeks
INNER JOIN (
SELECT
p.player_id,
IF(WEEK(g.ended_stamp) = 0, WEEK(CONCAT(YEAR(g.ended_stamp) - 1, '-12-31')), WEEK(g.ended_stamp)) AS week_num
FROM games AS g
INNER JOIN participation AS p ON p.game_id=g.id
GROUP BY p.player_id, week_num
WHERE g.ended_stamp >= DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY) - INTERVAL 5 WEEK) AS _p ON weeks.week_num=_p.week_num

5. Дальше нужно оперируя позицией установить нужные биты и получить таки карту недель в виде целого числа. Мне не пришло в голову ничего лучше чем возвести 10 в степень pos и сложить получившееся для каждого пользователя. Таким образом получаем число, которое можно считать двоичным представлением нашей мапы. И осталось только конвертировать ее в десятичное представление:
SQL

SELECT
player_id,
CONV(SUM(POW(10, pos)), 2, 10) AS map
FROM (
SELECT
weeks.week_num,
weeks.pos,
_p.player_id,
FROM (
SELECT IF(
WEEK(NOW())=0,
WEEK(CONCAT(YEAR(NOW()) - 1), '-12-31'),
WEEK(NOW()))AS week_num,
0 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 1 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 1 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 1 WEEK)) AS week_num,
1 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 2 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 2 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 2 WEEK)) AS week_num,
2 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
3 AS pos
UNION SELECT IF(
WEEK(NOW() - INTERVAL 3 WEEK)=0,
WEEK(CONCAT(YEAR(NOW() - INTERVAL 3 WEEK) - 1, '-12-31')),
WEEK(NOW() - INTERVAL 3 WEEK)) AS week_num,
4 AS pos) AS weeks
INNER JOIN (
SELECT
p.player_id,
IF(WEEK(g.ended_stamp) = 0, WEEK(CONCAT(YEAR(g.ended_stamp) - 1, '-12-31')), WEEK(g.ended_stamp)) AS week_num
FROM games AS g
INNER JOIN participation AS p ON p.game_id=g.id
GROUP BY p.player_id, week_num
WHERE g.ended_stamp >= DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY) - INTERVAL 5 WEEK) AS _p ON weeks.week_num=_p.week_num) AS _t
GROUP BY player_id


В общем из этого вытекает 2 вопроса:
1. Как оптимальнее чем CONV(SUM(POW(10, pos)), 2, 10) сгенерить битовую последовательность
2. Может все что написано выше - полная хрень и надо делать иначе?
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Oyeme  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Reality is wrong. Dreams are for real
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1725
Пользователь №: 16955
На форуме: 8 лет, 4 месяца, 24 дня
Карма: 96




С годами у тебя будет проблема поэтому тебе нужно групировать неделя и год
Используй YEARWEEK

https://dev.mysql.com/doc/refman/5.5/en/dat...nction_yearweek

Решение в виде

IF(WEEK(g.ended_stamp) = 0, WEEK(CONCAT(YEAR(g.ended_stamp) - 1, '-12-31')), WEEK(g.ended_stamp))


Выглядит как хаком.

Вообще выложи сюда чтобы можно было протестировать http://sqlfiddle.com/


--------------------
Programming: Private lessons via skype £45/h

Частные уроки в Лондоне / удаленно по skype.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
kaww  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1787
Пользователь №: 20757
На форуме: 7 лет, 5 месяцев, 18 дней
Карма: 187




Цитата (Oyeme @ 30.01.2017 - 19:42)
Решение в виде Выглядит как хаком.

Это несколько для другого - длина недели на границе года.
Что касается YEARWEEK Изначально было так, но пока писал пост, понял, что проблемы с неделями нет вовсе. Т.е. пять недель - это всегда не пересекающиеся множества, т.к. ,в году больше чем 5 недель. Изначально добавил год, т.к. думал, что нужно сортировать по week_num, но это оказалось лишним
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Valick  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 5662
Пользователь №: 35718
На форуме: 4 года, 8 месяцев
Карма: 176




Цитата (kaww @ 30.01.2017 - 18:27)
Нужно взять дни из первой (нулевой) недели следующего года и добавить к предыдущему

нет, сначала необходимо получить искомый интервал (подзапрос в конструкции FROM), и далее уже работать с этой таблицей, абстрагировавшись от года.


--------------------
wmr - R281553014107
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
kaww  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1787
Пользователь №: 20757
На форуме: 7 лет, 5 месяцев, 18 дней
Карма: 187




Valick, что такое искомый интервал в твоем понимании? Что-то никак не выходит понять о чем ты. Искомый интервал - это пять недель - g.ended_stamp >= DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY) - INTERVAL 5 WEEK)
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Valick  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 5662
Пользователь №: 35718
На форуме: 4 года, 8 месяцев
Карма: 176




kaww, ой мама)) я теперь уже даже не знаю в каком понимании должен быть интервал))

но в моём понимании например сегодня понедельник 30.01.2017, последние пять недель это интервал 02.01.2017 - 30.01.2017


--------------------
wmr - R281553014107
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
  Быстрый ответ
Информация о Госте
Введите Ваше имя
Кнопки кодов
Для вставки цитаты, выделите нужный текст и
НАЖМИТЕ СЮДА
Введите сообщение
Смайлики
:huh:  :o  ;) 
:P  :D  :lol: 
B)  :rolleyes:  <_< 
:)  :angry:  :( 
:unsure:  :blink:  :ph34r: 
     
Показать всё

Опции сообщения  Включить смайлики?
 Включить подпись?
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темы Ответ в темуСоздание новой темыСоздание опроса