다음과 같은 테이블이 2개 있습니다.
A Table
log_date | user_id |
2022-12-03 11:00:30 | u001 |
2022-12-04 11:20:30 | u001 |
2022-12-04 18:30:30 | u001 |
2022-12-05 13:00:30 | u001 |
2022-12-05 15:00:30 | u001 |
2022-12-05 11:00:30 | u002 |
2022-12-13 16:55:30 | u002 |
2022-12-13 12:55:30 | u003 |
B Table
num_date | user_id | num |
2022-12-03 12:11:30 | u001 | 5 |
2022-12-03 12:20:30 | u001 | 10 |
2022-12-04 12:30:30 | u001 | 30 |
2022-12-05 12:40:30 | u001 | 17 |
2022-12-05 16:50:30 | u002 | 9 |
2022-12-05 17:00:30 | u002 | 2 |
2022-12-10 11:00:30 | u002 | 21 |
A테이블에 B테이블의 num값을 부여하는데 log_date를 기준으로 B테이블의 동일 유저(user_id)의 직전 최신(num_date)의 num값을 취득하여 부여하는 조건
입니다.
해당 user_id의 직전 시간의 num값이 없는 경우에는 NULL
결과값
log_date | user_id | num |
2022-12-03 11:00:30 | u001 | 5 |
2022-12-04 11:20:30 | u001 | 10 |
2022-12-04 18:30:30 | u001 | 30 |
2022-12-05 13:00:30 | u001 | 17 |
2022-12-05 15:00:30 | u001 | 17 |
2022-12-05 11:00:30 | u002 | NULL |
2022-12-13 16:55:30 | u002 | 21 |
2022-12-13 12:55:30 | u003 | NULL |
그럼 잘 부탁드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT T1. NO , T1.LOG_DATE, T1.USER_ID, T1.NUM, T1.NO_B FROM ( SELECT A. NO , A.LOG_DATE, A.USER_ID , B.NUM, B. NO AS NO_B , ROW_NUMBER() OVER(PARTITION BY A. NO , A.USER_ID ORDER BY B.NUM_DATE DESC ) AS RN FROM TABLE_A A LEFT OUTER JOIN TABLE_B B ON A.USER_ID = B.USER_ID AND A.LOG_DATE > B.NUM_DATE ) T1 WHERE T1.RN = 1 ORDER BY T1. NO , T1.LOG_DATE |
비고 부분의 NULL 설명 부분을 제외하면 이런 쿼리로 도출 될 수 있을듯 합니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | WITH tab_a AS ( SELECT '2022-12-03 11:00:30' log_date, 'u001' user_id UNION ALL SELECT '2022-12-04 11:20:30' , 'u001' UNION ALL SELECT '2022-12-04 18:30:30' , 'u001' UNION ALL SELECT '2022-12-05 13:00:30' , 'u001' UNION ALL SELECT '2022-12-05 15:00:30' , 'u001' UNION ALL SELECT '2022-12-05 11:00:30' , 'u002' UNION ALL SELECT '2022-12-13 16:55:30' , 'u002' UNION ALL SELECT '2022-12-13 12:55:30' , 'u003' ) , tab_b AS ( SELECT '2022-12-03 12:11:30' num_date, 'u001' user_id, 5 num UNION ALL SELECT '2022-12-03 12:20:30' , 'u001' , 10 UNION ALL SELECT '2022-12-04 12:30:30' , 'u001' , 30 UNION ALL SELECT '2022-12-05 12:40:30' , 'u001' , 17 UNION ALL SELECT '2022-12-05 16:50:30' , 'u002' , 9 UNION ALL SELECT '2022-12-05 17:00:30' , 'u002' , 2 UNION ALL SELECT '2022-12-10 11:00:30' , 'u002' , 21 ) SELECT log_date , user_id , num FROM ( SELECT a.log_date , a.user_id , b.num , ROW_NUMBER() OVER(PARTITION BY a.user_id, a.log_date ORDER BY b.num_date DESC ) rn FROM tab_a a LEFT OUTER JOIN tab_b b ON a.user_id = b.user_id AND a.log_date > b.num_date ) c WHERE rn = 1 ; |