Grup MySQL berdasarkan interval minggu pada stempel waktu Linux mulai dari hari Minggu

Saya memiliki tabel pengguna yang telah bergabung dan kolom yang melacak stempel waktu ketika mereka bergabung adalah stempel waktu UNIX.

Saya ingin mengelompokkannya berdasarkan waktu berminggu-minggu dalam hitungan detik, 604800, tetapi saya menemui hambatan. Pencarian lainnya menggunakan minggu MySQL, tetapi bukan itu yang saya cari karena minggu-minggu tersebut tidak selalu penuh pada akhir tahun dan bervariasi tergantung pada hari awal.

Kueri untuk pengelompokan Minggu:

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

Saya ingin mengelompokkan pengguna saya berdasarkan stempel waktu mulai Minggu depan. Jadi, itu akan terjadi pada hari Minggu berikutnya, dan interval satu minggu mundur sampai saya kehabisan catatan.

Saya telah mencoba FLOOR( joined / 604800 ) AS weekno tetapi itu tidak akurat karena dimulai dari catatan paling awal atau terbaru, dan saya memerlukan minggu untuk dimulai pada hari Minggu, seperti:

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

Apakah ada yang punya tips?

Contoh data yang saya cari

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

Hasil:

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 sumber
comment
Sudahkah Anda mencoba mengonversi stempel waktu menjadi tanggal UTC, lalu menggunakan fungsi tanggal?   -  person fubar    schedule 07.01.2020
comment
@fubar ya, tapi fungsi tanggal yang dikelompokkan berdasarkan minggu cenderung mengakhiri minggu lebih awal di akhir tahun, dan tidak selalu dimulai pada hari Minggu di awal tahun baru.   -  person Ice76    schedule 07.01.2020
comment
Harap berikan contoh data dan hasil yang diharapkan agar Anda bisa mendapatkan jawaban yang lebih akurat.   -  person GMB    schedule 07.01.2020
comment
@GMB Menambahkan apa yang saya cari, tetapi anggota yang bergabung sudah ada sejak bertahun-tahun yang lalu   -  person Ice76    schedule 07.01.2020

Jawaban (2)


arrow_upward
4
arrow_downward

Inilah yang Anda inginkan. Ekspresi ini mengambil nilai unixtimestamp apa pun dan mengonversinya menjadi nilai DATETIME tengah malam pada hari Minggu dalam minggu yang berisi unixtimestamp Anda.

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

Jadi pertanyaan ini akan membantu Anda.

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

Saya suka menggunakan fungsi tersimpan ini untuk tujuan tersebut. Lebih mudah untuk menulis dan membaca pertanyaan Anda saat Anda menggunakannya.

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))$$

Jika Anda menggunakan fungsi tersimpan, Anda dapat menulis kueri seperti ini (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

Jika Anda ingin minggu Anda dimulai pada hari Senin, gunakan -2 dan bukan -1 dalam ekspresi.

Lihat ini dan ini.

Sebagai bonus, teknik ini menghormati zona waktu lokal Anda saat menentukan minggu kalender dari stempel waktu unix mana pun.

person O. Jones    schedule 06.01.2020
comment
Terima kasih, saya akan mengujinya. Apa pun yang terjadi, tautan tersebut memberi saya informasi yang sangat bagus, khususnya tentang YEARWEEK mod yang mengatur minggu lengkap! - person Ice76; 07.01.2020
comment
Saya baru saja mendapatkan kesalahan sintaksis terkait dengan versi MySQL saya... memilahnya. - person Ice76; 07.01.2020
comment
Saya memiliki beberapa kesalahan ketik dalam kode saya, tapi saya mengedit jawaban saya untuk memperbaikinya. Maaf tentang itu, Lihat db-fiddle. - person O. Jones; 07.01.2020

arrow_upward
0
arrow_downward

kode saya berasal dari O. Jones, di bawah ini yang saya coba:

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)

05-01-2020 dan 05-01-2020 adalah hari Minggu

di bawah ini saya ganti to_days() ke-2 dengan 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>

dan mendapatkan hasil yang sama.

BTW, hari kerja() dimulai dari Senin:

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

to_days() dimulai dari hari Sabtu, berikut buktinya:

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
Terima kasih atas tanggapan Anda, tapi ini adalah salinan jawaban di atas... Anda bahkan menggunakan week_beginning yang sama seperti dia (yang tidak saya sebutkan). - person Ice76; 07.01.2020