[HADOOP] 조건에 일치하는 행 시퀀스에 대한 식별자를 생성하는 하이브 쿼리

조건에 일치하는 행 시퀀스에 대한 식별자를 생성하는 하이브 쿼리

다음과 같은 하이브 테이블을 입력으로 사용한다고 가정 해 봅시다.

userid  | timestamp   
--------|-------------
1       | 1433258019  
1       | 1433258020
2       | 1433258080
2       | 1433258083
2       | 1433258088
2       | 1433258170
[...]   | [...]

다음 쿼리로 :

SELECT
    userid,
    timestamp,
    timestamp - LAG(timestamp, 1, 0) OVER w AS timediff
    CASE
      WHEN timediff > 60
      THEN 'new_session'
      ELSE 'same_session'
    END AS session_state
FROM connections
WINDOW w PARTITION BY userid ORDER BY timestamp ASC;

나는 다음 출력을 생성하고있다 :

userid  | timestamp   | timediff   | session_state
--------|-------------|------------|---------------
1       | 1433258019  | 1433258019 | new_session
1       | 1433258020  | 1          | same_session
2       | 1433258080  | 1433258080 | new_session
2       | 1433258083  | 3          | same_session
2       | 1433258088  | 5          | same_session
2       | 1433258170  | 82         | new_session
[...]   | [...]       | [...]      | [...]

어떻게 생성 할 것인가?

userid  | timestamp   | timediff   | sessionid
--------|-------------|------------------------------
1       | 1433258019  | 1433258019 | user1-session-1
1       | 1433258020  | 1          | user1-session-1
2       | 1433258080  | 1433258080 | user2-session-1
2       | 1433258083  | 3          | user2-session-1
2       | 1433258088  | 5          | user2-session-1
2       | 1433258170  | 82         | user2-session-2
[...]   | [...]       | [...]      | [...]

HQL과 "유명한"UDF만을 사용하면 가능합니까 (커스텀 UDF 나 감속기 스크립트를 사용하지 않겠습니까?).

해결법

1. ==============================

   1.재미있는 질문. @ Madhu에 대한 귀하의 의견에 따라, 저는 2 행 1433258172를 당신의 예제에 추가했습니다. 필요한 것은 timediff> 60이 만족 될 때마다 증가시키는 것입니다. 가장 쉬운 방법은 플래그를 지정하고 창 전체를 누적 합계하는 것입니다.

   재미있는 질문. @ Madhu에 대한 귀하의 의견에 따라, 저는 2 행 1433258172를 당신의 예제에 추가했습니다. 필요한 것은 timediff> 60이 만족 될 때마다 증가시키는 것입니다. 가장 쉬운 방법은 플래그를 지정하고 창 전체를 누적 합계하는 것입니다.

   질문:

   select userid
     , timestamp
     , concat('user', userid, '-session-', s_sum) sessionid
   from (
     select *
       , sum( counter ) over (partition by userid
                              order by timestamp asc
                              rows between unbounded preceding and current row) s_sum
     from (
       select *
         , case when timediff > 60 then 1 else 0 end as counter
       from (
         select userid
           , timestamp
           , timestamp - lag(timestamp, 1, 0) over (partition by userid
                                                    order by timestamp asc) timediff
         from connections ) x ) y ) z
   

   산출:

   1   1433258019  user1-session-1
   1   1433258020  user1-session-1
   2   1433258080  user2-session-1
   2   1433258083  user2-session-1
   2   1433258088  user2-session-1
   2   1433258170  user2-session-2
   2   1433258172  user2-session-2
   

2. ==============================

   2.다음을 사용하십시오 직원으로부터 concat_ws ( '-', 이름, 도시)를 선택하십시오. concat_ws의 첫 번째 매개 변수는 구분 기호입니다. name W city는 employee 테이블에 대한 컬럼 이름입니다. 유형 문자열임을 확인하십시오. 더 많은 것을 보시려면 여기를 클릭하십시오.

   다음을 사용하십시오 직원으로부터 concat_ws ( '-', 이름, 도시)를 선택하십시오. concat_ws의 첫 번째 매개 변수는 구분 기호입니다. name W city는 employee 테이블에 대한 컬럼 이름입니다. 유형 문자열임을 확인하십시오. 더 많은 것을 보시려면 여기를 클릭하십시오.

3. ==============================

   3.작동 방식 :

   작동 방식 :

   SELECT 
     userid,
     timestamp,
     timediff,
     CONCAT(
       'user',
        userid,
        '-',
        'session-',
        CAST(timediff / 60 AS INT) + 1
     ) AS session_id
     FROM (
       SELECT   
         userid,
         timestamp,
         timestamp - LAG(timestamp, 1, timestamp) OVER w AS timediff
       FROM connections
       WINDOW w AS (
         PARTITION BY userid
         ORDER BY timestamp ASC
       )
   ) a;
   

   산출:

   userid  timestamp   timediff    session_state
   1       1433258019  0.0         user1-session-1
   1       1433258020  1.0         user1-session-1
   2       1433258080  0.0         user2-session-1
   2       1433258083  3.0         user2-session-1
   2       1433258088  5.0         user2-session-1
   2       1433258170  82.0        user2-session-2
   3       1433258270  0.0         user3-session-1
   

   timediff가 필요하지 않은 경우 다음과 같이 시도 할 수 있습니다.

   ( 'user', userid, '-', 'session -', string으로 w1을 통해 LAG (session_count-1,1,0)을 캐스팅합니다.)) session_state AS user_id, timestamp, session_count + --LAG (session_count-1,1,0) over w1 AS session_count_new 에서 (고르다      사용자 ID,      타임 스탬프,      시간표, 캐스트 (timediff / 60 int) +1로 session_count

from https://stackoverflow.com/questions/30600543/hive-query-generating-identifiers-for-a-sequence-of-row-matching-a-condition by cc-by-sa and MIT license