如何基于时间戳为challenge_name字段的'challenge'值设置递增别名？

需求说明 #

需要为challenge_name列设置别名updated_name，规则如下：

• 当challenge_name的值为'challenge'时，按照日期先后顺序命名为challenge 1、challenge 2……的格式
• 当challenge_name的值不为'challenge'时，updated_name直接沿用challenge_name的原值

解决方案SQL #

假设数据表名为challenges，可以使用窗口函数结合条件判断实现需求：

SELECT 
    date,
    challenge_name,
    CASE 
        WHEN challenge_name = 'challenge' THEN CONCAT('challenge ', ROW_NUMBER() OVER (ORDER BY STR_TO_DATE(date, '%m/%d/%Y')))
        ELSE challenge_name
    END AS updated_name
FROM challenges
ORDER BY STR_TO_DATE(date, '%m/%d/%Y');

逻辑解释 #

1. 条件判断：通过CASE语句区分两种场景，分别处理别名内容
2. 序号生成：对值为'challenge'的行，使用ROW_NUMBER()窗口函数按日期排序生成递增序号，再与固定前缀拼接成编号名称
3. 日期排序：用STR_TO_DATE()将字符串格式的日期转换为可排序的日期类型，确保序号生成和结果顺序符合时间先后

输入输出验证 #

输入数据

+-----------+----------------+
|   date    | challenge_name |
+-----------+----------------+
| 1/11/2022 | find the bug   |
| 1/12/2022 | challenge      |
| 1/13/2022 | kill pill      |
| 1/14/2022 | hello copter   |
| 1/15/2022 | challenge      |
| 1/16/2022 | miami          |
| 1/17/2022 | hello world    |
| 1/18/2022 | challenge      |
| 1/19/2022 | dominc         |
| 1/20/2022 | challenge      |
+-----------+----------------+

执行后输出

+-----------+----------------+--------------+
|   date    | challenge_name | updated_name |
+-----------+----------------+--------------+
| 1/11/2022 | find the bug   | find the bug |
| 1/12/2022 | challenge      | challenge 1  |
| 1/13/2022 | kill pill      | kill pill    |
| 1/14/2022 | hello copter   | hello copter |
| 1/15/2022 | challenge      | challenge 2  |
| 1/16/2022 | miami          | miami        |
| 1/17/2022 | hello world    | hello world  |
| 1/18/2022 | challenge      | challenge 3  |
| 1/19/2022 | dominc         | dominc       |
| 1/20/2022 | challenge      | challenge 4  |
+-----------+----------------+--------------+

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