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
Близко, но нам надо разбитие. По аналогии с запросом "Распределение трафика по тарифам" вот что приходит в голову:
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)
