Tamkovich.com: Телеком/VoIP блог

MySQL: о пользе вложенных запросов

28 ноября, 2010 by Сергей Тамкович

Не так давно, начиная с версии MySQL 4.1 — СУБД MySQL получила поддержку так называемых вложенных запросов. Большинство вложенных запросов могут быть переписаны с помощью стандартного join. Например вот такой вложенный запрос:

SELECT a, b FROM t1 WHERE c IN (SELECT d FROM t2);

Можно безболезненно заменить эквивалентом без вложенного запроса:

SELECT t1.a, t1.b FROM t1, t2 WHERE t1.c=t2.d;

Однако, существует масса задач, решение которых без использования вложенных запросов — крайне уродливо или же его нет вообще. Приведу пример, близкий любителям Asterisk.

Задача не особо экзотическая — имея таблицу CDR с записями о состоявшихся звонках оценить загрузку сотрудников отвечающих на телефон, по дням недели. Сотрудники, чью загрузку будем оценивать, имеют внутренние номера 102 и 117. По этому, критерием отбора CDR записей будет поле dstchannel начинающиеся с ‘SIP/102’ или с ‘SIP/117’. В первую очередь, нам необходимо просуммировать звонки по дням что бы получить суточные данные о количестве и длительность звонков, делается это тривиальным запросом

SELECT DATE(calldate) AS dt, SUM(billsec)/3600 AS hours, COUNT(billsec) AS calls
FROM cdr 
WHERE calldate>'2010-10-01 00:00:00' AND calldate<'2010-11-01 00:00:00' 
AND (dstchannel LIKE 'SIP/102%' OR dstchannel LIKE 'SIP/117%') 
GROUP BY DATE(calldate);

В этом запросе будут выбраны: дата, длительность звонков преведенная к часам и количество звонков за указазнную дату. Суть вложенных SQL запросов, заключается в том, что результаты одного запроса можно использовать как таблицу для другого. Имея распределение звонков и их длительности по датам мы можем посчитать загрузку сотрудников по дням недели. Для этого нам понадобится функция DAYNAME() которая вычесляет название дня недели по дате. В результате мы имеем SQL запрос который с помощью функции AVG() считает среднее количество и среднюю длительность звонков за сутки, группируя данные по дням недели:

SELECT DAYNAME(dt), AVG(hours), AVG(calls) 
FROM (
    SELECT DATE(calldate) AS dt, SUM(billsec)/3600 AS hours, COUNT(billsec) AS calls 
    FROM cdr
    WHERE calldate>'2010-10-01 00:00:00' AND calldate<'2010-11-01 00:00:00' 
    AND (dstchannel LIKE 'SIP/102%' OR dstchannel LIKE 'SIP/117%') 
    GROUP BY DATE(calldate)
) AS t1 
GROUP BY DAYNAME(dt);

На выходе получаем интересующий нас результат:

+-------------+------------+------------+
| DAYNAME(dt) | AVG(hours) | AVG(calls) |
+-------------+------------+------------+
| Friday      | 2.89125000 |    96.0000 |
| Monday      | 4.06492500 |   139.0000 |
| Saturday    | 4.36542500 |   170.5000 |
| Sunday      | 2.72717500 |    98.0000 |
| Thursday    | 2.80562500 |    90.5000 |
| Tuesday     | 3.93172500 |   118.2500 |
| Wednesday   | 3.01332500 |    99.7500 |
+-------------+------------+------------+

Оставить отзыв

Имя

Mail

Website

CAPTCHA Code *

Current month ye@r day *

Leave this field empty *