mysql - 计算两个计算字段的差异

Question

我有这个查询，它基本上获取了客户过去一年和 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

Answer 1

如果您乐于使用 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 作为差异的列名，因为 change 是保留字，所以可能会结束导致问题。

注意 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.

因此请谨慎使用，并进行适当的测试!