database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Tutorials 24113 게시물 읽기
No. 24113
[Tip]과연 쿼리의 끝은 어디인가??
작성자
이경환(babocom)
작성일
2005-07-23 13:36ⓒ
2005-10-12 11:20ⓜ
조회수
37,729

# 몇가지 어려운(?) 쿼리들입니다...

# 꼼꼼이 들여다보시면 쿼리 이해에 많은 도움이 될것입니다.

# 앞으로 지속적으로 추가할 예정입니다... 추후 모아서 상,중,하로 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 의 원리를 안다면 금방 이해할 수 있음... 인덱스와 함께 사용하면 효과적일듯..

이 글에 대한 댓글이 총 7건 있습니다.

 

oracle에서 (아마 9부터인가?) 누적 관련 함수를 제공합니다.

기존에 복잡하게 처리하던 것들을 쉽게 처리할 수 있지요.

장홍창(changaya)님이 2005-07-25 16:28에 작성한 댓글입니다.

누적합계 2

 

크로스 조인을 이용한 누적합계

 

select a.v1, a.v2, b.v1, b.v2
from tblx a cross join tblx b
 where b.v1 <= a.v1
order by a.v1, b.v2

 

select a.v2,  b.v2
from tblx a cross join tblx b
 where b.v1 <= a.v1
order by a.v1, b.v2

 

select a.v2,  sum(b.v2)
from tblx a cross join tblx b
 where b.v1 <= a.v1
group by a.v2
order by min(a.v1)

석이님이 2005-07-25 21:01에 작성한 댓글입니다. Edit

cross join 이 뭔지 정확히 모르겠네요...

inner join 은 교집합이고.. 

outer join 은 outer 테이블의 행만큼 나오죠..  (없을땐 null 로..)

cross join 은 카티션 곱(cartesian product)인가요??  100x10=1000  ???

-----------------------------------------------------------

DB2 의 SQL 설명을 보니 역시 카티션곱 인가 보네요...

그렇다면 엄청난 과부하가 생길텐데..  (있으나마나한...)

혹시 MSSQL 의 cross join 은 좀 다른 의미인가요??

이경환(babocom)님이 2005-07-25 21:23에 작성한 댓글입니다.
이 댓글은 2005-07-25 23:18에 마지막으로 수정되었습니다.

재미있는 시도입니다. ^^;

 

앞으로 더욱더 기발하고 도움이 되는 Query 들이 나왔으면 생각해 봅니다. ^^;

정재익(advance)님이 2005-07-25 21:38에 작성한 댓글입니다.

누적 합계 - 3

( Analytic Funtion - Window Aggregate Family 참고 )

 

SELECT id,
 SUM(qty) OVER ( ORDER BY id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as acc
FROM test

오성균(triad77)님이 2006-04-28 13:06에 작성한 댓글입니다.
이 댓글은 2006-04-28 13:33에 마지막으로 수정되었습니다.

그룹 내 Rank 구하기 - 2

( Analytic Function - Ranking Family 참고 )

 

SELECT  group_id, article_id, count_sum,
RANK() OVER (PARTITION BY group_id ORDER BY article_id ) AS 그룹내순위,
RANK() OVER (ORDER BY article_id ) AS 전체순위
FROM a_table

 

* 전체 순위 기준을 article_id로 가정함

오성균(triad77)님이 2006-04-28 13:40에 작성한 댓글입니다.
이 댓글은 2006-04-28 13:43에 마지막으로 수정되었습니다.

group 내 rank 구하기 sql 문이 참 흥미로와서

pgsql 에서 테스트 해 봤습니다.

(mysql 은 아니지만 일반 쿼리문이야.. 에러가 없다면 결과는 같으리라 믿습니다.)


근데..그것이 문제가 있습니다.


상위 2 개는 이상없이 나오는거 같으니..

상위 3 개 또는 아예 having 절을 없애면...결과가 이상하다는걸 알 수 있습니다.



tmp=> select a.group_id,

tmp->        max(a.article_id) as article_id,

tmp->        a.count_sum, count(a.group_id) as rank

tmp-> from a_table a left outer join a_table b

tmp->   on (a.group_id = b.group_id and a.count_sum <= b.count_sum)

tmp-> group by a.article_id, a.count_sum, a.group_id

tmp-> --  having count(a.group_id) <= 3

tmp-> 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

        1 |         44 |         1 |    4

        1 |          3 |         1 |    4

        2 |          8 |         4 |    1

        2 |          9 |         3 |    2

        2 |         15 |         2 |    3

        2 |          2 |         1 |    4

        3 |          1 |         3 |    1

        3 |          6 |         2 |    2

(10 rows)


쿼리문과 데이타는 그대로 사용하였으며, having 절만 주석 처리 했습니다.

group id 가 1 인 것에 rank 가 3 이 없고 4 가 두개 있습니다.

tyro님이 2007-05-05 21:02에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
25899[옮김] MySQL 서버 최적화 참고 문서
문태준
2006-09-30
61155
25898[옮김] MySQL DataBase 서버 튜닝 - MySQL Administrator
문태준
2006-09-30
62070
25897[옮김] MySQL DataBase 서버 튜닝 - Connection과 Memory
문태준
2006-09-30
21101
24113[Tip]과연 쿼리의 끝은 어디인가?? [7]
이경환
2005-07-23
37729
23619결과를 가로로 출력하여 보여주기(Pivot) [2]
이경환
2005-04-22
37803
23534SAP DB용 Python Client
남녀평등
2002-12-04
27984
23533SAPDB + JSP(PHP)를 이용한 간단한 페이징 팁
신정호
2002-12-04
18787
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다