[HADOOP] 그룹화 쿼리에서 조인 작업 제거

그룹화 쿼리에서 조인 작업 제거

다음과 같은 테이블이 있습니다.

usr_id  query_ts
12345   2019/05/13 02:06
123444  2019/05/15 04:06
123444  2019/05/16 05:06
12345   2019/05/16 02:06
12345   2019/05/15 02:06

쿼리를 실행할 때 사용한 사용자 ID를 포함합니다. 테이블의 각 항목은 지정된 타임 스탬프에서 1 개의 쿼리를 실행하는 ID를 나타냅니다.

나는 이것을 생산하려고 노력하고있다 :

usr_id  day_1   day_2   …   day_30
12345   31       13           15
123444  23       41           14

각 ID에 대해 지난 30 일 동안 매일 실행 된 쿼리 수를 표시하고 싶습니다. 해당 날짜에 쿼리가 실행되지 않은 경우 0이됩니다.

다음은 내가 찾은 쿼리의 일부입니다.

SELECT
t1.usr_id,
case when t1.count_day_1 is null then 0 else t1.count_day_1 end as day_1,
case when t2.count_day_2 is null then 0 else t2.count_day_2 end as day_2
FROM

(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_1,
        COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_1
        FROM db.table
        WHERE
            DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 1
        AND
            from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
                BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
                AND from_unixtime(unix_timestamp())
        GROUP BY usr_id, day_1) t1

LEFT JOIN
(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_2,
        COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_2
        FROM db.table
        WHERE
            DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 2
        AND
            from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
                BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
                AND from_unixtime(unix_timestamp())
        GROUP BY usr_id, day_2) t2
ON (t1.usr_id = t2.usr_id)
ORDER BY t1.usr_id;

이것은 훌륭하게 작동하며 처음 2 일 동안 매일 실행 된 쿼리 수를 보여 주며 NULL을 0으로 바꿉니다.

문제는 30 일 동안이 작업을 수행하는 것입니다. 클러스터에서 ~ 400GB + 메모리를 가져 오는 30 개의 LEFT JOIN을 사용해야합니다.

더 쉬운 방법이 있습니까?

해결법

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

   1.결합없이 수행하고 WHERE에서 unix_timestamp ()가 아닌 current_timestamp 상수 또는 current_timestamp 상수를 사용하십시오.이 함수는 결정적이지 않으며 쿼리 실행 범위에 대해 그 값이 고정되지 않으므로 쿼리의 적절한 최적화를 방해합니다. CURRENT_TIMESTAMP 상수를 위해 2.0 이후로 더 이상 사용되지 않습니다.

   결합없이 수행하고 WHERE에서 unix_timestamp ()가 아닌 current_timestamp 상수 또는 current_timestamp 상수를 사용하십시오.이 함수는 결정적이지 않으며 쿼리 실행 범위에 대해 그 값이 고정되지 않으므로 쿼리의 적절한 최적화를 방해합니다. CURRENT_TIMESTAMP 상수를 위해 2.0 이후로 더 이상 사용되지 않습니다.

   select usr_id,
   nvl(count(case when from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "dd") = 1 then 1 end),0) as day_1,
   nvl(count(case when from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "dd") = 2 then 1 end),0) as day_2
   ...
   from db.table
           WHERE
               from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
                   BETWEEN date_sub(current_date, 30) AND current_date)
   group by usr_id
   

from https://stackoverflow.com/questions/56450300/removing-join-operations-from-a-grouping-query by cc-by-sa and MIT license