mysql实现环比

SELECT 
    DATE_FORMAT(s1.date, '%Y-%m') AS month,
    s1.amount AS current_amount,
    s2.amount AS previous_amount,
    s1.amount - s2.amount AS absolute_difference
FROM 
    sales s1
JOIN 
    sales s2 
ON 
    DATE_FORMAT(s1.date, '%Y-%m') = DATE_FORMAT(DATE_ADD(s2.date, INTERVAL 1 MONTH), '%Y-%m')
WHERE 
    s1.date > '2023-01-01' -- 假设我们只关心2023年之后的数据
ORDER BY 
    s1.date;

SELECT 
    DATE_FORMAT(s1.date, '%Y-%m') AS month,
    s1.amount AS current_amount,
    s2.amount AS previous_amount,
    ((s1.amount - s2.amount) / s2.amount) * 100 AS growth_rate
FROM 
    sales s1
JOIN 
    sales s2 
ON 
    DATE_FORMAT(s1.date, '%Y-%m') = DATE_FORMAT(DATE_ADD(s2.date, INTERVAL 1 MONTH), '%Y-%m')
WHERE 
    s1.date > '2023-01-01' -- 假设我们只关心2023年之后的数据
ORDER BY 
    s1.date;

SELECT 
    DATE_FORMAT(s1.date, '%Y-%m') AS month,
    COALESCE(s1.amount, 0) AS current_amount,
    COALESCE(s2.amount, 0) AS previous_amount,
    ((COALESCE(s1.amount, 0) - COALESCE(s2.amount, 0)) / COALESCE(s2.amount, 1)) * 100 AS growth_rate
FROM 
    sales s1
LEFT JOIN 
    sales s2 
ON 
    DATE_FORMAT(s1.date, '%Y-%m') = DATE_FORMAT(DATE_ADD(s2.date, INTERVAL 1 MONTH), '%Y-%m')
WHERE 
    s1.date > '2023-01-01'
ORDER BY 
    s1.date;

CREATE INDEX idx_date ON sales(date);