MSSQL 조회 쿼리 질의드립니다. 0 6 145

by 아이디오스 [SQL Query] 쿼리 [2018.10.12 18:57:29]


IP가 저장된 테이블에서 연속된 IP를 묶어서 범위를 표현하며 조회하고 싶은데
숫자로 바꾸어 LAG, LEAD를 사용해 비교 해보려했지만 능력부족으로 최종단계가 안나와 도움을 요청하게 되었습니다.

예시 테이블 : 
select 1 as seq, '192.168.1.2' as ip union
select 2 as seq, '192.168.1.3' as ip union
select 3 as seq, '192.168.1.4' as ip union
select 4 as seq, '192.168.1.5' as ip union
select 5 as seq, '192.168.1.6' as ip union
select 6 as seq, '192.168.5.5' as ip union
select 7 as seq, '192.168.7.8' as ip union
select 8 as seq, '192.168.7.9' as ip union
select 9 as seq, '192.168.10.2' as ip union
select 10 as seq, '192.168.10.3' as ip union
select 11 as seq, '192.168.10.4' as ip union
select 12 as seq, '192.168.10.5' as ip

예시 결과 내용 1 : 

seq ip
1,5 192.168.1.2 ~ 192.168.1.6
6 192.168.5.5
7,8 192.168.7.8 ~ 198.168.7.9
9,12 192.168.10.2 ~ 192.168.10.5

예시결과내용2 : 

seq ip
1,2,3,4,5 192.168.1.2 ~ 192.168.1.6
6 192.168.5.5
7,8 192.168.7.8 ~ 198.168.7.9
9,10,11,12 192.168.10.2 ~ 192.168.10.5

 

결과는 둘 중 아무거나 나와도 상관 없으며 MSSQL 2017버전을 사용중입니다

by 마농 [2018.10.15 08:45:18]

3번째 자리까지 동일한 ip 로 그룹핑 하면 됩니다.
다만, 4번째 자리가 연속인 것끼리 그룹핑 하는지?
연속 상관없이 3번째 자리만 동일하면 그룹핑 하는 것인지?
예시 자료만으로는 판단이 안되네요?


by 아이디오스 [2018.10.15 09:36:45]

4번째 자리가 연속인것끼리만 그룹핑이 되어야 합니다


by 구웃 [2018.10.15 09:40:47]

group by ip 하면 되지 않을까요?


by 우리집아찌 [2018.10.15 10:06:30]
WITH T AS (
select 1 as seq, '192.168.1.2' as ip union
select 2 as seq, '192.168.1.3' as ip union
select 3 as seq, '192.168.1.4' as ip union
select 4 as seq, '192.168.1.5' as ip union
select 5 as seq, '192.168.1.6' as ip union
select 6 as seq, '192.168.5.5' as ip union
select 7 as seq, '192.168.7.8' as ip union
select 8 as seq, '192.168.7.9' as ip union
select 9 as seq, '192.168.10.2' as ip union
select 10 as seq, '192.168.10.3' as ip union
select 11 as seq, '192.168.10.4' as ip union
select 12 as seq, '192.168.10.5' as ip union
select 12 as seq, '192.168.10.15' as ip
) , T2 AS ( 
SELECT A.*
    , SEQ - ROW_NUMBER() OVER(PARTITION BY GROUP3 ORDER BY GROUP4) GRP 
    FROM ( SELECT T.*
            , LEFT(SUBSTRING( IP,CHARINDEX('.',IP,8) + 1 , LEN(IP)), CHARINDEX('.',SUBSTRING( IP,CHARINDEX('.',IP,8) + 1 , LEN(IP)))-1) AS GROUP3
            , RIGHT(IP, CHARINDEX('.', REVERSE(IP)) - 1) AS GROUP4
         FROM T
     ) A 
)

SELECT STUFF((
          SELECT ',' + CAST(B.SEQ AS VARCHAR)
          FROM T2 B
          WHERE A.GRP = B.GRP
          FOR XML PATH('')
          ),1,1,'') AS SEQ
      ,MIN(A.IP) +  CASE WHEN MIN(A.IP) = MAX(A.IP) THEN '' ELSE '~' + MAX(A.IP) END  AS IP
  FROM T2 A
 GROUP BY GRP          

 


by 마농 [2018.10.15 10:40:29]
WITH t AS
(
SELECT 1 seq, '192.168.1.2' ip 
UNION ALL SELECT  2, '192.168.1.3'
UNION ALL SELECT  3, '192.168.1.4'
UNION ALL SELECT  4, '192.168.1.5'
UNION ALL SELECT  5, '192.168.1.6'
UNION ALL SELECT  6, '192.168.5.5'
UNION ALL SELECT  7, '192.168.7.8'
UNION ALL SELECT  8, '192.168.7.9'
UNION ALL SELECT  9, '192.168.10.2'
UNION ALL SELECT 10, '192.168.10.3'
UNION ALL SELECT 11, '192.168.10.4'
UNION ALL SELECT 12, '192.168.10.5'
UNION ALL SELECT 13, '192.168.10.7'
UNION ALL SELECT 14, '192.168.7.10'
)
, tmp AS
(
SELECT seq, ip
     , v1, v2, v3, v4
     , v4 - ROW_NUMBER() OVER(PARTITION BY v1, v2, v3 ORDER BY v4) grp
  FROM (SELECT seq, ip
             , SUBSTRING(ip, p0+1, p1-p0-1)+0 v1
             , SUBSTRING(ip, p1+1, p2-p1-1)+0 v2
             , SUBSTRING(ip, p2+1, p3-p2-1)+0 v3
             , SUBSTRING(ip, p3+1, p4-p3-1)+0 v4
          FROM (SELECT seq, ip
                     , 0 p0
                     , CHARINDEX('.', ip, 1) p1
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip, 1) + 1) p2
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip
                     , CHARINDEX('.', ip, 1) + 1) + 1) p3
                     , LEN(ip) + 1 p4
                  FROM t
                ) a
        ) a
)
SELECT STUFF((SELECT CONCAT(',', seq)
                FROM tmp
               WHERE v1 = a.v1
                 AND v2 = a.v2
                 AND v3 = a.v3
                 AND grp = a.grp
               ORDER BY v4
                 FOR XML PATH('')
              ), 1, 1, '') seq
     , CONCAT( v1, '.', v2, '.', v3, '.', MIN(v4)
             , CASE WHEN COUNT(*) = 1 THEN ''
                    ELSE CONCAT(' ~ ', v1, '.', v2, '.', v3, '.', MAX(v4))
                END
             ) ip
  FROM tmp a
 GROUP BY v1, v2, v3, grp
;

 


by 아이디오스 [2018.10.15 11:12:53]

두분 답변 너무 감사드립니다!

이렇게 보니 원리 자체는 어렵지 않은거였는데 지레 겁먹고는 생각을 너무 좁게 하고 있었네요

더 많은 훈련을 해야겠습니다..

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