SQL 가로를 세로로 / 세로를 가로로 (with where 와 left join)

결론

가로에서 세로로 union 함수 사용, 세로에서 가로로 max 함수 사용

 

 

 

 

가로에 있는 칼럼들을 세로로 나열하는 방법

상황설명

넷플릭스 아이디를 공유하는 팀을 운영하고 있다.

각 팀 당 4명이 있다.

필요한 정보만 뽑고 각 구성원을 세로로 표현하고 싶다.

 

 

group_info

team_id	team_password	start_date	pay	pay_actual	profit
1	1111	2021.10.27	14500	7600	6900
2	1111	2021.10.23	14500	7600	6900
3	1111	2021.9.28	14500	7600	6900

group_member

team_id	user1_name	user2_name	user3_name	user4_name
1	박지민	강영찬	김지영	박지영
2	이가람	장평화	김지민	최원빈
3	허현수	이영조	조성원	강지인

 

sqldeveloper 팁 

CTL + F7 을 누르면 모두 자동정렬된다.

 

t1은 group_info의 모든 칼럼을 부르는 게 아니라 부분만 가져온 테이블.

t2는 union 함수를 이용하여 user_name의 칼럼으로 가로 값을 세로로 나열한다.

t1, t2 로 t라는 새로운  테이블을 만든다.

조건은 group_info 와 group_member 가 일치

칼럼을 한글로  새롭게 별칭

조건문을 사용하여 이름이 없으면 000 있으면 그대로 사용

모든 칼럼값을 하나의 문자열로 만들고 싶어서 별칭을 사용했는데 오류

그 이유는  as 사용하여 방금 막 만든 칼럼을 같은 레벨에서 바로 사용불가.

이게 t를 다시 서브쿼리로 두는 이유.

 

 

union 과 unionall 함수

unionall은 중복 포함
중복체크를 하기 때문에 union이 더 느림

 

 

 

해결 법은 with을 사용하는 방법도 있겠지만 서브 쿼리로 사용하여 해결 가능

 

중복 제거하는 법 group by 두 컬럼 사용

 

order by 세 번 연속 사용하여 순서 정렬

 

---

세로에 있는 열을 가로의 칼럼으로 나열하는 방법

 

상황 설명

공병과에 이예나, 권다은, 배현아, 문정민이 지원했다.

이예나, 권다은, 문정민은 같은 부대 출신이다.  

배현아는 다른 부대에서 지원했다.

문정민은 시험날 결시했다.

시험은 각개1, 각개2 채점관 2명이 채점했다.

공정성을 위해 채점관 2명은 다른 부대 출신이다.

barcode의 응시여부 칼럼은 지원자가 출석해서 코드를 찍으면 Y 입력되고

지원자가 안 와서 코드를 안 찍었으면 N이다. 

score 결시여부는 지원자가 불참하여 채점자가 결시로 체크하면 Y

참여하여 점수를 입력하면 N이다. 

 

필요한 정보만 뽑아서 보여주고 세로로 나열되어 있는 지원자 점수를 가로로 표현하고 싶다.

 

 

member

모집이름(major_nm)	주민번호(user_no)	이름(user_nm)	부대번호(list_id)
공병과	199901	이예나	11
공병과	199902	권다은	11
공병과	199903	배현아	12
공병과	199904	문정민	11

 

barcode

주민번호(user_no)	임시번호(temp_no)	응시여부(abs)
199901	1	Y
199902	2	Y
199903	3	Y
199904	4	N

 

score

가번호(temp_no)	문제번호(qst_no)	채점자(scorer_nm)	점수(score)	결시여부(abs_yn)	부대번호(list_id)
1	1	각개1	6	N	12
2	1	각개1	7	N	12
3	1	각개1	8	N	12
4	1	각개1		Y	12
1	1	각개2	5	N	12
2	1	각개2	7	N	12
3	1	각개2	8	N	12
4	1	각개2		Y	12

 

 

 

 

 

모집이름	주민번호	이름	부대번호	임시번호	응시여부	채점자	점수	결시여부
공병과	199901	이예나	11	1	Y	각개1	6	N
공병과	199901	이예나	11	1	Y	각개2	5	N
공병과	199902	권다은	11	2	Y	각개1	7	N
공병과	199902	권다은	11	2	Y	각개2	7	N
공병과	199904	문정민	11	4	N	각개1		Y
공병과	199904	문정민	11	4	N	각개2		Y

조건절로 11부대 출신 지원자만 걸었기 때문에 배현아는 안 보인다.

12 부대 출신 채점자를 조건으로 걸었다.

지금은 12 부대 채점자밖에 없기 때문에 모든 채점자가 보인다. 

b와 c left join을 걸 때 임시번호 하나에 두 개니까 지원자 한 명 당 2개의 열이 나온다.

 

모집이름	주민번호	이름	부대번호	임시번호	응시여부	채점자	점수	결시여부
공병과	199901	이예나	11	1	Y	각개1	6	N
공병과	199902	권다은	11	2	Y	각개1	7	N
공병과	199904	문정민	11	4	N	각개1		Y

group by를 걸면 채점자 2개 중에서 하나만 표시.

select * 을 썼기 때문에 사실 모든 칼럼이 나와야 하지만 지면상 모두 보여줄 수 없기 때문에 필요한 부분만 썼음.

 

모집이름	임시번호	주민번호	결시구분	각개1	각개2
공병과	1	199901	응시	6	5
공병과	2	199902	응시	7	7
공병과	4	199904	결시

 

group by로 묶여있을 때 max함수와 조건문을 써서 안 보이는 내용을 칼럼에 추가하여 붙일 수 있다.

이걸 서브쿼리로 넣고 최종 결과 테이블은

 

 

모집이름	임시번호	주민번호	결시구분	각개1	각개2	결시확인
공병과	1	199901	응시	6	5	응시
공병과	2	199902	응시	7	7	응시
공병과	4	199904	결시			결시

이렇게 나온다.

결시 확인은 조건문을 걸었다.

확인필요는 어떤 상황에서 생길까?

지원자가 코드를 안 찍어 결시인데 채점자가 점수를 넣었다든가

지원자가 참여했는데 채점자가 점수를 안 줬다든가

이런 상황에 발생한다.

 

---

 

left join의 원리

left join은 왼쪽 테이블 내용은 그대로 살리면서

오른쪽 테이블에서 왼쪽 테이블과 같은 부분만 가져와 붙이는 작동 원리

 

where 와 차이점은??

inner join과 유사하다.

같은 부분만 가져온다.

 

각 방법의 용도는 어떻게 쓰면 좋을까?

left join은 왼쪽을 살리면서 조건에 맞는 것을 가져오기 때문에 왼쪽의 모든 데이터를 볼 수 있는 장점이 있다.

다만 왼쪽 데이터가 엄청 많다면??? 직관적이지 않다.

이럴 땐 where 절을 사용하면 해당 데이터만 보기 때문에 보이는 데이터량을 줄일 수 있다.

상황에 따라서 적절히 사용하면 될 것 같다.

 

 

왼쪽 우선 외부 조인 | Microsoft Docs

왼쪽 우선 외부 조인

       

---

세로에 있는 열을 가로의 칼럼으로 나열하는 방법 2

상황설명

이전 상황을 이어서 간다.

한 사람 당 2명의 채점자가 붙는다.

2문제를 풀어야 한다.

 

score

임시번호(temp_no)	문제번호(qst_no)	채점자(scorer_nm)	점수(score)
1	1	각개1	3
1	1	각개2	4
1	2	각개1	9
1	2	각개2	8
2	1	각개1	5
2	1	각개2	3
2	2	각개1	7
2	2	각개2	8

 

scorer

scorer_no	scorer_nm
1	각개1
2	각개2

상황 설명

 

 

채점자의 순번을 알기 위해 서브쿼리를 사용했다.

별칭 사용에 유의하자.

특히나 서브쿼리에 서브쿼리가 들어가는 부분인 A의 범위.

전 문제는 서브쿼리 안에서 바깥으로 별칭 사용.

이번에는 서브쿼리 밖에서 안으로 별칭 사용.

임시번호로 group by를 하고 max 함수로 칼럼에 나열.

max 함수에서 case when이 조건절 역할.