我有这个查询,它基本上得到了客户在过去一年和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