SQL:如何正确加入以便从通话记录中计算联系人统计信息

Question

通话记录有以下列:

CallingParty, CalledParty, Duration, EventTime

示例数据如下:

X, a, 10, 10:20
X, b, 12, 10:34
X, c, 8, 12:08
a, X, 22, 12:45
X, a, 10, 13:55
d, X, 30, 15:01

我想做的是计算每个联系人的统计信息(用户X给联系人打了多少次电话，去电总时长是多少，联系人给用户X打了多少次，以及是来电持续时间的总和)。其实我是想从用户 X 的通话记录中挖掘数据。

上述示例的统计信息如下:

contactName, incomingCallsCount, IncomingCallsDuration, OutgoingCallsCount, OutgoingCallsDuration
a, 1, 10, 2, 20
b, 0, 0, 1, 12
c, 0, 0, 1, 8
d, 1, 30, 0, 0

我用不同的连接 () 尝试了以下查询，但无法获得正确的结果

    SELECT t1.CallingParty AS Contact, t1.CallingPartyCount, t1.CallingPartyDuration, t2.CalledPartyCount, t2.CalledPartyDuration FROM
(SELECT e.CallingParty, COUNT(*) AS CallingPartyCount, SUM(CAST(REPLACE(e.Duration, 'NULL', '0') AS int)) AS CallingPartyDuration FROM Events e WHERE Duration <> 'NULL' GROUP BY e.CallingParty) t1
<JOIN>
(SELECT e.CalledParty, COUNT(*) AS CalledPartyCount, SUM(CAST(REPLACE(e.Duration, 'NULL', '0') AS int)) AS CalledPartyDuration FROM Events e WHERE Duration <> 'NULL' GROUP BY e.calledParty) t2
ON t1.CallingParty = t2.CalledParty

有谁知道什么是正确的查询以获得正确的统计信息？

谢谢!

Answer 1

试试这个:

WITH qry AS
(
    SELECT a.*,
          CASE CallingParty
            WHEN 'X' THEN CalledParty
            ELSE  CallingParty
          END AS contactName
        FROM CallLog a
     WHERE ( CallingParty = 'X' OR  CalledParty = 'X')
)
SELECT  contactName,
        SUM( CASE CallingParty WHEN 'X' 1 ELSE 0 END) AS incomingCallsCount,
        SUM( CASE CallingParty WHEN 'X' Duration ELSE 0 END) AS incomingDurationCount
        SUM( CASE CallingParty WHEN 'X' 0 ELSE 1 END) AS outgoingCallsCount,
        SUM( CASE CallingParty WHEN 'X' 0 ELSE Duration END) AS outgoingDurationCount
  FROM  qry
GROUP BY contactName