我当前正在使用此类查询聚合字符串：

select
  STRING_AGG(pet_name) over (partition by id, name, second_id, second_name ORDER BY pet_id) pet_names
  id, 
  name, 
  second_id, 
  second_name
 FROM `example`

|---------------------|--------|------------------|------------------|------------------|
|      pet_names      |   id   |        name      |     second_id    |   second_name    |
|---------------------|--------|------------------|------------------|------------------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |    
|---------------------|--------|------------------|------------------|------------------|
|  [cat, bear, tiger] |    2   |       kate       |          3       |       mike       |  
|---------------------|--------|------------------|------------------|------------------|
|    [cat, tiger]     |    3   |       john       |          2       |       bate       | 
|---------------------|--------|------------------|------------------|------------------|

但是，我想以这样的表结束（这里是上面显示的表的第一行的示例）：

|---------------------|--------|------------------|------------------|------------------|--------|
|      pet_names      |   id   |        name      |     second_id    |   second_name    |pet_name|
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   cat  |
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   dog  | 
|---------------------|--------|------------------|------------------|------------------|--------|
|   [cat, dog, bird]  |    1   |       anna       |          2       |       rose       |   bird |
|---------------------|--------|------------------|------------------|------------------|--------|

当我尝试时：

select
  STRING_AGG(pet_name) over (partition by id, name, second_id, second_name ORDER BY pet_id) pet_names
  id, 
  name, 
  second_id, 
  second_name,
  pet_name
 FROM `example`

把猫还给我真的不太好 pet_name “猫”，然后是[猫，狗] pet_name “狗”等。