데이터베이스 질문과 답변

시간차에 의한 값 부여 0 6 2,134

by 김선우 [SQL Query] PostgreSQL [2022.12.16 10:48:00]

다음과 같은 테이블이 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

 

그럼 잘 부탁드립니다.

by 우주민 [2022.12.16 12:00:05]

혹시 결과값 중에 NO = a 인 row 는 결과가 잘못 나온거 아닌가요? null 이 나와야 할거 같은데요...;;;

by 우주민 [2022.12.16 12:05:23]
?
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 설명 부분을 제외하면 이런 쿼리로 도출 될 수 있을듯 합니다.

by 김선우 [2022.12.16 12:58:36]

No, 비고 칼럼은 이해를 돕기 위해서 넣었었던 건데 오히려 오해를 불러 일으킨거 같네요.

No와 비고 칼럼을 빼고 다시 데이터 수정해서 올렸습니다.

 

by 우주민 [2022.12.16 15:04:20]

1 LINE : 2022-12-03 11:00:30 보다 전 데이터를 추출하는 것으로 이해 했는데...

TABLE B 에서 num = 5 인 LINE 은 LOG_DATE 가 2022-12-03 12:11:30 라서 조건에 맞지 않는 결과 같아 문의 해보았습니다.

by 마농 [2022.12.19 10:54:32]
?
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
;

 

by 김선우 [2023.04.01 23:32:56]

감사의 댓글이 늦었습니다.

우주민님 마농님 너무 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입