基于当前日期按账户统计各月份最近两期销售额的SQL查询需求

需求回顾 #

针对每个账户，基于当前日期生成24个统计字段：12个月份各自的最近一期销售额总和和次近一期销售额总和。规则如下：

• 若当前日期的月份 ≥ 目标月份，最近一期为当前年份的目标月份，次近为当前年份-1的目标月份（例：当前2022-10-28，6月的最近是2022年6月，次近是2021年6月）
• 若当前日期的月份 < 目标月份，最近一期为当前年份-1的目标月份，次近为当前年份-2的目标月份（例：当前2022-10-28，11月的最近是2021年11月，次近是2020年11月；进入2022-11后，11月最近是2022年11月，次近是2021年11月）

假设源表结构 #

假设你的销售数据表名为sales，包含字段：

• account_id：账户唯一标识
• sale_date：销售日期（DATE类型）
• amount：单笔销售金额（数值类型）

实现SQL代码 #

WITH monthly_sales AS (
    -- 预计算每个账户每个年月的销售额总和
    SELECT
        account_id,
        EXTRACT(YEAR FROM sale_date)::INT AS sale_year,
        EXTRACT(MONTH FROM sale_date)::INT AS sale_month,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY account_id, sale_year, sale_month
),
current_date_info AS (
    -- 获取当前日期的年份和月份，用于判断最近/次近的年份
    SELECT
        EXTRACT(YEAR FROM CURRENT_DATE)::INT AS current_year,
        EXTRACT(MONTH FROM CURRENT_DATE)::INT AS current_month
)
SELECT
    ms.account_id,
    -- 1月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 1 AND ms.sale_year = CASE WHEN cdi.current_month >= 1 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS JanuaryMostRecent,
    SUM(CASE WHEN ms.sale_month = 1 AND ms.sale_year = CASE WHEN cdi.current_month >= 1 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS January2ndMostRecent,
    -- 2月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 2 AND ms.sale_year = CASE WHEN cdi.current_month >= 2 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS FebruaryMostRecent,
    SUM(CASE WHEN ms.sale_month = 2 AND ms.sale_year = CASE WHEN cdi.current_month >= 2 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS February2ndMostRecent,
    -- 3月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 3 AND ms.sale_year = CASE WHEN cdi.current_month >= 3 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS MarchMostRecent,
    SUM(CASE WHEN ms.sale_month = 3 AND ms.sale_year = CASE WHEN cdi.current_month >= 3 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS March2ndMostRecent,
    -- 4月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 4 AND ms.sale_year = CASE WHEN cdi.current_month >= 4 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS AprilMostRecent,
    SUM(CASE WHEN ms.sale_month = 4 AND ms.sale_year = CASE WHEN cdi.current_month >= 4 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS April2ndMostRecent,
    -- 5月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 5 AND ms.sale_year = CASE WHEN cdi.current_month >= 5 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS MayMostRecent,
    SUM(CASE WHEN ms.sale_month = 5 AND ms.sale_year = CASE WHEN cdi.current_month >= 5 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS May2ndMostRecent,
    -- 6月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 6 AND ms.sale_year = CASE WHEN cdi.current_month >= 6 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS JuneMostRecent,
    SUM(CASE WHEN ms.sale_month = 6 AND ms.sale_year = CASE WHEN cdi.current_month >= 6 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS June2ndMostRecent,
    -- 7月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 7 AND ms.sale_year = CASE WHEN cdi.current_month >= 7 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS JulyMostRecent,
    SUM(CASE WHEN ms.sale_month = 7 AND ms.sale_year = CASE WHEN cdi.current_month >= 7 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS July2ndMostRecent,
    -- 8月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 8 AND ms.sale_year = CASE WHEN cdi.current_month >= 8 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS AugustMostRecent,
    SUM(CASE WHEN ms.sale_month = 8 AND ms.sale_year = CASE WHEN cdi.current_month >= 8 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS August2ndMostRecent,
    -- 9月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 9 AND ms.sale_year = CASE WHEN cdi.current_month >= 9 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS SeptemberMostRecent,
    SUM(CASE WHEN ms.sale_month = 9 AND ms.sale_year = CASE WHEN cdi.current_month >= 9 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS September2ndMostRecent,
    -- 10月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 10 AND ms.sale_year = CASE WHEN cdi.current_month >= 10 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS OctoberMostRecent,
    SUM(CASE WHEN ms.sale_month = 10 AND ms.sale_year = CASE WHEN cdi.current_month >= 10 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS October2ndMostRecent,
    -- 11月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 11 AND ms.sale_year = CASE WHEN cdi.current_month >= 11 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS NovemberMostRecent,
    SUM(CASE WHEN ms.sale_month = 11 AND ms.sale_year = CASE WHEN cdi.current_month >= 11 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS November2ndMostRecent,
    -- 12月的最近/次近销售额
    SUM(CASE WHEN ms.sale_month = 12 AND ms.sale_year = CASE WHEN cdi.current_month >= 12 THEN cdi.current_year ELSE cdi.current_year - 1 END THEN ms.total_sales ELSE 0 END) AS DecemberMostRecent,
    SUM(CASE WHEN ms.sale_month = 12 AND ms.sale_year = CASE WHEN cdi.current_month >= 12 THEN cdi.current_year - 1 ELSE cdi.current_year - 2 END THEN ms.total_sales ELSE 0 END) AS December2ndMostRecent
FROM monthly_sales ms
CROSS JOIN current_date_info cdi
GROUP BY ms.account_id
ORDER BY ms.account_id;

代码逻辑说明 #

1. monthly_sales CTE：先按账户、年份、月份聚合，计算每个账户每个月的总销售额，避免重复计算。
2. current_date_info CTE：提取当前日期的年份和月份，作为判断最近/次近年份的基准。
3. 主查询条件聚合：
   • 对每个月份（1-12），用CASE语句判断目标年份：如果当前月份≥目标月份，最近年份是当前年，否则是当前年-1；次近年份则是最近年份减1。
   • 通过SUM(CASE ...)筛选对应年月的销售额，得到每个账户每个月份的最近/次近销售额总和。

验证示例 #

以当前日期2022-10-28为例：

• 对于11月：current_month(10) < 11，所以最近年份是2022-1=2021，次近是2021-1=2020，对应NovemberMostRecent取2021年11月销售额，November2ndMostRecent取2020年11月销售额。
• 对于6月：current_month(10) ≥6，最近年份是2022，次近是2021，对应JuneMostRecent取2022年6月销售额，June2ndMostRecent取2021年6月销售额。

当日期进入2022-11-01后：

• 11月的current_month(11)≥11，最近年份变为2022，次近变为2021，符合需求。

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