SQL-запрос: кто сколько платит на каждом тарифе

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

(Различия между версиями)
Перейти к: навигация, поиск
 
Строка 72: Строка 72:
</source>
</source>
--[[Участник:DimOn|dimOn]] 06:45, 25 мая 2010 (UTC)
--[[Участник:DimOn|dimOn]] 06:45, 25 мая 2010 (UTC)
 +
 +
 +
а вот и отчет по этому запросу
 +
 +
http://wiki.bgbilling.ru/index.php/%D0%9A%D1%82%D0%BE_%D1%81%D0%BA%D0%BE%D0%BB%D1%8C%D0%BA%D0%BE_%D0%BF%D0%BB%D0%B0%D1%82%D0%B8%D1%82_%D0%B2_%D0%BC%D0%B5%D1%81%D1%8F%D1%86_%D0%BF%D0%BE_%D0%BA%D0%B0%D0%B6%D0%B4%D0%BE%D0%BC%D1%83_%D1%82%D0%B0%D1%80%D0%B8%D1%84%D1%83

Текущая версия на 04:21, 28 мая 2010

Было такое задание: сделать sql запрос, который будет выводить количество человек напротив ТП которые платят в месяц <100 р 100-200 и тд с шагом 100 р до какой-либо суммы.

Развитие мысли начинается с вывода списка всех тарифов (заодно с количеством их использований в договорах), за базу берём contract_tariff, ибо всё будет крутиться вокруг договора и связок его. 

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=455) за какой-либо месяц (03.2009) проще всего сделать через contract_balance, так: 

SELECT summa2 FROM contract_balance WHERE yy=2009 AND mm=3 AND cid=455

Объединяем эти два запроса и что получается? Верно, сколько было всех приходов по каждому тарифу. 

SELECT
	contract_tariff.tpid,
	tariff_plan.title,
	count(contract_tariff.id),
	sum(contract_balance.summa2)
FROM contract_tariff
	LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id
	LEFT JOIN contract_balance ON contract_balance.yy=2009 AND contract_balance.mm=3 AND contract_balance.cid=contract_tariff.cid
GROUP BY contract_tariff.tpid

Близко, но нам надо разбитие. По аналогии с запросом "Распределение трафика по тарифам" вот что приходит в голову: 

SELECT
	contract_tariff.tpid,
	tariff_plan.title,
	count(contract_tariff.id),
	SUM(IF(contract_balance.summa2>=000&&contract_balance.summa2<100, contract_balance.summa2, 0)) AS 000_100,
	SUM(IF(contract_balance.summa2>=100&&contract_balance.summa2<200, contract_balance.summa2, 0)) AS 100_200,
	SUM(IF(contract_balance.summa2>=200&&contract_balance.summa2<300, contract_balance.summa2, 0)) AS 200_300,
	SUM(IF(contract_balance.summa2>=300&&contract_balance.summa2<400, contract_balance.summa2, 0)) AS 300_400,
	SUM(IF(contract_balance.summa2>=400&&contract_balance.summa2<500, contract_balance.summa2, 0)) AS 400_500,
	SUM(IF(contract_balance.summa2>=500&&contract_balance.summa2<600, contract_balance.summa2, 0)) AS 500_600,
	SUM(IF(contract_balance.summa2>=600&&contract_balance.summa2<700, contract_balance.summa2, 0)) AS 600_700,
	SUM(IF(contract_balance.summa2>=700&&contract_balance.summa2<800, contract_balance.summa2, 0)) AS 700_800,
	SUM(IF(contract_balance.summa2>=800&&contract_balance.summa2<900, contract_balance.summa2, 0)) AS 800_900,
	SUM(IF(contract_balance.summa2>=900&&contract_balance.summa2<1000, contract_balance.summa2, 0)) AS 900_1000,
	SUM(IF(contract_balance.summa2>=1000, contract_balance.summa2, 0)) AS 1000_xxx,
	sum(contract_balance.summa2) AS summmm
FROM contract_tariff
	LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id
	LEFT JOIN contract_balance ON contract_balance.yy=2009 AND contract_balance.mm=3 AND contract_balance.cid=contract_tariff.cid
GROUP BY contract_tariff.tpid

Но это разбито не на кол-во договоров, которые принесли столько-то денег, а просто просуммированы сами деньги, попадающие в диапазон. То есть это типа как предыдущий запрос, но общая сумма разбита на промежутки согласно величине платежей за месяц. С SUM и IF очевидно выглядит. А вот посчитать количество нам поможет одна хитрость вкупе с IF - count агрегирует только значения столбца НЕ равные NULL. Итог: 

SELECT
	contract_tariff.tpid,
	tariff_plan.title,
	count(contract_tariff.id),
	count(IF(contract_balance.summa2>=000&&contract_balance.summa2<100, contract_balance.summa2, NULL)) AS 000_100,
	count(IF(contract_balance.summa2>=100&&contract_balance.summa2<200, contract_balance.summa2, NULL)) AS 100_200,
	count(IF(contract_balance.summa2>=200&&contract_balance.summa2<300, contract_balance.summa2, NULL)) AS 200_300,
	count(IF(contract_balance.summa2>=300&&contract_balance.summa2<400, contract_balance.summa2, NULL)) AS 300_400,
	count(IF(contract_balance.summa2>=400&&contract_balance.summa2<500, contract_balance.summa2, NULL)) AS 400_500,
	count(IF(contract_balance.summa2>=500&&contract_balance.summa2<600, contract_balance.summa2, NULL)) AS 500_600,
	count(IF(contract_balance.summa2>=600&&contract_balance.summa2<700, contract_balance.summa2, NULL)) AS 600_700,
	count(IF(contract_balance.summa2>=700&&contract_balance.summa2<800, contract_balance.summa2, NULL)) AS 700_800,
	count(IF(contract_balance.summa2>=800&&contract_balance.summa2<900, contract_balance.summa2, NULL)) AS 800_900,
	count(IF(contract_balance.summa2>=900&&contract_balance.summa2<1000, contract_balance.summa2, NULL)) AS 900_1000,
	count(IF(contract_balance.summa2>=1000, contract_balance.summa2, NULL)) AS 1000_xxx,
	sum(contract_balance.summa2) AS summmm
FROM contract_tariff
	LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id
	LEFT JOIN contract_balance ON contract_balance.yy=2009 AND contract_balance.mm=3 AND contract_balance.cid=contract_tariff.cid
GROUP BY contract_tariff.tpid

--dimOn 06:45, 25 мая 2010 (UTC)


а вот и отчет по этому запросу

http://wiki.bgbilling.ru/index.php/%D0%9A%D1%82%D0%BE_%D1%81%D0%BA%D0%BE%D0%BB%D1%8C%D0%BA%D0%BE_%D0%BF%D0%BB%D0%B0%D1%82%D0%B8%D1%82_%D0%B2_%D0%BC%D0%B5%D1%81%D1%8F%D1%86_%D0%BF%D0%BE_%D0%BA%D0%B0%D0%B6%D0%B4%D0%BE%D0%BC%D1%83_%D1%82%D0%B0%D1%80%D0%B8%D1%84%D1%83

Источник — «http://wiki.bitel.ru/index.php/SQL-%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81:_%D0%BA%D1%82%D0%BE_%D1%81%D0%BA%D0%BE%D0%BB%D1%8C%D0%BA%D0%BE_%D0%BF%D0%BB%D0%B0%D1%82%D0%B8%D1%82_%D0%BD%D0%B0_%D0%BA%D0%B0%D0%B6%D0%B4%D0%BE%D0%BC_%D1%82%D0%B0%D1%80%D0%B8%D1%84%D0%B5»
Личные инструменты