mysql 计算 两个字段 之差_mysql – 计算两个计算字段的差异

我有这个查询,它基本上得到了客户在过去一年和3个月的平均支出：

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

但我还需要得到支出平均值的百分比差异：

例如. (伪代码)

if (1_year_average_spend = 0)

change = N/A

else

change = 3_month_average_spend / 1_year_average_spend - 1

怎么可以,或者你建议我做什么来实现这个？

我能想到的唯一方法是可怕的：

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

解决方法:

如果您乐意使用MySQL特定代码,可以像这样使用User-Defined Variables(简化版)：

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 |

+------+------+--------+

那会变成：

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

注1：我使用diff作为差异的列名,因为更改是保留字,因此可能最终导致问题.

注意2：您需要了解文档中的以下警告,因为它们可能会影响您的结果：

Assignment of decimal and real values does not preserve the precision

or scale of the value.

和：

As a general rule, you should never assign a value to a user variable

and read the value within the same statement. You might get the

results you expect, but this is not guaranteed. The order of

evaluation for expressions involving user variables is undefined and

may change based on the elements contained within a given statement.

In SELECT @a, @a:=@a+1, …, you might think that MySQL will evaluate

@a first and then do an assignment second. However, changing the

statement (for example, by adding a GROUP BY, HAVING, or ORDER BY

clause) may cause MySQL to select an execution plan with a different

order of evaluation.

所以要谨慎使用,并进行适当的测试！

标签：mysql,sql

来源： https://codeday.me/bug/20190630/1338103.html