Tamkovich.com: Телеком/VoIP блог
Современные технологии: Asterisk, SIP, Kamailio, Linux, Cisco, Linksys
MySQL: о пользе вложенных запросов
28 ноября, 2010 by Сергей Тамкович
Разное MySQLНе так давно, начиная с версии 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 | +-------------+------------+------------+
Разное MySQL