sql案例

数据拼接成一个长串按照排序保存，常见于聊天会话的合并

        select create_time,msg_from,msg_to,concat_ws('\n',collect_list(content2)) as content2s
        ,min(send_time) as send_timemin ,max(send_time) as send_timemax,
        concat_ws(',', sort_array(collect_list
        (concat_ws('|', lpad(cast(rn as string), 2, '0'), content2)))) as middle
        from (
        select *
            ,row_number() over (partition by create_time,msg_from,msg_to order by send_time) rn
            from b
        ) t1
        group by create_time,msg_from,msg_to