데이터베이스 질문과 답변
by 민수홍 [2019.09.24 12:53:42]
현재 java spring mvc project로 오라클 sql에서 처리한 내용을 웹으로 불러오는 연습을 하고 있는 학생입니다.
박인호님이 쿼리문 작성을 도와 주셔서 쿼리까지는 작성되어 있으며, 쿼리는
예를 들어 아래 처럼 아래와 같은 테이블 형태가 있습니다.
HIT_EP : 회차
HIT_NUMBER1 : 첫번째공
HIT_NUMBER2 : 두번째공
HIT_NUMBER3 : 세번째공
HIT_NUMBER4 : 네번째공
HIT_NUMBER5 : 다섯번째공
HIT_NUMBER6 : 여섯번째공
위에 친구들을 HIT_NUMBER에 UNPIVOT으로 담아서
어떤 공이 가장 많이 나왔나? 확인을 하고
HIT_NUMBER : 공번호
CNT : 몇번 나왔는지 확인
근데 java에서 불러올때 HIT_NUMBER 이 친구는 잘 불러와지는데
cnt를 불러올때는 Property [cnt] not found on type [com.spring.lotto.domain.TestVO]
이런 에러 메세지를 불러오면서 cnt를 확인 할 수가 없다는데
count를 체크 할 수 있는 절묘한 쿼리수정 방법이 있을까 해서
여쭤봅니다
아 그리고 count할때 현재 문제가 한번도 나오지 않은 공들의 경우에는 0으로 된것이 아니라
아애 count에서 제외가 되어서 순위를 최하위로 놓을 수가 없는데
1~45 count해서 아애 안나온 친구들은 0으로 처리 할 수 있는 방법 sql에서 가능할까요?
with number_table as (
select 876 HIT_EP, 5 HIT_NUMBER1, 16 HIT_NUMBER2, 21 HIT_NUMBER3, 26 HIT_NUMBER4, 34HIT_NUMBER5, 42 HIT_NUMBER6, 24 HIT_BOUNS FROM DUAL
UNION ALL
select 875, 19 , 22 , 30 , 34 , 39 , 44 , 36 FROM DUAL
UNION ALL
select 874, 1 , 15 , 19 , 23 , 28 , 42 , 32 FROM DUAL
UNION ALL
select 873, 3 , 5 , 12 , 13 , 33 , 39 , 38 FROM DUAL
UNION ALL
select 872, 2 , 4 , 30 , 32 , 33 , 43 , 29 FROM DUAL
)
select *
from (
SELECT HIT_NUMBER, COUNT(*) CNT
FROM (
SELECT *
FROM (select * from number_table
where rownum <=10)
UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6) )
)
GROUP BY HIT_NUMBER
ORDER BY cnt desc
)
where rownum <= 6
;
select *
from (
SELECT HIT_NUMBER, COUNT(*) CNT
FROM (
SELECT *
FROM (select * from number_table
where rownum <=10)--> 왜 이렇게 하시는 거죠? 랜덤으로 10개 회차만 가지고 오게 됩니다.
UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6) )
)
GROUP BY HIT_NUMBER
HAVING COUNT(*) > 0
ORDER BY cnt desc
자바 오류는 TestVO에 cnt 프로퍼티 선언을 안 하신 것 같네요.
이클립스라면 해당 VO 선언부에 컨트롤 클릭해서 구현부로 이동 후 확인해보시면 됩니다.
having count(1) > 0은 무시하세요; 제가 질문의 의도를 완전 잘못 이해했네요 ㅎㅎ
count가 0이 나올리도 없고;;
where rownum <=10)--> 왜 이렇게 하시는 거죠?
최근회차부터 최근 10개만 조회하고 싶어서 rownum을 주었습니다.
TestVO에 cnt가 선언되었는데도 안되네요 ㅠ.ㅠ
WITH NUMBER_TABLE AS (
SELECT 876 HIT_EP, 5 HIT_NUMBER1, 16 HIT_NUMBER2, 21 HIT_NUMBER3, 26 HIT_NUMBER4, 34HIT_NUMBER5, 42 HIT_NUMBER6, 24 HIT_BOUNS FROM DUAL
UNION ALL
SELECT 875, 19 , 22 , 30 , 34 , 39 , 44 , 36 FROM DUAL
UNION ALL
SELECT 874, 1 , 15 , 19 , 23 , 28 , 42 , 32 FROM DUAL
UNION ALL
SELECT 873, 3 , 5 , 12 , 13 , 33 , 39 , 38 FROM DUAL
UNION ALL
SELECT 872, 2 , 4 , 30 , 32 , 33 , 43 , 29 FROM DUAL
),
BALLS AS
(SELECT LEVEL HIT_NUMBER FROM DUAL CONNECT BY LEVEL <= 45)
SELECT B.HIT_NUMBER, NVL(A.CNT,0) CNT, A.RNK
FROM (SELECT HIT_NUMBER, COUNT(*) CNT
, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RNK -- 동일횟수로 발생한 볼은 같은 순위로..
FROM (SELECT *
FROM (SELECT *
FROM NUMBER_TABLE
ORDER BY HIT_EP DESC
)
WHERE ROWNUM <= 10-- 최근 10회
)
UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6))
GROUP BY HIT_NUMBER
) A,
BALLS B
WHERE B.HIT_NUMBER = A.HIT_NUMBER(+)
--AND A.RNK <= 6
볼이 당첨된 횟수가 동률일 경우 어떻게 처리할지 생각해야 합니다.
위의 경우 aA.RNK <= 6 주석을 풀면 많이 나타난 볼 순위로 6위까지 조회하는데요.
보시면 아시겠지만 2번 나온 것, 1번 나온 것이 여러개의 볼이라
6개까지 커트하는 기준이 있어야 합니다.
횟수가 동일하게 나왔다면 모두 표시하는 게 맞다면 위 쿼리 그대로 사용하셔도 되고요.
쿼리문은 아주 완벽합니다!! 제 질문에 이렇게 훌륭한 답변을 달아주셔서 정말 감사합니다 ^^
앞으로는 제가 열심히 공부해서 다른분에게 답변을 달 수 있는 그날까지 노력해보겠습니다
WITH A AS (
SELECT 111 ID, 1 NUM1,3 NUM2, 45 NUM3, 3 NUM4,12 NUM5,9 NUM6,10 BONUS FROM DUAL UNION ALL
SELECT 112 ID, 11 NUM1,13 NUM2, 45 NUM3, 2 NUM4,13 NUM5,9 NUM6,11 BONUS FROM DUAL UNION ALL
SELECT 113 ID, 21 NUM1,23 NUM2, 45 NUM3, 32 NUM4,14 NUM5,9 NUM6,12 BONUS FROM DUAL UNION ALL
SELECT 114 ID, 31 NUM1,33 NUM2, 45 NUM3, 32 NUM4,15 NUM5,9 NUM6,13 BONUS FROM DUAL UNION ALL
SELECT 115 ID, 41 NUM1,43 NUM2, 45 NUM3, 33 NUM4,16 NUM5,9 NUM6,14 BONUS FROM DUAL
),B AS(
SELECT LEVEL NUMBERS FROM DUAL CONNECT BY LEVEL<=45
)
SELECT
B.NUMBERS,NVL(CNT,0)
FROM (
SELECT
NUMBERS,COUNT(*) CNT
,ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) RM
FROM A
UNPIVOT(
NUMBERS
FOR NUM_NM
IN (NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,BONUS)
)
GROUP BY NUMBERS
) T,B
WHERE 1=1
AND T.NUMBERS(+) = B.NUMBERS
--AND T.RM <=10 /*출현횟수 상위 10개만 가져오고 싶다면...*/
ORDER BY B.NUMBERS
;
꼬랑지님 쿼리에서
with a as (
가상 테이블 대신
)
number_table 실제 테이블을 참고 하려면 어디를 수정해야 할까요 ? sql 초보라 기초적인것을 물어봐서 죄송합니다 ..
with 문 없애고 가상 테이블명 A 대신 실제 테이블 사용하면 됩니다.
위에 제 댓글에 대한 답변도 필요해보이네요.
아이고 이것 저것 하느라 답변이 늦었습니다 ... ㅠ.ㅠ
작성해주신 쿼리문은 아주 완벽합니다!
1. 단순 로넘 조건으로는 최근 회차를 가져오지 못합니다.
- 정렬 후 로넘 방식으로 해야 합니다.
2. 가져오는 범위를 명확하게 해야 합니다.
- 상위 6개인지? 순위로 6등 이내인지?
- 동순위 등도 고려해야 합니다.
3. 순위의 기준
- 단순 빈도수로만 하는 것 보다는
- 6개 번호의 건수와 보너스 번호의 건수를 별도로 카운트하여 순위를 구하는게 좋을 듯 합니다.
4. 안나온 번호 처리는
- 상위 6개만 가져온다면? 불필요한 부분입니다.
- 전체 45개 번호를 다 가져올 경우에만 필요한 부분입니다.
) 버튼을 클릭하여 작성 하시면 됩니다.