질문이 있어서 글을 올립니다.
회원들의 연속 가입년도수를 구하려고합니다.
SEQ | ID | 가입년도(REG_UYEAR) |
1 | AAA | 2017 |
2 | AAA | 2018 |
3 | AAA | 2019 |
4 | BBB | 2017 |
5 | BBB | 2019 |
6 | CCC | 2018 |
7 | CCC | 2019 |
8 | DDD | 2018 |
9 | EEE | 2019 |
10 | FFF | 2019 |
11 | GGG | 2019 |
이렇게 데이터가 있습니다.
각 회원별 올해(2019년)기준으로 연속으로 가입한 횟수를 구하려고 하는데요
결과는 아래와 같이 나와야합니다.
ID | CNT |
AAA | 3 |
BBB | 1 |
CCC | 2 |
DDD | 0 |
EEE | 1 |
FFF | 1 |
GGG | 1 |
어떻게 쿼리를 짜는게 맞을까요??
지금.. CTE가 되는 DBMS를 가지고 있지 않아서 돌려보진 못했는데, 이런 식으로 풀 수 있을 것 같네요.
(MS-SQL 에서는 RECURSIVE 키워드를 뺍니다.)
WITH RECURSIVE C AS ( SELECT ID, REG_UYEAR FROM tbl_name WHERE REG_UYEAR = 2019 UNION ALL SELECT T.ID, T.REG_UYEAR FROM C INNER JOIN tbl_name T ON T.ID = C.ID AND T.REG_UYEAR = (C.REG_UYEAR - 1) ) SELECT ID, COUNT(*) FROM C GROUP BY ID;
단, DDD 회원이 0회인건 안나올텐데, 꼭 필요한거면 LEFT OUTER JOIN을 적절히 추가하시면 됩니다.
WITH t AS ( SELECT 1 seq, 'AAA' id, 2017 reg_uyear UNION ALL SELECT 2, 'AAA', 2018 UNION ALL SELECT 3, 'AAA', 2019 UNION ALL SELECT 4, 'BBB', 2017 UNION ALL SELECT 5, 'BBB', 2019 UNION ALL SELECT 6, 'CCC', 2018 UNION ALL SELECT 7, 'CCC', 2019 UNION ALL SELECT 8, 'DDD', 2018 UNION ALL SELECT 9, 'EEE', 2019 UNION ALL SELECT 10, 'FFF', 2019 UNION ALL SELECT 11, 'GGG', 2019 ) -- 1. Row_number 를 이용하여 조건 주기 SELECT id , COUNT(CASE WHEN x = 2019 THEN 1 END) cnt FROM (SELECT id , reg_uyear + ROW_NUMBER() OVER(PARTITION BY id ORDER BY reg_uyear DESC) - 1 x FROM t WHERE reg_uyear <= 2019 ) a GROUP BY id ;
WITH t AS ( SELECT 1 seq, 'AAA' id, 2017 reg_uyear UNION ALL SELECT 2, 'AAA', 2018 UNION ALL SELECT 3, 'AAA', 2019 UNION ALL SELECT 4, 'BBB', 2017 UNION ALL SELECT 5, 'BBB', 2019 UNION ALL SELECT 6, 'CCC', 2018 UNION ALL SELECT 7, 'CCC', 2019 UNION ALL SELECT 8, 'DDD', 2018 UNION ALL SELECT 9, 'EEE', 2019 UNION ALL SELECT 10, 'FFF', 2019 UNION ALL SELECT 11, 'GGG', 2019 ) -- 2. Recursive SQL 이용 , t1 AS ( SELECT id , MIN(CASE WHEN reg_uyear = 2019 THEN reg_uyear END) reg_uyear FROM t GROUP BY id UNION ALL SELECT b.id , b.reg_uyear FROM t1 a INNER JOIN t b ON a.id = b.id AND a.reg_uyear - 1 = b.reg_uyear ) SELECT id , COUNT(reg_uyear) cnt FROM t1 GROUP BY id ;