如何用SQL实现按月判断用户颜色从Red到Blue的二进制标记列

现有包含Person、month、color三列的用户月度颜色数据，示例如下：用户A的Dec（12月）、Nov（11月）为Blue，Oct（10月）、Sept（9月）为Red；用户B的Dec为Red，Nov为Blue，Jan（1月）为Red。需求新增Change列，用二进制0/1标记是否从上一月的Red变为当前月的Blue（1表示是，0表示否）。尝试过IF语句，但无法实现跨月颜色对比，请教如何用SQL实现该需求？

要实现跨月的颜色对比，核心是用**窗口函数LAG()**获取每个用户上一个月的颜色值，再结合条件判断生成Change列。

关键逻辑 #

1. 分组排序：按用户（Person）分组，同时确保每个用户的月份按时间顺序排列（注意英文月份字符串无法直接排序，需转成可排序的日期/月份数字）。
2. 提取上月数据：通过LAG(color)窗口函数，获取当前记录所属用户的上一个月颜色值。
3. 条件判断：用CASE语句判断——若当前月颜色是Blue且上月颜色是Red，返回1，否则返回0。

示例SQL（通用语法） #

假设你的表名为user_color_data，可执行以下语句：

SELECT
    Person,
    month,
    color,
    CASE
        WHEN color = 'Blue' 
             AND LAG(color) OVER (PARTITION BY Person ORDER BY TO_DATE(month, 'Mon')) = 'Red'
        THEN 1
        ELSE 0
    END AS Change
FROM user_color_data;

兼容性与细节说明 #

• 月份排序适配：
  • MySQL中需将TO_DATE替换为STR_TO_DATE(month, '%b')来转换英文月份为日期；
  • SQL Server用DATEPART(month, CONVERT(date, month + ' 01 2000'))提取月份数字；
  • 也可以用CASE手动映射月份（如CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 ... WHEN 'Dec' THEN 12 END），避免日期转换的兼容性问题。
• 边界处理：每个用户最早的月份没有上月数据，LAG()会返回NULL，此时Change列自动为0，符合需求。

测试结果示例 #

基于你提供的样本数据，执行后结果如下：

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