MariaDB 10中计算年度总额及同比占比的SQL异常问题

问题根源 #

你当前的SQL直接将原表与自身关联，导致笛卡尔积：当前年份的每一条记录都会匹配上一年的所有记录。比如2022年有3条记录，2021年有3条，关联后会生成3×3=9条记录，分组求和时year_total和prev_year都会被重复计算，所以出现了171（57×3）、68（34×2）这类错误结果。

正确解法 #

先按年份聚合得到每年的总金额，再基于这个聚合结果做自关联，避免重复计算：

方法1：使用CTE（MariaDB 10支持）

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

方法2：不使用CTE（兼容更早版本）

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

正确执行结果 #

+------+------------+-----------+------------+
| year | year_total | prev_year | percentage |
+------+------------+-----------+------------+
| 2022 |         57 |        34 |         60 |
| 2021 |         34 |        25 |         74 |
| 2020 |         25 |      NULL |       NULL |
+------+------------+-----------+------------+

内容的提问来源于stack exchange，提问作者Jibeji