同时通话

我试图通过查看日期时间范围来计算在进行特定呼叫时同时呼叫的数量。 我的查询工作正常，但只需要10分钟即可完成95000条记录，这些记录太长。 任何想法优化？

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+上工作。

---

像这样使用SQL来获取开始/结束事件列表...

Select CallStart, 1 As CallCount From #rg
Union All
Select CallEnd, -1 From #rg
Order By CallStart

...然后将其视为一个简单的运行总计问题，根据您的SQL Server版本可以不同地解决这个问题，或者如果这是一个选项，可以在代码中轻松解决。

链接地址: http://www.djcxy.com/p/72459.html

上一篇: Simultaneous calls

下一篇: XML encryption and decryption for multiple recipients with X509 certificates