Làm cách nào để có được danh sách đơn hàng theo máy chủ và theo phút (máy chủ SQL)

Xin chào-

Tôi đang cố gắng lấy danh sách các đơn đặt hàng đến từng máy chủ theo từng phút. Tôi có thể hiểu được điều đó bằng một tập lệnh đơn giản nhưng tôi phải thay đổi mệnh đề WHERE cho mỗi phút. Tôi hy vọng tìm thấy thứ gì đó có thể truy vấn phạm vi trong một truy vấn.

Mình đang mong nhận được kết quả như thế này….

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

Cảm ơn !!!

Những gì tôi đã thử:

Đây là truy vấn hoạt động nhưng tôi phải thay đổi số phút mỗi lần. Tôi phải thay đổi 09:40 thành 09:41 rồi 09:42, v.v.

SQL

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

Tôi đã thử cách này nhưng nó mất rất nhiều thời gian và tôi vẫn phải thay đổi số phút nếu muốn truy vấn một phạm vi khác.

SQL

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

Giải pháp 1

Bắt đầu bằng cách đếm số lượng đơn đặt hàng cho mỗi máy chủ trong mỗi phút:

SQL

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
;

Nếu bạn thực sự muốn có một cột cho mỗi phút thì bạn sẽ cần sử dụng PIVOT – điều này vẫn sẽ yêu cầu bạn thêm mục nhập cho mỗi phút:
Sử dụng PIVOT và UNPIVOT – SQL Server | Microsoft Tìm hiểu[^]

SQL

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
;

Nếu bạn muốn tránh phải nhập hết số phút thì bạn sẽ cần một trục xoay động – ví dụ:

SQL

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;