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

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

(Различия между версиями)
Перейти к: навигация, поиск
(→Выбор должников для системы автоматического обзвона)
 
(19 промежуточных версий не показаны.)
Строка 103: Строка 103:
Правильный запрос - это через создание (временной) таблицы.
Правильный запрос - это через создание (временной) таблицы.
<source lang="sql">
<source lang="sql">
-
CREATE TABLE _good_cid SELECT cid FROM `session_detail_21_200810` GROUP BY cid HAVING sum(`amount`) > 10);
+
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)  
SELECT contract_tariff.tpid, tariff_plan.title, count(contract_tariff.id)  
Строка 167: Строка 167:
</source>
</source>
-
== Выбрать все информацию по тарифу(все узлы тарифа) ==
+
== Выбрать все информацию по тарифу( версия <=5.1 ) ==
<source lang="sql">
<source lang="sql">
Строка 179: Строка 179:
XXX - id тарифного плана
XXX - id тарифного плана
 +
 +
 +
== Выбрать все информацию по тарифу( версия >= 5.2) ==
 +
<source lang="sql">
 +
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
 +
</source>
 +
 +
XXX - id тарифного плана
 +
 +
== Выбрать все информацию по персональному тарифу( версия >= 5.2) ==
 +
<source lang="sql">
 +
SELECT * FROM contract_tree_link
 +
LEFT JOIN tariff_tree ON tariff_tree.id = contract_tree_link.tree_id
 +
LEFT JOIN module_tariff_tree ON  module_tariff_tree.tree_id =  contract_tree_link.tree_id
 +
LEFT JOIN mtree_node ON mtree_node.mtree_id = module_tariff_tree.id
 +
WHERE contract_tree_link.id = XXX
 +
</source>
 +
 +
XXX - id тарифного плана
 +
 +
== Все тарифы, в которых есть узел модуля ==
 +
вариант предыдущего запроса
 +
<source lang="sql">
 +
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
 +
</source>
== Работа с группами ==
== Работа с группами ==
Строка 208: Строка 242:
<source lang="sql">
<source lang="sql">
в разработке
в разработке
 +
</source>
 +
 +
== Удаление абонплат, привязанных к уже несуществующим договорам ==
 +
<source lang="sql">
 +
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;
 +
</source>
 +
 +
== Получение последних балансов ==
 +
Выборка yy, mm последних записей из contract_balance.
 +
 +
<source lang="sql">
 +
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
 +
</source>
 +
 +
Без ограничения по дате:
 +
 +
<source lang="sql">
 +
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 GROUP BY cid
 +
</source>
 +
 +
Далее выбирать из contract_balance с JOIN на эту таблицу по cid, yy, mm.
 +
 +
== Получение балансов за конкретный  месяц(без темповой таблицы, запрос может долго выполнятся )  ==
 +
 +
<source lang="sql">
 +
select balance.* 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 ) <=  ( ( 12 * <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> - интересующий год, месяц.
 +
 +
== Обновление поля с расходами за конкретный месяц для всех договоров  ==
 +
<source lang="sql">
 +
update contract_balance set summa4 = (select sum(summa)  from contract_charge where dt >= '2013-10-01' and dt <=  '2013-10-31' and contract_charge.cid=contract_balance.cid  )
 +
where yy = 2013 and mm = 10
 +
</source>
 +
 +
== Обновление поля с платежами за конкретный месяц для всех договоров  ==
 +
 +
<source lang="sql">
 +
update contract_balance set summa2 = (select sum(summa)  from contract_payment where dt >= '2013-09-01' and dt <=  '2013-09-30' and contract_payment.cid=contract_balance.cid  )
 +
  where yy = 2013 and mm = 9
 +
</source>
 +
 +
== Как найти все договора без услуги  ==
 +
 +
<source lang="sql">
 +
 +
SELECT c.id as cid, title FROM contract AS c
 +
LEFT JOIN npay_service_object_2 AS s ON s.cid = c.id AND s.sid = 2
 +
WHERE s.cid IS NULL
</source>
</source>

Текущая версия на 01:28, 20 ноября 2016

Содержание

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

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

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

SELECT * FROM contract_tree_link 
LEFT JOIN tariff_tree ON tariff_tree.id = contract_tree_link.tree_id
LEFT JOIN module_tariff_tree ON  module_tariff_tree.tree_id =  contract_tree_link.tree_id
LEFT JOIN mtree_node ON mtree_node.mtree_id = module_tariff_tree.id
WHERE contract_tree_link.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

Без ограничения по дате: 

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 GROUP BY cid

Далее выбирать из contract_balance с JOIN на эту таблицу по cid, yy, mm.

Получение балансов за конкретный месяц(без темповой таблицы, запрос может долго выполнятся ) 

SELECT balance.* 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 ) <=  ( ( 12 * <year> ) + <mm> ) GROUP BY cid
 
) AS dump 
ON dump.cid =balance.cid AND balance.yy = dump.yy AND balance.mm=dump.mm

Где <year>, <mm> - интересующий год, месяц.

Обновление поля с расходами за конкретный месяц для всех договоров 

UPDATE contract_balance SET summa4 = (SELECT sum(summa)  FROM contract_charge WHERE dt >= '2013-10-01' AND dt <=  '2013-10-31' AND contract_charge.cid=contract_balance.cid  ) 
WHERE yy = 2013 AND mm = 10

Обновление поля с платежами за конкретный месяц для всех договоров 

UPDATE contract_balance SET summa2 = (SELECT sum(summa)  FROM contract_payment WHERE dt >= '2013-09-01' AND dt <=  '2013-09-30' AND contract_payment.cid=contract_balance.cid  )
  WHERE yy = 2013 AND mm = 9

Как найти все договора без услуги 

SELECT c.id AS cid, title FROM contract AS c
LEFT JOIN npay_service_object_2 AS s ON s.cid = c.id AND s.sid = 2
WHERE s.cid IS NULL
Источник — «http://wiki.bitel.ru/index.php/%D0%A0%D0%B0%D0%B7%D0%BD%D1%8B%D0%B5_SQL-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81%D1%8B»
Личные инструменты