如何根据另一列在Hive中取出所有记录

Question

如果这是我的表格下面的样子：

my_id       my_words               my_people     my_number
100       need more info?            Jim           1
100       now                        Mary          2
100       what's that?               Jim           3
101       okay now                   Jim           1
101       sounds good                Mary          2
102       still hungry?              Jim           1
102       now I'm thirsty though     Mary          2
102       I don't understand         Jim           3
102       no I'm not hungry          Mary          4
103       are you there?             Jim           1
103       I don't know               Mary          2
103       That's okay                Jim           3

如何获得此输出？

my_id       my_words               my_people     my_number
100       need more info?            Jim           1
100       now                        Mary          2
100       what's that?               Jim           3
102       still hungry?              Jim           1
102       Now I'm thirsty though     Mary          2
102       I don't understand         Jim           3

现在我有：选择my_id，my_words，my_people，my_number，来自table的my_people =“ mary”和lower（my_words），例如'now％';

但是我不仅想返回那些行，我还想在玛丽（Mary's）之前和之后（根据my_number列之前/之后）返回吉姆的评论，

也许是无关的以这种格式出色：

my_id       Jim_words           Mary_words                 Jim_next_words
100      need more info?           now                      what's that?
102       still hungry?     now I'm thirsty though       I don't understand

Answer 1

您能尝试以下代码吗？代码的解释为评论。

WITH cte as (SELECT my_id, my_words, my_people, my_number 
row_number() over( partition by my_id order by my_number) as rn --giving a unique row number for a my id 
from table)
SELECT 
distinct mytab.my_id, chosentab.my_words jims_words, mytab.my_people, mytab.my_number,
case when case when lower(mytab.my_words) like 'now%' then mytab.rn+1 end  = chosentab.rn then chosentab.my_words end jims_words_after_marys_now,
case when case when lower(mytab.my_words) like 'now%' then mytab.rn-1 end  = chosentab.rn then chosentab.my_words end jims_words_before_marys_now
FROM 
cte mytab,
cte chosentab
where
mytab.my_id=chosentab.my_id and
case when lower(mytab.my_words) like 'now%' then mytab.rn+1 end  = chosentab.rn and   -- selecting jims rows where mary said now after jim 
case when lower(mytab.my_words) like 'now%' then mytab.rn-1 end  = chosentab.rn  -- selecting jims rows where mary said now before jim 

现在，我根据我们的讨论创建了SQL。您能验证并让我知道吗？