본 게시글은
서울대학교 데이터사이언스대학원 이상원 교수님의
데이터사이언스 응용을 위한 빅데이터 및 지식기반시스템 강의를
학습을 목적으로 재구성하였습니다
이번 시간에는 Assertion과 Trigger에 대해서 잠깐 살펴보고
그 이후에는 SQL에서의 한계를 극복하기 위한
Data Cube와 Pivoting and Cross-Tab과 같은
advanced SQL에 대해서 배워본다
좀 더 복잡한 Integrity Constraint에 대해 알아보자
지금까지 우리는 primary key, foreign key를 배웠는데
이번에는 general constraint를 배워보자
General Constraint 에서
table을 만들 때 CHECK로 column의 데이터에 제약조건을 줄 수 있다
위 예시에서
CHECK (rating >= 1 AND rating <= 10) 의 경우
Sailors 테이블에서 rating은 1이상이거나 10이하여만 한다
해당 table에서 각 tuple들의 column 값이
이렇게 되어야한다고 정의해주는 것이다
같은 table 내에서 rating은 age보다 커야한다와 같이
column끼리의 값 비교도 가능하다
밑의 예시를 보면 Reserves 테이블에서
Boats 테이블을 subquery로 가져와서
bid가 같은 것들 중에서 name이
'Interlake'가 있으면 안된다고 정의해준 것이다
이렇게 다른 테이블과 상호 관련을 지어서
check도 가능하다
위의 예시를 한 번 더 보자
Sailors table을 생성할 때 CHECK로 데이터를 제한한다
그런데 잘 살펴보면
Sailors의 개수와 Boats의 개수의 합이 100이 넘으면
더이상 insert를 못하게 제약을 걸어주었다
하지만 이렇게 하면 치명적인 단점이 있는데
이 check문은 결국 Sailors에 걸린 check문이라
sailors table이 변화할때만 해당 구문이 작동한다
한마디로 Boat 테이블이 변화할 때는 check문이 작동하지 않고
그래서 check에 위배되는 상황이 발생할 수 있다
그래서 어느 한쪽 table의 constraint로
어떤 규칙을 specify하는 것이 좋은 방법은 아니다
따라서 이러한 문제를 해결하기 위해서 나온게
Assertion이다
Assertion은 table을 create할 때 만드는게 아니고
별도로 따로 생성해주는 것이다
이제 Trigger와 Active Database에 대해서 알아보자
trigger 기능이 있으면 우리는 그걸 active database라고 부른다
우리가 지금까지 배운 DB는 passive database의 개념에 가까운데
그 이유는 그냥 DBMS가 우리가 수행하라는대로만 하고
스스로 어떤 action을 취하지는 않았기 때문이다
active 하다는 것은 어떤 event가 발생했을 때
DBMS가 무언가를 한다는 의미이다
예를들어서 우리가 CASCADE를 지정해주면
우리가 딱히 child table을 건드리지않고
parent table만 건드려도
child table의 row들까지 자동으로 삭제된다
Trigger는 이와 유사한 것인데
CASCADE는 모든 DBMS에 정의되어있는 semantic이라면
Trigger는 사용자가 필요한 기능을
DBMS에 define해서 등록해서 사용하게 하는 것을 말한다
Trigger에는 ECA Rule이라는 것이 있는데
Event가 발생하고
Condition이 만족하면
Action을 취하라
라고 한다
위 ppt에서 쿼리의 예시를 잘 살펴보자
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N WHERE N.age <= 18
youngSailorUpdate라는 Trigger를 새로 생성해주는데
Sailors라는 테이블에 새롭게 insertion이 생기면
그걸 새로운 NewSailors라고 정의를하는데
For Each Statement -> SQL이 실행될 때 한 번만
YoungSailors 라는 테이블에
age <=18인 것만 insert를 하라는 의미이다
gpt가 알려준 예시에서
INSERT INTO Sailors (sid, name, age, rating) VALUES
(1, 'Tom', 20, 3),
(2, 'Jerry', 17, 4),
(3, 'Anna', 16, 5);
이런 쿼리를 실행시키면
이런 결과가 나온다고한다
위 예시에서는 for each statement를 썼는데
이건 아무리 많은 row를 같이 insert해도
쿼리가 실행될 때 한 번만 실행되게 하는거고
for each row로 하면
insertion되는 row의 개수에 따라 trigger가 실행된다
trigger를 사용하면 뭐 이렇게 많은 것들을 할 수 있다고 한다
지금까지 배운 5장 내용에 대한 요약이다
우선 SQL에 대해서 배웠고
기본적인 SQL의 문법에 대해 배웠다
그리고 SQL을 실행하면 DBMS 내부에서 수행하는 연산인
Relational Algebra에 대해서 배웠고
이 관계 대수에는 기본 연산이 있고
기본연산을 조합해서 연산하는 다른 추가적인 연산들도 존재했다
그다음 여러 가지 쿼리를
효율적으로 수행하는 법도 배웠으며
NULL이 포함될 때의 쿼리 연산
그 다음 오늘 배웠던
CHECK와 ASSERTION을 통한 general constraint
마지막으로 trigger까지 배웠다
SQL과 Python의 Pandas를 비교해보자
얼핏보면 RDB이나 pandas의 dataframe이나 비슷해보일수있지만
뭐.. 당연히 두 가지는 활용도나 완성도 측면에서 다르며
잘 사용해야한다구한다 ^-^
이제부터 Data Cube와 Pivot에 대해서 알아보도록하자
우리가 relation을 흔히 2차원이라고 하지만
정말 사실상 1차원이라고 한다
엄밀하게는 그냥 tuple들의 set이기 때문에
우리가 생각하는 table은 1차원의 개념인데
이러한 table을 2, 3차원으로 만들면 통계작업을
더욱 편리하게 수행할 수 있다
따라서 통계 연산을 빠르게 하기 위해
1차원의 Table을 2차원 혹은 3차원으로 변경하는데
2차원으로 만들면 pivot
3차원으로 만들면 data cube의 개념이다
excel에서 나오는 pivot table의
pivot과 동일한 개념이다
교수님께서 이런 excel의 table을 예시로 설명해주셨는데
위 excel은 우리가 흔히 보는 1차원의 relation이다
이를 excel의 Pivot table 기능을 사용하면
2차원의 table 형태로 변경할 수 있다
예를 들면
row에는 Year을 column에는 Quarter라는 기준을 두고
2차원의 테이블로 재정렬을 하면
각 year별 Quarter를 한 눈에 알 수 있고
year별 Quarter를 이용하는 연산,
예를 들면 1990년의 총 Quarter의 합은?
과 같은 질문들을 손쉽게 해결할 수 있다
이게 2차원의 Table로 만드는 pivot & cross-tab이라는 개념이고
이를 3차원으로 만들면 data cube가 되는 것이다
위 예시를 보며 CrossTab과 Data Cube의 개념을
제대로 살펴보자
가장 처음은 단순 Aggregate이다
그냥 합계만 있는 상태이다
이건 우리가 앞에서 배운 SQL로 쉽게 구할 수 있다
SELECT SUM(amount) FROM sales;
위 SQL은 단순 예시이다
아무튼 저런식으로 SUM 함수를 써주면
합계는 간단하게 구할 수 있다
그다음 두 번째는 Group By 연산을 시킨 것이다
color라는 attribute로 group by 연산을 시킨 후
모든 색의 sum을 구하였다
SELECT color, SUM(amount)
FROM sales
GROUP BY color;
이도 우리가 이전에 배운 SQL로
간단하게 구할 수 있다
여기까지는 그냥 1개의 속성을 기준으로 정렬하고
통계적 연산을 하는 1차원 table의 구조이다
그럼 이제 세 번째 Cross Tab을 보자
속성을 2개를 이용해서 정렬을 했다
row는 Color을 기준으로 정렬했고
column은 make(제조사)를 기준으로 정렬했다
그런 다음 각각의 기준별로 sum과 같은 aggregate function을 수행했다
excel의 pivot table과 매우 유사한 개념이다
이를 SQL로 하면
SELECT make, color, SUM(amount)
FROM sales
GROUP BY make, color;
이렇게 나타낼 수 있다
마지막은 3차원의 Data Cube이다
차원이 3개 이상일 때를 Data Cube라고 부른다
위 ppt를 보면
한 면은 Make & Year
한 면은 Color & Year
한 면은 Make & Color로
데이터들을 정렬한 것을 알 수 있다
이런 Data Cube에서는
각 면, 선, 꼭짓점은 각각의 특징을 지니는데
Make & Year의 면을 예시로 든다면
한 면은 Make와 Year의 총합을,
한 선은 한 개의 속성(Make)별 총합을,
마지막 꼭짓점은 모든 조건 없는 전체 합계를 나타낸다
여기서 사실 살짝 궁금한게 생겼다
그럼 결국 그냥 1차원의 table에서 하는 통계 연산도
2차읜의 crosstab에서 하는 통계 연산도
3차원의 Data Cube에서 하는 통계 연산도
사실 그냥 우리가 이전에 배운 SQL로 다 실행할 수 있는 것들이지않나?
그래서 내가 궁금한건
crosstab이나 datacube가 그냥
단순 2차원, 3차원으로 정렬하면
aggregate function을 수행할 수 있다는 개념적인 정의인건지
아니면 이런 통계를 더 빠르게 수행하기 위해서
물리적으로 DBMS 내부에서 2차원 혹은 3차원 이상의 구조를 생성해서
해당 통계연산이 들어오면 더 빠르게 수행해준다는것인지?
그게 궁금해져서 요놈은 GPT에게 질문하였다
그렇다고한다,,
gpt의 답변을 요약하자면
cross-tab과 data cube는 기본적으로
어떤 "개념적 정의"가 맞다고 한다
데이터를 다양한 차원에서 분석할 수 있는
OLAP를 가능하게 해주는 어떤 개념적인 정의가 맞으나
일부 DBMS에서는 이런 개념적 정의를 바탕으로
연산을 더 빠르게 하기 위해서
최적화된 작업을 하는 DBMS도 존재한다고한다
즉, 개념적 정의를 이용해서 물리적으로 내부에서 어떤 구조를 만들어
해당 개념과 관련된 연산을 더 빠르게 수행하도록 하기도 하는 것이다
위예서 예시를 드는
Microsoft, Apache Kylin과 같은 DBMS에서는
이런 data cube를 materialized view와 같이
물리적 table로 생성하기도 한다고 한다
이렇다고 한다
내가 궁금했던 점을 정확하게 풀어줬다
옛날에 GPT없이 어떻게 코딩하고
어떻게 공부했는지 기억이 안나는 수준이다
이제 Rollup의 개념을 알아보자
Rollup은 Group By의 확장같은 개념으로
계층적으로 group을 묶어서 부분 집계로 전체 집계를
모두 수행해주는 기능이다
위 쿼리에서 예시를 보자
GROUP BY ROLLUP(deptno, job);를 해줬다
이렇게 제시해주면 grouping을 하는 순서는
1. deptno
2. job
3. deptno & job 합계
와 같다
그래서 결과를 보면
deptno10에있는 각각 clerk, manager, president의
salary의 합이 있고
그 아래에는 deptno10의 전체 각 직무별 합을 더한 값이 제시되어있다
아래 row로 가면 deptno20, deptno30에 관한 계산도 되어있고
가장 마지막에는 총합도 있다
Rollup은 2차원의 데이터 내부에서 수행했다면
Cube는 3차원 이상에서
모든 방향으로 연산을 수행하는 것이다
위 예시를 보면 1차원 table을 data cube로 만들면
첫 번째 row는 Model, Year, Color에 대한 합계
두 번째와 세 번째 row는 Model을 기준으로 chevy, ford에 대한 합계
그 다음은 Year을 기준으로 각 연도별 합계
그 다음은 다시 색상별 합계를 계산한다음
이제 Model + Year의 조합에 대한 합계
Model + Color 조합에 대한 합계
Year + Color 조합에 대한 합계까지 수행하는 것을 볼 수 있다
따라서 각 차원에 대해서 CUBE를 실행하면 결국
차원 n개에 대해서 조합의 개수는 2의 n제곱이 나오게 된다
그렇다면 지금까지 배운 Rollup이
내부적으로 어떻게 쿼리를 수행하는지를 살펴보자
우리가 위에서
GROUP BY ROLLUP(deptno, job)에 대해서 수행했는데
이 쿼리를 받으면 DBMS는 내부에서
딱 위의 3가지 과정을 거쳐서 연산을 수행한다
우선 처음으로는 deptno와 job를 groupby해서
deptno 별 job의 총 합계를 계산한다
그 다음은 deptno를 기준으로 groupby를 해서
deptno별 총 합계를 구한다
여기서 NULL은 job이 된다
마지막으로는 deptno + job의 총 합계를 구하는데
deptno도 NULL, job도 NULL이 된다
이 3가지 연산을 다 합집합하면
우리가 위에서 봤던 Rollup의 결과가 나오는 것이다
한마디로 요약하면 부분합 -> 전체합으로 확장되는
group by의 확장개념이다
그럼 이제 같은 예시인데 Rollup 대신 Cube를 실행한걸 알아보자
앞에서 Cube는 3차원 이상의 차원에 대해서 수행하는거라고 했지만
3차원 이상에서 어떤 통계 연산을 수행하려면
Cube로만 수행해야 가능하다는 것이지
Cube가 반드시 3차원 이상만 가능하다는 것은 아니다
Rollup은 단순하계 계층적으로 연산을 수행하지만
Cube는 모든 차원의 조합에 대해서 연산을 수행하는 것이기 때문이다
따라서 위의 CUBE(deptno, job)같은 경우는
(deptno, job), (deptno), (job), (총합계)
와 같이 4개 조합의 집계 연산을 수행한다.
위에서 CUBE는 n개의 차원에 대해서
2의 n제곱의 조합이 나온다고 했으므로
위의 경우도 따라서 2의 2제곱인 4개의 조합이 나오는 것이다
따라서 위 쿼리 수행 결과를 보면
가장 처음에는 그냥 단순 총합계(29025)가 있고
다음 row부터는 deptno없이 job별로 sum
그 다음 row에는 deptno 10의 SUM과
deptno별 job의 sum이 차례로 나와있다
따라서 여기서
rollup은 일방향적인 연산이라면
(계층적으로 수행하니깐)
cube는 모든 조합에 대해서 다 연산하므로
양방향 연산이 된다
이제 grouping function에 대해서 알아보자
위 쿼리에서 보면 SELECT문에 grouping(job)이 있는 것을 볼 수 있다
이는 어떤 column이 NULL일 때
이게 진짜 NULL인지 아니면
cube로 만들어진 통계를 위한 row라 NULL이 된건지를
구분할 수 있게 해주는 function이다
따라서 만약 해당 row가 통계행이면 grouping function은 1이
그렇지 않으면 0이 나온다
따라서 위의 예시를 살펴보면
grouping(job)를 T1으로 나타내게 해줬는데
각 row에서 job이 NULL일 경우에
진짜로 job이 NULL이라서 NULL이 된거라면 0이
그렇지 않고 해당 row가 통계 row라서 job이 NULL이 된것이라면 1이 된다
그래서 가장 위의 row에는 DEPTNO과 JOB이 둘다 NULL인데
이는 cube로 만들어진 총 합계의 row이므로
T1은 1이 된다
그 아래는 job field가 NULL이 아니기 때문에 T1은 0이 되고
그 아래에 job field가 NULL이 되었는데
이도 진짜 job이 NULL이어서가 아닌
deptno 10의 통계에 의한 행이기 때문에 T1은 1이 된다
다음으로는 pivot과 unpivot의 개념을 배워보자
앞에서도 계속 말했듯이
excel의 pivot table과 유사한 개념이다
가운데에 있는 table이 우리가 흔히 말하는
1차원의 relation이다
데이터가 증가할수록 세로로 증가하기 때문에
이를 narrow table이라고도 한다
이를 Months를 row로 변경하면
(pivot by month)
위와 같은 테이블이 되는데
이러한 테이블을
Wide Table of Months라고 부른다
왜냐하면 Months의 개수가 증가할수록 가로로 넓어지기 때문이다
이를 다시 unpivoting을 하면
narrow table로 돌아가게 되고
이를 Years를 row로 변경하면
아래와 같이 Wide Table of Years가 된다
즉, unpivot -> pivot은 narrow table -> wide table이고
pivot -> unpivot은 wide table -> narrow table이다
위는 2005년 기준 MS SQL에서
pivot과 unpivot을 수행하는 쿼리이다
Pivoting을 수행하는 쿼리를 보면
FROM (SalesTable PIVOT (Sales for Month IN 'Jan', 'Feb', 'Mar')
와 같이 되어있다
위 쿼리를 구문별로 해석하면
원래 1차원의 narrow table인 SalesTable에
pivot을 수행해주는데
month라는 column에서 'Jan', 'Feb', 'Mar'을
row로 확장해주고
각 값들은 Sales로 해달라는 뜻이다
따라서 위 쿼리를 수행하면
Wide Table of Months와 같이
엑셀에서 피벗테이블을 수행한것과 동일한 테이블의 형태가
쿼리 결과로 반환된다
그렇다면 pivot된 테이블을 unpivot을 하는 쿼리도 당연히 존재한다
아래의 Year에 대해 pivot한 테이블의 이름이 SalesReport라고 하면
현재 pivot 테이블은 Year인 2001, 2002, 2003이 row로
Months인 Jan, Feb, Mar이 column으로 구성이 되어있다
따라서 SalesReport라는 테이블을 UNPIVOT 시키는데
Months인 Jan, Feb, Mar을
column에서 다시 row로 변경해달라는 것이
위의 Un-pivoting SQL의 의미가 된다
해당 쿼리를 실행하면 아래의 SalesReport 테이블에서
Jan, Feb, Mar이 다시 row로 들어간
위의 narrow table이 된다
그렇다면 이런 쿼리를 수행하면
물리적으로 Pivot table이 저장되는 것인가?
그렇지는 않다고 한다
그냥 pivoting 시킨 것을 결과로 뱉기만 하는
일시적인 view와 같은 개념이지
해당 테이블을 물리적으로 저장하려면
SELECT *
INTO PivotSales
FROM (
SELECT year, month, sales
FROM Sales
) AS SourceTable
PIVOT (
SUM(sales) FOR month IN ([Jan], [Feb], [Mar])
) AS PivotTable;
위와 같은 쿼리를 수행해주면 된다고한다
pivot + cross-tab에 대해서 살펴보자
위 쿼리는 테이블에서 job과 deptno을 기준으로 group by를 수행하고
각각의 count를 계산하는 쿼리이다
위 쿼리를 수행해서 나온 결과인 왼쪽 table을 바탕으로
오른쪽과 같이 cross-tab(교차표)을 만들고싶다
그렇다면 이전 ppt slide의 쿼리 결과를 바탕으로
pivot 쿼리를 수행해 cross-tab을 생성해보자
SELECT에서 각각의 deptno과 job의 조합에 대해서
count를 column으로 만들어준다
그런 다음 job를 기준으로 group by를 수행해준다
그렇게 하면 결과가 저렇게 cross-tab의 형태로 나온다고한다
마지막으로 Oracle에서 해당 쿼리를
수행하는 법을 알아보고 이번 수업을 마무리하도록 하자
이전 slide에서 봤던
SELECT
job, deptno, count(*) cnt
FROM emp
GROUP BY job, deptno
이 쿼리아 FROM에 subquery로 들어가있다
그 다음 주의깊게 봐야할 것은 SELECT문인데
각각 max와 decode로 감싸져있다
decode부터 차근차근 살펴보자
decode는 oracle에서 if문과 동일한 개념이다
따라서 decode(deptno, 10, cnt, null)이라고 한다면
deptno이 10이면 cnt를 출력하고
만약 그렇지 않으면 null을 출력하라는 의미이다
그 다음 max로 감싸주는 이유는
왼쪽은 위 subquery의 결과인데
(CLERK, DEPT_10) -> 1
(CLERK, DEPT_20) -> 2
(CLERK, DEPT_30) -> 1
이게 각각 다른 row로 나오게 된다
우리는 Job별로 한개의 row만 나오면 좋겠으므로
job을 기준으로 group by를 수행하는데
이렇게 되어있는 것에서
MAX를 취해주면 CLERK, dept_10에서 가장 큰 값
(NULL이 아닌 값)만 출력하게 되는 것이다
그래서 각각을 MAX로 감싸주면
우리가 생각한 cross-tab의 형태가 나오게 되는 것이다
이번 수업은 제대로 이해하려니
시간이 약간 많이 걸렸던 수업이었던 것 같다
복습을 제대로 해줘야겠다..
그럼 이번 수업 정리는 여기서 마무리-!