如何在Oracle中获取sales_data表的最近连续年度数据

看起来你需要从sales_data表中提取每个客户（按ID分组）的最近连续年度销售数据。先明确下原始表的结构和数据：

原始表结构与数据 #

下面分两种常见的需求场景给出解决方案：

场景1：获取每个ID的「最晚结束的连续年度区间」 #

逻辑是：找到每个客户所有连续年份的区间，取其中结束年份最新的那个（哪怕这个区间只有1年，比如ID2000的2017年）。

实现SQL #

WITH ranked_data AS (
    SELECT 
        ID,
        Name,
        Year,
        Sales,
        -- 连续年份会得到相同的group_id，以此区分不同连续区间
        Year - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Year ASC) AS group_id
    FROM sales_data
),
group_max_year AS (
    -- 计算每个连续组的最晚年份
    SELECT 
        ID,
        group_id,
        MAX(Year) AS max_year
    FROM ranked_data
    GROUP BY ID, group_id
),
latest_group AS (
    -- 筛选每个ID中最晚结束的连续组
    SELECT 
        ID,
        group_id
    FROM group_max_year
    WHERE max_year = (SELECT MAX(max_year) FROM group_max_year gm WHERE gm.ID = group_max_year.ID)
)
-- 提取目标数据并按ID、年份降序排列
SELECT 
    rd.ID,
    rd.Name,
    rd.Year,
    rd.Sales
FROM ranked_data rd
JOIN latest_group lg ON rd.ID = lg.ID AND rd.group_id = lg.group_id
ORDER BY rd.ID, rd.Year DESC;

执行结果 #

场景2：获取每个ID的「最长且最晚结束的连续年度区间」 #

如果需求是优先找最长的连续年份区间，当有多个长度相同的区间时，再取最晚结束的那个（比如ID2000的最长连续区间是2013-2015，共3年），可以用下面的SQL：

实现SQL #

WITH ranked_data AS (
    SELECT 
        ID,
        Name,
        Year,
        Sales,
        Year - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Year ASC) AS group_id
    FROM sales_data
),
group_stats AS (
    -- 计算每个连续组的长度和最晚年份
    SELECT 
        ID,
        group_id,
        MAX(Year) AS max_year,
        COUNT(*) AS year_count
    FROM ranked_data
    GROUP BY ID, group_id
),
latest_longest_group AS (
    -- 筛选每个ID中最长且最晚结束的连续组
    SELECT 
        ID,
        group_id
    FROM group_stats gs
    WHERE year_count = (SELECT MAX(year_count) FROM group_stats WHERE ID = gs.ID)
      AND max_year = (SELECT MAX(max_year) FROM group_stats WHERE ID = gs.ID AND year_count = gs.year_count)
)
SELECT 
    rd.ID,
    rd.Name,
    rd.Year,
    rd.Sales
FROM ranked_data rd
JOIN latest_longest_group lg ON rd.ID = lg.ID AND rd.group_id = lg.group_id
ORDER BY rd.ID, rd.Year DESC;

执行结果 #

思路说明 #

核心是利用窗口函数生成连续年份的分组标识：当年份连续时，Year - ROW_NUMBER()的结果会保持一致，以此区分不同的连续区间。之后根据你的具体需求（最晚区间/最长区间）筛选目标分组，最后提取对应数据即可。

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