我有一个查询,基本上是获取客户在过去一年和三个月内的平均消费:
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
if null or null = any or null if a = b
有点令人困惑,但是在我理解之后,听起来不错。 - Petah