데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MySQL Q&A 27618 게시물 읽기

No. 27618

이런경우 조인쿼리를 어떻게 해야 하나요?
작성자	김수정	작성일	2008-06-23 13:17ⓒ 2008-06-23 17:23ⓜ	조회수	4,576

table A는 운전자정보, table B는 위치로그 데이터를 각각 저장합니다.

A에 2개의 운전자가 등록이 되어 있고요.

drvid	drvname
1	홍길동
2	하나

B에는 5개의 위치로그 데이터가 저장되어 있습니다.

단, 날짜데이터는 운전자별로 같은날 업데이트되지 않았을 수도 있어요.

logid	drvid	x	y	date
1	1	12	12	2008-06-21
2	2	22	33	2008-06-21
3	2	45	33	2008-06-22
4	1	34	23	2008-06-23
5	2	24	4	2008-06-23

이런경우 가장 최신의 로그 데이터만 운전자별로 데이터를 뽑으려면 쿼리를 어떻게 해야 하나요?

원하는 결과는

drvid	drvname	logid	x	y	date
1	홍길동	4	34	23	2008-06-23
2	하나	5	24	4	2008-06-23

그냥 left join하니까 이렇게 안 나오던데요..

답변 꼭 해주세요..;;

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

좀 복잡하긴 하지만 가능합니다.

먼저 date 컬럼 값에 음수를 갖는 int 컬럼을 추가 해야 합니다.
- alter table tbl_B add column rvs_date int;

그리고 테스트를 위하여 위에 생성한 컬럼으로 인덱스를 생성합니다.
(실제 사용되는 조건에 따라 인덱스는 달리 해야 겠지만 복합키 등에서도 힌트를 사용하여 항상 rvs_date 컬럼이 존재하는 인덱스에 사용이 되어야 합니다.)
- alter table tbl_B add index index_1 (rvs_date);

다음과 같이 쿼리를 작성하면 원하는 데이터를 얻을수 있습니다.

select straight_join a.drvid, a.drvname, b.logid, b.x, b.y, b.date
from tbl_B as b use index (index_1), tbl_A as a
where a.drvid = b.drvid
and b.rvs_date < 0
group by a.drvid
order by a.drvid, b.rvs_date desc;

trinity님이 2008-06-23 17:31에 작성한 댓글입니다. Edit Ｘ

이런말 하기 싫지만..제가 인덱스를 사용해보질 못해서..
위에 쓰신대로 했는데, 결과가 안 나오네요..
rvs_date에 어떤 값이 들어가야 하는지 좀 더 설명해주시면 안될까요?
기존에 있는 date 칼럼의 데이터를 모두 음수로 입력을 해야 하는 건가요?

김수정님이 2008-06-23 19:12에 작성한 댓글입니다.
이 댓글은 2008-06-23 19:54에 마지막으로 수정되었습니다. Edit Ｘ

제가 설명이 좀 부족했던 것 같네요.
rvs_date 컬럼은 아래와 같이 date 의 음수(int) 값 입니다.

logid	drvid	x	y	date	rvs_data
1	1	12	12	2008-06-21	-20080621
2	2	22	33	2008-06-21	-20080621
3	2	45	33	2008-06-22	-20080622
4	1	34	23	2008-06-23	-20080623
5	2	24	4	2008-06-23	-20080623

이 컬럼을 추가하는 이유는...
최근 데이터 순서로 결과를 받아야 하는데. date 컬럼이 정방향 정렬로 되어 있기 때문에 역방향으로 정렬되어 있는 인덱스를 이용하기 위함입니다.
(이 설명도 좀 어려우실듯 한데.. 글로는 설명의 한계가 있을듯 하네요.)

해당 쿼리는 다른 DBMS 에서 실행되지 않습니다. (신택스 에러가 발생됩니다.)

더 궁금하신 점은..... 댓글 달아주세요..

trinity님이 2008-06-23 20:18에 작성한 댓글입니다. Edit Ｘ

테스트 해보았어요..
그런데 정방향 정렬된 상태의 값들만 선택이 되는 이유는 멀까요?
tbl_B에서 다음처럼 하면 정렬을 잘 되는거 같은데,
select * from tbl_B order by drvid, rvs_date desc: 이렇게 하면 가장 최신이 가장 작은
값을 정렬이 되는 거 맞지요?

근데 왜 조인이 제대로 안 되는걸까요?
지금 결과는

drvid	drvname	logid	x	y	date	rvsdate
1	홍길동	1	12	12	2008-06-21	-20080621
2	하나	2	22	33	2008-06-21	-20080621

김수정님이 2008-06-24 09:21에 작성한 댓글입니다. Edit Ｘ

테스트 환경 구축 - b테이블의 date는 MySQL의 예약어 이므로 ldate로 변경해서 표현했습니다.

create table a ( drvid int, drvname varchar(5) );

insert into a ( drvid, drvname ) values( 1, 'alpha' );

insert into a ( drvid, drvname ) values( 2, 'bravo' );

create table b ( logid int , drvid int , x int , y int , ldate date );

insert into b ( logid, drvid, x, y, ldate ) values( 1, 1, 12, 12, '2008-06-21' );

insert into b ( logid, drvid, x, y, ldate ) values( 2, 2, 22, 33, '2008-06-21' );

insert into b ( logid, drvid, x, y, ldate ) values( 3, 2, 45, 33, '2008-06-22' );

insert into b ( logid, drvid, x, y, ldate ) values( 4, 1, 34, 23, '2008-06-23' );

insert into b ( logid, drvid, x, y, ldate ) values( 5, 2, 24, 4, '2008-06-23' );

원하는 결과 쿼리 문

SELECT a.drvid, a.drvname, b2.logid, b2.x, b2.y, b2.ldate

FROM

(

SELECT drvid, MAX( ldate ) AS md

FROM

GROUP BY drvid

) AS b1

INNER JOIN b AS b2 ON b1.drvid = b2.drvid AND b1.md = b2.ldate

INNER JOIN a ON a.drvid = b2.drvid;

제일 안쪽의 () 안의 쿼리문을 통해서 각 drvid별로 최신의 날짜를 구하고

이를 다시 self-join을 통해서 그에 해당하는 logid를 구하고

이름은 A 테이블과 inner join을 통해서 구했습니다.

이때 b 테이블에 drvid, ldate로 PK가 잡혀있고 a 테이블에는 drvid에 index가 걸려있을때

가장 좋은 성능을 보여줄 것으로 기대합니다.

우욱님이 2008-06-24 10:01에 작성한 댓글입니다. Edit Ｘ

되네요..ㅋㅋ 신기하다..우욱님의 쿼리 잘되여..쿼리를 잘 몰라서 고생하고 있었는데..
질문또 있는데..
답변해주시려나.. view table로 결과를 저장하고 싶은데, 위의 쿼리에 view table생성하는
쿼리를 어떻게 넣어줘야 하는지 모르겠네요..
create view view_1 as (위의코드);
이렇게 하니까 View's SELECT contains a subquery in the FROM clause 라고 에러나네요.

view 테이블로 만들어서 프로그램 시작할때마다 업데이트된 데이터를 읽으려고 하는데요.
저의 경우에도 사용해도 되는지도 갑자기 궁금해지네요.

일반 응용프로그래머가 db하려니 넘 어려워요..ㅡ.ㅡ;;;

김수정님이 2008-06-24 10:37에 작성한 댓글입니다.
이 댓글은 2008-06-24 11:24에 마지막으로 수정되었습니다. Edit Ｘ

아쉽게도 MySQL의 view는 from 절에 subquery를 넣을 수 없습니다.

꽤나 아쉬운 부분이죠. -_-;

원문: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

A view definition is subject to the following restrictions:

The SELECT statement cannot contain a subquery in the FROM clause.
The SELECT statement cannot refer to system or user variables.
The SELECT statement cannot refer to prepared statement parameters.
Within a stored routine, the definition cannot refer to routine parameters or local variables.
Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
The tables named in the view definition must already exist.
You cannot associate a trigger with a view.

우욱님이 2008-06-24 12:44에 작성한 댓글입니다. Edit Ｘ

그러면 결과를 테이블로 저장하는 방법이라도 있나요?

그리고 빠른 답변 정말로 감사합니다.

김수정님이 2008-06-24 12:50에 작성한 댓글입니다. Edit Ｘ

임시로 사용하실 거라면

CREATE TEMPORARY TABLE tbl_recent_info
( drvid int,
drvname varchar(5),
logid int,
x int,
y int,
ldate date );

계속 가지고 계실 거라면

CREATE TABLE tbl_recent_info
( drvid int,
drvname varchar(5),
logid int,
x int,
y int,
ldate date );

아까 쿼리 문 위에 살짝 insert 만 씌워주면
INSERT INTO tbl_recent_info( drvid, drvname, logid, x, y, ldate )
SELECT a.drvid, a.drvname, b2.logid, b2.x, b2.y, b2.ldate
FROM
(
SELECT drvid, MAX( ldate ) AS md
FROM
b
GROUP BY drvid
) AS b1
INNER JOIN b AS b2 ON b1.drvid = b2.drvid AND b1.md = b2.ldate
INNER JOIN a ON a.drvid = b2.drvid;

끝입니다.

당연히 temporary table은 세션 끊기면 자동 삭제구요.
테이블을 초기화할 경우에는 drop table & create table보다는 truncate table을 사용하시는 건 알고 계시죠? *^_^*

뱀다리...
1. 어지간해서는 저 쿼리가 그다지 느리지 않을 것으로 생각되는데(index가 잘 걸려 있다는 전제하에), 그냥 사용하셔도 될텐데요.
2. 하루에 한두번 조회하는데 그냥 하자니 너무 느리고, insert, update, delete가 많아서 index를 만들기가 부담스러우시면서 꼭 실시간의 data가 필요하지 않다면 위와 같이 table로 임시 저장하는 것도 하나의 대안일거라고 생각합니다.

우욱님이 2008-06-24 13:15에 작성한 댓글입니다. Edit Ｘ

감사합니다.

이제 한숨 돌린거 같네요.

프로그램내에서 쿼리날려서 바로 값을 가져오는 방법을 사용하기로 했습니다.

임시테이블도 만들어보고 했는데,

무식하게 바로 읽기로 했어요..

프로그램 실행할때 한번이니까..머 문제없을듯 합니다.

너무 열심히 답변달아주셔서 진짜로 감사합니다.

가~~암~~동~~~했어요..ㅠ.ㅠ;;

김수정님이 2008-06-24 14:30에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
27624	mysql 에서 사용자 변경 방법은.	손님	2008-06-24	3202
27623	mysql6.0에서 기존db연결이 안되요.. [1]	울라	2008-06-24	3474
27621	한 행의 내용 제한 옵션? [2]	유승효	2008-06-24	3363
27618	이런경우 조인쿼리를 어떻게 해야 하나요? [10]	김수정	2008-06-23	4576
27617	디비업데이트와 추가시 소요시간의 차이? [1]	김수정	2008-06-23	3486
27616	테이블3개 조인시 키를 그룹으로 묶으면서 카운트구하는 쿼리 도와주세요. [3]	김명현	2008-06-22	7843
27615	insert 후 select만 일어날때 어떤 DBMS가 가장 빠를까요? [2]	DB	2008-06-21	3587