use hue;

select user.username as account, query.last_modified as last_modified, user.first_name as user_name, query.search as query, query.type as search_type, ip.ip_address as ip_address, DATE_FORMAT(query.last_modified, "%Y-%m") from desktop_document2 as query join auth_user as user on user.id=query.owner_id join ( select orition.ip_address, origin.username, origin.attempt_time, case when origin.logout_time is null then case when origin.username != lead_log.username then from_unixtimestamp()) else lead_log.logout_time_cust end else origin.logout_time end as logout_time from (( select ($rownum_origin := $rownum_origin+1) as rownum_origin, ip_adress, username, attempt_time, logout_time, case when logout_time is null then attempt_time else logout_time end as logout_time_cust from axes_accesslog, (select $rownum_origin:=1) tmp order by username, attempt_time) as origin, ( select ($rownum_lead_log:=@rownum_lead_log+1) as rownum_lead_log, username, case when logout_time is null then attempt_time else logout_time end as logout_time_cust from axes_accesslog, (select rownum_lead_log:=0) tmp order by username, attempt_time) as lead_log ) where origin.rownum_origin = lead_log.rownum_lead_log) as ip on ip.username = user.username and ip.attempt_time < query.last_modified and query.last_modified <= ip.logout_time where query.last_modified > '2020-02-03 17:10:43.0' order by query.ast_modified;

결과 예시

account    last_modified            user_name     query                                   search_type         ip_address     DATE_FORMAT(query.last_modified, "%Y-%m")

hadoop    2020-02-03 17:18:46   hadoop         select * from db.tb_test;           query-impala       xx.xx.xx.xx     2020-02