根據 15 分鐘的間隔獲取計數

我有一個包含通話記錄的表：

tbl_calls

cl_Id
cl_StartDate
cl_endDate

我將兩個參數傳遞給我@StartDate的@EndDate儲存過程。

我的要求是在每 15 分鐘的持續時間之間獲取通話記錄的計數。

例如，如果：

@StartDate = '2015-11-16 00:00:00.000', 
@EndDate = '2015-11-16 23:59:00.000'

輸出應該是：

Date                        Count
2015-11-16 00:00:00.000      10(Count of startDate between '2015-11-16 00:00:00.000' AND '2015-11-16 00:15:00.000')
2015-11-16 00:15:00.000       7(Count of startDate between '2015-11-16 00:15:00.000' AND '2015-11-16 00:30:00.000')
2015-11-16 00:30:00.000      50(Count of startDate between '2015-11-16 00:30:00.000' AND '2015-11-16 00:45:00.000')

upto @EndDate

我已經嘗試了以下查詢，但是它不能正常工作。有一個更好的方法嗎？

DECLARE @StartDate DATETIME = DATEADD(DAY,-1,GETUTCDATE()),
       @EndDate DATETIME = GETUTCDATE()

SELECT New 
FROM
   (SELECT 
        (CASE 
            WHEN cl_StartTime BETWEEN @StartDate AND 
                        DATEADD(MINUTE, 15, @StartDate) 
               THEN 1 
               ELSE 0 
         END) AS New 
    FROM          
        tbl_Calls WITH (NOLOCK)    
    WHERE 
        cl_StartTime BETWEEN @StartDate AND @EndDate) AS Inners 
GROUP BY 
   New

如果您需要更多詳細資訊，請告訴我。

這是“數字表”如何真正幫助獲得所需結果的經典範例。

本質上，您創建一個包含所需 15 分鐘增量的表，然後加入您的表以獲得每 15 分鐘增量的呼叫總數。

例如，我對兩個表都使用臨時表。您可能希望使該#Intervals表永久化。

創建測試平台，並填充一些範例數據：

USE tempdb;

IF (OBJECT_ID('tempdb..#Calls') IS NOT NULL)
DROP TABLE #Calls;

CREATE TABLE #Calls
(
   CallID INT NOT NULL
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , CallStart DATETIME NOT NULL
   , CallEnd DATETIME NOT NULL
);

;WITH cte AS
(
   SELECT rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
       , rn1 = ROW_NUMBER() OVER (PARTITION BY o.object_id 
                                    ORDER BY o1.object_id)
   FROM sys.objects o, sys.objects o1
)
INSERT INTO #Calls (CallStart, CallEnd)
SELECT DATEADD(MINUTE, c.rn, DATEADD(DAY, -1, GETDATE()))
   , DATEADD(MINUTE, c.rn + c.rn1, DATEADD(DAY, -1, GETDATE()))
FROM cte c;

IF (OBJECT_ID('tempdb..#Intervals') IS NOT NULL)
DROP TABLE #Intervals;

CREATE TABLE #Intervals
(
   DateStart DATETIME NOT NULL
   , DateEnd DATETIME NOT NULL
);

;WITH cte AS
(
   SELECT TOP(35040) /* approx. number of 15 minute intervals in a year */
       rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id) * 15
   FROM sys.objects o
       , sys.objects o1
       , sys.objects o2
)
INSERT INTO #Intervals (DateStart, DateEnd)
SELECT DATEADD(MINUTE, c.rn, '2015-10-01T00:00:00')
   , DATEADD(MINUTE, c.rn + 15, '2015-10-01T00:00:00')
FROM cte c;

顯示兩個表中的行：

SELECT *
FROM #Intervals i
ORDER BY i.DateStart;

SELECT *
FROM #Calls c
ORDER BY c.CallStart;

加入兩個表以獲取 15 分鐘日期範圍內的呼叫總數：

SELECT i.DateStart
   , i.DateEnd
   , TotalCalls = COUNT(1)
FROM #Calls c
   INNER JOIN #Intervals i ON c.CallStart >= i.DateStart
       AND c.CallStart < i.DateEnd
GROUP BY i.DateStart
   , i.DateEnd
ORDER BY i.DateStart;

select在我的測試平台上，我從三個語句中得到以下結果：

查看您對@Thofle 答案的評論，您似乎想查看所有時間間隔，即使在給定的時間間隔內沒有電話。為此，您可以簡單地修改select查詢以使用 aLEFT JOIN和行COUNT(...)數Calls，例如：

SELECT i.DateStart
   , i.DateEnd
   , TotalCalls = COUNT(c.CallID)
FROM #Intervals i 
   LEFT JOIN #Calls c
       ON i.DateStart <= c.CallStart
           AND i.DateEnd > c.CallStart
GROUP BY i.DateStart
   , i.DateEnd
ORDER BY i.DateStart;

像這樣的東西可能會起作用

SELECT 
 count(1)
 ,DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))
FROM tbl_Calls
WHERE 
 cl_StartTime BETWEEN @StartDate AND @EndDate
GROUP BY 
 DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))

要獲得季度，這將為您提供 0-3 的值。乘以 15 得到 0,15,30,45。

DATEPART(MINUTE, date_recorded) / 15 

將 cl_StartTime 向下舍入到整小時。

DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)

然後，您只需像在查詢中一樣將其添加在一起，以獲得正確的時間戳並按此分組。

引用自：https://dba.stackexchange.com/questions/121305