Nhóm mysql theo phạm vi ngày

Question

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

Nội dung chính Show

Dữ liệu ví dụ
Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ
Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ
Nhóm theo khoảng thời gian 15 phút - Làm tròn đến gần nhất
Làm cách nào để nhóm theo tháng kể từ ngày trong MySQL?
Làm cách nào để tìm nạp dữ liệu giữa hai ngày trong sql?
Nhóm theo trong MySQL là gì?
Làm thế nào để giữa làm việc trong MySQL?

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

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;

2 để tính chênh lệch phút giữa login_datetime và MySQL begin_datetime '1000-01-01 00. 00. 00'

Chia chênh lệch phút tính được cho 15

Gọi 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;

3 để trả về giá trị số nguyên nhỏ nhất lớn hơn hoặc bằng kết quả phép chia

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;

1

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,
    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

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;

2 để tính chênh lệch phút giữa login_datetime và MySQL begin_datetime '1000-01-01 00. 00. 00'

Chia chênh lệch phút tính được cho 15

Gọi 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;

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ất

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

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;

4FLOOR' để có khoảng thời gian 15 phút gần nhất

________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ốn

Ví 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

Làm cách nào để nhóm theo tháng kể từ ngày trong MySQL?

Để nhóm dữ liệu theo tháng trong MySQL, hãy sử dụng các hàm MONTH() và YEAR() . Hàm MONTH() trích xuất số của tháng (1 cho tháng 1, 2 cho tháng 2, v.v. ) từ ngày. Hàm YEAR() trích xuất năm từ ngày.

Làm cách nào để tìm nạp dữ liệu giữa hai ngày trong sql?

Cú pháp. CHỌN * TỪ TABLE_NAME WHERE DATE_TIME_COLUMN GIỮA 'STARTING_DATE_TIME' VÀ 'ENDING_DATE_TIME';

Nhóm theo trong MySQL là gì?

Câu lệnh GROUP BY nhóm các hàng có cùng giá trị thành các hàng tóm tắt , chẳng hạn như "tìm số lượng khách hàng ở mỗi quốc gia". Câu lệnh GROUP BY thường được sử dụng với các hàm tổng hợp ( COUNT() , MAX() , MIN() , SUM() , AVG() ) để nhóm tập hợp kết quả theo một hoặc nhiều cột.

Làm thế nào để giữa làm việc trong MySQL?

Toán tử BETWEEN của MySQL . Các giá trị có thể là số, văn bản hoặc ngày tháng. Toán tử BETWEEN đã bao hàm. giá trị bắt đầu và kết thúc được bao gồm. selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

programming mysql

Nhóm mysql theo phạm vi ngày

Dữ liệu ví dụ

Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ​

Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ​

Nhóm theo khoảng thời gian 15 phút - Làm tròn đến gần nhất​

Làm cách nào để nhóm theo tháng kể từ ngày trong MySQL?

Làm cách nào để tìm nạp dữ liệu giữa hai ngày trong sql?

Nhóm theo trong MySQL là gì?

Làm thế nào để giữa làm việc trong MySQL?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội

Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ

Nhóm theo khoảng thời gian 15 phút - Làm tròn ngày/giờ

Nhóm theo khoảng thời gian 15 phút - Làm tròn đến gần nhất