So sánh thời gian trong sql
|
Ở bài viết Oracle so sánh thời gian ngày tháng – Oracle compare date time mình có chia sẻ về cách ta có thể so sánh thời gian trong Oracle. Nhưng trong quá trình triển khai thực thế, việc turning hệ thống thì thấy việc sử dụng các câu lệnh này sẽ có vấn đề về index trên cột này. Ta cùng phân tích và tối ưu câu lệnh SQL cho vấn đề này nhé. Show Đối với câu lệnh PL/SQL, cụ thể là Oracle thì hàm to_char(…) gây mất index đối với cột dữ liệu kiểu DATETIME. Tương tự với việc sử dụng toán tử LIKE cho kiểu dữ liệu VARCHAR hoặc NVARCHAR. Nhưng đổi lại việc sử dụng hàm TRUNC lại không làm mất index kiểu dữ liệu DATETIME. Để tối ưu câu lệnh phải so sánh thời gian ta tránh việc sử dụng hàm to_char(…) để tránh mất index column này. Mặc dù ta nhìn câu lệnh SQL khi turning seckhông được tường minh và dễ hiểu lắm. Đúng thôi, ta buộc phải đánh đổi, cuộc sống mà. Cách tối ưu khi so sánh ngày tháng– So sánh ngày: Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh: ví dụ: Cần lấy ngày tạo lớn hơn hoặc bằng ngày 06/04/2018
Turning: Ta có thể viết lại hàm này một chút để nó sẽ chạy nhanh hơn, tránh gây ảnh hưởng hiệu năng hệ thống. Như câu lệnh trên ta có thể thấy ta đã đều ép kiểu dữ liệu về String để so sánh rồi, chính việc luôn phải convert dữ liệu từ kiểu DATETIME về kiểu String nên việc này sẽ ảnh hưởng đến chỉ số CPU. Ta sẽ để việc so sánh này về so sánh bằng kiểu DATETIME, nghĩa là ta sẽ chuyển đối tượng so sánh về kiểu thời gian, cụ thể câu lệnh trên được viết lại thành:
Như vậy, hàm Hoặc trong khoảng ngày cũng tương tự:
Cách 1:
– So sánh ngày chi tiết tới thời gian: Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh: ví dụ: Cần lấy ngày tạo lớn hơn hoặc bằng 7h tối ngày 06/04/2018:
Turning: Việc này thì ta cũng làm tương tự như trên nhưng cần lưu ý xử lý về vấn đề thời gian giờ – phút – giây. Việc chuyển định dạng thời gian này hơi phức tạp, mình sẽ nghiên cứu và bổ sung thêm. Tối ưu câu lệnh SQL không chỉ tập trung vào các cú pháp và hướng xử lý như việc tối ưu trên mà ta còn cần phải chú ý tới dữ liệu ta sử dụng. Đối với những dữ liệu khi cần tìm kiếm nhiều ta cũng cần quan tâm đến việc đánh Index và bố trí phân vùng cho chúng được hợp lý và tốt hơn. Giả sử chúng ta có bảng dữ liệu mẫu về một nhóm 6 người bạn trong SQL như sau (bảng dữ liệu này được sử dụng chung cho các tính toán bên dưới). Friends First_name Last_name Birthday Le Thi Thu 1996-12-12 Vu Thi Nga 1997-11-17 Nguyen Van C 2000-12-17 Pham Van D 1998-10-11 Tran Dinh Trong 1997-04-25 Bui Tien Dung 1997-02-28 Giả sử ngày hiện tại là 2021-06-03. Để tính toán còn bao nhiêu ngày nữa, MySQL sử dụng hàm DATEDIFF() để trừ hai giá trị ngày và trả về số ngày giữa chúng. Giả sử chúng ta muốn tính toán xem ngày hiện tại so với ngày sinh của nhóm 6 người bạn ở mẫu dữ liệu trên là bao nhiêu thì bạn code như sau: SELECT DATEDIFF(CURDATE(), birthday) AS days_difference FROM friends LIMIT 6; Và đây là kết quả được trả về: Để biết được nhóm 6 người này có lần lượt số tuổi là bao nhiêu thì bạn chỉ cần chia cho 365 ngày là ra số tuổi của từng người. Code như sau: SELECT ROUND(DATEDIFF(CURDATE(), birthday) / 365, 0) AS years FROM friends LIMIT 6; Trong đó hàm toán học ROUND () được dùng để làm tròn kết quả thành 1 số nguyên Và đây là kết quả Ngoài ra bạn cũng có thể tính toán tuổi của bạn bè mình bằng cú pháp sau: SELECT first_name, last_name, (YEAR(CURDATE()) - YEAR(birthday)) - (RIGHT(CURDATE(), 5) < RIGHT(birthday, 5)) AS years FROM friends Đến đây mình giải thích 1 chút nhé. Lấy ví dụ như Thu có ngày sinh là 1996-12-12. Hàm CURDATE () được áp dụng nhằm mục đích trả về ngày hiện tại của máy tính. Giả sử là ngày 2021-06-03. Hàm YEAR () dùng để trả về năm của ngày đã chỉ định. Ở đây, (YEAR(CURDATE()) - YEAR(birthday)) sẽ trả về kết quả số năm chênh lệch giữa năm hiện tại và năm sinh của người được tính tuổi. Nghĩa là lấy 2021-1996 = 25. Hàm RIGHT () dùng để trả về số lượng ký tự như được chỉ định trong hàm từ chuỗi hoặc ngày đã cho. Hàm right có công thức RIGHT(chuoi, so_ky_tu). Số ký tự là 5, nghĩa là giá trị mà hàm Right lấy ra là ngày và tháng của ngày hiện tại và ngày sinh nhật. Ở đây hàm RIGHT(CURDATE(), 5) lấy ra giá trị là 06-03 và hàm RIGHT(birthday, 5) lấy ra giá trị là 12-12. Trong đó, phần của biểu thức so sánh các trả về từ hàm RIGHT () ước tính 1 hoặc 0. Nghĩa là 06-03 < 11-11 nên lấy giá trị 1. Từ đó SQL sẽ tính toán được tuổi của Thu là 25-1=24 tuổi. Và đây là kết quả trả về Sau khi tính toán ra số tuổi của bạn bè mình, bạn có thể sắp xếp lại trong list danh sách bạn bè theo thứ tự số tuổi tăng dần hoặc giảm dần bằng cách thêm các trường sau đây vào cuối mệnh đề ở trên. ORDER BY age ASC; //sắp xếp tăng dần ORDER BY age DESC; // sắp xếp giảm dần Tính toán ngày sinh nhậtKhi tính toán và xử lý ngày tháng bằng câu lệnh SQL, bạn có thể tính toán xem trong tuần có ngày sinh nhật nào của bạn bè mình hay không bằng cách sử dụng hàm DAYOFWEEK() là hàm trả về một giá trị số cho tham số giá trị ngày tháng. Và những con số đó đại diện cho: 1 = Chủ nhật, 2 = Thứ hai, v.v. Ở đây bạn có thể đặt 1 case biểu thức để tính toán như sau: SELECT first_name, last_name, birthday, CASE WHEN DAYOFWEEK(birthday) = '1' THEN 'Sunday' WHEN DAYOFWEEK(birthday) = '2' THEN 'Monday' WHEN DAYOFWEEK(birthday) = '3' THEN 'Tuesday' WHEN DAYOFWEEK(birthday) = '4' THEN 'Wednesday' WHEN DAYOFWEEK(birthday) = '5' THEN 'Thursday' WHEN DAYOFWEEK(birthday) = '6' THEN 'Friday' WHEN DAYOFWEEK(birthday) = '7' THEN 'Saturday' ELSE 'not a day of week' END AS day_of_week FROM friends LIMIT 10 Vàkết quả có được như sau: Bằng cách này thì kết quả tương đối ổn. Tuy nhiên để lấy tên 1 ngày trong tuần thì nó còn khá dài. Vì thế có sử dụng 1 function DAYNAME() phù hợp hơn cho việc này. Đơn giản chỉ cần cung cấp cho nó một giá trị ngày và kết quả sẽ nhanh hơn rất nhiều. Code như sau: SELECT first_name, last_name, DAYNAME(birthday) FROM friends LIMIT 10 Và đây là kết quả Tính toán và xử lý thángKhi tính toán và xử lý ngày tháng bằng câu lệnh SQL , từ 1 giá trị ngày tháng cung cấp thì các hàm MONTH() được sử dụng để lấy giá trị số theo tháng. Khi kết quả trả về là 1 (đại diện cho tháng 1) và 2 (đại diện cho tháng 2)…tương tự các tháng còn lại cũng tuân theo quy luật trên. SELECT (MONTH(birthday)) AS month, COUNT(*) AS number_of_birthdays FROM friends GROUP BY month ORDER BY month ASC Trong truy vấn này, hàm COUNT() có nhiệm vụ đếm số người có cùng ngày sinh trong mỗi tháng. Kết quả như sau: Sau đó chúng ta dùng hàm MONTHNAME() để lấy tên của tháng. Code như sau: SELECT DISTINCT (MONTHNAME(birthday)) AS month, COUNT(*) AS number_of_birthdays FROM friends GROUP BY month Và kết quả trả về như sau: Tính toán và xử lý ngàyGiả sử như bạn muốn nhập thông tin về ngày sinh nhật của một người mới trong quá trình tính toán và xử lý ngày tháng bằng câu lệnh SQL bạn phải nhập ngày ở dạng chuỗi. Giả sử người đó sinh vào ngày 10 tháng 8 năm 2017 thì bạn nhập như sau: SELECT STR_TO_DATE("August 10 2017", "%M %d %Y") Trong chuỗi ký hiệu được định dạng %M %d,%Y thì %M là tên tháng. %d là ngày trong tháng. %Y là 4 chữ số năm. Ngày có giá trị NOT NULLCó thể dùng câu lệnh MySQL dưới đây để kiểm tra ngày không phải là Null SELECT first_name, last_name, birthday FROM friends WHERE birthday IS NOT NULL; Nó sẽ lọc ra các hàng có ngày sinh nhật không phải là null cho bạn. Lấy ra những ngày trong khoảng ngày từ ngày... đến ngày ...Trong tính toán và xử lý ngày tháng bằng câu lệnh SQL thì để có thể lấy ra những ngày trong 1 khoảng thời gian định sẵn thì các bạn code như sau: SELECT * FROM friends WHERE birthday BETWEEN '1996-10-05 00:00:00' AND '1996-12-25 23:59:59' Và đây là kết quả trả về dữ liệu của người có ngày sinh trong khoảng thời gian trên. Đến đây có lẽ bạn đã hiểu được cách tính toán và xử lý ngày tháng bằng câu lệnh SQL thông qua những ví dụ cụ thể trong bài viết này. Nếu để ý kỹ bạn sẽ thấy nó không hề khó chút nào. Chỉ cần bạn kiên nhẫn thực hành luyện tập nhiều lần thì sẽ không còn bỡ ngỡ nếu phải tính toán và xử lý ngày tháng bằng câu lệnh SQL trong công việc của mình ở hiện tại và tương lai. Chúc bạn thành công. BizFly Cloud là nhà cung cấp dịch vụ điện toán đám mây với chi phí thấp, được vận hành bởi VCCorp. BizFly Cloud là một trong 4 doanh nghiệp nòng cốt trong "Chiến dịch thúc đẩy chuyển đổi số bằng công nghệ điện toán đám mây Việt Nam" của Bộ TT&TT; đáp ứng đầy đủ toàn bộ tiêu chí, chỉ tiêu kỹ thuật của nền tảng điện toán đám mây phục vụ Chính phủ điện tử/chính quyền điện tử. |
