Simultaneous calls
I'm trying to calculate the number of simultaneous calls at the time a particular call is made by looking at the datetime ranges. My query works, but takes ~10 minutes to perform for only 95,000 records, which is too long. Any ideas for optimization?
SELECT r.*,
rr.ChannelsActive 'ChannelsActive'
FROM #rg r
OUTER APPLY
(
SELECT SUM(1) AS ChannelsActive
FROM #rg r_inner
WHERE
(
r_inner.CallStart BETWEEN r.CallStart AND r.CallEnd
OR r_inner.CallEnd BETWEEN r.CallStart AND r.CallEnd
OR r.CallStart BETWEEN r_inner.CallStart AND r_inner.CallEnd
OR r.CallEnd BETWEEN r_inner.CallStart AND r_inner.CallEnd
)
) rr
Example Data
CREATE TABLE #rg
(
CallStart DATETIME,
CallEnd DATETIME
)
CREATE INDEX ix1
ON #rg(CallStart, CallEnd)
CREATE INDEX ix2
ON #rg(CallEnd, CallStart);
WITH T(N, R)
AS (SELECT TOP (95000) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN,
ABS(120 + 30 * SQRT(-2 * LOG(ABS(CAST(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) AS FLOAT) / 9223372036854775807))) * COS(2 * PI() * ABS(CAST(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) AS FLOAT) / 9223372036854775807)))
FROM sys.all_objects o1,
sys.all_objects o2)
INSERT INTO #rg
SELECT DATEADD(SECOND, N, GETDATE()),
DATEADD(SECOND, N + R, GETDATE())
FROM T
This should do it:
;WITH cteCallEvents As
(
SELECT *, CallStart As EventTime, 1 As EventType FROM #rg r
UNION ALL
SELECT *, CallEnd As EventTime, 0 As EventType FROM #rg r
)
, cteCallCounts As
(
SELECT *,
ROW_NUMBER() OVER(Order By EventTime) as EventCount,
ROW_NUMBER() OVER(Partition By EventType Order By EventTime) as TypeCount
FROM cteCallEvents
)
SELECT *,
2*TypeCount - EventCount As OpenCalls
FROM cteCallCounts
WHERE EventType = 1
It should take a couple of seconds at most. Should work on any SQL Server 2005+.
Use SQL like this to get a list of start/end events...
Select CallStart, 1 As CallCount From #rg
Union All
Select CallEnd, -1 From #rg
Order By CallStart
...then treat this as a simple running totals problem, which is solved differently depending upon your SQL Server version or can be easily addressed in code if that's an option.
链接地址: http://www.djcxy.com/p/72460.html上一篇: Android Studio中的LEVEL EXCEPTION
下一篇: 同时通话