MySQL: จัดกลุ่มข้อมูล Login ต่อชั่วโมง และหาผู้ใช้ Login ล่าสุดแต่ละชั่วโมง

เมื่อต้องการจัดกลุ่มข้อมูล login ต่อชั่วโมง และดึงเฉพาะรายการล่าสุด (latest) ในแต่ละชั่วโมง สามารถใช้ Subquery กับ MAX(UNIX_TIMESTAMP()) และ JOIN กลับมา

รูปแบบคำสั่ง

SELECT t1.*
FROM (
    SELECT MAX(UNIX_TIMESTAMP(datetime_col)) AS max_ts
    FROM table_name
    GROUP BY HOUR(datetime_col)
) AS latest_per_hour
JOIN table_name t1 
  ON UNIX_TIMESTAMP(t1.datetime_col) = latest_per_hour.max_ts
WHERE DATE(t1.datetime_col) = 'target_date';

ตัวอย่างการใช้งาน

1. สร้างตารางและเพิ่มข้อมูล Login

CREATE TABLE getLatestHour (
    UserId INT,
    UserName VARCHAR(20),
    UserLoginDateTime DATETIME
);

INSERT INTO getLatestHour VALUES
(100, 'John', '2019-02-04 10:55:51'),
(101, 'Larry', '2019-02-04 12:30:40'),
(102, 'Carol', '2019-02-04 12:40:46'),
(103, 'David', '2019-02-04 12:44:54'),
(104, 'Bob', '2019-02-04 12:47:59');

ข้อมูลในตาราง:

+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime   |
+--------+----------+---------------------+
|    100 | John     | 2019-02-04 10:55:51 |
|    101 | Larry    | 2019-02-04 12:30:40 |
|    102 | Carol    | 2019-02-04 12:40:46 |
|    103 | David    | 2019-02-04 12:44:54 |
|    104 | Bob      | 2019-02-04 12:47:59 |
+--------+----------+---------------------+

2. หา Login ล่าสุดแต่ละชั่วโมง ในวันที่ 2019-02-04

SELECT tbl1.*
FROM (
    SELECT MAX(UNIX_TIMESTAMP(UserLoginDateTime)) AS m1
    FROM getLatestHour
    GROUP BY HOUR(UserLoginDateTime)
) AS var1
JOIN getLatestHour tbl1 
  ON UNIX_TIMESTAMP(tbl1.UserLoginDateTime) = var1.m1
WHERE DATE(tbl1.UserLoginDateTime) = '2019-02-04';

ผลลัพธ์:

+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime   |
+--------+----------+---------------------+
|    100 | John     | 2019-02-04 10:55:51 |
|    104 | Bob      | 2019-02-04 12:47:59 |
+--------+----------+---------------------+

คำอธิบาย

• ชั่วโมง 10: มีแค่ John → John เป็นล่าสุด
• ชั่วโมง 12: มี Larry, Carol, David, Bob → Bob ล่าสุด (12:47:59)
• คำสั่งคืน 2 แถว: ล่าสุดของชั่วโมง 10 และ 12

วิธีทางเลือก: ใช้ ROW_NUMBER() (MySQL 8.0+)

WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY HOUR(UserLoginDateTime) ORDER BY UserLoginDateTime DESC) AS rn
    FROM getLatestHour
    WHERE DATE(UserLoginDateTime) = '2019-02-04'
)
SELECT UserId, UserName, UserLoginDateTime
FROM ranked
WHERE rn = 1;

วิธีนี้อ่านง่ายกว่า ประสิทธิภาพดีกว่า และรองรับ tie-breaking ได้ชัดเจน

วิธีทางเลือก: Correlated Subquery (MySQL เก่า)

SELECT *
FROM getLatestHour g1
WHERE DATE(UserLoginDateTime) = '2019-02-04'
  AND UserLoginDateTime = (
    SELECT MAX(UserLoginDateTime)
    FROM getLatestHour g2
    WHERE HOUR(g2.UserLoginDateTime) = HOUR(g1.UserLoginDateTime)
      AND DATE(g2.UserLoginDateTime) = '2019-02-04'
  );

สรุป

แนะนำใช้ ROW_NUMBER() สำหรับ MySQL 8.0+ เพื่อความชัดเจนและประสิทธิภาพ