我有以下信息

id     user                 date                          test

102   123~456~897   01JAN2019~6JUL2018~21DEC2017        abc~qwer~~wer~
103  13~45~88~34   01JAN2020~6JUN2019~21JAN2018~12MAR2017 pqr~~qw~wer*123~qwerty

我需要的结果是，记录必须根据升序进行扩展。

id      user     date            test 

102     897      21DEC2017        wer
102     456      06JUL2018        qwer
102     123      01JAN2019        abc
103     34       12MAR2017        qwerty
103     88       21JAN2018        wer*123
103     45       06JUN2019        qw
103     13       01JAN2020        pqr

我尝试使用横向View爆炸，但无法正常工作，请帮忙。

a.id,from demo a
    lateral view posexplode(a.test,'\\~')) t1 as exploded_test,test
    lateral view posexplode(a.user,'\\~')) t2 as exploded_user,user
    lateral view posexplode(a.date,'\\~')) t3 as exploded_date,date
    where exploded_test == exploded_user and exploded_user == exploded_date
    order by 1,3