Группировка MySQL по недельному интервалу на временных метках Linux, начиная с воскресенья

У меня есть таблица пользователей, которые присоединились, и столбец, в котором отслеживается отметка времени, когда они присоединились, является отметкой времени UNIX.

Я хочу сгруппировать их по неделям в секундах, 604800, но я наткнулся на препятствие. Другие поисковые запросы используют неделю MySQL, но это не то, что мне нужно, поскольку эти недели не всегда полны в конце года и меняются в зависимости от дня начала.

Запрос на группировку по неделям:

SELECT 
    COUNT(member_id) as new_members,
    MAX(joined) as last_joined,
    MIN(joined) as first_joined,
    YEAR(FROM_UNIXTIME(joined)) AS yr,
    MONTH(FROM_UNIXTIME(joined)) AS mn,
    WEEK(FROM_UNIXTIME(joined)) AS wk
FROM members
WHERE member_group_id NOT IN (2, 4, 7) 
GROUP BY `yr`,`mn`,`wk`
ORDER BY new_members DESC

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

Я пытался FLOOR( joined / 604800 ) AS weekno, но это неточно, так как он начинается с самой ранней или последней записи, и мне нужно, чтобы неделя начиналась в воскресенье, например:

SELECT COUNT(member_id) as new_members, 
       MAX(joined) as last_joined, MIN(joined) as first_joined, 
       FLOOR( joined / 604800 ) AS weekno 
FROM `members` 
WHERE member_group_id NOT IN (2, 4, 7) 
GROUP BY `weekno` 
ORDER BY weekno DESC

Есть ли у кого-нибудь советы?

Примеры данных, которые я ищу

member_id | joined
1         | 1578182420
2         | 1578182430
3         | 1578182500
4         | 1578183400
5         | 1576082400
6         | 1576082410
7         | 1576082420

Результат:

new_members | last_joined | first_joined | week_start
4           | 1578183400  | 1578181400   | 1578182400
3           | 1576082420  | 1576082400   | 1577577600

mysql sql date datetime group-by
person Ice76    schedule 06.01.2020    source источник
comment
Вы пробовали преобразовать метку времени в дату в формате UTC, а затем использовать функции даты?   -  person fubar    schedule 07.01.2020
comment
@fubar да, но функции даты, которые группируются по неделям, обычно заканчивают неделю раньше в конце года и не всегда запускаются в воскресенье в начале нового года.   -  person Ice76    schedule 07.01.2020
comment
Предоставьте образцы данных и ожидаемые результаты, чтобы вы могли получить более точные ответы.   -  person GMB    schedule 07.01.2020
comment
@GMB Добавил то, что я ищу, но даты присоединения участников насчитывают несколько лет   -  person Ice76    schedule 07.01.2020

Ответы (2)


arrow_upward
4
arrow_downward

Вот что тебе нужно. Это выражение принимает любое значение unixtimestamp и преобразует его в значение DATETIME, которое соответствует полуночи воскресенья недели, содержащей вашу unixtimestamp.

FROM_DAYS(TO_DAYS(FROM_UNIXTIME(unixtimestamp)) - 
      MOD(TO_DAYS(FROM_UNIXTIME(unixtimestamp)) -1, 7))

Так что этот запрос должен помочь вам.

SELECT COUNT(member_id) as new_members,
       MAX(joined) as last_joined,
       MIN(joined) as first_joined,
       FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - 
             MOD(TO_DAYS(FROM_UNIXTIME(joined)) -1, 7) week_beginning
  FROM members
 WHERE member_group_id NOT IN (2, 4, 7) 
 GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - 
             MOD(TO_DAYS(FROM_UNIXTIME(joined)) -1, 7)
 ORDER BY new_members DESC

Мне нравится использовать эту сохраненную функцию для этой цели. Когда вы его используете, легче писать и читать ваши запросы.

DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_SUNDAY$$
CREATE
  FUNCTION TRUNC_SUNDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding Sunday'
  RETURN FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -1, 7))$$

Если вы используете сохраненную функцию, вы можете написать свой запрос следующим образом (https://www.db-fiddle.com/f/cbtf9rueAvtFNUxE1PS387/0)

SELECT COUNT(member_id) as new_members,
       MAX(joined) as last_joined,
       MIN(joined) as first_joined,
       TRUNC_SUNDAY(FROM_UNIXTIME(joined)) week_beginning
  FROM members
 GROUP BY TRUNC_SUNDAY(FROM_UNIXTIME(joined))
 ORDER BY new_members DESC

Если вы хотите, чтобы ваши недели начинались с понедельника, используйте в выражении -2 вместо -1.

См. this и this.

В качестве бонуса этот метод учитывает ваш местный часовой пояс при вычислении календарной недели любой unixtimestamp.

person O. Jones    schedule 06.01.2020
comment
Спасибо, я это проверю. В любом случае, ссылки предоставляют мне очень хорошую информацию, особенно о моде YEARWEEK, который регулирует полные недели! - person Ice76; 07.01.2020
comment
Я просто получаю синтаксическую ошибку, связанную с моей версией MySQL ... сортирую ее. - person Ice76; 07.01.2020
comment
В моем коде было несколько опечаток, но я отредактировал свой ответ, чтобы исправить их. Извините за это, см. Db-fiddle. - person O. Jones; 07.01.2020

arrow_upward
0
arrow_downward

мой код исходит от О. Джонса, вот что я пробую:

mysql> SELECT COUNT(member_id) as new_members,
    ->        MAX(joined) as last_joined,
    ->        MIN(joined) as first_joined,
    ->        FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(TO_DAYS(FROM_UNIXTI
ME(joined)) -1, 7)) as week_beginning
    ->   FROM members
    ->  GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(TO_DAYS(FROM_UNI
XTIME(joined)) -1, 7))
    ->  ORDER BY new_members DESC
    -> ;
+-------------+-------------+--------------+----------------+
| new_members | last_joined | first_joined | week_beginning |
+-------------+-------------+--------------+----------------+
|           4 |  1578183400 |   1578182420 | 2020-01-05     |
|           3 |  1576082420 |   1576082400 | 2019-12-08     |
+-------------+-------------+--------------+----------------+
2 rows in set (0.07 sec)

2020-01-05 и 2020-01-05 - воскресенье

ниже я заменяю 2-й to_days () на weekday ():

mysql> SELECT COUNT(member_id) as new_members,
    -> MAX(joined) as last_joined,
    -> MIN(joined) as first_joined,
    -> FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(WEEKDAY(FROM_UNIXTIME(join
ed))+1,7)) as week_beginning
    -> FROM members
    -> GROUP BY FROM_DAYS(TO_DAYS(FROM_UNIXTIME(joined)) - MOD(WEEKDAY(FROM_UNIX
TIME(joined))+1,7))
    -> ORDER BY new_members DESC
    ->
    -> ;
+-------------+-------------+--------------+----------------+
| new_members | last_joined | first_joined | week_beginning |
+-------------+-------------+--------------+----------------+
|           4 |  1578183400 |   1578182420 | 2020-01-05     |
|           3 |  1576082420 |   1576082400 | 2019-12-08     |
+-------------+-------------+--------------+----------------+
2 rows in set (0.00 sec)

mysql>

и получите те же результаты.

Кстати, будний день () начинается с понедельника:

Monday    -> 0
Tuesday   -> 1
Wednesday -> 2
Thursday  -> 3
Friday    -> 4
Saturday  -> 5
Sunday    -> 6

to_days () начинаются с субботы, что подтверждается ниже:

mysql> select to_days('2020-1-4');
+---------------------+
| to_days('2020-1-4') |
+---------------------+
|              737793 |
+---------------------+
1 row in set (0.00 sec)

mysql> select mod(to_days('2020-1-4'),7);
+----------------------------+
| mod(to_days('2020-1-4'),7) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql>
person MLKu    schedule 07.01.2020
comment
Спасибо за ваш ответ, но это копия ответа выше ... вы даже использовали тот же week_beginning, что и он (который я не назвал). - person Ice76; 07.01.2020