'ASP.NET MVC/SQL'에 해당하는 글 3건

SQL :: 3 시도별 총회원 등급 카운트

ASP.NET MVC/SQL 2018. 5. 6. 12:15

안녕하세요 .. 요새 직장에 다시 들어가서 정신없이 배우느라 포스팅할 시간이없어서 주말동안에 회사에서 배운것을 정리할겸 포스팅을 할계획입니다  (_ _) 



회사업무 특성상 집계, 카운팅을 자주 하게되는데 오늘 배운 쿼리를 공부 할겸 다시보기 해보려고한다


클라이언트의 요구사항은 이것이었다 


일반회원중 등급 1, 등급 2의 거주지역을 시도별로 집계 해달라는것이었다



1. 우선 일반회원중 등급이 1,2 인 사람들의 총합을 구해보도록 하려한다


1
2
3
4
5
/* 총회원(등급 1,2) */   
SELECT COUNT(*)   
FROM USER_MASTER UM (NOLOCK)                                        -- 유저 마스터 테이블 ( 개인회원 , 기업회원 )
JOIN USER_PERSONAL UP (NOLOCK) ON UP.[USER_ID] = UM.[USER_ID]       
WHERE UM.USER_FG IN ('1', '2')                                         -- 개인회원중 등급이 1,2인 회원
cs


총회원을 구한후 평소와 같이 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
/* 정상우편번호 회원(등급 1,2) */   
SELECT T.COUNTY, COUNT(T.[USER_ID]) AS CNT                            -- 시도 (서울,경기,부산) , 시도별 유저수 카운팅
  INTO #ADDRESS1   
FROM (   
   SELECT Z.COUNTY, UM.[USER_ID]                                      -- 시도 (서울,경기,부산) , 시도별 유저수 그후  시도,사도별유저수로 묶기위해 group by
   FROM USER_MASTER UM (NOLOCK)
   JOIN USER_PERSONAL UP (NOLOCK) ON UP.[USER_ID] = UM.[USER_ID]
   LEFT JOIN ZIPCODE Z (NOLOCK) ON Z.ZIP_CD = REPLACE(UM.ZIP_CD, '-', '')  -- 우편번호 테이블을 가져온다 유저마스터의 zip 조건으로
   WHERE UM.USER_FG IN ('1', '2') 
     AND Z.COUNTY IS NOT NULL
   GROUP BY Z.COUNTY, UM.[USER_ID]
) AS T   
GROUP BY T.COUNTY   
ORDER BY 1   
cs


우편번호 테이블의 기본주소  유저 마스터 기본주소 를  키값으로 LEFT 조인 시켜준다


그후 시도, 시도별 유저수를 select 시켜준다 


하지만....


총회원값 과 가져온 회원수가 일치하지 않는 것이었다 ..


아무래도 회원가입을 할때 Validate 가 제대로 되지않은거 같다... 몇가지 샘플을 찾아보니 맨앞에 공백이 들어가있는 컬럼도 몇개있었다..


그래서 생각한것이 주소지 앞에 2자리만 추출을 하는것이었다 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* 주소지 텍스트로 시도추출로 추출() */                          
SELECT T.COUNTY, COUNT(T.[USER_ID]) AS CNT   
  INTO #ADDRESS2   
FROM (   
   SELECT Z1.COUNTY, UM.[USER_ID]
   FROM USER_MASTER UM (NOLOCK)
   JOIN USER_PERSONAL UP (NOLOCK) ON UP.[USER_ID] = UM.[USER_ID]
   LEFT JOIN SYS_ZIPCODE Z (NOLOCK) ON Z.ZIP_CD = REPLACE(UM.ZIP, '-', '')
   LEFT JOIN (SELECT SIDO FROM SYS_ZIPCODE (NOLOCK) GROUP BY SIDO) Z1 ON Z1.SIDO = LEFT(LTRIM(UM.ADDR1), 2) --앞에 2자리 (서울,경기) 만 추출
   WHERE UM.USER_FG IN ('1', '2') 
     AND Z.ZIP_CD IS NULL
     AND Z1.COUNTY IS NOT NULL
   GROUP BY Z1.COUNTY, UM.[USER_ID]
) AS T   
GROUP BY T.COUNTY   
ORDER BY 1 
cs


이렇게 추출한 2개의 쿼리문을 임시테이블에 INSERT 시킨후 


1
2
3
4
5
6
7
8
/* 정상우편번호 + 주소지 텍스트 추출 회원*/   
SELECT T.SIDO, SUM(CNT) AS CNT   
FROM (   
   SELECT SIDO, CNT FROM #ADDRESS1
   UNION ALL
   SELECT SIDO, CNT FROM #ADDRESS2
) AS T   
GROUP BY T.SIDO   
cs


정상우편번호 + 주소지 에서 앞에 2자리만 추출한 테이블을 UNION ALL 시킨후 총합을 계산해


총회원과 비교를 해본후 정상을 확인해볼수 있다 ..

'ASP.NET MVC > SQL' 카테고리의 다른 글

MSSQL :: 1 Join  (0) 2017.01.13
MSSQL :: 0 CURSOR  (0) 2016.12.01
WRITTEN BY
내가달이다
,

MSSQL :: 1 Join

ASP.NET MVC/SQL 2017. 1. 13. 02:42

LEFT,RIGHT JOIN 에 대해서 포스팅해보려 합니다 


SQLD 라는 자격증을 공부해보려고 이론부터 천천히 공부해보려고합니다


사실 프로시저 짜면서 RIGHT 조인을 써본적이 거의 없다 ... 필요성을 못느낀다고해야되나 LEFT 로 작성을많이했다




LEFT OUTER JOIN


조인 수행시 좌측 테이블에 해당하는 데이터를 먼저 읽은후 , 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다


간단히 말해 


Table A,B 가 있을때 (테이블 'A' 기준) A 와 B 를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을때 그 해당 데이터를 가져오고 ,


B의 JOIN 칼럼에 없을경우 NULL 값으로 채운다 




1
2
3
4
select *
from stadium left join team
on STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
order by HOMETEAM_ID
cs


스타디움에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다 . STADIUM 과 TEAM 을 JOIN 하되 홈팀이 없는 경기장의 정보도 


같이 출력 하도록 해보자






RIGHT OUTER JOIN


조인 수행시 LEFT JOIN 과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 



1
2
3
4
select *
from Emp E Right outer join Dept D
on E.DEPTNO  = D.DEPTNO
 
cs



DEPT 에 등록된 부서 중에는 사원이 없는 부서도 있다. 


DEPT 와 EMP 를 조인하되 사원이 없는 부서 정보도 같이 출력 하도록 해보자 




INNER JOIN 이라면 사원이 배정되지 않은 부서는 출력이 되지않았지만 LEFT , RIGHT 조인을 할경우에는 배정되지않은 부서 까지 


출력 되는걸 확인할수 있다


'ASP.NET MVC > SQL' 카테고리의 다른 글

SQL :: 3 시도별 총회원 등급 카운트  (0) 2018.05.06
MSSQL :: 0 CURSOR  (0) 2016.12.01
WRITTEN BY
내가달이다
,

MSSQL :: 0 CURSOR

ASP.NET MVC/SQL 2016. 12. 1. 22:54

포스팅하던중 친구가 이러한 쿼리가 가능하냐고 연락이와서 생각난김에 글을적어본다


친구의 요구사항은 테이블이 db_20161025,db_20161125,db_20161225 매달마다 백업하는 테이블이 있다고 가정을하고


'db_' 가 포함된 테이블을 전부 셀렉트 할수있냐는것이었다



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--커서 시작
DECLARE UserTable_Cursor CURSOR
FOR
-- 테이블 중에 name 이 'db_'값 찾기
select name   from sysobjects where name like('db_%')
OPEN UserTable_Cursor
 
DECLARE @name varchar(20)           
DECLARE @strsql nvarchar(300) 

set @name = (select top 1 name   from sysobjects where name like('db_%'))
 
FETCH NEXT FROM UserTable_Cursor INTO @name
 
WHILE (@@FETCH_STATUS = 0)
 
BEGIN    
    SET @strsql =  ' SELECT * FROM ' + @name
       EXEC SP_EXECUTESQL @strsql  
    FETCH NEXT FROM UserTable_Cursor INTO @name       
END
 
CLOSE UserTable_Cursor
 
DEALLOCATE UserTable_Cursor
cs



4번 셀렉트문을 돌린값이다 ( 샘플 예제)


커서실행시 





'ASP.NET MVC > SQL' 카테고리의 다른 글

SQL :: 3 시도별 총회원 등급 카운트  (0) 2018.05.06
MSSQL :: 1 Join  (0) 2017.01.13
WRITTEN BY
내가달이다
,