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

MySQL Q&A 31199 게시물 읽기

No. 31199

count 를 사용하여 그룹 단위로 top n 쿼리 속도 문제
작성자	김경준(kkchlove)	작성일	2018-11-22 10:04	조회수	2,522

안녕하세요? 디비사랑넷 눈팅만 하다가 처음 글 쓰내요...

다름이 아니라... 사용자 정의 변수와 row_number() 함수를 사용할 수 없어 아래와 같이 카운트를 이용하여 랭킹 쿼리를 작성 했습니다.

select * from test s where ( select count(*) from test f where f.name = s.name and f.score >= s.score ) <= 5

그런데 문제는 속도가 너무 느리다는 단점이 있는데... 개선 할 수 있을까요?

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

쿼리를 잘못 작성하신 것 같은데요?
test 테이블의 PK 가 어떻게 되나요?
PK 가 같지 않다는 조건이 들어가야 할 것 같은데요.
서브쿼리가 아닌 직접 조인하는 걸로 개선 가능합니다.

SELECT s.*
     , COUNT(f.name) + 1 AS rnk
FROM test s
LEFT OUTER JOIN test f
    ON f.name = s.name
   AND f.score > s.score
GROUP BY s.name, s.pk
HAVING COUNT(f.name) < 5
;

마농(manon94)님이 2018-11-22 11:19에 작성한 댓글입니다.
이 댓글은 2018-11-22 11:31에 마지막으로 수정되었습니다.

답변 감사합니다. 우선 테이블은 서브 쿼리에 결과물입니다.

아래와 같이 있을때 created_ts 그룹으로 top 을 뽑고 싶어 위와 같은 쿼리를 인터넷에서 찾았습니다.

+---------------------+------------+----------+--------------------------------+

+---------------------+------------+----------+--------------------------------+

| 1536710400000000000 | 0tna_J0Yss | 4 | 9110 |

| 1536710400000000000 | 0tna_J0Yss | 2 | 7160 |

| 1536710400000000000 | 0tzvxJB4Ds | 3 | 6237 |

| 1536710400000000000 | 0GlAyJ0YDs | 2 | 5350 |

| 1536710400000000000 | 0GlAyJ0YDs | 1 | 3890 |

| 1536710400000000000 | 0tna_J0Yss | 1 | 2598 |

| 1536710400000000000 | 0StWfU0YDD | 3 | 2034 |

| 1536710400000000000 | 0ucT1d04ss | 3 | 1716 |

| 1536710400000000000 | 0tzvxJB4Ds | 2 | 1209 |

| 1536710400000000000 | 0tna_J0Yss | 5 | 965 |

| 1536710400000000000 | 0ucT1d04ss | 2 | 704 |

| 1536710400000000000 | 0StWfU0YDD | 1 | 640 |

| 1536710400000000000 | 0GlAyJ0YDs | 3 | 472 |

| 1536710400000000000 | 0tzvxJB4Ds | 4 | 443 |

| 1536710400000000000 | 0tzvxJB4Ds | 1 | 202.296875 |

| 1536710400000000000 | 0GlAyJ0YDs | 5 | 0 |

| 1536710400000000000 | 02X6_UB4ss | 1 | 0 |

| 1536710400000000000 | 0tzvxJB4Ds | 5 | 0 |

| 1536710400000000000 | 0tna_J0Yss | 3 | 0 |

| 1536710400000000000 | 0GlAyJ0YDs | 4 | 0 |

| 1536710400000000000 | 0StWfU0YDD | 2 | 0 |

| 1536710400000000000 | 0ucT1d04ss | 1 | 0 |

| 1536711300000000000 | 0tna_J0Yss | 4 | 9290 |

| 1536711300000000000 | 0tna_J0Yss | 2 | 8300 |

| 1536711300000000000 | 0GlAyJ0YDs | 2 | 7910 |

| 1536711300000000000 | 0tzvxJB4Ds | 3 | 5409 |

| 1536711300000000000 | 0GlAyJ0YDs | 1 | 4709 |

| 1536711300000000000 | 0tna_J0Yss | 1 | 3185 |

| 1536711300000000000 | 0tzvxJB4Ds | 2 | 1909 |

| 1536711300000000000 | 0ucT1d04ss | 3 | 1855 |

| 1536711300000000000 | 0StWfU0YDD | 3 | 1841 |

| 1536711300000000000 | 0tna_J0Yss | 5 | 874 |

| 1536711300000000000 | 0ucT1d04ss | 2 | 670 |

| 1536711300000000000 | 0StWfU0YDD | 1 | 641 |

| 1536711300000000000 | 0tzvxJB4Ds | 1 | 457.6015625 |

| 1536711300000000000 | 0tzvxJB4Ds | 4 | 304 |

| 1536711300000000000 | 0GlAyJ0YDs | 3 | 172 |

| 1536711300000000000 | 0StWfU0YDD | 2 | 42.599609375 |

| 1536711300000000000 | 0GlAyJ0YDs | 5 | 0 |

| 1536711300000000000 | 02X6_UB4ss | 1 | 0 |

| 1536711300000000000 | 0tzvxJB4Ds | 5 | 0 |

| 1536711300000000000 | 0tna_J0Yss | 3 | 0 |

| 1536711300000000000 | 0ucT1d04ss | 1 | 0 |

| 1536711300000000000 | 0GlAyJ0YDs | 4 | 0 |
+------------------------------------------------------------------------------+

김경준(kkchlove)님이 2018-11-22 11:57에 작성한 댓글입니다.

SELECT m.created_ts
     , m.logical_id
     , m.group_id
     , m.accumulate_power_consumption_increment
     , COUNT(s.created_ts) + 1 rk
FROM test m
LEFT OUTER JOIN test s
    ON m.created_ts = s.created_ts
   AND m.accumulate_power_consumption_increment
     < s.accumulate_power_consumption_increment
GROUP BY m.created_ts, m.logical_id, m.group_id
HAVING COUNT(s.created_ts) < 5
ORDER BY created_ts, rk
;

마농(manon94)님이 2018-11-22 12:27에 작성한 댓글입니다.
이 댓글은 2018-11-22 12:30에 마지막으로 수정되었습니다.

혹시 서브쿼리를 테이블로 사용안하고 작성할 방법이 있나요? 테이블이 두개가 같은 서브쿼리를 하기 때문에 비효율적인거 같아요.

아래와 같은 쿼리에서 랭킹을 바로 뽑을 수 있을까요?

SELECT t1.created_ts, t1.logical_id, t1.group_id, (t1.accumulate_power_consumption_last-t2.accumulate_power_consumption_last) as accumulate_power_consumption_increment FROM tbl_metering_ac_group_data_15_min t1 left JOIN tbl_metering_ac_group_data_15_min t2 ON (t1.created_ts - (15 * 60 * 1000000000) = t2.created_ts and t1.logical_id = t2.logical_id and t1.group_id = t2.group_id) WHERE (t1.created_ts >= (1536710400000000000 - (15 * 60 * 1000000000)) and t1.created_ts <= 1536714000000000000) AND (t2.created_ts >= (1536710400000000000 - (15 * 60 * 1000000000)) and t2.created_ts <= 1536714000000000000) AND ((t1.logical_id, t1.group_id) in ( (‘0ucT1d04ss’, 1), (‘0ucT1d04ss’, 2), (‘0ucT1d04ss’, 3), (‘0tzvxJB4Ds’, 1), (‘0tzvxJB4Ds’, 2), (‘0tzvxJB4Ds’, 3), (‘0tzvxJB4Ds’, 4), (‘0tzvxJB4Ds’, 5), (‘02X6_UB4ss’, 1), (‘0tna_J0Yss’, 1), (‘0tna_J0Yss’, 2), (‘0tna_J0Yss’, 3), (‘0tna_J0Yss’, 4), (‘0tna_J0Yss’, 5), (‘0GlAyJ0YDs’, 1), (‘0GlAyJ0YDs’, 2), (‘0GlAyJ0YDs’, 3), (‘0GlAyJ0YDs’, 4), (‘0GlAyJ0YDs’, 5), (‘0I_9L6BYsD’, 1), (‘0I_9L6BYsD’, 2), (‘0I_9L6BYsD’, 3), (‘0I_9L6BYsD’, 4), (‘0I_9L6BYsD’, 5), (‘0StWfU0YDD’, 1), (‘0StWfU0YDD’, 2), (‘0StWfU0YDD’, 3), (‘0GpfMJBYss’, 1), (‘0GpfMJBYss’, 2), (‘06dAht0YDs’, 1), (‘06dAht0YDs’, 2) )) order by t1.created_ts, accumulate_power_consumption_increment desc

김경준(kkchlove)님이 2018-11-22 13:01에 작성한 댓글입니다.

동일 쿼리 두번 쓸 수 밖에 없겠는데요?
대신 인라인뷰 안에서 order by 구문은 빼시구요.
조건절이 약간 이상한 부분이 보이는데 제 생각에는 고쳐야 할 것 같아요.
-- 변경전
WHERE t1.created_ts >= 1536710400000000000 - (15 * 60 * 1000000000)
   AND t1.created_ts <= 1536714000000000000
   AND t2.created_ts >= 1536710400000000000 - (15 * 60 * 1000000000)
   AND t2.created_ts <= 1536714000000000000
-- 변경후
WHERE t1.created_ts >= 1536710400000000000 -- 제거
   AND t1.created_ts <= 1536714000000000000
   AND t2.created_ts >= 1536710400000000000 - (15 * 60 * 1000000000)
   AND t2.created_ts <= 1536714000000000000 - (15 * 60 * 1000000000) -- 추가

마농(manon94)님이 2018-11-22 14:48에 작성한 댓글입니다.
이 댓글은 2018-11-22 14:59에 마지막으로 수정되었습니다.

감사합니다. 검토 한번 해보겠습니다. 결국은 전에 알려주신 쿼리에 테이블 부분에 두번 제가 만든 쿼리가 들어가야한다는거죠?

김경준(kkchlove)님이 2018-11-22 17:42에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
31203	select table_name쿼리문 질문이요 ㅠㅠ [7]	데베너무어려워요	2018-12-07	2626
31202	[속도문제] 증가키 구조 테이블 레코드 삭제 or 플레그 비트?? [1]	권순환	2018-12-04	2371
31201	replace 질문입니다. [2]	이기자	2018-11-28	2381
31199	count 를 사용하여 그룹 단위로 top n 쿼리 속도 문제 [6]	김경준	2018-11-22	2522
31198	MySql 질문드립니다! [1]	SS	2018-11-14	2498
31197	쿼리문 인터셉터 질문드립니다 ㅠ	김영욱	2018-11-13	2414
31196	mysql 포린키를 모르겠어요 [1]	순자	2018-11-12	2444