Athena SQL - Самостоятельное присоединение подзапроса с использованием внешнего столбца

Итак, я каталогизировал данные в корзине S3, которая очень похожа на эти данные здесь:

+-----+-----------+---------+-----------------------+
| id  | title     | event   | time                  |
+-----+-----------+---------+-----------------------+
|1    | book A    | BORROW  | 2018-07-01 09:00:00   |
|1    | book A    | RETURN  | 2018-08-01 09:00:00   |
|2    | book B    | BORROW  | 2018-08-01 13:00:00   |
|2    | book B    | RETURN  | 2018-10-01 17:00:00   |
|1    | book A    | BORROW  | 2018-11-01 09:00:00   |
|1    | book A    | RETURN  | 2018-12-01 09:00:00   |
+-----+-----------+---------------------------------+

Я в основном хочу иметь возможность написать оператор SELECT в Amazon Athena, который отображает время заимствования и возврата рядом друг с другом в строке, например:

+-----+-----------+-----------------------+-----------------------+
| id  | title     | borrow_time           | return_time           |
+-----+-----------+-----------------------+-----------------------+
|1    | book A    | 2018-07-01 09:00:00   | 2018-08-01 09:00:00   |
|2    | book B    | 2018-08-01 13:00:00   | 2018-10-01 17:00:00   |
|1    | book A    | 2018-11-01 09:00:00   | 2018-12-01 09:00:00   |
+-----+-----------+-----------------------+-----------------------+

Я потратил кучу времени на написание около 5 разных запросов (используя такие вещи, как OUTER APPLY, но Афина, похоже, очень чувствительна к работе, особенно учитывая, что у нее нет никаких функций с OUTER APPLY. Это логика для моих последних утверждение:

SELECT b.id,
       b.title,
       b.time AS borrow_time,
       MIN(r.time) AS return_time
FROM (
      SELECT id,
             title,
             time
      FROM books
      WHERE event = 'BORROW'
     ) b
OUTER JOIN (
            SELECT id,
                   time
            FROM books
            WHERE event = 'RETURN'
           ) r
        ON b.id = r.id
       AND b.time < r.time
GROUP BY b.id,
         b.title,
         borrow_time
ORDER BY borrow_time;

Мы будем очень благодарны за любые идеи, чтобы обойти это!


amazon-web-services sql amazon-athena presto inner-join
person Nick de Silva    schedule 17.08.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Предполагая, что все заимствования и доходы являются парными, вы можете перечислить их, а затем использовать условную агрегацию:

select id, title,
       max(case when event = 'BORROW' then b.time end) as borrow_time,
       max(case when event = 'RETURN' then b.time end) as return_time
from (select b.*,
             row_number() over (partition by b.id, b.event order by b.time) as sequm
      from books b
     ) b
group by id, title, seqnum
order by id, title, seqnum;
person Gordon Linoff    schedule 17.08.2018
comment
Вы ответили на мои молитвы, это сработало отлично! Большое спасибо за вашу помощь, я очень ценю это! - person Nick de Silva; 24.08.2018

arrow_upward
0
arrow_downward

Попробуйте использовать функцию CASE WHEN и row_number ():

with pcte as 
(
 SELECT id,
           title,event,time, row_number() over(order by id,title,event) as rn
           FROM books
)
    SELECT id,
           title,
           case when event = 'BORROW' then b.time end AS borrow_time,
           case when event = 'RETURN' then b.time end AS return_time
     FROM pcte order by id, title, rn
person Fahmi    schedule 17.08.2018