当前位置：首页 > news >正文

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

news 来源：原创 2024/9/30 13:55:39

1.需求题目如下

1.使用MonthlyTotals子查询计算每个账户在每个月的总交易金额。这里使用了substr函数将transaction_date字段的前7个字符提取为年份和月份，并使用SUM函数计算每个账户每个月的总金额。

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

使用LaggedMonthlyTotals子查询将每个月的总交易金额与相邻月和相同月的总交易金额进行连接。这里使用了两个左连接，分别连接相邻月的总金额和相同月的总金额。连接条件使用了日期函数和字符串处理函数将当前月份前一个月和前一年的同一月计算出来，并与对应的账户进行匹配。

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

3.使用最外层的查询从LaggedMonthlyTotals中选择账户ID、月份、总金额以及计算出的环比和同比百分比。环比百分比使用了一个CASE语句进行计算，如果前一个月的总金额存在，则将当前月的总金额减去前一个月的总金额，除以前一个月的总金额，并乘以100。同样，同比百分比的计算方式类似。

最后，按照账户ID和月份进行排序，并返回结果。

附代码及测试数据

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;