sql – 同时通话

发布时间：2020-12-12 07:26:46 所属栏目：MsSql教程来源：网络整理

导读：我正在尝试通过查看日期时间范围来计算特定呼叫时的同时呼叫数.我的查询有效,但只需要约10分钟即可执行95,000条记录,这太长了.任何优化的想法？ SELECT r.*,rr.ChannelsActive 'ChannelsActive'FROM #rg rOUTER APPLY( SELECT SUM(1) AS ChannelsActive FROM

我正在尝试通过查看日期时间范围来计算特定呼叫时的同时呼叫数.我的查询有效,但只需要约10分钟即可执行95,000条记录,这太长了.任何优化的想法？

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

示例数据

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

解决方法

这应该这样做：

;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

它最多需要几秒钟.应该适用于任何SQL Server 2005.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!