데이터베이스 질문과 답변
안녕하세요.
다음과 같은 입력 레코드가 있습니다.
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 환경입니다.
수고하세요.
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에서도 비슷하게 하면 되지 않을까요?
감사합니다.
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
)
)
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
;
마농님의 SQL은 PostgreSQL에서 수정도 필요없이 실행되네요.
나도잘하고싶다....
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.