Разные SQL-запросы
Материал из BiTel WiKi
(→Получение последних балансов) |
Stark (Обсуждение | вклад) |
||
Строка 249: | Строка 249: | ||
Далее выбирать из contract_balance с JOIN на эту таблицу по cid, yy, mm. | Далее выбирать из contract_balance с JOIN на эту таблицу по cid, yy, mm. | ||
+ | |||
+ | |||
+ | == Получение балансов за конкретный месяц(без темповой таблицы, запрос может долго выполнятся ) == | ||
+ | |||
+ | <source lang="sql"> | ||
+ | select * from contract_balance as balance | ||
+ | inner join | ||
+ | ( | ||
+ | SELECT cid, max( yy * 12 + ( mm - 1 ) ) % 12 + 1 AS mm, | ||
+ | FLOOR( max(yy * 12 + ( mm - 1 ) ) / 12 ) AS yy | ||
+ | FROM contract_balance WHERE ( ( yy * 12 ) + mm ) <= ( ( 2013 * <year> ) + <mm> ) GROUP BY cid | ||
+ | |||
+ | ) as dump | ||
+ | on dump.cid =balance.cid and balance.yy = dump.yy and balance.mm=dump.mm | ||
+ | |||
+ | </source> | ||
+ | |||
+ | Где <year>, <mm> - интересующий год, месяц. |
Версия 09:09, 8 ноября 2013
Тарифы договора
Активные на сегодня тарифы для договора. Версия 4.6/5.0
SELECT tariff_plan.title FROM tariff_plan INNER JOIN contract_tariff ON contract_tariff.tpid = tariff_plan.id AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) WHERE contract_tariff.cid=352715
или лучше
SELECT contract_tariff.tpid, contract_tariff.date1, contract_tariff.date2, tariff_plan.title FROM contract_tariff LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid WHERE contract_tariff.cid=352715 AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE())
--dimOn 07:49, 18 февраля 2010 (UTC)
Активные договоры без тарифов
Все договоры, у которых статус "активен" и у которых на сегодня нет ни одного тарифного плана. Версия 4.6/5.0
SELECT contract.id, contract.title, contract.comment, contract.STATUS, ct.id FROM contract LEFT JOIN contract_tariff AS ct ON contract.id=ct.cid AND (ct.date1 IS NULL OR ct.date1<=CURDATE()) AND (ct.date2 IS NULL OR ct.date2>=CURDATE()) WHERE ct.id IS NULL AND contract.STATUS=0
--dimOn 12:32, 17 февраля 2010 (UTC)
Дублированные тарифы
Показывает сколько в каждом договоре каждых тарифов, по убыванию. Было сделано для поиска одинаковых тарифов в договорах. Правда, не учитывает даты (добавить несложно), но для поиска дубляжей сойдёт. Версия 4.6/5.0
SELECT contract_tariff.cid, contract_tariff.tpid, count(contract_tariff.id) AS tariff_count FROM contract_tariff GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
или то же самое, с дополнительной инфой:
SELECT count(contract_tariff.id) AS tariff_count, contract_tariff.tpid, contract.id, contract.title, contract.comment, tariff_plan.title FROM contract_tariff LEFT JOIN contract ON contract.id=contract_tariff.cid LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
вот с добавлением учёта дат:
SELECT count(contract_tariff.id) AS tariff_count, contract_tariff.tpid, contract_tariff.cid, contract.title, contract.comment, tariff_plan.title FROM contract_tariff LEFT JOIN contract ON contract.id=contract_tariff.cid LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid WHERE (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
ORDER BY tariff_count DESC
HAVING tariff_count>1
--dimOn 12:32, 17 февраля 2010 (UTC)
Использование тарифов (трафик по договорам)
Нужна таблица Тариф | Количество активных договоров (тех, у кого трафик за месяц больше определённого значения)
# чистыми цифрами: тарифный план -> его использований в договорах SELECT contract_tariff.tpid, count(contract_tariff.id) FROM contract_tariff GROUP BY contract_tariff.tpid
# джойним название SELECT contract_tariff.tpid, tariff_plan.title, count(contract_tariff.id) FROM contract_tariff LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id GROUP BY contract_tariff.tpid
Разбираемся с трафиком:
# связка cid => сумма трафика за месяц (+выборка тех, у кого трафик больше чего-либо) SELECT `detail`.cid, sum(`detail`.`amount`) AS trafic FROM `session_detail_21_200810` AS `detail` GROUP BY `detail`.cid HAVING trafic > 10
Вот первая версия запроса, "в лоб". Просто объединяем предыдущие и суём вторую выборку в подзапрос. Работает оооооочень медленно %(
SELECT contract_tariff.tpid, tariff_plan.title, count(contract_tariff.id) FROM contract_tariff LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id WHERE contract_tariff.cid IN (SELECT cid FROM `session_detail_21_200810` GROUP BY cid HAVING sum(`amount`) > 10) GROUP BY contract_tariff.tpid
Правильный запрос - это через создание (временной) таблицы.
CREATE TABLE _good_cid (SELECT cid FROM `session_detail_21_200810` GROUP BY cid HAVING sum(`amount`) > 10); SELECT contract_tariff.tpid, tariff_plan.title, count(contract_tariff.id) FROM contract_tariff LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id INNER JOIN _good_cid ON contract_tariff.cid=_good_cid.cid GROUP BY contract_tariff.tpid
--dimOn 09:42, 4 марта 2010 (UTC)
Распределение трафика по тарифам
Делает статистику по месяцам - сколько в каждый час трафика прошло, с разбивкой по привязанным тарифам. Несколько синтетично, но кому-то бывает полезно. Получается таблица строки - все тарифы, столбцы - часы.
SELECT tariff.id, tariff.title, HOUR(detail.dtime) AS hh, SUM(IF(HOUR(detail.dtime)=0, detail.amount, 0)) AS 0_amount, SUM(IF(HOUR(detail.dtime)=1, detail.amount, 0)) AS 1_amount, SUM(IF(HOUR(detail.dtime)=2, detail.amount, 0)) AS 2_amount, SUM(IF(HOUR(detail.dtime)=3, detail.amount, 0)) AS 3_amount, SUM(IF(HOUR(detail.dtime)=4, detail.amount, 0)) AS 4_amount, SUM(IF(HOUR(detail.dtime)=5, detail.amount, 0)) AS 5_amount, SUM(IF(HOUR(detail.dtime)=6, detail.amount, 0)) AS 6_amount, SUM(IF(HOUR(detail.dtime)=7, detail.amount, 0)) AS 7_amount, SUM(IF(HOUR(detail.dtime)=8, detail.amount, 0)) AS 8_amount, SUM(IF(HOUR(detail.dtime)=9, detail.amount, 0)) AS 9_amount, SUM(IF(HOUR(detail.dtime)=10, detail.amount, 0)) AS 10_amount, SUM(IF(HOUR(detail.dtime)=11, detail.amount, 0)) AS 11_amount, SUM(IF(HOUR(detail.dtime)=12, detail.amount, 0)) AS 12_amount, SUM(IF(HOUR(detail.dtime)=13, detail.amount, 0)) AS 13_amount, SUM(IF(HOUR(detail.dtime)=14, detail.amount, 0)) AS 14_amount, SUM(IF(HOUR(detail.dtime)=15, detail.amount, 0)) AS 15_amount, SUM(IF(HOUR(detail.dtime)=16, detail.amount, 0)) AS 16_amount, SUM(IF(HOUR(detail.dtime)=17, detail.amount, 0)) AS 17_amount, SUM(IF(HOUR(detail.dtime)=18, detail.amount, 0)) AS 18_amount, SUM(IF(HOUR(detail.dtime)=19, detail.amount, 0)) AS 19_amount, SUM(IF(HOUR(detail.dtime)=20, detail.amount, 0)) AS 20_amount, SUM(IF(HOUR(detail.dtime)=21, detail.amount, 0)) AS 21_amount, SUM(IF(HOUR(detail.dtime)=22, detail.amount, 0)) AS 22_amount, SUM(IF(HOUR(detail.dtime)=23, detail.amount, 0)) AS 23_amount FROM session_detail_21_200909 AS detail LEFT JOIN contract_tariff ON contract_tariff.cid=detail.cid AND contract_tariff.date1<=detail.dtime AND (contract_tariff.date2 IS NULL OR detail.dtime<=contract_tariff.date2) LEFT JOIN tariff_plan AS tariff ON contract_tariff.tpid=tariff.id GROUP BY tariff.id ORDER BY tariff.title
Если надо разделять по входящему/исходящему (и/или по прочим услугам), то добавить между JOIN и GROUP BY условие
WHERE detail.sid = <ид услуги нужной>
Если были битые ссылки на тарифы, то будет строка с нулём, куда припишется всё левое, если надо откинуть поле с нулём, прибавить (к WHERE, разумеется) условие:
AND ( tariff.id IS NOT NULL )
--dimOn 10:40, 10 марта 2010 (UTC)
Выбрать все договоры без установленного параметра
Если pid параметра = 10, то:
SELECT contract.id FROM contract LEFT JOIN contract_parameter_type_1 ON contract_parameter_type_1.cid=contract.id AND contract_parameter_type_1.pid=10 WHERE contract_parameter_type_1.val IS NULL
Выбрать все информацию по тарифу( версия <=5.1 )
SELECT * FROM tariff_plan LEFT JOIN tariff_tree_link ON tariff_tree_link.tpid= tariff_plan.id LEFT JOIN tariff_tree ON tariff_tree.id = tariff_tree_link.tree_id LEFT JOIN module_tariff_tree ON module_tariff_tree.tree_id = tariff_tree_link.tree_id LEFT JOIN mtree_node ON mtree_node.mtree_id = module_tariff_tree.id WHERE tariff_plan.id = XXX
XXX - id тарифного плана
Выбрать все информацию по тарифу( версия >= 5.2)
SELECT * FROM tariff_plan -- LEFT JOIN tariff_tree_link ON tariff_tree_link.tpid= tariff_plan.id LEFT JOIN tariff_tree ON tariff_tree.id = tariff_plan.tree_id LEFT JOIN module_tariff_tree ON module_tariff_tree.tree_id = tariff_plan.tree_id LEFT JOIN mtree_node ON mtree_node.mtree_id = module_tariff_tree.id WHERE tariff_plan.id = XXX
XXX - id тарифного плана
Все тарифы, в которых есть узел модуля
вариант предыдущего запроса
SELECT * FROM tariff_plan JOIN tariff_tree_link ON tariff_tree_link.tpid= tariff_plan.id JOIN module_tariff_tree ON module_tariff_tree.tree_id = tariff_tree_link.tree_id WHERE module_tariff_tree.mid=XXX ORDER BY tariff_plan.id
Работа с группами
Установка группы с кодом 7 договору с кодом 455:
UPDATE contract SET gr = gr | (1<<7) WHERE id=455
Удаление группы с кодом 7 из договора с кодом 455:
UPDATE contract SET gr = gr&(~(1<<7)) WHERE id=455
Поиск договоров с группой 7:
SELECT * FROM contract WHERE gr&(1<<7) > 0
Генерация паролей
UPDATE CONTRACT SET pswd=SUBSTRING(RAND(9), 3, 14)
Выбор должников для системы автоматического обзвона
Стоит задача написать некий код, который бы выбирал номера телефона из контракта, и сумму задолженности, для передачи в систему обзвона должников. Условия отбора должников - Контракт, входящий остаток которого меньше нуля и по которому нет поступлений в текущем месяце, а так же в параметрах которого установлен флаг "Включить в систему обзвона должников". Обсуждение сдесь: http://forum.bgbilling.ru/viewtopic.php?f=19&t=5303
в разработке
Удаление абонплат, привязанных к уже несуществующим договорам
DELETE service_object FROM npay_service_object_66 AS service_object LEFT JOIN contract ON service_object.cid=contract.id WHERE contract.id IS NULL;
Получение последних балансов
Выборка yy, mm последних записей из contract_balance.
CREATE TABLE balance_dump( UNIQUE(cid) ) SELECT cid, MAX(yy*12+(mm-1))%12 + 1 AS mm, FLOOR(MAX(yy*12+(mm-1)) / 12) AS yy FROM contract_balance WHERE ((yy*12) + mm)<=((?*12) + ?) GROUP BY cid
Далее выбирать из contract_balance с JOIN на эту таблицу по cid, yy, mm.
Получение балансов за конкретный месяц(без темповой таблицы, запрос может долго выполнятся )
SELECT * FROM contract_balance AS balance INNER JOIN ( SELECT cid, max( yy * 12 + ( mm - 1 ) ) % 12 + 1 AS mm, FLOOR( max(yy * 12 + ( mm - 1 ) ) / 12 ) AS yy FROM contract_balance WHERE ( ( yy * 12 ) + mm ) <= ( ( 2013 * <year> ) + <mm> ) GROUP BY cid ) AS dump ON dump.cid =balance.cid AND balance.yy = dump.yy AND balance.mm=dump.mm
Где <year>, <mm> - интересующий год, месяц.