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

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

(Различия между версиями)
Перейти к: навигация, поиск
(→Использование тарифов (трафик по договорам))
(+запрос)
Строка 112: Строка 112:
</source>
</source>
--[[Участник:DimOn|dimOn]] 09:42, 4 марта 2010 (UTC)
--[[Участник:DimOn|dimOn]] 09:42, 4 марта 2010 (UTC)
 +
 +
== Распределение трафика по тарифам ==
 +
Делает статистику по месяцам - сколько в каждый час трафика прошло, с разбивкой по привязанным тарифам. Несколько синтетично, но кому-то бывает полезно.
 +
<source lang="sql">
 +
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
 +
</source>
 +
Если надо разделять по входящему/исходящему (и/или по прочим услугам), то добавить между JOIN и GROUP BY условие
 +
<source lang="sql">
 +
WHERE detail.sid = <ид услуги нужной>
 +
</source>
 +
Если были битые ссылки на тарифы, то будет строка с нулём, куда припишется всё левое, если надо откинуть поле с нулём, прибавить (к WHERE, разумеется) условие:
 +
<source lang="sql">AND ( tariff.id is not null )</source>
 +
--[[Участник:DimOn|dimOn]] 10:40, 10 марта 2010 (UTC)

Версия 10:41, 10 марта 2010

Содержание

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

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

Источник — «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»
Личные инструменты