MYSQL求月份同比数据和环比数据

1.需求题目如下 

1.首先求出每月每个account_id 对应的amount金额

 

2.利用表自关联，获取上月，上年对应月份及金额，

关联条件利用 主表月份-1个月=上月月份 和 主表月份-1年=上年月份

3.最后求同比和环比

 

附代码及测试数据

CREATE TABLE transactions (  transaction_id VARCHAR(255),  account_id VARCHAR(255),  amount DECIMAL(10, 2),  transaction_date DATE  
);INSERT INTO transactions (transaction_id, account_id, amount, transaction_date) VALUES  
('0C', 'C1', 100.00, '2021-01-15'),  
('10', 'C1', 150.00, '2021-02-15'),  
('01', 'C2', 200.00, '2021-03-15'),  
('02', 'C2', 250.00, '2021-04-15'),  
('03', 'C1', 300.00, '2022-01-20'),  
('04', 'C1', 350.00, '2022-02-20'),  
('05', 'C2', 400.00, '2021-02-18'),  
('06', 'C2', 450.00, '2021-03-18'),  
('07', 'C1', 500.00, '2021-04-18'),  
('08', 'C2', 550.00, '2022-02-18');WITH MonthlyTotals AS (SELECTaccount_id,substr(transaction_date,1,7) AS month_year,SUM(amount) AS total_amountFROMtransactionsGROUP BYaccount_id,substr(transaction_date,1,7)
),
LaggedMonthlyTotals AS (SELECTmt.account_id,mt.month_year,mt.total_amount,sy.month_year as month_year_sy,sn.month_year as month_year_sn,sy.total_amount as prev_month_amount,sn.total_amount as prev_year_same_month_amountFROMMonthlyTotals mtleft join MonthlyTotals sy on mt.account_id=sy.account_id and DATE_FORMAT(  DATE_SUB(STR_TO_DATE(CONCAT(mt.month_year, '-01'), '%Y-%m-%d'), INTERVAL 1 Month ), '%Y-%m')=sy.month_yearleft join MonthlyTotals sn on mt.account_id=sn.account_id and DATE_FORMAT(  DATE_SUB(STR_TO_DATE(CONCAT(mt.month_year, '-01'), '%Y-%m-%d'), INTERVAL 1 YEAR ), '%Y-%m')=sn.month_year
)
SELECTlmt.account_id,lmt.month_year,lmt.total_amount,-- 计算环比百分比CASEWHEN lmt.prev_month_amount IS NULL THEN NULLELSE ((lmt.total_amount - lmt.prev_month_amount) / lmt.prev_month_amount) * 100END AS 环比,-- 计算同比百分比CASEWHEN lmt.prev_year_same_month_amount IS NULL THEN NULLELSE ((lmt.total_amount - lmt.prev_year_same_month_amount) / lmt.prev_year_same_month_amount) * 100END AS 同比
FROMLaggedMonthlyTotals lmtorder by account_id,month_year;