데이터베이스 질문과 답변
안녕하세요.
하루종일 생각했는데 정말 모르겠어서 도움을 요청하고자 글을 올립니다.
아래와 같은 데이블인 경우 조건은
1) 번호와 코드값이 동일한 경우,
2) 각 시행일자를 기준으로 다음에 이어지는 시행일자가 28일 이상 간격이 있는 경우 새로운 행으로 추가하고 싶습니다.
어떻게 쿼리를 짜야할까요?
(기본 데이터)
| 번호 | 코드 | 시행일자 |
|---|---|---|
| 10 | SEP | 2020-01-01 |
| 10 | SEP | 2020-01-02 |
| 10 | SEP | 2020-01-03 |
| 10 | SEP | 2020-01-04 |
| 10 | SEP | 2020-02-02 |
| 10 | SEP | 2020-02-03 |
| 10 | SEP | 2020-02-04 |
| 10 | SEP | 2020-02-05 |
| 10 | SEP | 2020-02-06 |
| 10 | SEP | 2020-03-07 |
| 10 | SEP | 2020-03-08 |
| 10 | SEP | 2020-03-09 |
| 10 | SEP | 2020-03-10 |
(결과데이터)
1) 결과값으로 3건의 데이터가 추출
2) 시행일자주기(28일간격): 위의 시행일자의 각 데이터를 기준으로 다음일자에 시행되는 데이터가 28일 이상 간격이 있는 경우 새로운 행을 추가한다.
(현재 01-01~01-04 까지는 시행일자가 각 시행일자 기준으로 +1일 간격이나
01-04일을 기준으로 02-02 일은 28일 이상 간격이 있으므로 새로운 행을 추가하고 주기를 구분합니다.)
3) 더불어 28일 간격으로 데이터가 나눠지므로 하나의 데이터에 총 몇번을 시행했는지 count를 하고 싶습니다.
| 번호 | 코드 | 시행일자주기(28일간격) | 주기별연속된데이터(시행건수) | |
|---|---|---|---|---|
| 10 | SEP | 1 | 4 | |
| 10 | SEP | 2 | 5 | |
| 10 | SEP | 3 | 4 |
제발 도와주세요,
일자는 항상 연속되나요?
샘플엔 연속되는 일자만 나오네요?
결과의 타이틀도 "연속된데이터"라고 표시되고 있구요?
연속되는 데이터와 28일 이상 간격인 데이터만 샘플로 주셨는데?
연속되지 않는 데이터는 없는지?
28일 미만 자료는 없는지?
예를 들면 샘플예시엔 2월 2,3,4,5,6 인데
2월 2,3, 5, 7 인 경우 결과가 어떻게 나와야 하는지?
WITH T AS
(
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-01-01') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-01-02') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-01-03') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-01-04') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-02-02') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-02-03') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-02-04') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-02-05') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-02-06') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-03-07') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-03-08') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-03-09') DT UNION ALL
SELECT '10' NO, 'SEP' CD, CONVERT(DATE,'2020-03-10') DT
)
SELECT NO, CD, ROW_NUMBER() OVER(ORDER BY GB) RN, COUNT(1) CNT
FROM
(
SELECT NO, CD, SUM(FG) OVER (PARTITION BY NO, CD ORDER BY DT) GB
FROM
(
SELECT NO, CD, DT
, CASE WHEN DATEDIFF(DAY, LAG(DT) OVER(PARTITION BY NO, CD ORDER BY DT), DT) >= 28 THEN 1 ELSE 0 END FG
FROM T
) S1
) S2
GROUP BY NO, CD, GB
알려주셔서 감사합니다.
그런데 현재는 예시로 기재한 상태인데, 실제 데이터에서는 번호와 코드가 다양하고 데이터 수가 많아
하나씩 Union을 할수가 없는데 어떻게 해야하나요?제가 너무 초보라 도움을 청합니다~~
with문은 제가 민영님의 테이블을 알 수 없어 임시로 만든 가상 테이블일 뿐입니다.
t라고 하는 임의의 테이블에 데이터를 생성하려고 union 구문으로 데이터를 생성한 것이고요.
위에 드린 쿼리를 이해하시고 실제 테이블에 적용하셔야 합니다.
이해 안 된 상태로 단순히 복사 붙여넣기만 하시면 분명 문제가 생깁니다.
from절 제일 안쪽 쿼리부터 하나씩 하나씩 쿼리 수행하면서 함수의 사용법 등을 익히세요.
네~말씀해주신 대로 하나하나 익혀야겠습니다. 조언 감사합니다~
네~항상 연속되지는 않아요.
그렇지만 연속되지 않아도 이전의 날짜 기준으로 다음에 시행되는 날짜 간격이 28일 미만이라면 결과 항목에 표시되지 않아도 됩니다.
중요한건 이전 시행일자와 다음 시행일자 간격이 28일 이상 간격이 있는 데이터에 대해서만 뽑고 싶거든요~
그리고 데이터가 현재 예시에는 13개로만 축약하였지만 데이터양이 너무 많아요. 번호도 다양하게 있구요.
WITH t AS
(
SELECT 10 no, 'SEP' cd, '2020-01-01' dt
UNION ALL SELECT 10, 'SEP', '2020-01-02'
UNION ALL SELECT 10, 'SEP', '2020-01-03'
UNION ALL SELECT 10, 'SEP', '2020-01-04'
UNION ALL SELECT 10, 'SEP', '2020-02-02'
UNION ALL SELECT 10, 'SEP', '2020-02-03'
UNION ALL SELECT 10, 'SEP', '2020-02-04'
UNION ALL SELECT 10, 'SEP', '2020-02-05'
UNION ALL SELECT 10, 'SEP', '2020-02-06'
UNION ALL SELECT 10, 'SEP', '2020-03-07'
UNION ALL SELECT 10, 'SEP', '2020-03-08'
UNION ALL SELECT 10, 'SEP', '2020-03-09'
UNION ALL SELECT 10, 'SEP', '2020-03-10'
)
SELECT no, cd, grp
, COUNT(*) cnt
FROM (SELECT no, cd, dt
, SUM(flag) OVER(PARTITION BY no, cd ORDER BY dt) grp
FROM (SELECT no, cd, dt
, CASE WHEN DATEDIFF(day, LAG(dt) OVER(PARTITION BY no, cd ORDER BY dt), dt) < 28
THEN 0 ELSE 1 END flag
FROM t
) a
) a
GROUP BY no, cd, grp
;
감사합니다!!!!!!!!!!!!!!!!!!!!!
덕분에 해결하였어요~너무 감사드려요~~
) 버튼을 클릭하여 작성 하시면 됩니다.