레코드의 랭킹 붙이기 0 6 101

by 김선우 [SQL Query] [2017.12.05 15:38:53]


안녕하세요.

다음과 같은 입력 레코드가 있습니다.

       itime                           userid

2017/10/1 6:39                    AA

2017/10/1 7:08                    AA

2017/10/1 7:37                    AA

2017/10/2 23:03                   AA

2017/10/2 23:15                   AA

2017/10/2 23:40                   AA

2017/10/3 00:02                   AA

2017/10/3 00:12                   AA

2017/10/3 00:15                   BB

2017/10/3 00:50                   BB

2017/10/3 00:55                   BB

위와 같은 레코드에 다음과 같이 랭킹을 부여하고자 합니다.

       itime                           userid          rank

2017/10/1 6:39                    AA               1

2017/10/1 7:08                    AA               2

2017/10/1 7:37                    AA               3

2017/10/2 23:03                   AA               1

2017/10/2 23:15                   AA               2

2017/10/2 23:40                   AA               3

2017/10/3 00:02                   AA               4

2017/10/3 00:12                   AA               5

2017/10/3 00:15                   BB               1

2017/10/3 00:50                   BB               1

2017/10/3 00:55                   BB               2

포인트는 시간차가 30분이내 이면 같은 유저내에서 랭킹을 부여하고 30분을 넘어갈 경우 새롭게 랭킹을 부여하고자 합니다.

PostgreSQL9.4 환경입니다.

수고하세요.

by 랑에1 [2017.12.05 16:54:05]
WITH T(itime, userid) AS (
SELECT TO_DATE('20171001 6:39', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171001 7:08', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171001 7:37', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171002 23:03', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171002 23:15', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171002 23:40', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171003 00:02', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171003 00:12', 'YYYYMMDDHH24:MI'), 'AA' FROM dual UNION ALL
SELECT TO_DATE('20171003 00:15', 'YYYYMMDDHH24:MI'), 'BB' FROM dual UNION ALL
SELECT TO_DATE('20171003 00:50', 'YYYYMMDDHH24:MI'), 'BB' FROM dual UNION ALL
SELECT TO_DATE('20171003 00:55', 'YYYYMMDDHH24:MI'), 'BB' FROM dual 
)

SELECT itime, userid, RANK() OVER(PARTITION BY userid, gb2 ORDER BY itime) AS rk
FROM 
(
  SELECT itime, userid, SUM(gb) OVER(PARTITION BY userid ORDER BY itime) AS gb2
  FROM 
  (
    SELECT itime, userid, CASE WHEN ((itime - LAG(itime, 1) OVER(PARTITION BY userid ORDER BY itime)) * 24 * 60) <= 30 THEN 0 ELSE 1 END AS gb
    FROM T
  )
)  

오라클 환경에서 해본거지만 PostgreSQL에서도 비슷하게 하면 되지 않을까요?

 


by 김선우 [2017.12.05 17:16:36]

감사합니다.

Postgresql에서는 다음과 같이 수정해서 실행하니 원하는 답이 주어지네요.. ^^

SELECT itime, userid, RANK() OVER(PARTITION BY userid, gb2 ORDER BY itime) AS rk

FROM

(

  SELECT itime, userid, SUM(gb) OVER(PARTITION BY userid ORDER BY itime) AS gb2

  FROM

  (

    SELECT itime, userid, CASE WHEN ((itime - LAG(itime, 1) OVER(PARTITION BY userid ORDER BY itime)) ) <= '00:30:00' THEN 0 ELSE 1 END AS gb

    FROM T

  )


by 마농 [2017.12.05 17:12:08]
WITH t AS
(
SELECT '2017/10/01 06:39'::timestamp itime, 'AA' userid
UNION ALL SELECT '2017/10/01 07:08', 'AA'
UNION ALL SELECT '2017/10/01 07:37', 'AA'
UNION ALL SELECT '2017/10/02 23:03', 'AA'
UNION ALL SELECT '2017/10/02 23:15', 'AA'
UNION ALL SELECT '2017/10/02 23:40', 'AA'
UNION ALL SELECT '2017/10/03 00:02', 'AA'
UNION ALL SELECT '2017/10/03 00:12', 'AA'
UNION ALL SELECT '2017/10/03 00:15', 'BB'
UNION ALL SELECT '2017/10/03 00:50', 'BB'
UNION ALL SELECT '2017/10/03 00:55', 'BB'
)
SELECT itime, userid
     , ROW_NUMBER() OVER(PARTITION BY userid, gb ORDER BY itime) rn
  FROM (SELECT itime, userid
             , SUM(flag) OVER(PARTITION BY userid ORDER BY itime) gb
          FROM (SELECT itime, userid
                     , CASE WHEN LAG(itime) OVER(PARTITION BY userid ORDER BY itime)
                              >= itime - INTERVAL '30' MINUTE
                            THEN 0 ELSE 1 END flag
                  FROM t
                ) a
        ) a
;

 


by 김선우 [2017.12.05 17:16:18]

마농님의 SQL은 PostgreSQL에서 수정도 필요없이 실행되네요.


by 고수가되고싶어요 [2017.12.05 17:18:21]

나도잘하고싶다....


by 마농 [2017.12.05 20:36:04]
SELECT itime, userid
     , rn - MAX(CASE WHEN flag = 1 THEN rn END) OVER(PARTITION BY userid ORDER BY itime) + 1 rn
  FROM (SELECT itime, userid
             , ROW_NUMBER() OVER(PARTITION BY userid ORDER BY itime) rn
             , CASE WHEN LAG(itime) OVER(PARTITION BY userid ORDER BY itime)
                      >= itime - INTERVAL '30' MINUTE
                    THEN 0 ELSE 1 END flag
          FROM t
        ) a
;

 

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