使用Pandas计算月度支出的同比季度变化（含设施过滤）

核心需求回顾 #

• 仅保留当前季度与去年同期季度均有对应月份数据的设施（如示例中Facility 2因其中一个季度无数据需排除）
• 允许最近季度仅包含1-2个月数据
• 计算符合条件设施的季度总支出同比变化率

修正思路与代码实现 #

以下以SQL场景为例（数据分析中最常用的处理方式），给出修正后的完整逻辑：

步骤1：为交易数据标记季度/年度标签 #

先给每条数据打上季度、年度及去年同期年份的标签，方便后续分组筛选：

WITH transaction_with_qtr AS (
    SELECT
        FacilityID,
        Date,
        Amount,
        EXTRACT(QUARTER FROM Date) AS qtr,
        EXTRACT(YEAR FROM Date) AS yr,
        EXTRACT(YEAR FROM Date) - 1 AS prev_yr
    FROM transactions
),

步骤2：筛选符合条件的有效设施 #

精准定位同时在当前季度和去年同期季度有数据的设施：

valid_facilities AS (
    SELECT DISTINCT FacilityID
    FROM transaction_with_qtr
    -- 可替换为动态时间：EXTRACT(YEAR FROM CURRENT_DATE)、EXTRACT(QUARTER FROM CURRENT_DATE)
    WHERE (yr = 2024 AND qtr = 2) 
       OR (yr = 2023 AND qtr = 2)
    GROUP BY FacilityID
    -- 确保该设施在两个季度都有数据记录
    HAVING COUNT(DISTINCT CONCAT(yr, qtr)) = 2
),

步骤3：计算有效设施的季度总支出 #

对筛选后的设施，分别统计当前季度和去年同期的总支出：

qtr_totals AS (
    SELECT
        t.FacilityID,
        CASE WHEN t.yr = 2024 THEN 'current_qtr' ELSE 'prev_qtr' END AS period,
        SUM(t.Amount) AS total_spend
    FROM transaction_with_qtr t
    JOIN valid_facilities v ON t.FacilityID = v.FacilityID
    WHERE (t.yr = 2024 AND t.qtr = 2) 
       OR (t.yr = 2023 AND t.qtr = 2)
    GROUP BY t.FacilityID, period
)

步骤4：计算同比变化率 #

合并两个季度的数据，最终计算同比变化百分比：

SELECT
    FacilityID,
    MAX(CASE WHEN period = 'current_qtr' THEN total_spend END) AS current_qtr_spend,
    MAX(CASE WHEN period = 'prev_qtr' THEN total_spend END) AS prev_qtr_spend,
    -- 处理除数为0的情况，保留2位小数
    ROUND(
        (MAX(CASE WHEN period = 'current_qtr' THEN total_spend END) - 
         MAX(CASE WHEN period = 'prev_qtr' THEN total_spend END)) / 
         NULLIF(MAX(CASE WHEN period = 'prev_qtr' THEN total_spend END), 0) * 100,
        2
    ) AS yoy_change_pct
FROM qtr_totals
GROUP BY FacilityID;

关键修正点说明 #

• 通过valid_facilities CTE彻底排除了仅在单个季度有数据的无效设施，解决了原代码误包含不合格设施的问题
• 使用NULLIF避免了去年同期支出为0时的计算报错
• 若需适配动态时间，可将硬编码的年份/季度替换为CURRENT_DATE相关函数，无需手动修改参数

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