MySQL:计算与上一年相比的总数和百分比

Question

我有这个表结构

+------------+--------+
| date       | amount |
+------------+--------+
| 2020-05-01 |     10 |
| 2020-10-01 |     15 |
| 2021-03-01 |      9 |
| 2021-09-01 |     10 |
| 2021-12-01 |     15 |
| 2022-06-01 |     19 |
| 2022-08-01 |     25 |
| 2022-09-01 |     13 |
+---------------------+

我想计算每年的总额，以及与前一年相比的百分比

SELECT YEAR(p.date) AS year, SUM(p.amount) AS year_total, SUM(p1.amount) AS prev_year, ROUND(SUM(p1.amount)/SUM(p.amount)*100) AS percentage
FROM payments p
LEFT JOIN payments p1
ON YEAR(p1.date) = YEAR(p.date)-1
GROUP BY year
ORDER BY year DESC;

但有了这个查询，结果就变得疯狂了。。。除第一年外，总数都不正确。

+------+------------+-----------+------------+
| year | year_total | prev_year | percentage |
+------+------------+-----------+------------+
| 2022 |        171 |       102 |         60 |
| 2021 |         68 |        75 |        110 |
| 2020 |         25 |      NULL |       NULL |
+------+------------+-----------+------------+

我想我有一个问题小组，但我找不到解决方案。

编辑：忘记提到我正在使用MariaDB 10

Answer 1

您的查询将一年的每条记录与上一年的每个记录连接起来。然后取按年份分组的总和。最简单的解决方案是像Tim Biegeleisen那样使用像LAG这样的窗口函数。如果你必须使用旧版本的MySQL，你必须在加入之前计算总和。

草图：

SELECT c.year, c.total, p.total
FROM (SELECT year(date) AS year, sum(amount) AS total FROM payments GROUP BY year(date)) c
LEFT JOIN (SELECT year(date) AS year, sum(amount) AS total FROM payments GROUP BY year(date)) p
ON p.year = c.year - 1

Answer 2

在MySQL 8+上，我们可以在这里使用LAG()：

SELECT YEAR(date) AS year, SUM(amount) AS year_total,
LAG(SUM(amount)) OVER (ORDER BY YEAR(date)) AS prev_year,
100.0 * LAG(SUM(amount)) OVER (ORDER BY YEAR(date)) / SUM(amount) AS percentage
FROM payments
GROUP BY YEAR(date)
ORDER BY YEAR(date) DESC;