Встала задача выяснить играл ли игрок в течении последних недель и в каких неделях играл, т.е. важен сам факт, что играл в течении недели, и запомнить результат.
Дано: таблица с играми 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. Может все что написано выше - полная хрень и надо делать иначе?