한번에 소계와 합계 평균을 구해야 할 때가 있습니다.
Oracle에서는 ROLLUP 함수가 있으니 쉽게 구할수 있으나, Sqlite나 예전...Oracle에서 ROLLUP 함수가 없는 시절 DECODE를 사용했더랍니다. (진짜 20년전 얘기 입니다.)
하지만 Sqlite에서는 ROLLUP이란 함수 자체가 없으니 CASE WHEN문을 이용하여 구해 보도록 합니다.
만약 Oracle이었다면 CASE WHEN문 부분을 DECODE로 치환해서 사용하면 됩니다.
목적
둘리와 길동이의 수학과 국어 성적을 갖는 exam_score 테이블이 있습니다.
둘리와 길동의 합계와 소계, 평균을 각각 한번에 구해서 넣고 싶습니다. 최종 원하는 결과는 아래와 같습니다.
사람별로 소계가 존재하고 마지막에 합계가 존재합니다.
데이터 준비
일단 필요한 테이블과 data를 넣습니다.
CREATE TABLE exam_score(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
subject INTEGER,
score INTEGER
);
insert into exam_score (name, subject, score) values ('둘리', '수학', '100');
insert into exam_score (name, subject, score) values ('둘리', '국어', '30');
insert into exam_score (name, subject, score) values ('길동이', '수학', '60');
insert into exam_score (name, subject, score) values ('길동이', '국어', '70');그리고 복제용도로 사용할 dummy table 인 copy_t 테이블을 하나 만듭니다.
CREATE TABLE copy_t(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
id2 INTEGER
);
insert into copy_t (id2) values ('1');
insert into copy_t (id2) values ('2');
insert into copy_t (id2) values ('3');
insert into copy_t (id2) values ('4');
insert into copy_t (id2) values ('5');
insert into copy_t (id2) values ('6');
insert into copy_t (id2) values ('7');
insert into copy_t (id2) values ('8');
insert into copy_t (id2) values ('9');
insert into copy_t (id2) values ('10');
생성된 결과는 위와 같습니다.
copy_t table의 id2 컬럼은 여기서는 사용하진 않습니다.
소계 구하기
최종 원하는 결과는 위의 테이블 입니다.
기존 결과에 사람별로 소계를 보여주는 row가 추가되었습니다.
여기서 key되는 작업은 소계를 구하기 위해서는 cartesian product를 이용하여 데이터 row를 복제 시킵니다.
두배로 row를 복제 시킨 후에 하나는 그대로 표시하고, 하나는 합쳐서(sum) 소계용으로 씁니다.
먼저 copy_t table을 이용하여 row를 두배로 복제 시킵니다.
select a.*, b._id as copy_t_id
from exam_score a, copy_t b
where b._id < 3;
copy_t table의 row 개수를 2개로 제한 했으니, exam_core 테이블에 있던 4개의 row x copy_t 테이블의 2개의 row로 인하여 모든 경우에 수가 표현되어 총 8개의 row로 복제 됩니다.
여기서 copy_t_id가 '1' 인 row는 그대로 표기하고, '2'인 row는 소계용으로 사용하겠습니다.
CASE WHEN 문을 이용하여 copy_t_id=1 이면 그대로 subject를 표기, 아니면 '소계'로 문구를 바꿉니다.
select a.name,
case when b._id =1 then a.subject else '소계' end as subject
from exam_score a, copy_t b
where b._id < 3;
이제 group by 잘 묶습니다.
select a.name,
case when b._id =1 then a.subject else '소계' end as subject,
sum(a.score) as score,
avg(a.score) as average
from exam_score a, copy_t b
where b._id < 3
group by a.name, case when b._id =1 then a.subject else '소계' end;group by에서 기준값은 name + subject 입니다.
단 이때 subject는 copy_t_id가 1인경우 subject 값을 그대로 남고 2인경우 '소계'가 되기 때문에 이 소계를 한꺼번에 묶입니다.
잘 나오긴 했는데, 정렬이 필요하네요.
subject의 원래 값 앞에 공백 ' '을 추가하여 기본 정렬순서를 높여 주어 원하는대로 정렬되도록 유도해 줍니다.
select a.name,
case when b._id =1 then ' ' || a.subject else '소계' end as subject,
sum(a.score) as score,
avg(a.score) as average
from exam_score a, copy_t b
where b._id < 3
group by a.name, case when b._id =1 then ' ' || a.subject else '소계' end;
소계가 마지막에 잘 정렬되서 나오네요.
합계 구하기
이제 총계를 포함할 차례입니다.
예상한것처럼 이번에는 기존 row를 세배로 복제 시킵니다.
그리고 하나는 그냥 보여주고, 나머지 하나로는 소계를 만들고, 나머지 하나로는 총합계를 만듭니다.
select
case when b._id=3 then '합계' else a.name end as name,
case when b._id =1 then ' ' || a.subject
when b._id = 2 then '소계' else '합계'
end as subject,
sum(a.score) as score,
avg(a.score) as average
from exam_score a, copy_t b
where b._id < 4
group by case when b._id = 3 then '합계' else a.name end,
case when b._id =1 then ' ' || a.subject
when b._id = 2 then '소계' else '합계' end;
소계와 합계가 예쁘게 잘 출력되었습니다.
Sqllite의 CASE WHEN문을 이용하니 코드가 복잡해 보이지만 사실 key point는 cartesian product로 데이터를 복제해서 사용하는 부분입니다. 물론 group by로 잘 묶어주는것도 필요합니다.
Oracle이라면 DECODE를 사용하여 좀더 가독성 있는 SQL를 만들수 있으며, 사실 ROLLUP을 쓰면 더 편하게 원하는 결과를 얻을 수 있습니다.
