从一段文字中切分出user

with t1 as (
    select id ticket_audit_trail_id,
           created_date audit_created_date,
           ticket_id,
           action,
           description audit_description,
           REGEXP_EXTRACT(description, '\\[([A-Z]+\\.[A-Z]+)\\]', 1) AS assigned_user,
          if(action = 6,row_number() over (partition by ticket_id ,action order by created_date),1) rn
    FROM ods_ticket_audit_trail_full
   WHERE dt = '2023-12-12'
)
,t2 as (
    select ticket_audit_trail_id,
           audit_created_date,
           ticket_id,
           action,
           audit_description,
           if(action = 6 and rn = 1, assigned_user, null) grabber
    from t1
)
,t3 as (
    select
           ticket_id,
           description
    FROM t2
    lateral view explode(split(regexp_replace(audit_description, "[^A-Za-z\\[\\].,\\s]+", ''), '\\]\\s*,\\s*\\[')) tmp_table as description
    WHERE  audit_description not like '%用户%'
)
,t4 as (
    select partitioner,
           ticket_id
    from (
             select trim(data_col2) partitioner,
                    ticket_id
             from (
                      select regexp_replace(exploded_column, '([^\\[\\]])\\[([^\\[\\]])', '$1,$2') AS data_col,
                             ticket_id
                      from (
                               SELECT exploded_column,
                                      ticket_id
                               from t3
                                        LATERAL VIEW explode(split(description, '\\]\\[')) exploded_table AS exploded_column
                           ) tb
                  ) tc
                      LATERAL VIEW explode(split(regexp_replace(data_col, '\\[|\\]', ''), ',')) exploded_table AS data_col2
         )ta
    group by ticket_id,partitioner
)
insert overwrite table dwd_ticket_audit_info partition (dt = '2023-12-12')
select ticket_audit_trail_id,
       audit_created_date,
       t2.ticket_id,
       action,
       audit_description,