如何连接sql中无序的字符串片段消息

fcipmucu 于 2021-06-25 发布在 Hive

关注(0)|答案(2)|浏览(393)

我有一个有三列的表第一列表示消息id（message_id）第二列表示顺序特征，表示消息的顺序（message_order），最后第三列是消息的片段（message_fragment）：

+------------+---------------+------------------------------+
| message_id | message_order |           message            |
+------------+---------------+------------------------------+
| Message 1  |             2 | Best, Jose                   |
| Message 1  |             1 | Thanks for your advice       |
| Message 2  |             1 | I only have one line of text |
+------------+---------------+------------------------------+

在sql中有没有一种方法可以按消息顺序将消息行连接起来？最终得到以下结果：

+------------+-----------------------------------+
| message_id |              message              |
+------------+-----------------------------------+
| Message 1  | Thanks for your advice Best, Jose |
| Message 2  | I only have one line of text      |
+------------+-----------------------------------+

sql Hive concatenation hiveql string-aggregation

来源：https://stackoverflow.com/questions/58769448/how-do-i-concatenate-fragmented-messages-of-strings-that-are-out-of-order-in-sql

2条答案

按热度按时间

ujv3wf0j1#

对于oracle：

with msg(msg_id, msg_order, msg_text) as (
  select 1, 2, 'Best, Jose' from dual union all
  select 1, 1, 'Thanks for your advice' from dual union all
  select 2, 1, 'I only have one line of text' from dual
)
select msg_id, listagg(msg_text, ' ') within group (order by msg_id, msg_order) message
from msg
group by msg_id;

赞(0）回复(0）举报 2021-06-26

rkue9o1l2#

对于Hive：

select message_id, concat_ws(' ', collect_list(message)) as message
  from
      (select message_id, message_order, message 
         from table
       distribute by message_id sort by message_order 
      )s  
 group by message_id
 order by message_id;

赞(0）回复(0）举报 2021-06-26

我来回答

如何连接sql中无序的字符串片段消息

2条答案

相关问题

热门标签

最新问答