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

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 (
    SELECT
        account_id,
        substr(transaction_date,1,7) AS month_year,
        SUM(amount) AS total_amount
    FROM
        transactions
    GROUP BY
        account_id,
        substr(transaction_date,1,7)
),
LaggedMonthlyTotals AS (
    SELECT
        mt.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_amount
    FROM
        MonthlyTotals mt
left 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_year
left 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
)
SELECT
    lmt.account_id,
    lmt.month_year,
    lmt.total_amount,
    -- 计算环比百分比
    CASE
        WHEN lmt.prev_month_amount IS NULL THEN NULL
        ELSE ((lmt.total_amount - lmt.prev_month_amount) / lmt.prev_month_amount) * 100
    END AS 环比,
    -- 计算同比百分比
    CASE
        WHEN lmt.prev_year_same_month_amount IS NULL THEN NULL
        ELSE ((lmt.total_amount - lmt.prev_year_same_month_amount) / lmt.prev_year_same_month_amount) * 100
    END AS 同比
FROM
    LaggedMonthlyTotals lmt
order by account_id,month_year
;

原文地址：https://blog.csdn.net/weixin_42698221/article/details/142631517

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！