Mysql chọn mỗi ngày trong phạm vi

Tôi có một trường hợp sử dụng phổ biến khi tôi cần chuyển đổi phạm vi ngày thành một tập hợp các hàng trong bảng SQL Server. Ví dụ: tôi có một bản ghi nói rằng một nhân viên đã nghỉ lễ từ 2020-08-01 đến 2020-08-20. Hàng đơn này cần được chuyển đổi thành 20 hàng, một hàng cho mỗi ngày của kỳ nghỉ. Làm cách nào tôi có thể thực hiện việc này một cách nhanh chóng và có thể mở rộng, vì các bảng của tôi chứa hàng nghìn bản ghi có thể dẫn đến hàng triệu hàng ở đầu ra?

Chuyển đổi phạm vi ngày [hoặc bất kỳ loại phạm vi nào khác] thành một tập hợp các hàng là một yêu cầu phổ biến. Ví dụ: hệ thống nguồn cung cấp các hàng có ngày bắt đầu và ngày kết thúc trong một khoảng thời gian cụ thể, nhưng trong cơ sở dữ liệu của bạn, bạn cần một bảng giao dịch có một hàng cho mỗi ngày, vì điều này đơn giản hóa việc tính toán. Ví dụ, bạn có thể lọc ra các ngày cuối tuần và ngày lễ dễ dàng hơn nhiều so với khi bạn chỉ sử dụng ngày bắt đầu và ngày kết thúc của khoảng thời gian

Thông thường, thuật ngữ "bùng nổ bảng" được sử dụng vì một tập hợp nhỏ các phạm vi có thể dẫn đến một lượng lớn hàng. Nếu bạn có một hàng với 2020-01-01 là ngày bắt đầu và 2020-12-31 là ngày kết thúc, điều này sẽ dẫn đến 366 hàng. Hãy tưởng tượng bạn cần thực hiện phép tính tương tự cho hàng triệu khách hàng. Vì đầu ra có thể lớn nên điều quan trọng là giải pháp phải nhanh và có thể mở rộng. Điều này loại bỏ các vòng lặp và con trỏ trong T-SQL, vì chúng dựa trên hàng và không phù hợp với khối lượng hàng lớn. Các thuật ngữ khác bao gồm "starbursting" hoặc "unpacking một mối quan hệ trong một khoảng thời gian"

Trong mẹo này, một giải pháp được trình bày bằng cách sử dụng "bảng số", đôi khi còn được gọi là "bảng kiểm đếm". Để biết thêm thông tin cơ bản về loại bảng này, hãy xem các mẹo tuyệt vời này của Aaron Bertrand

• Giải thích về bảng số máy chủ SQL - Phần 1
• Giải thích về bảng số máy chủ SQL - Phần 2

Mẹo Hàm máy chủ SQL để trả về một phạm vi ngày thực hiện điều gì đó tương tự, nhưng sử dụng CTE đệ quy không thể mở rộng và cũng có giới hạn về số lần truy cập tối đa

Dữ liệu mẫu

Với câu lệnh SQL sau, chúng ta có thể tạo một bảng đơn giản để chứa dữ liệu mẫu

CREATE TABLE dbo.EmployeeHoliday
[EmployeeID VARCHAR[10] NOT NULL
,StartDate DATE NOT NULL
,EndDate DATE NOT NULL];

Hãy chèn 2 hàng vào bảng đó. Hai nhân viên đang đi nghỉ, cả ngày bắt đầu và ngày kết thúc

INSERT INTO dbo.EmployeeHoliday
SELECT [EmployeeID] = 'A', [StartDate] = '2020-06-01', [EndDate] = '2020-06-05'
UNION ALL
SELECT 'B','2020-12-15','2020-12-31';

Đây là kết quả cuối cùng mong muốn, một hàng cho mỗi ngày trong phạm vi ngày bắt đầu và ngày kết thúc

Tạo bảng số máy chủ SQL

Có nhiều phương pháp khác nhau để tạo bảng số, chẳng hạn như nối chéo hai tập hợp lớn lại với nhau [hãy xem mẹo của Aaron nếu bạn quan tâm đến các ví dụ], nhưng bạn cũng có thể tạo bảng "ảo" bằng cách sử dụng các biểu thức bảng phổ biến [ . Cú pháp sau đây được đặt ra bởi Itzik Ben-Gan, một bậc thầy về T-SQL

WITH E00[N] AS [SELECT 1 UNION ALL SELECT 1]
    ,E02[N] AS [SELECT 1 FROM E00 a, E00 b]
    ,E04[N] AS [SELECT 1 FROM E02 a, E02 b]
    ,E08[N] AS [SELECT 1 FROM E04 a, E04 b]
    ,E16[N] AS [SELECT 1 FROM E08 a, E08 b]
    ,E32[N] AS [SELECT 1 FROM E16 a, E16 b]
    ,cteTally[N] AS [SELECT ROW_NUMBER[] OVER [ORDER BY [SELECT NULL]] FROM E32]
SELECT N
FROM cteTally
WHERE N