I'm trying to calculate churn using one m开发者_JAVA百科ysql query... I can do this in PHP, but it would be very helpful if mysql could do it.
Here's my query:
SELECT
bill_date_bil,
account_number_bil,
SUM(amount_bil)
FROM
billing_bil
WHERE account_number_bil = 20017
AND type_bil <> 'CA'
AND type_bil <> 'CK'
GROUP BY period (bill_date_bil)
ORDER BY bill_date_bil ASC
and here's what I get:
bill_date_bil account_number_bil sum(amount_bil)
------------- ------------------ ---------------
2007-09-01 20017 3498.5000
2007-10-01 20017 8248.5000
2007-11-01 20017 9886.0000
2007-12-01 20017 9411.0000
2008-01-01 20017 9411.0000
2008-02-01 20017 9411.0000
2008-03-01 20017 -6151.5000
2008-04-01 20017 6118.5400
2008-05-01 20017 11171.9800
2008-06-01 20017 6434.3800
2008-07-01 20017 6434.3800
I would like to have another column that gives me the difference between the current bill and the previous months bill.
Thoughts?
here's what I need:
bill_date_bil account_number_bil sum(amount_bil) churn
------------- ------------------ ---------------
9/1/2007 20017 3498.5 0
10/1/2007 20017 8248.5 4750
11/1/2007 20017 9886 1637.5
12/1/2007 20017 9411 -475
1/1/2008 20017 9411 0
2/1/2008 20017 9411 0
3/1/2008 20017 -6151.5 -15562.5
4/1/2008 20017 6118.54 12270.04
5/1/2008 20017 11171.98 5053.44
6/1/2008 20017 6434.38 -4737.6
7/1/2008 20017 6434.38 0
Use this:
SET @sum_bill=0;
SELECT
bill_date_bil,
account_number_bil,
SUM(amount_bil),
@sum_bill := @sum_bill+SUM(amount_bil)
FROM
billing_bil
WHERE account_number_bil = 20017
AND type_bil <> 'CA'
AND type_bil <> 'CK'
GROUP BY period (bill_date_bil)
ORDER BY bill_date_bil ASC
精彩评论