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? Show
Dung dịchChuyể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
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ẫuVớ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ủ SQLCó 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 <= 10000;
Trong CTE E00 đầu tiên, hai hàng được nối với nhau. Trong CTE E02 tiếp theo, CTE đầu tiên này được nối chéo với chính nó. Điều này mang lại cho chúng tôi 4 hàng. Quá trình này được lặp lại một vài lần. CTE E32 cuối cùng sẽ trả về 2^32 hàng, đây cũng là số cao nhất mà một số nguyên có thể chứa trong SQL Server. Nhưng CTE này chỉ trả về các hàng có giá trị 1 Sử dụng chức năng cửa sổ ROW_NUMBER, chúng ta có thể gán số cho mỗi hàng. Đối với ORDER BY trong mệnh đề OVER, chúng ta sử dụng truy vấn con (SELECT NULL). Thủ thuật này SQL Server không sắp xếp tập dữ liệu. Nếu vậy, nó có thể là một vấn đề hiệu suất nghiêm trọng. Chạy toàn bộ câu lệnh SQL sẽ trả về một số thứ tự duy nhất cho mỗi hàng, bắt đầu bằng số 1 Tạo bảng một triệu hàng chỉ mất vài giây trên máy của tôi. Nhưng trong trường hợp sử dụng này, chúng tôi cần ngày tháng, không phải số. Chúng ta có thể chuyển đổi các số thành ngày bằng cách sử dụng hàm DATEADD. Câu lệnh SQL bây giờ trở thành 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 ExplodedDate = DATEADD(DAY,N - 1,'2020-01-01')
FROM cteTally
WHERE N <= 366
Điều này sẽ tạo ra tất cả các ngày của năm 2020 (hãy nhớ rằng đó là một năm nhuận) Khai thác phạm vi trong bảng số máy chủ SQLSử dụng bảng số (hoặc bảng ngày chính xác hơn), cuối cùng chúng ta có thể "bùng nổ" dữ liệu mẫu của mình. Chúng ta chỉ cần nối bảng kiểm đếm với bảng mẫu bằng phép nối dãy 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)
,DateRange AS
(
SELECT ExplodedDate = DATEADD(DAY,N - 1,'2020-01-01')
FROM cteTally
WHERE N <= 366
)
SELECT *
FROM dbo.EmployeeHoliday eh
JOIN DateRange d ON d.ExplodedDate >= eh.[StartDate]
AND d.ExplodedDate <= eh.[EndDate];
Điều này cho chúng ta tập kết quả mong muốn Bắt đầu từ 2020-01-01 và chỉ mất 366 ngày là khá hạn chế đối với bảng ngày được tạo. Tuy nhiên, bạn có thể mở rộng các giới hạn này và điều này sẽ không ảnh hưởng quá nhiều đến hiệu suất. Hãy lấy 2000-01-01 làm điểm bắt đầu và tạo ngày tháng trong 100 năm (khoảng 365.000 hàng) Truy vấn hiện kết thúc chỉ dưới 3 giây Thử nghiệm với nhiều dữ liệu hơnHãy thực hiện trường hợp sử dụng tương tự, nhưng bây giờ sử dụng bảng Fact Internet Sales của kho dữ liệu AdventureWorks 2017. Chúng tôi sẽ phát nổ các ngày giữa ngày đặt hàng và ngày đáo hạn. Bảng thực tế chứa 60.398 hàng. Tập hợp kết quả được chia nhỏ sẽ chứa 785.174 hàng. Truy vấn SQL được điều chỉnh 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)
,DateRange AS
(
SELECT ExplodedDate = DATEADD(DAY,N - 1,'2010-01-01')
FROM cteTally
WHERE N <= 5000
)
SELECT f.[SalesOrderNumber], f.[SalesOrderLineNumber], [f].[OrderDate], [f].[DueDate], d.[ExplodedDate]
INTO #Test
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales] f
JOIN DateRange d ON d.ExplodedDate >= f.[OrderDate]
AND d.ExplodedDate <= f.[DueDate];
SELECT COUNT(1) FROM #test;
Trong thử nghiệm này, tôi không trả lại các hàng về SSMS, vì việc hiển thị tất cả các hàng trong lưới sẽ mất quá nhiều thời gian. Thay vào đó, tôi đang chèn dữ liệu vào một bảng tạm thời và sau đó trả lại số lượng hàng từ bảng tạm thời này. Khai thác bảng Sự kiện Bán hàng qua Internet mất 20 giây trên máy của tôi Cập nhật mẹo - Phương pháp thay thếTrong các nhận xét, Jeff Moden đã đề xuất một cách khác để giải quyết vấn đề. Thay vì mã hóa cứng giới hạn thành 5.000 hàng, giới hạn được tính động bằng cách sử dụng DATEDIFF của đơn đặt hàng và ngày đến hạn. Giá trị này được áp dụng cho mệnh đề TOP, chỉ chọn số hàng của bảng kiểm đếm thực sự cần thiết. Các hàng kết quả sau đó được nối vào bảng thực tế bằng cách sử dụng CHÉO ÁP DỤNG, giải phóng phạm vi dữ liệu một cách hiệu quả Đây là mã thích ứng WITH
H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) H0(N))
,cteTALLY(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM H1 a, H1 b, H1 c, H1 d, H1 e, H1 f, H1 g, H1 h)
SELECT
f.SalesOrderNumber
,f.SalesOrderLineNumber
,f.OrderDate
,f.DueDate
,ExplodedDate = DATEADD(dd,t.N-1,f.OrderDate)
INTO #Test
FROM AdventureWorksDW2017.dbo.FactInternetSales f
CROSS APPLY (
SELECT TOP (DATEDIFF(dd,f.OrderDate,f.DueDate) + 1)
N
FROM cteTally
ORDER BY N
) t;
Ví dụ, giả sử rằng một hàng cụ thể của bảng Dữ liệu doanh số bán hàng qua Internet, số ngày giữa các ngày là 5 (có nghĩa là chúng ta cần 6 hàng được chia nhỏ, vì chúng ta cần bao gồm ngày đầu tiên). Điều này sẽ dẫn đến truy vấn con sau SELECT TOP 6 N FROM cteTally ORDER BY N Điều này sẽ trả về 6 hàng của bảng kiểm đếm (1,2,3,4,5,6). Sử dụng ỨNG DỤNG CHÉO, 6 hàng này được nối với bảng thực tế, lặp lại hàng ban đầu của bảng thực tế 6 lần, nhưng với mỗi hàng, thêm (N-1) ngày vào ngày đặt hàng Giải pháp hiệu quả hơn vì không có bảng làm việc nào được tạo trong cơ sở dữ liệu tempdb và bảng kiểm đếm không được cụ thể hóa trong bộ nhớ. Để biết thêm thông tin, hãy xem các ý kiến Sự kết luậnTrong mẹo này, chúng ta đã thấy cách bạn có thể phân tách phạm vi ngày bằng cách sử dụng bảng "số". Đó là một kịch bản phổ biến để phân tích chuỗi thời gian chẳng hạn. Mã có thể được tối ưu hóa bằng cách sử dụng bảng ngày cố định thay vì tạo một bảng khi đang di chuyển. Tuy nhiên, mục đích của thủ thuật này là chỉ cho bạn các kỹ thuật tạo nhanh một bảng số lớn để cải thiện mã T-SQL của bạn bằng cách sử dụng logic dựa trên tập hợp Bước tiếp theo
Những bài viết liên quanChuyển đổi ngày và giờ bằng SQL Server Hàm thời gian tiết kiệm ánh sáng ban ngày trong SQL Server Hàm SQL Server để chuyển đổi ngày nguyên thành định dạng ngày giờ Cộng và trừ ngày bằng cách sử dụng DATEADD trong SQL Server Định dạng ngày máy chủ SQL với chức năng FORMAT Tạo thứ nguyên ngày hoặc bảng lịch trong SQL Server Các phương pháp hay nhất về DateTime của máy chủ SQL Tạo Thứ nguyên ngày mở rộng cho Kho dữ liệu máy chủ SQL Các hàm ngày và giờ của SQL Server với các ví dụ Đơn giản hóa tính toán khoảng thời gian ngày trong SQL Server Điền vào các ngày bị thiếu cho đầu ra truy vấn SQL Server Hàm FORMAT của SQL Server cho Ngày, Số và Ngày của Tác nhân Máy chủ SQL Chỉ cập nhật Năm, Tháng hoặc Ngày trong Máy chủ SQL Ngày SQL Chuyển đổi ngày thành YYYYMMDD Chức năng máy chủ SQL DATEDIFF Cách lấy ngày hiện tại trong SQL Server Sử dụng bảng lịch trong SQL Server - Phần 1 Hàm DATE trong SQL Hiểu các múi giờ trong SQL Server Tạo ngày ngẫu nhiên trong T-SQL Các bài viết phổ biếnChuyển đổi ngày và giờ bằng SQL Server Định dạng ngày máy chủ SQL với chức năng FORMAT SQL Server ÁP DỤNG CHÉO và ÁP DỤNG NGOÀI SQL Server DROP TABLE IF EXISTS Các ví dụ Ví dụ về con trỏ máy chủ SQL Toán tử SQL NOT IN Cuộn nhiều hàng thành một hàng và cột cho dữ liệu SQL Server Làm cách nào để biết bạn đang chạy phiên bản SQL Server nào SQL Chuyển đổi ngày thành YYYYMMDD Giải quyết lỗi không thể mở kết nối với SQL Server Cộng và trừ ngày bằng cách sử dụng DATEADD trong SQL Server Máy chủ SQL lặp qua các hàng của bảng mà không cần con trỏ Số lượng hàng của máy chủ SQL cho tất cả các bảng trong cơ sở dữ liệu Sử dụng MERGE trong SQL Server để chèn, cập nhật và xóa cùng một lúc Cách lấy ngày hiện tại trong SQL Server Nối các cột Máy chủ SQL thành một Chuỗi với CONCAT() Các cách so sánh và tìm sự khác biệt cho các bảng và dữ liệu SQL Server Cơ sở dữ liệu máy chủ SQL bị kẹt trong trạng thái khôi phục Định dạng số trong SQL Server Thực thi các lệnh SQL động trong SQL Server Thông tin về các Tác giảXem tất cả mẹo của tôi Bài viết được cập nhật lần cuối. 2020-07-15 Nhận xét cho bài viết nàyThêm nhận xétChủ nhật, ngày 9 tháng 8 năm 2020 - 11. 56. 23 PM - Jeff Moden (86262)Chào Koen, Cảm ơn bạn đã phản hồi rất tử tế. Bạn đã thực hiện một phân tích tuyệt vời trong bản cập nhật của mình cho bài viết. Tôi chắc chắn không mong đợi một bản cập nhật cho bài viết dựa trên những gì tôi đã đăng và tôi cảm ơn bạn một lần nữa vì đã dành thời gian để giúp đỡ người khác. Bạn không chỉ là một người hiểu biết về SQL Server mà còn là một chuyên gia thực sự. Tất nhiên, sau nhiều năm "biết" bạn, tôi đã biết cả hai điều đó. Rất vui được "gặp lại" bạn và một lần nữa, cảm ơn bạn vì những gì bạn đã làm cho cộng đồng phi thường này. Thứ Tư, ngày 29 tháng 7 năm 2020 - 3. 30. 20:00 - Koen Verbeeck (86212) Chào Jeff, đã lâu không gặp. ) Dù sao, truy vấn của bạn chạy nhanh hơn. Thật kỳ lạ là khi bạn chạy truy vấn của tôi và truy vấn của bạn trong cùng một đợt, kế hoạch thực thi thực tế sẽ cho biết truy vấn của tôi chiếm khoảng 7% chi phí của tổng số đợt, trong khi của bạn là 93%. Lạ lùng Tôi đã gửi bản cập nhật cho các biên tập viên để đưa truy vấn của bạn vào bài viết cùng với một chút giải thích về cách hoạt động của truy vấn đó. Để biết chi tiết thực tế về hiệu suất, tôi tham khảo nhận xét của bạn tại đây. Mẹo sẽ được cập nhật sớm. Thứ tư, ngày 15 tháng 7 năm 2020 - 5. 43. 24h - Jeff Moden (86140) Tôi không thể biết câu trả lời này đã được gửi trước đó chưa và vì vậy tôi đang gửi lại. Nếu nó là một bản sao của lần gửi trước đây của tôi, xin vui lòng bỏ qua nó Đầu tiên, tôi phải nói rằng tôi rất cảm kích bất kỳ ai đã dành thời gian viết một bài báo để chia sẻ kiến thức và vì vậy cảm ơn bạn rất nhiều vì điều đó, Koen Đề xuất của bạn về việc sử dụng Itzik Ben-Gans cCTEs (Không nên nhầm lẫn CTE xếp tầng với rCTE hoặc CTE đệ quy) nhưng có một số vấn đề về triển khai trong ví dụ cuối cùng của bạn. Trước tiên hãy xem những vấn đề là gì Nếu chúng tôi bọc mã của bạn trong SET STATISTICS nhưng cũng bao gồm IO, chúng tôi sẽ nhận được gợi ý về sự cố đầu tiên --===== Nếu bảng thử nghiệm đã tồn tại, hãy bỏ bảng đó để làm cho việc chạy lại trong SSMS dễ dàng hơn. Bảng 'Bàn làm việc'. Số lần quét 5000, số lần đọc logic 1543429, số lần đọc vật lý {0, số lần đọc đọc trước {0, số lượt đọc lô-gic 0, số lượt đọc vật lý tác giả 0, số lần đọc lob phía trước đọc 0. Nếu bạn nhìn vào kế hoạch thực hiện, bạn sẽ phát hiện ra rằng "Worktable" là từ "Eager Spool" trong TempDB. Nó tạo ra số lượng hàng "thực tế" hơn 226 triệu hàng mà bạn đã dự đoán, nhưng chúng tôi thực sự không cần cụ thể hóa những hàng đó. Ngoài ra, nếu chúng ta chia 1543429 số lần đọc logic cho 128 để chuyển thành MegaBytes đọc, chúng tôi thấy rằng hơn 12.058 MegaByte (hơn 12 GigaByte) phải được . Bộ nhớ nhanh như vậy, đó là rất nhiều bộ nhớ không cần thiết IO. Các vấn đề khác bao gồm thực tế là phương pháp của bạn yêu cầu kiến thức bí truyền trước đó về ngày thấp nhất trong bảng có thể là ngày nào và thực tế là bạn đã bao gồm một giới hạn. 5000 ngày chỉ khoảng 13. 68 năm và có rất nhiều khoảng thời gian sẽ dễ dàng vượt xa điều đó. Vấn đề lớn với cả hai là bạn không nhất thiết phải biết khoảng thời gian ngày sẽ là bao nhiêu cũng như ngày bắt đầu thấp nhất là bao nhiêu Chúng tôi có thể giải quyết tất cả những điều đó. Điều quan trọng là không cụ thể hóa 5000 hàng cho cteTally. Trong phần sau, tôi cũng sử dụng phiên bản "Cơ sở 16" của mã "Cơ sở 2" tốt của Itzik chỉ để làm cho mã ngắn hơn và dễ nhớ hơn rất nhiều. Tuy nhiên, đó KHÔNG phải là tốc độ đến từ đâu. Tốc độ đến từ việc sử dụng TOP trong ÁP DỤNG CHÉO, đây là phương pháp hàm ý ưa thích cho các cCTE của Itzik (như bạn sẽ thấy ngay khi chạy đoạn mã sau) --===== Nếu bảng kiểm tra đã tồn tại, hãy bỏ nó để chạy lại trong SSMS dễ dàng hơn. Nếu bạn muốn tiến thêm một bước, hãy tạo một iTVF (Hàm có giá trị trong bảng nội tuyến) và mã của bạn sẽ trông đơn giản như sau Làm cách nào để lấy dữ liệu một ngày trong MySQL?Tìm hiểu MySQL từ đầu về Khoa học dữ liệu và Phân tích
. DATE_ADD() function.
Khoảng thời gian 1 ngày là gì?Và INTERVAL hoạt động như được đặt tên, e. g. THỜI GIAN 1 NGÀY = 24 giờ .
Làm cách nào để có được tất cả các ngày giữa phạm vi ngày trong truy vấn SQL?Để tìm sự khác biệt giữa các ngày, hãy sử dụng hàm DATEDIFF(datepart, startdate, enddate) . Đối số datepart xác định phần ngày/thời gian mà bạn muốn thể hiện sự khác biệt. Giá trị của nó có thể là năm, quý, tháng, ngày, phút, v.v.
Làm cách nào để thêm 15 ngày vào ngày hiện tại trong MySQL?Sử dụng hàm DATE_ADD() nếu bạn muốn tăng một ngày nhất định trong cơ sở dữ liệu MySQL. |
