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

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

Перейти к: навигация, поиск

Было такое задание: сделать 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

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

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)

Личные инструменты