从星期日开始的 Linux 时间戳按周间隔进行 MySQL 分组

lvdongjie 阅读:99 2024-12-31 21:38:35 评论:0

我有一个已加入的用户表,跟踪他们加入时的时间戳的列是 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

参见 thisthis .

作为奖励,此技术在计算任何 unixtimestamp 的日历周时会尊重您本地的时区。


标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号