Calculate the difference between two calculated fields

Question

I have this request, which basically gets the average customer expenses for the last year and 3 months:

SELECT SQL_CALC_FOUND_ROWS customer_id, customer_name, AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), spend_amount, NULL )) AS 1_year_average_spend, AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y-%m-01"), spend_amount, NULL )) AS 3_month_average_spend FROM customer_spends GROUP BY customer__id 

But I also need to get the percentage difference in average costs:

eg. (pseudo code)

 if (1_year_average_spend = 0) change = N/A else change = 3_month_average_spend / 1_year_average_spend - 1 

How can or what do you recommend to implement this?

The only way I can think of is terrible:

 IF( AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), `spend_amount`, NULL )) > 0, AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y-%m-01"), spend_amount, NULL )) / AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), `spend_amount`, NULL )) - 1, "N/A" ) AS 3_month_performance 

Answer 1

If you are happy to use specific MySQL code, you can use Custom Variables like this (simplified version):

 SELECT @avg1 := ROUND((1 + 2 + 3) / 3, 2) AS avg1, @avg2 := ROUND((4 + 5 + 6) / 3, 2) AS avg2, IF( @avg1, ROUND(@avg2 / @avg1 - 1, 2), NULL ) AS result; +------+------+--------+ | avg1 | avg2 | result | +------+------+--------+ | 2.00 | 5.00 | 1.50 | +------+------+--------+ 

This will become the following:

 SELECT SQL_CALC_FOUND_ROWS customer_id, customer_name, @1_year_average_spend := AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), spend_amount, NULL )) AS 1_year_average_spend, @3_month_average_spend := AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y-%m-01"), spend_amount, NULL )) AS 3_month_average_spend, IF( @1_year_average_spend, @3_month_average_spend / @1_year_average_spend - 1, NULL ) AS diff FROM customer_spends GROUP BY customer__id 

Note 1: I used diff as the column name for the difference, since change is a reserved word, so it can cause problems.

Note 2:. You should be aware of the following disclaimers from the documents, as they may affect your outcome:

Assigning decimal and real values ​​does not preserve the accuracy or scale of the value.

and

Generally, you should never assign a value to a user variable and read the value within the same statement. You can get the expected results, but this is not guaranteed. The recipient of something evaluating expressions using custom variables is undefined and may vary based on the elements contained in this statement. In SELECT @a, @a: = @a + 1, ..., you might think MySQL would evaluate @a and then do the second lesson. However, a change (for example, adding GROUP BY, HAVING, or ORDER BY) may force MySQL to choose an execution plan with a different evaluation order.

Therefore use with caution and proper testing!

Answer 2

Use the internal selection (it's like a temporary view) and select it. This should work:

 SELECT customer_id, customer_name, 1_year_average_spend, 3_month_average_spend, if (1_year_average_spend = 0, "N/A", (3_month_average_spend / 1_year_average_spend) - 1) AS 3_month_performance FROM (SELECT customer_id, customer_name, AVG(IF(DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), spend_amount, NULL)) AS 1_year_average_spend, AVG(IF(DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y-%m-01"), spend_amount, NULL)) AS 3_month_average_spend) FROM customer_spends GROUP BY customer_id, customer_name ) x 

Answer 3

Will NULL do as an N/A value? If so, you can apply NULLIF() to the denominator as follows:

 a / NULLIF(b, 0) - 1 

NULLIF returns NULL if its first argument is equal to the second argument. And if the operand is NULL , the whole expression evaluates to NULL .

Like @Bohemian, I also suggest using a subquery. Here's the full request:

 SELECT SQL_CALC_FOUND_ROWS customer_id, customer_name, 1_year_average_spend, 3_month_average_spend, 3_month_average_spend / NULLIF(1_year_average_spend, 0) - 1 AS change FROM ( SELECT customer_id, customer_name, AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 1 YEAR, "%Y-%m-01"), spend_amount, NULL )) AS 1_year_average_spend, AVG(IF( DATE(CONCAT(year_of_spend, "-", month_of_spend, "-01")) >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y-%m-01"), spend_amount, NULL )) AS 3_month_average_spend FROM customer_spends GROUP BY customer__id ) s 

Answer 4

Get rid of calls to the IF() , DATE() and CONCAT() functions. Your request, as now, should scan the entire customer_spends table and check these complex conditions for ALL rows, even if they have 10-year data.

This will also use the index (year_of_spend, month_of_spend) or (customer_id, year_of_spend, month_of_spend) to speed up the request:

 SELECT c.customer_id , c.customer_name , 1_year_average_spend , 3_month_average_spend , CASE WHEN 1_year_average_spend = 0 THEN 'N/A' ELSE (3_month_average_spend / 1_year_average_spend) - 1 END AS percent_difference FROM customer AS c JOIN ( SELECT customer_id , AVG(spend_amount) AS 1_year_average_spend FROM customer_spends WHERE (year_of_spend, month_of_spend) >= ( YEAR(CUR_DATE() - INTERVAL 1 YEAR) , MONTH(CUR_DATE() - INTERVAL 1 YEAR) ) GROUP BY customer_id ) AS grp1year ON grp1year.customer_id = c.customer_id LEFT JOIN ( SELECT customer_id , AVG(spend_amount) AS 3_month_average_spend FROM customer_spends WHERE (year_of_spend, month_of_spend) >= ( YEAR(CUR_DATE() - INTERVAL 3 MONTH) , MONTH(CUR_DATE() - INTERVAL 3 MONTH) ) GROUP BY customer_id ) AS grp3month ON grp3month.customer_id = c.customer_id