데이터베이스 질문과 답변
안녕하세요. 현재 쿼리를 짜고 있는데 속도가 잡히지 않아 질문 드립니다..
구하고 싶은 내용은 'DataID 별 각 Category 안에서 가장 많은 name들을 계산'하고 싶습니다.
Data_Category { DataId : number, name:string, category:string }
이며 전체적으로 중복하여 입력이 가능합니다.
select dataId, name, category, count(*) as cnt , rank() over (partition by dataId, category order by count(*) desc) as "ranking" from data_category group by dataId, name, category
위의 쿼리에서 rank 부분이 있고 없고로 속도가 너무 차이가 납니다.
rank가 없다면 0.0053, 있을 땐 0.5초가 소요가 됩니다.
select *, rank() over (partition by A.dataId, A.category order by cnt desc) as "ranking" from( select dataId, name, category, count(*) as cnt from data_category group by dataId, name, category ) A
위의 쿼리도 거의 동일한 속도가 나옵니다. select 문으로 감싸는 순간 임시테이블 생성으로 인해 느려지고 있습니다.
데이터는 40만건 정도입니다.
데이터Id별 각 카테고리 별 개수가 가장 많은 name들을 5건씩 추출하고 싶은데 쿼리 방향을 못잡겠습니다..
쿼리를 짜주시지 않더라도 간단한 힌트만이라도 간절히 부탁드립니다..!
감사합니다.
1. 조건을 안줘서 느린게 아닐런지?
2. rank 는 동순위 처리를 합니다.
- 5건 과는 조금 다른 의미, 5등의 의미로 5등이 여러명 나오면 5건 이상 나올 수 있습니다.
- 원하는 결과가 5등이 맞는지? 5건이 맞는지?
- 5건이 맞다면? ROW_NUMBER() 를 시용해야 하는데
- 이 때, 정렬순위가 동순위가 나오지 않도록하는 유니크한 추가 정렬 조건이 필요합니다.
- 예) ORDER BY COUNT(*) DESC, name -- 동순위의 경우 이름이 빠른게 우선
3. 별칭은 홑따옴표(비표준)가 아닌 쌍따옴표(표준) 사용 권장
SELECT *
FROM (SELECT dataId, category, name
, COUNT(*) cnt
, RANK() OVER(PARTITION BY dataId, category ORDER BY COUNT(*) DESC) rk
FROM data_category
GROUP BY dataId, category, name
) a
WHERE rk <= 5
;
rk <=5 를 제외했다는 건 rank가 없을 때 속도랑 비교하기 위해서 rank를 주석 처리 했을때 같이 했었습니다.
rank가 있을때는 rk<=5도 지우지 않았습니다
추가 답변 달아주신 것 확인했습니다.
1. 해당 쿼리로 뷰를 생성할 예정이라 따로 조건은 추가되는 부분이 없을 것 같습니다.!
2. 감사합니다 ROW_NUMBER()로 사용하겠습니다! 상위 5건이 맞습니다
3. 쌍따옴표로 모두 교체하겠습니다
답변 감사합니다! 해주신 쿼리로 확인해봤는데
rank가 있을 경우 0.9초, rank가 없을 경우엔 0.2초가 소요가 됩니다. (rk <=5도 제외)
혹시 속도를 조금 더 줄일 수 있는 방법이 있을지 궁금합니다.
현재 인덱스는 강제로 (dataId, category, name)에 주고 있습니다!
rk <=5 조건을 주셔야죠.
1. rank 가 없을 때 빠르게 나오는 것은 부분범위처리 때문일 수 있습니다.
- 40만건이 다 출력되는게 아니라 일부(첫페이지)만 우선 출력
2. 사용되는 3개 항목으로 구성된 인덱스가 있다면 좋을 것 같습니다.
2. 인덱스는 위 댓글에 적어놓았듯이 (dataId, category, name) 이렇게 생성되어있고 explain으로 확인 결과 정상적으로 타고 있습니다.
row_number()를 붙이면 Extra에 Using temporary; Using filesort가 추가되는 것 외에 다른 점은 없습니다
) 버튼을 클릭하여 작성 하시면 됩니다.