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 Q&A 31199 게시물 읽기
No. 31199
count 를 사용하여 그룹 단위로 top n 쿼리 속도 문제
작성자
김경준(kkchlove)
작성일
2018-11-22 10:04:24
조회수
162

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

다름이 아니라... 사용자 정의 변수와 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:20에 작성한 댓글입니다.
이 댓글은 2018-11-22 11:31:00에 마지막으로 수정되었습니다.

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

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

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

|     created_ts      | logical_id | group_id | accumulate_power_consumption_increment |

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

| 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:02에 작성한 댓글입니다.

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:58에 작성한 댓글입니다.
이 댓글은 2018-11-22 12:30:08에 마지막으로 수정되었습니다.

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

 
아래와 같은 쿼리에서 랭킹을 바로 뽑을 수 있을까요?
 
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:42에 작성한 댓글입니다.

동일 쿼리 두번 쓸 수 밖에 없겠는데요?
대신 인라인뷰 안에서 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:02에 작성한 댓글입니다.
이 댓글은 2018-11-22 14:59:26에 마지막으로 수정되었습니다.

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

김경준(kkchlove)님이 2018-11-22 17:42:17에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31203select table_name쿼리문 질문이요 ㅠㅠ [7]
데베너무어려워요
2018-12-07
90
31202[속도문제] 증가키 구조 테이블 레코드 삭제 or 플레그 비트?? [1]
권순환
2018-12-04
67
31201replace 질문입니다. [2]
이기자
2018-11-28
108
31199count 를 사용하여 그룹 단위로 top n 쿼리 속도 문제 [6]
김경준
2018-11-22
162
31198MySql 질문드립니다! [1]
SS
2018-11-14
159
31197쿼리문 인터셉터 질문드립니다 ㅠ
김영욱
2018-11-13
144
31196mysql 포린키를 모르겠어요 [1]
순자
2018-11-12
172
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.069초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다