根据时间戳设置别名

Question

我试图为列challenge_name设置别名，特别是当我遇到基于时间戳/日期作为挑战1，挑战2等的"挑战"时。如果不是'challenge'，我想将列名设置为与challenge_name

相同这是我当前的输入

+-----------+----------------+
|   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  |
+-----------+----------------+--------------+

Answer 1

一个简单的带有rank的例子就可以了。Rank将为每个特定的challenge_name组分配一个Rank，我没有只过滤掉challenge_name = challenge时的值，因为它们是根据case条件

过滤的

select `date`,
challenge_name,
case  when challenge_name = 'challenge' then concat('challenge ', rank() over(partition by challenge_name order by `date` asc )) 
else challenge_name end  as challenge_name                               
from test
order by `date` asc ;

https://dbfiddle.uk/oRJ0bQh7

Answer 2

这可能行得通:

-- Fuzzy match
CASE 
WHEN REGEXP_LIKE(challenge_name, 'challenge') THEN 
CONCAT('challenge ', 
ROW_NUMBER() OVER (
PARTITION BY REGEXP_LIKE(challenge_name, 'challenge')
ORDER BY date
)
)
ELSE challenge_name
END AS updated_name

编辑

添加order by子句到窗口函数