오늘 한 것
product를 카테고리별 리뷰수가 많은 제품순으로 정렬하는 sql 쿼리 작성
궁금한 것 & 어려운 것
rownum rnum을 select해서 where절로 페이지네이션을 하고 싶다!!!
문제점 - 개선방법
문제점
복잡한 sql을 한번에 짜려고 함
정확한 sql 개념이 없어서 고쳐도 뭐가 나아지는지 모름
- rownum rnum을 넣었을 때 왜 오류가 나는지 모르겠음
개선 방법
구하려는 값을 여러 단계로 나눔
- 무한 수정
해결 과정
어제 2단계로 넘어가서 1단계는 다시 안봐도 될줄 알았는데,
다시 보니 review_count라는 컬럼 값을 가져올게 아니라 리뷰에 글 개수를 셀거면 count(review.product_name)을 select 해야한다. 그러면
select Count(review.product_name) cnt,review.product_name,review.category_code
from review
inner join product
on review.product_code = product.product_code
이 쿼리로 값을 구해야 하는데,
ORA-00937: not a single-group group function 이런 오류가 난다.
=> 내가 count를 select하는 방법을 이해를 못했다.
그리고 어제 정의한 문제도 정확하지 않다는 걸 깨달았다.
내가 구하고 싶은 건
=>product를 리뷰의 갯수가 많은 순서대로 정렬하고 싶은 것이었다.
그럼 다시 단계를 나눠서
- 1단계 : count로 리뷰개수 구하기
- 2단계 : review와 product 테이블 join하기
(리뷰개수가 0인것도 나오게 하려면 left outer join) - 3단계: review와 product를 조인한 테이블에서 count로 리뷰개수 구하고 count로 order by 하며
rownum 매기기
이렇게 값을 구해야 한다.
잠시 눈물을 닦고..
[1단계 count로 리뷰개수 구해보자.]
count함수 사용
https://extbrain.tistory.com/54
전체 행 갯수 가져오기
SELECT COUNT(*) FROM review;
프로덕트 별 리뷰 갯수 가져오기
SELECT COUNT(product_code) FROM review
group by product_code;
하니까 딱 count만 나온다.
나는 product_name,product_code 등 다른 칼럼값도 구해야한다.
SELECT COUNT(product_code), review.product_code
FROM review
group by product_code;count를 product_code로 해서 review.product_code까지는 select되지만
SELECT COUNT(product_code), review.product_code, review.product_name FROM review
group by product_code;review.product_name까지 select했을때는 : not a GROUP BY expression 오류가 나왔다.
그래서 group by에 product_name을 더했더니
SELECT COUNT(product_code), review.product_code, review.product_name FROM review
group by product_code, product_name;실행이 됐다!!
💡
그럼 count()안에 들어가는 컬럼은 group by에 2번 안쓰고 count(컬럼), 컬럼 식으로 2번 select 해도 되지만 count()안에 쓰지 않은 컬럼을 셀렉트 하려면 group by에 그 컬럼도 써줘야 하는구나!
그럼 일단 리뷰가 있는 글의 정보를 모두 select 하려면
SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick ;이렇게 코드를 쓰면 review의 모든 컬럼을 select하면서 추가로 리뷰의 개수를 프로덕트 코드별로 출력한다.
[2단계 review와 product 테이블 join하기
(리뷰개수가 0인것도 나오게 하려면 left outer join)]
그럼 이제 product테이블과 Join해서 리뷰가 없는 제품도 출력해보자.
앞선 출력결과를 a로 묶어서 일단 count 해온 값이 잘 이어져 오는지 확인해본다.
select COUNT(a.product_code)
from product left outer join
(SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick)a
on product.product_code = a.product_code
group by a.product_code
order by a.product_code;select count를 a.product_code칼럼으로 하고
group by와 order by도 a.product로 하니 count값이 잘 출력됐다.
여기서도 group by 칼럼과 count()안의 칼럼을 통일시킨게 해답인 것 같다.
같은 컬럼이 있어도, count()안에 쓴 컬럼의 테이블 명까지 똑같이 써야한다.
그럼 나머지 칼럼도 출력해보자.
select COUNT(a.product_code), a.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
from product left outer join
(SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick)a
on product.product_code = a.product_code
group by a.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
order by a.product_code;별칭 안쓰고 다 적었고 , 여기서도 처음엔 group by에 product.product_code로 쓰니까 안되더니 a.product_code로 묶으니까 실행이 된다.
근데 여기서 문제점은, left outer join으로 묶어서 그런지 리뷰를 쓰지 않은 상품들의 product_code가 모두 null로 나오는 것이다. product테이블에는 product_code를 모두 썼음에도 불구하고.
그럼 어떻게 해야할까 ?
select count에는 리뷰가 있는 a결과 값의 a.product를 쓰고, product_code는 product테이블의 product_code로 검색했다.
그리고 아까는 group by에 a.product_code를 써서
select count(a.product.code)와 select a.product_code를 한번에 해결했는데
이번에는 group by에 a.product.code와 product.product_code 두가지 모두 써서 셀렉트를 각각 처리했다.
select COUNT(a.product_code), product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
from product left outer join
(SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick)a
on product.product_code = a.product_code
group by a.product_code, product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
order by a.product_code;결과가 아주 잘 나온다!
그럼 이제 마지막 단계로 넘어가서
[3단계: review와 product를 조인한 테이블에서 count로 리뷰개수 구하고 count로 order by 하며
rownum 매기기 ]
이제 결과값은 다 나왔다. 이걸 rownum으로 번호만 매겨보자.
조회 순번 매기는 방법 (ROWNUM)
https://gent.tistory.com/170
2단계에서 얻은결과를 다시 b로 묶고
select rownum rnum,에 b의 모든 칼럼을 select해준다.
select rownum rnum, b.* from
(select COUNT(a.product_code), product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
from product left outer join
(SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick)a
on product.product_code = a.product_code
group by a.product_code, product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
order by a.product_code) b리뷰가 있는 것부터 순서가 잘 매겨져서 나온다!!
근데 sql 파일에서는 여기에 where rnum = 1 and rnum = 10을 붙이면 오류가 난다.
xml에서는 멀쩡히 돌아가는 쿼리를
sql에 복사해서 돌려보면 오류가 남.. 다른 list들도..
그건 왜 그런건지 일단 다른거 해결하고 찾아봐야겠다.
xml에서는 3단계의 쿼리를 다시한번 묶어서
select *
from (select rownum rnum, b.*
from
(select COUNT(a.product_code), product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
from product left outer join
(SELECT COUNT(product_code), review.product_code, review.product_name, review.category_code, review.review_content,
review.review_img, review.review_date, review.member_nick
FROM review
group by product_code, product_name, category_code, review_content, review_img, review_date, member_nick)a
on product.product_code = a.product_code
group by a.product_code, product.product_code, product.product_name, product.product_detail,
product.category_code, product.product_price, product.product_img, product.product_original, product.product_date,
a.review_content, a.review_img, a.review_date, a.member_nick
order by a.product_code) b
)
where rnum >= #{start} and rnum <= #{end}이렇게 썼더니 돌아간다.. 이유는 아직 잘 모르겠다.
내일(혹은 가까운 미래에) 해보고 싶은 것
xml에서는 멀쩡히 돌아가는 쿼리가
sql 파일에서 돌리면 왜 오류가 나는지 알아보기
=왜 한번 더 묶어서 select*를 해야 where rnum >= #{start} and rnum <= #{end} 문장이 적용이 되는지 알아보기
