Разные SQL-запросы

Материал из BiTel WiKi

(Различия между версиями)

Версия 07:32, 26 мая 2011

Содержание

1 Тарифы договора
2 Активные договоры без тарифов
3 Дублированные тарифы
4 Использование тарифов (трафик по договорам)
5 Распределение трафика по тарифам
6 Выбрать все договоры без установленного параметра
7 Выбрать все информацию по тарифу(все узлы тарифа)
8 Все тарифы, в которых есть узел модуля
9 Работа с группами
10 Генерация паролей
11 Выбор должников для системы автоматического обзвона
12 Удаление абонплат, привязанных к уже несуществующим договорам

Тарифы договора

Активные на сегодня тарифы для договора. Версия 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

Выбрать все информацию по тарифу(все узлы тарифа)

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 тарифного плана

Все тарифы, в которых есть узел модуля

вариант предыдущего запроса

SELECT * FROM tariff_plan 
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 AND module_tariff_tree.mid=XXX

Работа с группами

Установка группы с кодом 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;