Khi làm việc với dữ liệu khách hàng hoặc theo các dự án khoa học dữ liệu chuỗi thời gian, bạn sẽ thường thấy cần phải tính toán sự khác biệt giữa hai ngày trong các truy vấn MySQL của mình. Hàm MySQL DATEDIFF[] làm cho quy trình này trở nên rất đơn giản và có thể được sử dụng cho cả ngày được lưu trữ trong cột hoặc ngày bạn tự tạo
Trong hướng dẫn SQL đơn giản này, tôi sẽ chỉ ra cách bạn có thể sử dụng DATEDIFF[] để tính toán sự khác biệt giữa hai ngày và tính xem đã bao lâu kể từ lần đặt hàng đầu tiên và cuối cùng của khách hàng bằng cách sử dụng một số dữ liệu thương mại điện tử thực
Thiết lập máy chủ MySQL của bạn
Nếu bạn muốn tự chạy các truy vấn này bằng Python, trước tiên bạn cần có quyền truy cập vào máy chủ MySQL và cơ sở dữ liệu để truy vấn. Tôi chạy máy chủ MySQL của mình trong bộ chứa Docker và đã tạo cơ sở dữ liệu thử nghiệm dựa trên Bộ dữ liệu bán lẻ trực tuyến được sử dụng rộng rãi
Tôi đã tạo một vài hướng dẫn từng bước mà bạn có thể làm theo để tạo bộ chứa MySQL Docker và nhập cơ sở dữ liệu MySQL vào bộ chứa Docker, vì vậy bạn có thể làm theo trên cùng một dữ liệu. Nó chỉ mất một vài phút
Cài đặt và tải các gói
Tiếp theo, bạn sẽ cần cài đặt gói SQLAlchemy được sử dụng để truy vấn MySQL và các cơ sở dữ liệu khác thông qua Pandas và trình điều khiển PyMySQL để kết nối với máy chủ MySQL từ Python. Bạn có thể cài đặt những thứ này bằng trình quản lý gói Pip, sau đó nhập các mô-đun cần thiết vào sổ ghi chép Jupyter hoặc tập lệnh Python
!pip3 install sqlalchemy
!pip3 install pymysql
import pandas as pd
from sqlalchemy import create_engine
Chọn ngày đặt hàng đầu tiên và cuối cùng của mỗi khách hàng
query = """
SELECT * FROM customers
"""
df = pd.read_sql[query, con=engine]
df.head[]
customer_idrevenueordersskusunitsfirst_order_datelast_order_date014646. 0279489. 02777031967192010-12-20 10. 09. 002011-12-08 12. 12. 00118102. 0256438. 4962151641222010-12-07 16. 42. 002011-12-09 11. 50. 00217450. 0187482. 1755127690292010-12-07 09. 23. 002011-12-01 13. 29. 00314911. 0132572. 622481794771802010-12-01 14. 05. 002011-12-08 15. 54. 00412415. 0123725. 4526444772422011-01-06 11. 12. 002011-11-15 14. 22. 00
Hàm MySQL DATEDIFF[] lấy hai ngày và trả về số ngày giữa chúng. Ngày đầu tiên là ngày sau và ngày thứ hai là ngày trước đó [lật chúng theo cách khác sẽ trả về giá trị âm]. Vì vậy, ví dụ,
import pandas as pd
from sqlalchemy import create_engine
Bạn có thể sử dụng DATEDIFF[] theo hai cách chính. Bạn có thể sử dụng hai giá trị cột, như được hiển thị trong ví dụ trên hoặc bạn có thể chuyển vào ngày hiện tại hoặc một ngày khác mà bạn đã tạo hiện không được lưu trữ trong giá trị cột MySQL
Ví dụ: giả sử chúng tôi muốn tính xem đã bao nhiêu ngày kể từ khi mỗi khách hàng đặt đơn hàng đầu tiên. Chúng ta có thể làm điều này bằng cách chuyển vào hàm
import pandas as pd
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy import create_engine
query = """
SELECT
customer_id,
first_order_date,
last_order_date,
DATEDIFF[last_order_date, first_order_date] AS days_between_orders,
DATEDIFF[NOW[], first_order_date] AS days_since_first_order,
DATEDIFF[NOW[], last_order_date] AS days_since_last_order
FROM customers
"""
df = pd.read_sql[query, con=engine]
df.head[]
customer_idfirst_order_datelast_order_datedays_between_ordersdays_since_first_orderdays_since_last_order014646. 02010-12-20 10. 09. 002011-12-08 12. 12. 0035341453792118102. 02010-12-07 16. 42. 002011-12-09 11. 50. 0036741583791217450. 02010-12-07 09. 23. 002011-12-01 13. 29. 0035941583799314911. 02010-12-01 14. 05. 002011-12-08 15. 54. 0037241643792412415. 02011-01-06 11. 12. 002011-11-15 14. 22. 0031341283815
Thí dụ
Trả về số ngày giữa hai giá trị ngày
CHỌN DATEDIFF["25-06-2017", "15-06-2017"];
Tự mình thử »Định nghĩa và cách sử dụng
Hàm DATEDIFF[] trả về số ngày giữa hai giá trị ngày
cú pháp
Giá trị tham số
Tham sốMô tảngày1, ngày2Bắt buộc. Hai ngày để tính số ngày giữa. [ngày 1 - ngày 2]chi tiết kỹ thuật
Thêm ví dụ
Thí dụ
Trả về số ngày giữa hai giá trị ngày
CHỌN DATEDIFF["25-06-2017 09. 34. 21", "2017-06-15 15. 25. 35"];
Tự mình thử »Thí dụ
Trả về số ngày giữa hai giá trị ngày
CHỌN DATEDIFF["2017-01-01", "24-12-2016"];
Tự mình thử »