这篇博客介绍了一个MySQL查询，用于计算每日销售总额，并展示销售额的平均值和增长率。通过LEFT JOIN操作结合自定义变量@curRow和@curSum，实现了连续三天销售额的对比和计算。查询结果包括日期、销售额、平均销售额以及销售增长率。
这是完整的查询,每行没有子选择:(感谢@nmarsh编写最难的部分)
SELECT
t1.Date,
CASE
WHEN t2.date IS NULL THEN 0 ELSE (t1.totalSales - t2.totalSales)
END AS sales,
CASE
WHEN t2.date IS NULL THEN 0 / (@curRow := @curRow + 1) ELSE ((@curSum := @curSum + (t1.totalSales - t2.totalSales)) / (@curRow := @curRow + 1))
END AS average,
CASE
WHEN t3.date IS NULL AND t2.date IS NULL THEN 0
WHEN t3.date IS NULL THEN (t1.totalSales - t2.totalSales)
WHEN t2.date IS NULL THEN 0 ELSE ((t1.totalSales - t2.totalSales) * 100) / (t2.totalSales - t3.totalSales)
END AS growth
FROM test t1
LEFT JOIN test t2 ON t2.date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
LEFT JOIN test t3 ON t3.date = DATE_ADD(t2.Date, INTERVAL -1 DAY)
JOIN (SELECT @curRow := 0) r
JOIN (SELECT @curSum := 0) ct
ORDER BY 1;
原表：
date | totalsales
=======================
2014-05-01 | 3000
2014-05-02 | 3100
2014-05-03 | 3500
2014-05-04 | 3650
OUTPUT
date | sales | average | growth
=======================================
2014-05-01 | 0 | 0 | 0
2014-05-02 | 100 | 50 | 100
2014-05-03 | 400 | 166.66 | 400
2014-05-04 | 150 | 162.5 | 37.5
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
