|
# 몇가지 어려운(?) 쿼리들입니다...
# 꼼꼼이 들여다보시면 쿼리 이해에 많은 도움이 될것입니다.
# 앞으로 지속적으로 추가할 예정입니다... 추후 모아서 상,중,하로
summary 할 예정
# 물론 기본적으로 서브쿼리가 되는 버전이어야 합니다.
# 가급적 ANSI-SQL 로 가능한 것들만 정리하겠습니다.
1. 누적합계 구하기
create table test (id char(10), qty int);
insert into test values ('a', 10);
insert into test values ('b', 11);
insert into test values ('c', 18);
insert into test values ('d', 9);
< subselect 사용 : 난이도 하 >
select A.id,A.qty
,(select sum(B.qty) from test B where B.id<=A.id) as acc
from test A
< self join 사용 : 난이도 중 >
select A.id,A.qty,sum(B.qty) as acc
from test A, test B
where A.id>=B.id
group by A.id
>> DB 에서 누적을 구하는 함수를 제공해주면
좋을텐데..(현재까지는 지원하는 DB 가 없는듯함.. 세션변수를 사용하면
구현이 어렵진 않을텐데..) 쿼리로 만드는건 데이타 양이 많아질수록
기하급수적으로 부하가 증가하므로 양이 적을경우 외에는 쓸모가 없는
쿼리라고 사료됨..
2. 결과 회전(pivot) 하기
CREATE TABLE
Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
< 난이도 중 >
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
>> 최근 MSSQL 에서 피봇 프로시져를 제공하는데 그것도
내부를 파헤쳐보면 이상과 같은 방식임.. 오라클과 같이 rownum 을
지원하는 경우 rownum 을 mod 연산하여 group by 하는 방식도
있음..
3. overlap 처리(예약 시스템 등에서...)
create table test (name char(10), start int, end int);
insert into test values ('홍길동', 1000, 1500);
insert into test values ('홍길동', 1100, 1700);
insert into test values ('홍길동', 1800, 1900);
insert into test values ('강감찬', 0900, 1000);
insert into test values ('이순신', 1000, 1400);
< 난이도 상 >
select P.name, min(P.start) start, P.end
from (
select x.name, x.start, (
select max(y.end)
from test y
where x.name = y.name
and x.end between y.start and y.end
) end
from test x
) P
group by P.name, P.end
>> 상당히 난이도 있는 쿼리임.. 인덱스를 적절히 사용하면
효과가 있을듯...
4. 그룹 내에서의 rank 구하기... (상위 2개)
create table a_table
(
group_id int
, article_id int
, count_sum int
)
insert into a_table values (1,44,1)
insert into a_table values (1,3,1)
insert into a_table values (1,5,4)
insert into a_table values (1,7,2)
insert into a_table values (2,2,1)
insert into a_table values (2,8,4)
insert into a_table values (2,15,2)
insert into a_table values (2,9,3)
insert into a_table values (3,1,3)
insert into a_table values (3,6,2)
select * from a_table
group_id article_id count_sum
----------- ----------- -----------
1 44 1
1 3 1
1 5 4
1 7 2
2 2 1
2 8 4
2 15 2
2 9 3
3 1 3
3 6 2
<난이도 중상>
select a.group_id, max(a.article_id) article_id, a.count_sum,
count(a.group_id) rank
from a_table a left outer join a_table b
on (a.group_id = b.group_id and a.count_sum < =
b.count_sum)
group by a.article_id, a.count_sum, a.group_id
having count(a.group_id) <= 2
order by a.group_id asc, a.count_sum desc
group_id article_id count_sum rank
----------- ----------- ----------- -----------
1 5 4 1
1 7 2 2
2 8 4 1
2 9 3 2
3 1 3 1
3 6 2 2
>> outer join 의 원리를 안다면 금방 이해할 수 있음...
인덱스와 함께 사용하면 효과적일듯..
|
