[ad_1]
Halo-
Saya mencoba mendapatkan daftar pesanan yang datang dari setiap server setiap menitnya. Saya bisa mendapatkannya dengan skrip sederhana tetapi saya harus mengubah klausa WHERE untuk setiap menit. Saya berharap menemukan sesuatu yang dapat menanyakan rentang dalam satu permintaan.
Saya berharap mendapatkan hasil seperti ini….
SERVER 9:40 9:41 9:42 9:43 9:44 9:45 SV1 9 12 7 8 1 2 SV2 16 10 8 11 18 25 SV3 27 22 29 11 4 7 SV4 22 20 22 22 47 47
Terima kasih !!!
Apa yang saya coba:
Ini adalah kueri yang berfungsi tetapi saya harus mengubah menitnya setiap kali. Saya harus mengubah 09:40 menjadi 09:41 lalu 09:42, dst.
SELECT h.line AS 'SERVER' ,COUNT(DISTINCT c.id) AS '9:40' FROM customer c INNER JOIN orders o ON c.id = o.id INNER JOIN handle h ON c.line = h.line WHERE c.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999' GROUP BY h.line ORDER BY 1
Saya telah mencoba ini tetapi memerlukan waktu yang sangat lama dan saya masih harus mengubah menitnya jika saya ingin menanyakan rentang yang berbeda.
SELECT h.line AS 'SERVER' ,(SELECT COUNT(DISTINCT c1.id) FROM customer c1 INNER JOIN orders o1 ON c1.id = o1.id INNER JOIN handle h1 ON c1.line = h1.line WHERE h.line = h1.line AND c1.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999') AS '9:40' ,(SELECT COUNT(DISTINCT c2.id) FROM customer c2 INNER JOIN orders o2 ON c2.id = o2.id INNER JOIN handle h2 ON c2.line = h2.line WHERE h.line = h2.line AND c2.datetime BETWEEN '2024-02-21 09:41:00.000' AND '2024-02-21 09:41:59.999') AS '9:41' ,(SELECT COUNT(DISTINCT c3.id) FROM customer c3 INNER JOIN orders o3 ON c3.id = o3.id INNER JOIN handle h3 ON c3.line = h3.line WHERE h.line = h3.line AND c3.datetime BETWEEN '2024-02-21 09:42:00.000' AND '2024-02-21 09:42:59.999') AS '9:42' ,(SELECT COUNT(DISTINCT c4.id) FROM customer c4 INNER JOIN orders o4 ON c4.id = o4.id INNER JOIN handle h4 ON c4.line = h4.line WHERE h.line = h4.line AND c4.datetime BETWEEN '2024-02-21 09:43:00.000' AND '2024-02-21 09:43:59.999') AS '9:43' ,(SELECT COUNT(DISTINCT c5.id) FROM customer c5 INNER JOIN orders o5 ON c5.id = o5.id INNER JOIN handle h5 ON c5.line = h5.line WHERE h.line = h5.line AND c5.datetime BETWEEN '2024-02-21 09:44:00.000' AND '2024-02-21 09:44:59.999') AS '9:44' ,(SELECT COUNT(DISTINCT c6.id) FROM customer c6 INNER JOIN orders o6 ON c6.id = o6.id INNER JOIN handle h6 ON c6.line = h6.line WHERE h.line = h6.line AND c6.datetime BETWEEN '2024-02-21 09:45:00.000' AND '2024-02-21 09:45:59.999') AS '9:45' FROM customer c INNER JOIN orders o ON c.id = o.id INNER JOIN handle h ON c.line = h.line GROUP BY h.line ORDER BY 1
Solusi 1
Mulailah dengan menghitung pesanan untuk setiap server setiap menitnya:
DECLARE @day datetime = '2024-02-21'; SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ORDER BY c.line, M.Minute ;
Jika Anda benar-benar menginginkan kolom untuk setiap menit, Anda harus menggunakan a PIVOT
– ini masih mengharuskan Anda menambahkan entri untuk setiap menit:
Menggunakan PIVOT dan UNPIVOT – SQL Server | Microsoft Belajar[^]
DECLARE @day datetime = '2024-02-21'; WITH cteSource As ( SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ) SELECT SERVER, [09:40], [09:41], [09:42], [09:43], [09:44], [09:45] FROM cteSource As S PIVOT ( SUM(OrderCount) FOR Minute In ([09:40], [09:41], [09:42], [09:43], [09:44], [09:45]) ) As P ORDER BY SERVER ;
Jika Anda ingin menghindari mengetik seluruh menit, maka Anda memerlukan pivot dinamis – misalnya:
DECLARE @day datetime = '2024-02-21'; DECLARE @columns nvarchar(max); DECLARE @sql nvarchar(max); WITH cteHours As ( SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As H FROM sys.all_columns ), cteMinutes As ( SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As M FROM sys.all_columns ) SELECT @columns = STUFF( (SELECT N', [' + FORMAT(H.H, '00') + N':' + FORMAT(M.M, '00') + N']' FROM cteHours H CROSS APPLY cteMinutes M FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, N'') ; SET @sql = N' WITH cteSource As ( SELECT c.line As SERVER, M.Minute, COUNT(DISTINCT c.id) As OrderCount FROM customer c INNER JOIN orders o ON o.id = c.id INNER JOIN handle h ON h.line = c.line CROSS APPLY (SELECT FORMAT(c.datetime, ''HH:mm'')) As M (Minute) WHERE c.datetime >= @day And c.datetime < DateAdd(day, 1, @day) GROUP BY c.line, M.Minute ) SELECT SERVER, ' + @columns + N' FROM cteSource As S PIVOT ( SUM(OrderCount) FOR Minute In (' + @columns + N') ) As P ORDER BY SERVER ;'; EXEC sp_executesql @sql, N'@day datetime', @day = @day;
[ad_2]
コメント