电话呼叫数据库SQL查询

Question

我一直在为一个电话呼叫处理数据库项目，这是一个图表的快速视图，callid在调用表中自动递增，sessionid在会话表中自动增值。这样，在三向调用中，调用具有相同的图表。除了sessionstarttime和sessionend-time ，我在每个字段中都输入了虚构的数据

使用phpmyadmin、

我的问题是：我需要做一个查询，给我客户的计费时间，也就是电话号码。

示例电话

A->B从下午12:00到下午1:00

B->C从下午12:30到下午1:30

A应为1小时计费

应向B收取1个半小时(1:30小时)的费用

C应按1小时计费

另一个例子A->B下午12:00至下午1:00A->C下午12:30至下午1:30

A应按1个半小时(1:30小时)计费

应向B收取1小时的费用

C应按1小时计费

以下是给定的数据格式

- <table name="Account">
<column name="AccountID">1</column> 
<column name="AcctHolderNum">617-100-5001</column> 
<column name="ProviderID">1</column> 
</table>
<table name="call">
<column name="callID">4</column> 
<column name="callSender">617-719-9000</column> 
<column name="callReceiver">617-730-8100</column> 
<column name="callStartTime">2012-11-06 06:44:50</column> 
<column name="callEndTime">2012-11-06 06:55:50</column> 
<column name="sessionID">1</column> 
- <table name="phoneNum">
<column name="phoneNum">617-300-2000</column> 
<column name="phoneNumFN">Nigel</column> 
<column name="phoneNumLN">Thornberry</column> 
<column name="PhoneAccountID">2</column> 
- <table name="Provider">
<column name="ProviderID">1</column> 
<column name="ProviderName">T-Mobile</column> 
</table>
- <table name="session">
<column name="sessionID">1</column> 
<column name="sessionStartTime">2012-11-06 06:44:50</column> 
<column name="sessionEndTime">2012-11-06 06:55:50</column> 

这是ER图

https://i.stack.imgur.com/rrh4B.jpg

以下是我开始思考的内容，但我陷入了困惑，试图使一个查询适合调用表中的每一个可能的输入

FROM `call` as `call1`, `call` as `call2`, `call` as `call3`
WHERE `call1.sessionid` = `call2.sessionid` = `call3.sessionid`
AND <REST OF STUFF>
UNION /* not union all, but union*/
SELECT same as above but for three way calls
FROM `call` as `call1`, `call` as `call2`,
WHERE `call1.sessionid` = `call2.sessionid`
AND <REST OF STUFF>
UNION
SELECT same as above but for two way calls
FROM `call`
WHERE <REST OF STUFF>

这里还有几个简单的查询供参考

计算每次调用的长度

SELECT TIMEDIFF(MIN(`callStartTime`), MAX(`callEndTime`)) 
FROM `call` GROUP BY `callID`

计算每个会话的长度

SELECT TIMEDIFF(MIN(`callStartTime`), MAX(`callEndTime`)) 
FROM `call` GROUP BY `sessionID`

账户的通话分钟数(注意来电发送者)

SELECT SUM(TIMEDIFF(`callStartTime`, `callEndTime`)) 
FROM `call`, `Phonenum` 
WHERE `phoneNum.phoneNum` = `call.callSender`  
GROUP BY `phoneAccountID`

账户收到的电话分钟数(注意收到的电话)

SELECT SUM(TIMEDIFF(`callStartTime`, `callEndTime`)) 
FROM `call`, `Phonenum` 
WHERE `phoneNum.phoneNum` = `call.callReciever` GROUP BY `phoneAccountID`

以下是模式的xml输出

- <pma:structure_schemas>
- <pma:database name="jr_Team5" collation="utf8_general_ci" charset="utf8">
<pma:table name="Account">CREATE TABLE `Account` ( `AccountID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'AI Primary Key', `AcctHolderNum` varchar(50) NOT NULL COMMENT 'Account Holder''s Phone Number i.e. "617-100-5001"', `ProviderID` int(11) DEFAULT NULL COMMENT 'Foreign Key from "ProviderID"', PRIMARY KEY (`AccountID`), KEY `AcctHolderNum` (`AcctHolderNum`), KEY `ProviderID` (`ProviderID`), CONSTRAINT `Account_ibfk_1` FOREIGN KEY (`ProviderID`) REFERENCES `Provider` (`ProviderID`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;</pma:table> 
<pma:table name="call">CREATE TABLE `call` ( `callID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'AI Primary Key', `callSender` varchar(50) NOT NULL COMMENT 'Phone Number of Caller', `callReceiver` varchar(50) NOT NULL COMMENT 'Phone Number of Reciever', `callStartTime` datetime NOT NULL COMMENT 'Time Call Begins', `callEndTime` datetime NOT NULL COMMENT 'Time Call Ends', `sessionID` int(11) NOT NULL COMMENT 'Foreign Key from "SessionID"', PRIMARY KEY (`callID`), KEY `callSender` (`callSender`), KEY `callReceiver` (`callReceiver`), KEY `sessionID` (`sessionID`), CONSTRAINT `call_ibfk_1` FOREIGN KEY (`callSender`) REFERENCES `phoneNum` (`phoneNum`), CONSTRAINT `call_ibfk_2` FOREIGN KEY (`callReceiver`) REFERENCES `phoneNum` (`phoneNum`), CONSTRAINT `call_ibfk_3` FOREIGN KEY (`sessionID`) REFERENCES `session` (`sessionID`) ) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;</pma:table> 
<pma:table name="phoneNum">CREATE TABLE `phoneNum` ( `phoneNum` varchar(50) NOT NULL COMMENT 'Phone Number on Record', `phoneNumFN` varchar(50) DEFAULT NULL COMMENT 'First Name of Phone User', `phoneNumLN` varchar(100) DEFAULT NULL COMMENT 'Last Name of Phone User', `PhoneAccountID` int(11) DEFAULT NULL COMMENT 'Foreign Key from "AccountID"', PRIMARY KEY (`phoneNum`), KEY `PhoneAccountID` (`PhoneAccountID`), CONSTRAINT `phoneNum_ibfk_1` FOREIGN KEY (`PhoneAccountID`) REFERENCES `Account` (`AccountID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pma:table> 
<pma:table name="Provider">CREATE TABLE `Provider` ( `ProviderID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'AI Primary Key', `ProviderName` varchar(50) NOT NULL COMMENT 'Network Provider i.e. "Verizon" or "Sprint"', PRIMARY KEY (`ProviderID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;</pma:table> 
<pma:table name="session">CREATE TABLE `session` ( `sessionID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'AI Primary Key', `sessionStartTime` datetime DEFAULT NULL COMMENT 'Session Begin Time', `sessionEndTime` datetime DEFAULT NULL COMMENT 'Session End Time', PRIMARY KEY (`sessionID`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;</pma:table> 
</pma:database>
</pma:structure_schemas>

感谢您提前提供的帮助，如果您认为该数据库有任何其他复杂的查询，请告诉我

xml 中的四路调用数据示例

- <table name="call">
<column name="callID">40</column> 
<column name="callSender">617-292-1309</column> 
<column name="callReceiver">617-300-2000</column> 
<column name="callStartTime">2012-10-31 09:07:35</column> 
<column name="callEndTime">2012-10-31 11:07:35</column> 
<column name="sessionID">7</column> 
</table>
- <table name="call">
<column name="callID">41</column> 
<column name="callSender">617-300-2000</column> 
<column name="callReceiver">617-234-1234</column> 
<column name="callStartTime">2012-10-31 09:37:35</column> 
<column name="callEndTime">2012-10-31 12:37:35</column> 
<column name="sessionID">7</column> 
</table>
- <table name="call">
<column name="callID">42</column> 
<column name="callSender">617-234-1234</column> 
<column name="callReceiver">617-200-4000</column> 
<column name="callStartTime">2012-10-31 10:37:35</column> 
<column name="callEndTime">2012-10-31 11:37:35</column> 
<column name="sessionID">7</column> 

Answer 1

我认为您有两个问题。第一个是确定在会话中为每个调用者分配多少时间。第二是汇总这些信息。

让我假设会话中给定电话号码的所有时间都是连续的。也就是说，在12:00到12:15之间没有来自B-->的调用(因为C不连续)。然后，您可以获得会话中每个用户的时间：

select c.sessionid, c.caller,
(max(c.EndTime) - min(c.StartTime)) as dur
from ((select c.sessionid, c.callSender as caller, c.StartTime, c.EndTime
from call c
) union all
(select c.sessionid, c.callReceiver, c.StartTime, c.EndTime
from call c
)
) c
on s.sessionid = c.sessionid
group by c.sessionid, c.caller

由此，您可以对所有会话进行聚合。

如果会话中的调用周期不连续，那么问题就更具挑战性。解决这个问题的最佳方法取决于数据库和数据库中可用的函数。