SQL-запрос: кто сколько платит на каждом тарифе
Материал из BiTel WiKi
(Различия между версиями)
DimOn (Обсуждение | вклад) (Новая страница: «Было такое задание: сделать sql запрос, который будет выводить количество человек напротив …») |
Skyb (Обсуждение | вклад) |
||
| (4 промежуточные версии не показаны) | |||
| Строка 24: | Строка 24: | ||
GROUP BY contract_tariff.tpid | GROUP BY contract_tariff.tpid | ||
</source> | </source> | ||
| + | Близко, но нам надо разбитие. По аналогии с запросом "[[Разные SQL-запросы#Распределение трафика по тарифам|Распределение трафика по тарифам]]" вот что приходит в голову: | ||
| + | <source lang="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 | ||
| + | </source> | ||
| + | Но это разбито не на кол-во договоров, которые принесли столько-то денег, а просто просуммированы сами деньги, попадающие в диапазон. То есть это типа как предыдущий запрос, но общая сумма разбита на промежутки согласно величине платежей за месяц. | ||
| + | С SUM и IF очевидно выглядит. А вот посчитать количество нам поможет одна хитрость вкупе с IF - count агрегирует только значения столбца НЕ равные NULL. Итог: | ||
| + | <source lang="sql"> | ||
| + | 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 | ||
| + | </source> | ||
| + | --[[Участник: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)
а вот и отчет по этому запросу
