[자격증] 정보처리기사 실기 : 22강 SQL 응용 (응용 SQL 작성하기)

 정보

• 업무명     : 정보처리기사 실기 : 22강 SQL 응용 (응용 SQL 작성하기)

• 작성자     : 이상호

• 작성일     : 2020-05-11

• 설   명      :

• 수정이력 :

 

 

 내용

 

[집계성 SQL 작성]

[1] 데이터 분석 함수의 개념

• 1. 데이터 분석 함수의 정의 및 목적 

  • 관계형 데이터베이스에서는 단일 행 기준의 처리가 주로 이루어진다. 그러나 총합, 평균 등의 데이터 분석을 위해서는 복수 행 기준의 데이터를 모아서 처리하는 것이 필수적이 다. 이와 같은 다중 행 처리를 목적으로 하는 다중 행 함수가 존재한다. 

• 2. 데이터 분석 함수의 특성 

  • 데이터 분석을 위한 다중 행 함수의 공통적인 특성은 아래와 같다. 

    • - 단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아 놓고 그룹당 단일 계산 결과를 반환한다. 

    • - GROUP BY 구문을 활용하여 복수 행을 그룹핑한다. 

    • - SELECT, HAVING, ORDER BY 등의 구문에 활용한다.

• 3. 데이터 분석 함수의 유형 

  • SQL 표준에서는 데이터 튜플간의 상호 연관 및 계산 분석을 위한 세 가지 함수가 있다. 

    • - 집계 함수(AGGREGATE FUNCTION) 

    • - 그룹 함수(GROUP FUNCTION) 

    • - 윈도우 함수(WINDOW FUNCTION) 집계 함수는 명령어도 상대적으로 단순하며 이를 기본으로 하여 그룹 함수나 윈도우 함수 에도 적용된다. 집계 함수도 그룹 함수의 한 부분이며, 통합하여 언급하기도 한다. 

 

[2] 집계 함수

• 1. 집계 함수의 구문 

  • 상기 언급한 바와 같이 GROUP BY 구문을 활용하여 복수 행을 그룹핑하여 분석 결과 데이터를 반환한다. 그룹 함수의 구문은 아래와 같다.

  • GROUP BY 구문 뒤에는 테이블을 구분하는 컬럼을 기재하여 그룹화한다. 여기에 지정하는 열을 집약키 또는 그룹화 열이라 칭한다. 

  • HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용한다. WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시에 HAVING을 사용하는 것이다. HAVING 구문은 선택적이며, 상수나 집약 함수, 집약키를 사용할 수 있다.

• 3. 집계 함수의 분류 

  • 자주 사용되는 집계 함수는 아래와 같다. 집계 특성상 숫자 유형의 계산에 사용되는 것이 대다수이나, MAX/MIN 또는 COUNT와 같이 문자열 유형의 최대/최소나 건수 계산 등에도 사용 가능하다. 

• 4. GROUP BY 구문 

  • SQL에서는 WHERE 구문을 활용하여 조건별 대상 ROW를 선택한다. 그러나 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있다. 

  • GROUP BY는 그 와 같은 경우에 사용하며, 아래와 같은 특성을 가진다. 

    • - NULL값을 가지는 ROW는 제외한 후 산출한다. 

    • - SELECT에서 사용하는 것과 같은 ALIAS 사용이 불가하다. 

    • - WHERE 구문 안에 포함되지 않는다. 

    • - WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 한다. 

  • GROUP BY 구문은 실제 구체적 데이터 분석 값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며, 이 부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해 준다

• 5. HAVING

  • HAVING 구문은 WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다. 

  • 일반적으로 GROUP BY 뒤에 기재하며, GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용한다. 

  • 쉽게 생각하면 GROUP BY 및 집계 함수에 대한 WHERE 구문이다.

[3] 그룹 함수

• 1. 그룹 함수의 개념 

  • 집계 함수와 유사한 개념이고 또한 집계 함수를 포함하기도 하나, 여기서는 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 부분을 말한다. 

  • 소계 및 총계 등을 구하기 위해서 집계 함수만 사용한다면 레벨별 집계를 위한 각 단계별 데이터 질의어 (DQL: Data Query Language)를 UNION ALL 등으로 결합하고 표시하는 단계를 거쳐야 한다. 

  • 그러나 그룹 함수를 사용한다면 단일 DQL만으로도 원하는 작업을 할 수 있다. 다음의 예제를 통해 해당 개념을 이해할 수 있다. 

• 2. 그룹 함수의 유형

  • (1) ROLLUP 

    • ROLLUP에 의해 지정된 컬럼은 소계 등 중간 집계값을 산출하기 위해 사용된다. 

    • 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간 집계값이 생성된다.

    • ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜을 유의한다. 

    • ROLLUP 은 다음과 같이 사용한다. 

• 소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재한다. 

• 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재한다. 

• SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 한다는 점만 숙지하고 쿼리를 작성한다. 

• 소계나 총합계 등의 경우는 집계 대상이 되는 행의 그룹핑 앞 또는 뒤에 표현하는 것이 일반적이다. 

• ORDER BY 구문을 활용해 계층 내 정렬에 사용이 가능하며, SQL의 결 과를 보다 체계적으로 보여 준다.

 

• (2) CUBE 

  • CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이며, 가능한 한 소집계 만을 생성하는 ROLLUP과 구별된다. 

  • CUBE는 내부적으로 대상 컬럼의 순서 를 변경하여 또 한 번의 쿼리를 수행한다. 

  • 또 총계는 양쪽 쿼리에서 모두 수행 후 한 쪽에서 제거되는 과정에 의해 ROLLUP에 비해 계산이 많다. CUBE 함수의 사용법도 ROLLUP과 유사하며 다음과 같다.

• (3) GROUPING SETS 

  • GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있다.

  •  집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다. 

  • 또 ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여 준다. 

  • GROUPING SETS의 사용법은 다음과 같다.

 

[4] 윈도우 함수

• 1. 윈도우 함수의 개념 

  • 윈도우 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능이다. 

  • 온라인 분석 처리는 시장 분석, 통계 작성, 경영 계획 분석 및 수립 등 비즈니스 현장에서 자주 사용되는 분석이 포함되며, 데이터 기반 의사 결정의 증가에 따라 그 중요성이 더욱 증가하는 실정이다.

  •  윈도우 함수를 이런 이유로 OLAP(OnLine Analytical Processing) 함수라고도 한다. 

• 2. 윈도우 함수의 구문 

  • 윈도우 함수의 구문은 다음과 같다

• PARTITION BY는 선택 항목이며, 순위를 정할 대상 범위의 컬럼을 설정한다.

• PARTITION BY구에는 GROUP BY구가 가진 집약 기능이 없으며, 이로 인해 ROW 수가 줄어들지 않는다. 

• 참고로, PARTITION BY를 통해 구분된 레코드 집합을 윈도우라고 하며, 윈도우 함수의 유래는 이에 기인한다. 윈도우 함수에는 OVER 문구가 필수적으로 포함되며, ORDER BY 뒤에는 SORT 컬럼을 입력한다. 

• 즉, 어떤 열을 어떤 순서로 순위를 정할지를 지정하며 SELECT 문의 마지막에 사용하는 ORDER BY와 동일하게 사용한다. 

 

• 3. 윈도우 함수의 예시 

  • 윈도우 함수의 간단한 예제는 다음과 같다. 

  • 학생들의 GRADE를 기준으로 하여 순위를 계산하는 예시이다. RANK(), DENSE_RANK(), ROW_NUMBER() 등의 목적에 맞는 윈도우 함수를 사용할 수 있으며, PARTITION BY 구문 뒤에는 순위의 기준이 되는 대상을 조정할 수 있다.

• 4. 윈도우 함수의 분류 

  • (1) 집계 함수 

    • 앞에서 설명한 집계 함수와 동일하다. 대다수의 DBMS에서 지원한다. 

  • (2) 순위 함수 

    • (가) RANK 

      • 레코드의 순위를 계산한다. 동일 순위의 레코드 존재 시 후순위는 넘어간다. 예) 2위가 3개인 레코드인 경우: 2위, 2위, 2위, 5위, 6위,  

    • (나) DENSE_RANK 

      • 레코드의 순위를 계산한다. 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않는다. 예) 2위가 3개인 레코드인 경우: 2위, 2위, 2위, 3위, 4위, … 

    • (다) ROW_NUMBER 

      • 레코드의 순위를 계산한다. 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여한다. 예) 2위가 3개인 레코드인 경우: 2위, 3위, 4위, 5위, 6위, …

  • (3) 행순서 함수(SQL Server에서는 지원하지 않음)

    • (가) FIRST_VALUE 

      • 파티션별 윈도우에서 가장 먼저 나오는 값을 찾는다. 집계 함수의 MIN과 동일한 결과를 출력한다. 

    • (나) LAST_VALUE 

      • 파티션별 윈도우에서 가장 늦게 나오는 값을 찾는다. 집계 함수의 MAX와 동일한 결과를 출력한다. 

    • (다) LAG 

      • 파티션별 윈도우에서 1부터 이전 몇 번째 행의 값을 가져온다. 

    • (라) LEAD 

      • 파티션별 윈도우에서 1부터 이후 몇 번째 행의 값을 가져온다. 

  • (4) 그룹 내 비율 함수 RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE 등의 그룹 내 비율 함수가 있다

 

[특정 기능 수행 SQL문 작성]

[1] 응용시스템 DBMS 접속

• 1. 응용시스템 DBMS 접속 개념 

  • (1) 응용시스템과 DBMS와의 관계 

    • DBMS에서 사용되는 SQL은 DB 관리도구를 사용하여 직접 접속해 실행이 가능하고, 역 시 응용시스템에서도 호출 방식을 통해 사용이 가능하다. 

    • 사용자는 응용시스템을 통해 DBMS에 접속하여 조회하고, 저장하는 것이 가장 일반적인 형태이다. 

    • 응용시스템은 입력변수(Input Parameter)를 전달하고 SQL을 실행하여 결과를 응용시스템을 통해 사용 자에게 전달하는 일종의 매개체인 것이다.

  • (2) 웹 프로그램 구조(WEB Application Architecture) 

    • 웹 응용시스템은 단순하게 표현하면 사용 단말기의 웹 브라우저로부터 요청을 받고 전달하는 웹 서버(WEB Server), 웹 서버로부터 서비스 요청을 받고 트랜잭션을 생성하여 DBMS에 요청하고 응답을 받아서 다시 웹 서버로 전송하는 웹 응용 프로그램 서버 (WAS: WEB Application Server)로 구성된다. 

    • DBMS에서 실행하는 SQL문은 웹 응용 프로그램 서버에서 실행되며, 간단하게 개념적으로 표현하면 아래와 같다.  

• 2. 응용시스템 DBMS 접속 기술 

  • (1) 자바 데이터베이스 연결(JDBC: Java DataBase Connectivity) 

    • Language마다 DBMS에 접근할 수 있게 하는 기술은 여러 가지가 있으나 기본적인 개념은 유사하다.

    • JAVA는 자바 데이터베이스 연결(JDBC: JAVA DataBase Connectivity)을 사용한다. 

    • JDBC는 SQL을 사용하여 DBMS에 질의하고 데이터를 조작하는 API (Application Programming Interface)를 제공한다. 

    • JDBC API를 사용하는 방법은 앞에서 순수한 JAVA 언어로 JDBC를 사용하는 방법과 동일하다. 

    • 아래는 JDBC API를 사용하여 Input Parameter가 존재하는 SELECT SQL을 수행하는 코드 예제이다. 

• (2) MyBatis 

  • MyBatis는 SQL Mapping 기반 오픈 소스 Access Framework이다.

  • MyBatis는 DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML 파일로 분리하고 Mapping을 통해서 SQL을 실행한다. 

  • MyBatis는 DBMS 의존도가 높고 SQL 질의 언어를 사용하기 때문에 SQL 친화 적인 국내 실무 개발 환경에 맞아서 많이 사용된다. 

  • MyBatis의 장점은 다음과 같다. 

    • - 복잡한 JDBC 코드를 단순화할 수 있다. 

    • - SQL을 거의 그대로 사용 가능하다. 

    • - Spring 기반 프레임워크와 통합 기능을 제공한다. 

  • 우수한 성능을 보여 준다. 

  • 여기서는 MyBatis를 위주로 응용시스템에서 사용하는 SQL 작성법을 학습한다. 

 

[2] MyBatis SQL 작성 문법

• 1. MyBatis SQL 문법 기본 구조 

  • MyBatis 설정 파일은 데이터 소스 연결, SQL 매퍼 파일, 타입 별칭(Type Alias), 타입 핸들러(Type Handler)와 같은 속성 설정이 가능하다. 

  • 또 다양한 속성(Attribute)을 조정하여 다중 접속, 연결 문자 등의 조정이 가능하다. 응용 SQL 작성을 위해 필요하기는 하나, 내용도 방대하고 SQL 작성 부분과는 조금 무관하여, 여기서는 MyBatis 설치 및 설정 관련 부분은 다루지 않고 MyBatis XML 작성에 대해 설명하도록 한다.

  •  MyBatis SQL 작성 문법은 다음과 같다. 

• 2. DQL, DML SQL 문장의 Input Parameter 입력 방법 

  • 응용시스템을 통해 SQL을 실행할 때 가장 중요한 부분 중 하나는 입력변수에 대한 처리 방법이다. MyBatis에서는 #{Parameter Name}으로 처리한다. 

  • 간단한 예는 다음과 같다.

• 3. 동적 SQL 

  • MyBatis를 이용한 SQL의 강점 중 하나는 조건에 따라 SQL 구문 자체를 변경할 수 있다는 것이다. 

  • 이를 동적 SQL(Dynamic SQL)이라 하며, 응용 프로그램 실행 시 수행할 SQL문이 결정된다. 

  • 위에서 설명한 Input Parameter만 변경하는 것이 아니라 SQL에 포함된 다양한 부분을 조건에 따라 변경 가능하다. 

  • 다음의 예제를 참조하자. 

• 구문을 사용해서 user_birthday가 있을 때에만 “AND BIRTH_DAY =”조건문이 실행 될 수 있도록 한 것이다.

• user_nm은 필수적으로 입력되는 값이나, 그 외 user_birth_day나 user_email 값이 선택적으로 입력된다면, 오류나 잘못된 결과를 얻는 상황 방지를 위해 위 와 같이 SQL을 작성해야 한다. 

• 위의 경우에는 상기 if 문이 없어도 정상적으로 실행 가능한 SQL 작성은 가능하나, 가독성이나 효율성 측면에서는 의 방법으로 사용하는 것이 좋다. 

• 외에도 다중 문자열 등의 반복적 입력 시 사용하는 , if보다 좀 더 경우별 SQL문 적용을 위한 등이 사용된다. 

• 이 부분에서 짚고 넘어가야 할 것은 응용시스템에서 사용하는 SQL을 작성할 때에는 결국 UI에서 어떻게 값이 입력되고, 입력 항목 검증 방법이 어떻게 되는지, 필수 값 여부 등 복합적 요소들의 관계를 고려하여 작성해야 한다는 것이다. 

• 사용자가 원하는 서비스 제공을 위해 응용시스템은 SQL을 통해 DBMS로부터 데이터를 얻거나 반영하기 때문에 SQL도 통합응용시스템의 부분으로서 역할한다는 것을 인식할 필요가 있다. 

 

• 4. 절차형 SQL 호출

  • DQL, DML SQL뿐만 아니라 학습 1에서 언급된 절차형 SQL도 실행 가능하다.

  • 절차형 SQL 중 사용자 정의함수는 DQL이나 DML에 포함하여 사용하기 때문에 별도 호출의 의미가 없 으며, 트리거 역시 DBMS에서 바로 실행되므로 별도 호출이 불필요하다.

  • 이 중에서 프로 시저를 호출하는 예제는 다음과 같다. 

  • 여기서 주의해야 할 부분은 프로시저를 호출하는 경우 statementType을 ‘CALLABLE’로 설정해야 한다는 것이며, 프로시저 호출 전에도 ‘CALL’ 문장을 사용해야 한다는 것이다.

  • SQL Type은 SELECT나 INSERT 등 어느 것을 사용해도 무방하나, Transaction을 하는 것이 주된 기능이라면 INSERT나 UPDATE를 사용하는 것이 의미상 전달에 좋다.

 

[DCL 명령문 작성]

[1] 사용자 그룹 정의

• 1. 사용자 그룹 관리의 개념 

  • 업무상 회사 내 타 부서 간 또는 외부 시스템 등과 데이터를 공유하기 위해 DB를 오픈하 는 경우가 발생한다. 

  • 이런 경우 전체 DB에 대해 오픈하는 경우는 거의 없고, 필요로 따라 제한적인 DB만을 오픈한다. 

  • 여기서 해당 DB에 접속하여 사용하는 대상자를 사용자라고 하며, 다수의 사용자가 접속하는 사용자를 사용자 그룹이라고 한다. 

  • 사용자 그룹은 동일한 권한과 제약을 가지는 사용자들이 공통으로 사용하는 계정이다. 

  • DBMS에서의 사용자 그룹의 관리는 역할기반 접근제어(RBAC: Role Based Access Control) 그룹 관리를 기반으로 한다. 

  • 사용자를 일일이 개별적으로 분할하지 않고 수행하는 역할을 기반 으로 그룹핑하여 먼저 분할하고 사용자 그룹에 권한을 부여한다. 

  • 그 다음 개별 사용자를 사용자그룹에 맞추어 배정하여 결국 사용자는 사용자 그룹에 속한 권한을 사용하게 한다. 

• 2. 역할(ROLE) 부여를 통한 사용자 그룹 관리 

  • 사용자 그룹 관리 시 해당 사용자 그룹에 다양한 권한을 부여할 수 있다. 

  • 데이터 질의어 (DQL: SELECT), 데이터 조작어(DML: INSERT, UPDATE, DELETE), 데이터 정의어(DDL: CREATE, ALTER, DROP) 모두 부여가 가능한데, 앞의 DQL 및 DML 뒤의 DDL은 분리하는 것이 일반적이다.

  • DQL과 DML에 제한적 권한을 부여하더라도 DDL 권한이 있는 경우 DDL을 통해 생성한 테이블에 대해 오너(Owner)가 되기 때문에 이 부분이 혼동되는 경우 가 많다. 

  • 또한 SQL 문장의 종류가 아주 많아서 일일이 설정하는 것이 매우 복잡하고 관리 하는 것도 어렵다.

  • 그렇기 때문에 일반 사용자 권한 개념으로 DQL, 중간 관리자 권한으로 DML까지 부여하고 DB관리자 권한 개념으로 DDL을 부여하는 방법이 사용된다. 

  • 역할 (ROLE)은 DB에서 사용자 그룹과 권한 사이에서 중개 역할을 하며, 이를 통해 빠르고 정 확하게 필요한 권한을 부여할 수 있게 한다

 

[2] 사용자 그룹 생성과 변경

• 1. 사용자 그룹 생성 

  • DBMS 제공 벤더에 따라 사용자 그룹 관리에 대해서는 SQL문 및 접근 관점이 많이 상이 하다. 

  • O사에서는 사용자 그룹 로그인을 통해 DBMS에 접근하여 사용한다. 

  • 아이디와 비밀 번호 방식으로 인스턴스 접속 후 그에 해당하는 권한을 받고 사용하는 것이다. 

  • S사는 인스턴스 접속을 위해 로그인이라는 것을 생성하고, 인스턴스 내 다수의 DBMS에 접근을 위 해 사용자 그룹을 생성한 후 로그인과 사용자 그룹을 매핑하는 방식으로 사용한다.

  • 사용자 그룹 생성을 위해서는 사용자 그룹 생성 권한이 있어야 하며, O사와 S사 SQL이 다르다. 

  • O사의 경우에는 사용자 그룹을 생성하고 난 다음, 사용자 그룹에 Session 생성 권 한도 별도로 부여해야 한다. 그 이후 해당 사용자 그룹으로 로그인이 가능하다

• 2. 사용자 그룹 변경 

  • 생성해 놓은 사용자 그룹을 삭제하거나 패스워드를 변경할 수 있다. 역시 O사와 S사가 각각 다르다

 

[3] 사용자 권한 부여 및 회수

• 1. 사용자 권한 부여 

  • 객체의 소유자는 객체에 대한 모든 권한을 가지며, 자신이 소유한 특정 권한을 GRANT문을 사 용하여 다른 사용자에게 부여할 수 있다. 부여하는 권한에 따라 아래와 같이 분류가 가능하다. 

    • (1) 데이터 질의어 

      • SELECT 권한을 아래 양식과 같이 타 사용자 그룹에 부여한다. 

      • 이때 USER_GROUP 대 신 PUBLIC을 선택하면 특정 사용자 그룹이 아닌 전체 사용자 그룹에 공통적으로 적용 이 가능하다. 

      • WITH GRANT OPTION의 경우 권한을 부여한 사용자 그룹이 또 다른 사용자 그룹에게 부여 받은 권한을 재부여할 수 있다는 의미이다.

      • 즉, 이 권한을 갖는 사 용자들이 체인 형식으로 증가할 수 있는 것이다. 

 

• 2. 사용자 권한 회수 

  • 사용자에게 부여된 권한을 다시 회수할 수 있다. 권한을 부여하는 것과 반대 개념이며, 사 용하는 방법도 유사하다. 

  • REVOKE를 사용하여 회수하며, TO가 아닌 FROM으로 사용자 그룹을 선택한다. 아래와 같이 사용한다.

 

[연습문제]

• GROUP BY 구문에 대한 설명이 잘못된 것은?

  • (1) NULL값을 가지는 ROW는 제외한 후 산출한다. 

  • (2) SELECT에서 사용하는 것과 같은 ALIAS 사용이 가능하다. 

  • (3) WHERE 구문 안에 포함되지 않는다. 

  • (4) WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 한다

 

• 다음 중 집계 함수의 종류가 아닌 것은?

  • (1) SUM()

  • (2) COUNTA()

  • (3) STDDEV()

  • (4) AVG() 

 

 참고 문헌

[논문]

• 없음

[보고서]

• 없음

[URL]

• 없음

 

 문의사항

[기상학/프로그래밍 언어]

• sangho.lee.1990@gmail.com

[해양학/천문학/빅데이터]

• saimang0804@gmail.com

 

 

 

 

 

 

 

 

 

 

 

본 블로그는 파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음