SQL如何识别连续4年购买的客户并提取对应消费记录

你现有用lag/lead生成的match_col仅能标记单条记录是否与相邻年份的购买行为连续，无法区分连续段的长度，也无法定位到满4年的连续序列区间，因此需要补充连续序列分组和长度校验的逻辑。

实现步骤 #

• 第一步：对每个客户的购买记录按年份去重，避免同一年多次购买干扰连续年份统计
• 第二步：用连续岛屿算法给同一客户的连续年份打相同的分组标记
• 第三步：筛选出分组内年份数量≥4的有效分组，关联回原inventory表即可取出对应所有购买记录

完整SQL示例 #

WITH user_year AS (
  -- 先提取每个客户的所有购买年份，去重处理
  SELECT DISTINCT
    name,
    YEAR(date) AS buy_year -- 如果原表已预处理year字段可直接用year替代YEAR(date)
  FROM inventory
),
continuous_group AS (
  -- 给连续年份分配相同的组号
  SELECT 
    *,
    buy_year - ROW_NUMBER() OVER(PARTITION BY name ORDER BY buy_year ASC) AS group_id
  FROM user_year
),
valid_group AS (
  -- 筛选出连续≥4年的客户分组
  SELECT 
    name,
    group_id,
    MIN(buy_year) AS start_year,
    MAX(buy_year) AS end_year,
    COUNT(*) AS continuous_year_cnt
  FROM continuous_group
  GROUP BY name, group_id
  HAVING continuous_year_cnt >=4
)
-- 关联回原表取出对应所有记录
SELECT i.*
FROM inventory i
INNER JOIN valid_group v
  ON i.name = v.name
  AND YEAR(i.date) BETWEEN v.start_year AND v.end_year
-- 仅需amith 2011-2014的记录可保留以下条件，全量查询则删除
WHERE i.name = 'amith' 
  AND YEAR(i.date) BETWEEN 2011 AND 2014
ORDER BY i.id ASC;

逻辑说明 #

如果原表的year字段为年月格式（如YYYY-MM），把SQL中所有YEAR(date)替换为LEFT(year,4)即可适配。如果不需要限制特定客户和年份，删掉最后的WHERE子句中的限定条件，就能直接查出所有满足连续4年购买的客户的全部对应消费记录。你要的amith的4/5/6/7号记录会被正常筛选出来，因为这四条对应的年份正好落在2011-2014这个满4年的连续分组区间内。

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