Khi tổng hợp dữ liệu cho các thử nghiệm phân tách lưu lượng truy cập cao, các bên liên quan trong kinh doanh thường yêu cầu các nhà phân tích dữ liệu nhóm và báo cáo dữ liệu theo khoảng thời gian 15 phút. Nó không đủ chi tiết để nhóm dữ liệu hàng giờ vì các thử nghiệm phân tách khối lượng lớn cần được theo dõi chặt chẽ đối với bất kỳ vấn đề nghiêm trọng nào có thể có tác động tiêu cực đáng kể đến doanh số bán hàng hoặc các số liệu khác
Trong bài đăng hôm nay, chúng ta sẽ xem qua một số đoạn mã SQL để làm tròn các bản ghi Ngày/Giờ thành khoảng tăng 15 phút và nhóm chúng theo khoảng thời gian 15 phút
Nếu bạn đang sử dụng công cụ cơ sở dữ liệu Microsoft SQL Server, bạn có thể truy cập SQL Server - Nhóm dữ liệu theo khoảng thời gian 5, 10, 15, 20 hoặc 30 phút để kiểm tra các tập lệnh T-SQL
Dữ liệu ví dụ
create table login_logs [
account_id INT,
login_datetime DATETIME
];
insert into login_logs [account_id, login_datetime] values [1, '2022-03-02 02:05:02'];
insert into login_logs [account_id, login_datetime] values [2, '2022-03-02 02:09:07'];
insert into login_logs [account_id, login_datetime] values [3, '2022-03-02 02:14:07'];
insert into login_logs [account_id, login_datetime] values [4, '2022-03-02 02:16:07'];
insert into login_logs [account_id, login_datetime] values [5, '2022-03-02 02:27:07'];
insert into login_logs [account_id, login_datetime] values [6, '2022-03-02 02:28:07'];
insert into login_logs [account_id, login_datetime] values [7, '2022-03-02 02:30:00'];
insert into login_logs [account_id, login_datetime] values [8, '2022-03-02 02:36:02'];
insert into login_logs [account_id, login_datetime] values [9, '2022-03-02 02:40:07'];
insert into login_logs [account_id, login_datetime] values [10, '2022-03-02 02:46:17'];
insert into login_logs [account_id, login_datetime] values [11, '2022-03-02 02:54:27'];
insert into login_logs [account_id, login_datetime] values [12, '2022-03-02 02:58:10'];
Sử dụng DB-Fiddle để thực thi các tập lệnh SQL trên dữ liệu mẫu
Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ
Nếu doanh nghiệp muốn làm tròn từng giá trị login_datetime thành khoảng thời gian 15 phút, chúng ta có thể áp dụng các logic sau
- Sử dụng hàm
2 để tính chênh lệch phút giữa login_datetime và MySQL begin_datetime '1000-01-01 00. 00. 00'SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime; - Chia chênh lệch phút tính được cho 15
- Gọi hàm
3 để trả về giá trị số nguyên nhỏ nhất lớn hơn hoặc bằng kết quả phép chiaSET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime; - Nhân kết quả trần với 15 để có giá trị chênh lệch phút làm tròn
- Cuối cùng, thêm giá trị chênh lệch phút làm tròn vào MySQL begin_datetime để nhận các giá trị login_datetime làm tròn mới trong khoảng thời gian 15 phút
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
account_idlogin_datetimediffdiff_round_uplogin_datetime_15_min_interval12022-03-02 02. 05. 025376068455376068552022-03-02 02. 15. 0022022-03-02 02. 09. 075376068495376068552022-03-02 02. 15. 0032022-03-02 02. 14. 075376068545376068552022-03-02 02. 15. 0042022-03-02 02. 16. 075376068565376068702022-03-02 02. 30. 0052022-03-02 02. 27. 075376068675376068702022-03-02 02. 30. 0062022-03-02 02. 28. 075376068685376068702022-03-02 02. 30. 0072022-03-02 02. 30. 005376068705376068702022-03-02 02. 30. 0082022-03-02 02. 36. 025376068765376068852022-03-02 02. 45. 0092022-03-02 02. 40. 075376068805376068852022-03-02 02. 45. 00102022-03-02 02. 46. 175376068865376069002022-03-02 03. 00. 00112022-03-02 02. 54. 275376068945376069002022-03-02 03. 00. 00122022-03-02 02. 58. 105376068985376069002022-03-02 03. 00. 00
Giờ đây, bạn có thể nhóm dữ liệu đăng nhập tài khoản theo khoảng thời gian 15 phút
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval,
COUNT[DISTINCT [account_id]] AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins2022-03-02 02. 15. 0032022-03-02 02. 30. 0042022-03-02 02. 45. 0022022-03-02 03. 00. 003
Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ
Nếu doanh nghiệp muốn làm tròn từng giá trị login_datetime thành khoảng thời gian 15 phút, chúng ta có thể áp dụng các logic tương tự được mô tả ở trên và chỉ cần thay thế
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
3 bằng hàm SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
1SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
FLOOR[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_down,
DATE_ADD[@begin_datetime, Interval FLOOR[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
account_idlogin_datetimediffdiff_round_downlogin_datetime_15_min_interval12022-03-02 02. 05. 025376068455376068402022-03-02 02. 00. 0022022-03-02 02. 09. 075376068495376068402022-03-02 02. 00. 0032022-03-02 02. 14. 075376068545376068402022-03-02 02. 00. 0042022-03-02 02. 16. 075376068565376068552022-03-02 02. 15. 0052022-03-02 02. 27. 075376068675376068552022-03-02 02. 15. 0062022-03-02 02. 28. 075376068685376068552022-03-02 02. 15. 0072022-03-02 02. 30. 005376068705376068702022-03-02 02. 30. 0082022-03-02 02. 36. 025376068765376068702022-03-02 02. 30. 0092022-03-02 02. 40. 075376068805376068702022-03-02 02. 30. 00102022-03-02 02. 46. 175376068865376068852022-03-02 02. 45. 00112022-03-02 02. 54. 275376068945376068852022-03-02 02. 45. 00122022-03-02 02. 58. 105376068985376068852022-03-02 02. 45. 00
Giờ đây, bạn có thể nhóm dữ liệu đăng nhập tài khoản bằng cách làm tròn khoảng thời gian 15 phút
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT DATE_ADD[@begin_datetime, Interval FLOOR[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval,
COUNT[DISTINCT [account_id]] AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins2022-03-02 02. 00. 0032022-03-02 02. 15. 0032022-03-02 02. 30. 0032022-03-02 02. 45. 003
Nhóm theo khoảng thời gian 15 phút - Làm tròn đến gần nhất
Nếu doanh nghiệp muốn làm tròn từng giá trị login_date thành khoảng thời gian 15 phút gần nhất, chúng tôi có thể sử dụng các logic sau để đạt được điều đó
- Sử dụng hàm
2 để tính chênh lệch phút giữa login_datetime và MySQL begin_datetime '1000-01-01 00. 00. 00'SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime; - Chia chênh lệch phút tính được cho 15
- Gọi hàm
3 để trả về trả về phần còn lại của kết quả bước trên chia cho 1. Điều này sẽ cung cấp cho chúng tôi một số từ 0 đến 1 mà chúng tôi có thể sử dụng cho chúng tôi một chỉ báo gần nhấtSET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime; - Nếu giá trị recent_indicator lớn hơn hoặc bằng 0. 5, chúng tôi sẽ áp dụng hàm
4FLOOR' để có khoảng thời gian 15 phút gần nhấtSET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
________số 8
account_idlogin_datetimenearest_indicatorlogin_datetime_15_min_interval12022-03-02 02. 05. 020. 33332022-03-02 02. 00. 0022022-03-02 02. 09. 070. 60002022-03-02 02. 15. 0032022-03-02 02. 14. 070. 93332022-03-02 02. 15. 0042022-03-02 02. 16. 070. 06672022-03-02 02. 15. 0052022-03-02 02. 27. 070. 80002022-03-02 02. 30. 0062022-03-02 02. 28. 070. 86672022-03-02 02. 30. 0072022-03-02 02. 30. 000. 00002022-03-02 02. 30. 0082022-03-02 02. 36. 020. 40002022-03-02 02. 30. 0092022-03-02 02. 40. 070. 66672022-03-02 02. 45. 00102022-03-02 02. 46. 170. 06672022-03-02 02. 45. 00112022-03-02 02. 54. 270. 60002022-03-02 03. 00. 00122022-03-02 02. 58. 100. 86672022-03-02 03. 00. 00
Giờ đây, bạn có thể nhóm dữ liệu đăng nhập tài khoản theo khoảng thời gian 15 phút gần nhất
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT CASE
WHEN MOD[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins, 1] >= 0.5
THEN DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute]
ELSE DATE_ADD[@begin_datetime, Interval FLOOR[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute]
END AS login_datetime_15_min_interval,
COUNT[DISTINCT [account_id]] AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins2022-03-02 02. 00. 0012022-03-02 02. 15. 0032022-03-02 02. 30. 0042022-03-02 02. 45. 0022022-03-02 03. 00. 002Nhóm theo khoảng thời gian 5, 10, 20, 30 phút
Bạn có thể sử dụng bất kỳ đoạn mã nào ở trên và chỉ cần đặt giá trị
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
5 thành 5, 10, 20 hoặc 30 để nhóm Dữ liệu/Thời gian của bạn theo khoảng thời gian X phút mong muốnVí dụ: đặt biến
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;
SELECT account_id,
login_datetime,
TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] AS diff,
CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins AS diff_round_up,
DATE_ADD[@begin_datetime, Interval CEILING[TIMESTAMPDIFF[MINUTE, @begin_datetime, login_datetime] / @interval_mins] * @interval_mins minute] AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
5 thành 5 bằng cách sử dụng kỹ thuật làm tròn đến gần nhất, bạn có thể truy xuất kết quả đăng nhập tổng hợp trong khoảng thời gian 5 phút