MySQL จัดกลุ่มตามช่วงเวลาสัปดาห์บนการประทับเวลา Linux เริ่มตั้งแต่วันอาทิตย์

นี่คือสิ่งที่คุณต้องการ นิพจน์นี้ใช้ค่า 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 ในนิพจน์

ดูสิ่งนี้ และ สิ่งนี้

โบนัสพิเศษคือ เทคนิคนี้จะยึดตามเขตเวลาท้องถิ่นของคุณเมื่อค้นหาสัปดาห์ปฏิทินของการประทับเวลายูนิกซ์ใดๆ

รหัสของฉันมาจาก O. Jones ด้านล่างคือสิ่งที่ฉันลอง:

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 เป็นวันอาทิตย์

ด้านล่างฉันแทนที่ 2nd 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>

และได้รับผลลัพธ์เดียวกัน

BTW,weekday() เริ่มตั้งแต่วันจันทร์:

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>