by 칼세이건 [SQLServer] 구분자 콤마 비교 연속 MSSQL MS-SQL SQL SERVER [2017.02.26 20:38:31]
no |
일시 |
구분 |
데이터 |
1 |
2017-02-01 |
A |
000, 000, 011, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000 |
2 |
2017-02-05 |
A |
000, 000, 013, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000 |
3 |
2017-02-10 |
A |
000, 000, 020, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
4 |
2017-02-15 |
B |
011, 000, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000 |
5 |
2017-02-20 |
B |
000, 015, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000 |
6 |
2017-02-25 |
B |
020, 015, 000, 000, 000, 000, 012, 000, 000, 000, 000, 000, 000 |
안녕하세요.
몇일째 이리저리 검색해보고 시도해보았는데, 답을 찾지 못해서 여쭙게 되었습니다.
데이터라는 하나의 컬럼 안에
각 좌표별 결과값이 구분자(콤마)로 이어져 있는 형태인데, (콤마가 무려 400개나 있습니다 ㅠㅠ)
구분(A / B)이 같은 것 끼리
최근 시간 기준 역순으로
같은 좌표에 0 보다 큰 값이 연속으로 발생된 것만 가져오고 싶은데,
MS-SQL도 처음이고, 프로시져도 아직 사용해본적이 없어서 난감 하네요 ㅠㅠ
아래와 같은 결과를 얻고 싶은데,
염치 불구하고 도움 부탁 드립니다.
no |
일시 |
구분 |
데이터 |
좌표별 최근 연속 발생 카운트 |
연속 발생 체크 |
1 |
2017-02-01 |
A |
000, 000, 011, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000 |
000, 000, 003, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
연속 발생 (A) |
2 |
2017-02-05 |
A |
000, 000, 013, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000 |
000, 000, 002, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
연속 발생 (A) |
3 |
2017-02-10 |
A |
000, 000, 020, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
000, 000, 001, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
최근 이력 (A) |
4 |
2017-02-15 |
B |
011, 000, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000 |
000, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
연속 미발생 (B) |
5 |
2017-02-20 |
B |
000, 015, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000 |
000, 002, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000 |
연속 발생 (B) |
6 |
2017-02-25 |
B |
020, 015, 000, 000, 000, 000, 012, 000, 000, 000, 000, 000, 000 |
001, 001, 000, 000, 000, 000, 001, 000, 000, 000, 000, 000, 000 |
최근 이력 (B) |
로그인이 오래 되어서 인지
글작성자명을 입력하라고 하면서
다시 처음부터 작성하면서
결과표 입력을 잘못 했었습니다
수정하여 다시 저장했습니다
원하는 결과는
최근 좌표별 0 이상의 값이
과거부터 연속으로 발생을 하였는지 파악하는 것 입니다
두가지 방법으로 구상 중인데
첫번째는 결과표에 예시한 바와 같이
최근 이력에서 좌표별 0 이상이면
해당 좌표에 1을 카운트 하고
동일 구분 중, 이전 날짜의 동일 좌표에도
0 이상의 값이 연속으로 있었다면 2로 카운트하고
없었다면 0으로 초기화 하고
이전 날짜 데이터 중, 모든 좌표가 0으로 초기화 되었다면
동일 좌표 연속성이 없는 것으로 판단을 하고자 합니다
그리고, 두번째는
굳이 동일 좌표 연속 발생 횟수를 카운트 하지 않고
첫번째 방식과 동일한 판단 기준으로
연속 발생한 경우(행)만 보여주고
연속 발생하지 않은 경우(행)은 보여주지 않는 방식으로 생각을 해보았습니다
WITH t(no, dt, gb, v) AS ( SELECT 1, '2017-02-01', 'A', '000, 000, 011, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000' UNION ALL SELECT 2, '2017-02-05', 'A', '000, 000, 013, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000' UNION ALL SELECT 3, '2017-02-10', 'A', '000, 000, 020, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 4, '2017-02-15', 'B', '011, 000, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 5, '2017-02-20', 'B', '000, 015, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 6, '2017-02-25', 'B', '020, 015, 000, 000, 000, 000, 012, 000, 000, 000, 000, 000, 000' ) , copy_t AS ( SELECT 1 lv UNION ALL SELECT lv + 1 FROM copy_t WHERE lv + 1 <= 13 -- 400 ) , tmp AS ( SELECT no, dt, gb, v , lv, rn , CASE SUM(SIGN(SUBSTRING(v, lv*5-4, 3))) OVER(PARTITION BY gb, lv ORDER BY rn) WHEN rn THEN rn ELSE 0 END x FROM (SELECT no, dt, gb, v , ROW_NUMBER() OVER(PARTITION BY gb ORDER BY dt DESC) rn FROM t ) a CROSS JOIN copy_t c ) SELECT no, dt, gb, v , STUFF((SELECT ', ' + RIGHT(CONCAT('00', x), 3) FROM tmp WHERE gb = a.gb AND dt = a.dt ORDER BY lv FOR XML PATH('') ), 1, 2, '') cnt FROM t a OPTION (MAXRECURSION 0) ;
WITH t(no, dt, gb, v) AS ( SELECT 1, '2017-02-01', 'A', '000, 000, 011, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000' UNION ALL SELECT 2, '2017-02-05', 'A', '000, 000, 013, 000, 000, 000, 000, 008, 000, 000, 000, 000, 000' UNION ALL SELECT 3, '2017-02-10', 'A', '000, 000, 020, 000, 000, 000, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 4, '2017-02-15', 'B', '011, 000, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 5, '2017-02-20', 'B', '000, 015, 000, 000, 000, 005, 000, 000, 000, 000, 000, 000, 000' UNION ALL SELECT 6, '2017-02-25', 'B', '020, 015, 000, 000, 000, 000, 012, 000, 000, 000, 000, 000, 000' ) , copy_t AS ( SELECT 1 lv UNION ALL SELECT lv + 1 FROM copy_t WHERE lv + 1 <= 13 -- 400 ) , tmp AS ( SELECT no, dt, gb, v , lv, rn , CASE SUM(SIGN(SUBSTRING(v, lv*5-4, 3))) OVER(PARTITION BY gb, lv ORDER BY rn) WHEN rn THEN rn ELSE 0 END x FROM (SELECT no, dt, gb, v , ROW_NUMBER() OVER(PARTITION BY gb ORDER BY dt DESC) rn FROM t ) a CROSS JOIN copy_t c ) SELECT no, dt, gb, v FROM tmp GROUP BY no, dt, gb, v HAVING MAX(x) > 0 OPTION (MAXRECURSION 0) ;