MS-SQL 한 컬럼 안에 구분자로 이어진 데이터를 이전 날짜와 비교하는 방법을 여쭙습니다. 1 4 1,370

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)

 

by 마농 [2017.02.27 08:20:21]

결과가 일관성이 없네요.
8,8,0
11,0,20
0,0,12 모두 0,0,0 인데
5,5,0 부분만 0,0,1 이네요?
결과표를 정확하게 만들어 주시고 설명도 명확하게 달아주세요.


by 칼세이건 [2017.02.27 08:53:33]

로그인이 오래 되어서 인지

글작성자명을 입력하라고 하면서

다시 처음부터 작성하면서

결과표 입력을 잘못 했었습니다

수정하여 다시 저장했습니다

 

원하는 결과는

최근 좌표별 0 이상의 값이

과거부터 연속으로 발생을 하였는지 파악하는 것 입니다

 

두가지 방법으로 구상 중인데

첫번째는 결과표에 예시한 바와 같이

최근 이력에서 좌표별 0  이상이면

해당 좌표에 1을 카운트 하고

동일 구분 중, 이전 날짜의 동일 좌표에도

0 이상의 값이 연속으로 있었다면 2로 카운트하고

없었다면 0으로 초기화 하고

이전 날짜 데이터 중, 모든 좌표가 0으로 초기화 되었다면

동일 좌표 연속성이 없는 것으로 판단을 하고자 합니다

 

그리고, 두번째는

굳이 동일 좌표 연속 발생 횟수를 카운트 하지 않고

첫번째 방식과 동일한 판단 기준으로

연속 발생한 경우(행)만 보여주고

연속 발생하지 않은 경우(행)은 보여주지 않는 방식으로 생각을 해보았습니다


by 마농 [2017.02.27 10:04:15]
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)
;

 


by 칼세이건 [2017.02.28 08:12:54]

정말 감사합니다.

어제 외근하느라 곧바로 확인해보지 못하였는데

이렇게 빨리 도움을 주실줄은 몰랐네요.

잘 모르는 부분은 좀 더 공부하고 질문 드리도록 하겠습니다.

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