按年份与scenario获取不晚于上月的最新场景数据行

如何获取按scenario分组的各年份数据行，要求对应各年份的最新场景，且该场景不晚于上月（存在未来预算及预测场景）。

字段说明 #

• scenario（VARCHAR）
  • 用于区分预算或预测：
    • 预测格式：Jan Fcst、Feb Fcst……Dec Fcst
    • 预算格式：Jan Adjusted Budget、Feb Adjusted Budget……Dec Adjusted Budget
  • 预测与预算每月更新一次，针对当年的每个月份（每个唯一预测包含12行数据，对应1-12月）。
• a_year（VARCHAR，格式为'FY%y'）示例：FY20、FY21……FY24
  • 表示预测或预算对应的年份
• a_month（VARCHAR，格式为'%b'）示例：Jan、Feb……Dec
  • 表示预测或预算对应的月份
• amount（DOUBLE）

示例数据 #

+----------------+------+-----+-----+
| Jan Fcst       | FY24 | Jan | 100 |
+----------------+------+-----+-----+
| Jan Fcst       | FY24 | ... | 150 |
+----------------+------+-----+-----+
| Jan Fcst       | FY24 | Dec | 80  |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | Jan | 110 |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | ... | 180 |
+----------------+------+-----+-----+
| Feb Fcst       | FY24 | Dec | 103 |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | Jan | 120 |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | ... | 90  |
+----------------+------+-----+-----+
| Jan Adj Budget | FY24 | Dec | 110 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | Jan | 130 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | ... | 200 |
+----------------+------+-----+-----+
| Feb Adj Budget | FY24 | Dec | 120 |
+----------------+------+-----+-----+

当每月更新预算/预测时，每个历史年份的每个scenario对应288行数据（12种预算与12种预测，每种包含12行）。

期望输出 #

若预测最后更新于2024年2月、预算最后更新于2024年4月，输出应仅包含24行数据，格式如下：

+---------------------+------+-----+----+
| Feb Fcst            | FY24 | Jan | $$ |
+---------------------+------+-----+----+
| Feb Fcst            | FY24 | ... | $$ |
+---------------------+------+-----+----+
| Feb Fcst            | FY24 | Dec | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | Jan | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | ... | $$ |
+---------------------+------+-----+----+
| Apr Adjusted Budget | FY24 | Dec | $$ |
+---------------------+------+-----+----+

解决方案 #

可以通过以下SQL语句实现需求（以MySQL为例）：

WITH scenario_info AS (
    SELECT
        *,
        -- 提取场景中的月份并转换为日期格式，用于时间比较
        STR_TO_DATE(LEFT(scenario, 3), '%b') AS scenario_month,
        -- 标记当前场景是预测还是预算
        CASE WHEN scenario LIKE '%Fcst' THEN 'forecast' ELSE 'budget' END AS scenario_type
    FROM your_table_name
    -- 筛选场景月份不晚于上月的数据
    WHERE STR_TO_DATE(LEFT(scenario, 3), '%b') <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
),
ranked_scenarios AS (
    SELECT
        *,
        -- 按年份、场景类型分组，对场景按月份倒序排名，最新场景排第1
        ROW_NUMBER() OVER (PARTITION BY a_year, scenario_type ORDER BY scenario_month DESC) AS rn
    FROM scenario_info
)
SELECT scenario, a_year, a_month, amount
FROM ranked_scenarios
WHERE rn = 1
ORDER BY scenario_type, scenario;

逻辑说明 #

1. scenario_info CTE：解析每个场景的月份并转换为可比较的日期，同时区分预测/预算类型，过滤掉晚于上月的场景。
2. ranked_scenarios CTE：按年份和场景类型分组，对每组内的场景按月份倒序排名，确保最新的场景排名为1。
3. 最终筛选出排名为1的记录，即为各年份下预测和预算的最新有效场景数据。

若使用其他数据库（如Oracle、SQL Server），需调整日期转换函数：

• Oracle：TO_DATE(LEFT(scenario,3), 'MON')
• SQL Server：CAST(LEFT(scenario,3) + ' 2000' AS DATE)

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