Chapter 2. Performing Advanced Queries Using PROC SQL (7) - Subqueries
Introducing Subqueries
PROC SQL에서는 WHERE이나 HAVING구절 말고도 이와 비슷한 기능을 하는게 있는데요. 바로 Subquery입니다. Subquery는 쿼리 안에 있는 또 다른 쿼리라고 보면 되어요. 그래서 Subqueries는 nested queries, inner queries 혹은 sub-selects라고 부르기도 합니다. 아래 그림처럼 쿼리 안에 subquery가 있는데요. 이렇게 안에 있는 쿼리는 괄호 ( ) 안에 넣어줘야 합니다.
이렇게 inner query는 불러오는 테이블에서 하나 혹은 그 이상의 행을 선택해서 한개 혹은 여러개의 cutter query에서 실행하게 됩니다. 지금 inner query에서는 sasuser라이브러리에 payrollmaster라는 데이터에서 salary값의 평균값이 불러오게 되네요. 그렇다고 항상 inner query가 outer query와 연관 있느냐! 이건 아니예요.
Noncorrelated / Correlated 이렇게 두가지로 유형이 나눠집니다. 그럼 이 두가지 유형에 대해 살펴보도록 하죠.
Subsetting Data By Suing Noncorrelated Subqueries
Noncorrelated subqueries란 inner query와 outerquery가 서로 연관이 없는 경우를 말합니다. 위에 코드가 그 예인데요. 다시 한번 살펴보죠.
코드를 보면, HAVING 구문에서~ 아! HAVING 구문이 뭔지 모르겠다면 그 전 포스팅 참고하세요.
Having 구문 안에 subquery가 있네요. 이 subquery는 하나의 값, 즉 sasuser 라이브러리에 있는 payrollmaster의 salary라는 데이터의 평균값, 하나의 값을 말합니다. 이 하나의 값을 가지고 그룹화되어있는 avg(salary) 값과 비교하라는 뜻이겠네요.
그래서 jobcoder그룹별의 평균값과 전체 평균값을 비교하기위해 이렇게 쓰고 있습니다.
이렇게 inner와 outer 쿼리가 서로 연관되지 않은 경우, sas 는 inner먼저 실행하고 그 다음 outer쿼리가 실행이 됩니다.
그래서 위에 코드 보면, inner쿼리 안에서의 전체 샐러리의 평균값이 계산되어지고요, 그 다음 jobcode의 그룹화 평균값이 계산되어지는데 전체 평균값보다 큰 그룹의 평균값만 선택(select)되어 결과값으로 출력하게 됩니다. 그래서 결과값은 아래와 같아요.
Using Multiple-Value Noncorrelated Subqueries
위의 예는 inner 쿼리가 하나의 값만 가지게 된 경우였는데요. 만약 inner 쿼리가 여러개의 값을 가지게되는 경우는 어떨까요.
예를들어, 생일이 곧 다가오는 회사 직원에게 생일카드를 보내고 싶어요. 그래서 PROC SQL을 이용해서 2월달 생일을 맞이하는 직원의 이름과 주소를 데이터로 가져오려고 합니다. 그런데 이름과 주소가 한 데이터 테이블, 즉 Staffmaster란 파일에 있고요. 생일은 다른 데이터테이블, 즉 payrollmaster라는 파일에 있다고 칩시다. 이 두개의 파일에는 EmpID 열이 공통으로 가지고 있습니다.
위에 코드 보면, inner 쿼리부터 실행된다고 했으니~ inner 쿼리를 봅시다. 괄호 안에, sasuser라이브러리에 있는 payrollmaster라는 파일에서 dateofbirth의 달이 2인 empid를 가진 사람만 선택되어 나옵니다. 위의 예와 다르게 여러명의 empid값들이 선택되어지겠죠. 그래서 outer 쿼리의 where구문 보면, where empid in 즉, where 구문의 조건 연산자인 IN이 나와서 이 조건을 만족하는 empid와, 성, 이름, 도시, 주 가 선택되어 결과값이 나오게 됩니다. 그래서 결과값은 다음과 같아요.
Subsetting Data By Suing Correlated Subqueries
이번엔 쿼리들끼리 연관되어있는 경우를 살펴보도록 합시다. 바로 예제로 들어갈게요.
navigator이면서 매니저인 사람들의 성과 이름만 출력하려고 하는데, 데이터가 행으로 읽어지는데 jobcategry가 'NA'인 사람이 sasuser라이브러리에 있는 supervisors라는 데이타에서 staffmaster라는 데이터의 EmpID가 suvervisors라는 데이터의 EmpID가 같을 경우 그 jobcategory가 선택되어져서 이것들이 NA와 같은 경우, 이럴때만 그 사람의 성과 이름을 출력하라는 내용입니다. 그러니까 행마다 데이터를 읽어내서, 그 행의 jobcategory가 NA일때, 이 NA가 inner 쿼리 값과 비교하는거라서 서로 연관되어있다고 보는겁니다.
결과값은 아래와 같고요. 이 사람들이 navigator이면서 (즉 jobcategory가 NA이면서) 매니저인 사람들입니다.
Using the EXISTS and NOT EXISTS Conditional Operator
이때 outer쿼리에 있는 WHERE 구문이나 HAVING 구문의 조건이 어떠냐에 따라 EXISTS 혹은 NOT EXISTS 두가지로 나눠지는데요. 말 그대로 inner 쿠리의 값이 하나 이상 해당될때가 EXISTS, 하나도 해당되는 값이 없을때 NOT EXISTS로 나눠집니다.
NOT EXISTS의 예를 들어봅시다.
Flightattendatns라는 파일에는 모든 승무원의 이름과 empID가 들어있고요.
FlightSchedule이라는 파일에는 각 날짜별로 일하는 승무원의 정보가 있다고 합시다.
만약 두개의 파일의 공통적으로 나타나는 승무원이 있다면~
승무원이면서도 스케쥴이 잡힌 승무원을 말하겠죠?
아래 그림과 같은 경우, 승무원이지만 아직 스케쥴이 잡히지 않은 승무원을 말합니다.
지금 아래 그림에 해당하는 승무원이 있는지 찾아보고자 해요.
다음과 같은 코드를 사용하면 됩니다.
sasuser라이브러리에 있는 flightattendatns라는 데이터에서 성과 이름을 출력하는데 누구를 선택하느냐!! 조건이 필요하겠죠. 그 조건 where은 NOT EXISTS 즉, inner 쿼리 안에서 살펴볼때, flightschedule이라느 데이터 파일에서 flightattendant의 empID인 사람이 flightshcedule의 empID와 같은 모든 경우를 찾아보고 얘네들을 선택해서 (select *), 여기에 해당되지 않은 경우 (not exists)의 성과 이름을 출력하란 내용입니다.
# sas 자격증, SAS Advanced, SAS advanced Programming, SAS Prep Guide, 자격증