从星期日开始的 Linux 时间戳按周间隔进行 MySQL 分组
我有一个已加入的用户表,跟踪他们加入时的时间戳的列是 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
请您参考如下方法:
这就是你想要的。此表达式采用任何 unixtimestamp 值并将其转换为包含 unixtimestamp 的星期日午夜的 DATETIME 值。
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
。
作为奖励,此技术在计算任何 unixtimestamp 的日历周时会尊重您本地的时区。
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。