SQL-запросы к базе данных телефонных звонков

Я работаю над проектом базы данных для телефонного звонка, вот краткий обзор диаграммы, callid автоматически увеличивается в таблице вызовов, а sessionid автоматически увеличивается в таблице сеансов. Таким образом, при трехстороннем вызове вызовы имеют та самая схема. Я ввел фиктивные данные во все поля, кроме времени начала сеанса и времени окончания сеанса.

Используя phpmyadmin,

Мой вопрос: мне нужно сделать один запрос, который даст мне оплачиваемое время для клиента, также известное как номер телефона.

Пример телефонного звонка

А -> Б с 12:00 до 13:00

B -> C с 12:30 до 13:30

A должен быть выставлен счет за 1 час

B должен быть выставлен счет за 1 1/2 часа (1:30 часа)

C должен быть выставлен счет за 1 час

Другой пример A -> B с 12:00 до 13:00 A -> C с 12:30 до 13:30

A должен быть выставлен счет за 1 1/2 часа (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>

Вот схема ЕР

http://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`

Минуты принятых звонков (обратите внимание на callreciever) по аккаунту

    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>

sql recursive-query recursive-datastructures

user1876673 04.12.2012 источник

comment

Телефонный звонок обычно имеет адресата и отправителя, как в вашей структуре данных. Можете ли вы объяснить, как у вас есть телефонный звонок с тремя частями? - Gordon Linoff 04.12.2012

comment

Вот пример четырехстороннего вызова, который будет иметь три записи в таблице вызовов, все с callid, автоматически увеличивающимися в таблице вызовов, с sessionid в качестве внешнего ключа, в этом случае четырехстороннего вызова все они имеют разные callid , но тот же sessionid - user1876673 04.12.2012

comment

Было бы хорошо сделать ваш вопрос немного более общим. Хорошо, что вы приложили некоторые усилия и включили справочную информацию, но, как правило, лучше избегать задавать вопросы, используя терминологию, определенную конкретно вашей предметной областью. Другими словами, «мне нужно сделать один запрос, который даст мне оплачиваемое время для клиента, также известное как номер телефона», должно быть что-то вроде: «учитывая следующие определения таблицы бла-бла-бла, как бы я выполнить следующую операцию псевдокод или простой английский' - Sheena 04.12.2012

Ответы (1)

arrow_upward
0
arrow_downward

Я думаю, что у вас есть две проблемы здесь. Во-первых, определяется, сколько времени выделяется каждому вызывающему абоненту в сеансе. Во-вторых, собрать эту информацию.

Позвольте мне предположить, что все время для данного телефонного номера в сеансе является непрерывным. То есть звонка из B--> с 12:00 до 12:15 нет (потому что 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

Затем вы можете агрегировать данные по всем сеансам.

Если периоды вызовов в рамках сеанса не являются смежными, проблема усложняется. Лучший способ решить эту проблему зависит от базы данных и функций, доступных в базе данных.

Gordon Linoff 04.12.2012

comment

Сначала позвольте мне поблагодарить вас, но если бы я мог приставать к вам с некоторыми уточняющими вопросами, это было бы здорово. 1. c.sessionid действительно должен быть идентификатором call.session? а s.sessionid будет session.sessionid 2. c.caller, что это должно быть, у меня нет этого поля 3. когда вы используете фразу из call c, что вы подразумеваете под этим 4. Когда вы имеете в виду смежный, вы означает, что телефонный звонок не перекрывается, что означает удержание кого-либо? Если да, я специально ввел данные, чтобы не было перекрытия с использованием нескольких дат. - user1876673; 04.12.2012

SQL-запросы к базе данных телефонных звонков

Ответы (1)

Вопросы по теме