태그들의 우선순위를 구하는 쿼리문을 만들수 있을까요? 0 10 2,311

by neomegas [DB 모델링/설계] 태그 쿼리 tag [2013.06.26 15:39:59]


언젠가  저도 실력이 쌓여, 필요를 구하는 분들에게 도움을 줄수 있기를 바랍니다.

휴...
테이블이 두개가 있습니다.
TRAVELSTORY 에는  TB_TAG라는 컬럼이 있어서, 태그를 적어 넣을 수 있습니다.
예를 들어, 여행기에 대한 글을 적고 관련 태그로 여행, 낭만, 런던, ... 등을 등록할 수 있구요

COUCHSURFING 에도 CB_TAG가 있어서 숙소, 민박, 파리... 등의 관련 태그를 등록할 수 있도록 만들어 놨습니다.


메인 페이지에서 이러한 태그들의 우선순위(즉 실시간 검색어 순위 같은것)를 보여주기 위해 Ajax를 쓰려고 하는데요...
좀 더 자세히 하면
런던 태그가 5번,
민박 태그가 4번,
게스트 하우스 원 6번.... 일때

메인화면에서 아래 처럼 보여주려고 합니다.
1. 게스트 하우스 원
2. 런던
3. 민박
...

<select id="GET_TAG_LIST" resultType="java.util.hashtable">
SELECT
cb_Tag AS    CBTAG,
tb_Tag AS    TBTAG,
REGEXP_CUNT(cb_Tag, '['||tb_Tag||']') cnt_11g
FROM
COUCHSURFING cs, TRAVELSTORY ts
</select>    
쿼리문을 위와 같이 만들었는데요... 여기서 더 이상 나아가지 못하고 있습니다.
태그글들이 콤마로 연결되 있는데 그것들을 어떻게 하면 나눌것인지와
나온태그값의 중복값을 구해 나열하는 것등등해서 참 난감합니다.

제가 알고 싶은것이 이런 처리를 db에서 불러내서 order by로 할 수 있는지...
아니면 위의 쿼리문은 맞으며(아주 희박하죠...), 나머지는 제가 콘트롤러 화일에서 제어해 주는 것이 맞는지 알고 싶습니다.
부탁드립니다.


조언을 구할 분이 없어서 책상에 하루 종일 앉아서 구글링만 하는 1인입니다.
너무 갑갑하네요... 
by 아린 [2013.06.26 16:13:18]
ROWNUM 에서 원하는 순위 갯수 까지 구하시면 되구요.
같은 순위 일경우는 가/나/다 순으로 했어요. 참고하세요.

WITH travelstory(seq, tb_tab) AS(
SELECT 1, '여행, 낭만, 런던, 인도' FROM dual UNION ALL
SELECT 2, '여행, 서울, 런던' FROM dual UNION ALL
SELECT 3, '여행, 여행, 인도, 스위스, 미국, 일본' FROM dual
), couchsurfing(seq, cb_tab) AS(
SELECT 1, '숙소, 민박1, 파리1, 서울' FROM dual UNION ALL
SELECT 2, '숙소, 민박2, 파리2, 태국, 말레이시아' FROM dual UNION ALL
SELECT 3, '숙소, 민박3, 파리3' FROM dual
)
SELECT *
  FROM (SELECT REGEXP_SUBSTR(tb_tab, '[^,]+', 1, lv) tb_tab, COUNT(*) cnt
          FROM (SELECT REPLACE(tb_tab, ' ') tb_tab FROM travelstory
                 UNION ALL
                SELECT REPLACE(cb_tab, ' ') tb_tab FROM couchsurfing
                ) a
             , (SELECT LEVEL lv FROM dual 
                 CONNECT BY LEVEL <= 30) d
         WHERE lv <= LENGTH(tb_tab) - LENGTH(REPLACE(tb_tab, ',')) + 1
         GROUP BY REGEXP_SUBSTR(tb_tab, '[^,]+', 1, lv)             
         ORDER BY cnt DESC, tb_tab
        )
 WHERE ROWNUM <= 5  

by neomegas [2013.06.26 16:30:13]
너무 고급 정보에 감사드립니다.
레벨1에 하늘나는 법을 묻는 기분입니다.

아린님, 만약 등록되는 태그 값을 글 쓰는 이들이 임의로 적기에
SELECT 1, 2, 3 에 여행1, 여행2, 숙소1 처럼 정할 수 없을 때는 다른 방법이 있을까요?
즉, 첫글에는 등록 태그가 5개 있고,
다음글에는 태그 1개등록
...
34번 글에는 등록 태그가 6개
...
...
각 태그는 글쓴이가 주제에 맞게 스스로 정해서 등록.
이런 경우에는 컨트롤러에서 등록 태그들의 분류를 제어하는 구문을 만들어야 하겠죠?

오늘 많이 배우고 감사드려요. 아직은 잘 몰라 힘은 듭니다.

by 아린 [2013.06.26 16:34:28]
태그의 숫자는 상관없어요. 원하는 만큼 넣어셔도 되고요. (샘플 수정했어요)

태그의 입력갯수를 max 로 30 개로 임시설정했고요. 수정하시면 됩니다.


-- 원하는 태그 숫자(max) 값설정
SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 30

by neomegas [2013.06.26 16:39:27]
너무 감사한 마음에 
우선 답변을 선택한 1인...

좀있다가 다시 질문할지도 몰라요...

by 아린 [2013.06.26 16:40:36]
1 과 lv 의 순서를 바꿔서 작성했네요. 다시 수정했어요.

REGEXP_SUBSTR(tb_tab, '[^,]+', 1, lv)

by neomegas [2013.06.26 18:22:06]
아린님 한가지만 더 여쭐께요. 거의 다 왔어요!

SELECT 1, '여행, 낭만, 런던, 인도' FROM dual UNION ALL
SELECT 2, '여행, 서울, 런던' FROM dual UNION ALL
SELECT 3, '여행, 여행, 인도, 스위스, 미국, 일본' FROM dual

에서 여행, 런던, 낭만, 인도같은 값을 제가 알지 못하는 경우는 어떻게 해야 하는지요?(아마 제가 왜 같은 질문을 계속하지라고 생각 하실거에요.)

제가 최대한 자세히 설명해보겠습니다.
1. 예를 들어, 여행소셜네트워크 사이트를 제가 만들었어요.
2. 회원들이 자기 여행기를 씁니다. 글에는 자기 글의 특성을 나타내는 태그들을 등록하구요.
그리고 이값은 DB에 들어 갑니다.
3. 이렇듯 수많은 사람들이 글을 작성할 때... 저는 실시간으로 많이 등록된 태그들을 찾아내서 순위별로 사이트의 메인화면 한곳에 보여주고 싶습니다.
4. 그러면 DB에 접속해서 각 글마다 태그가 어떤 값이 있는지 알아야 하고, 각 태그의 중복값을 구하고 크기에 맞게 보여주는... 이런식으로 가는게 맞는지요?

여기서 궁금한건 글이 점차 늘어나는데... 100개, 200개... 막 늘어나고, 등록된 태그들도 각양 각색일때... 어떻게 해야 하는지 막막합니다...

제가 두려워하면서 생각하는 바는...
1.각글의 태그를 ,로 자르고 공백이 나오면 태그입력이 더 이상 없으니 자르기 실행을 완료하고...
2.자른 태그를 FOR 문으로 해서 토막난 만큼 변수설정 해준다음에(아... 이러면 모든 글에 대한 이중 FOR문이 될 수 있겠네요... 뭔가 두려워집니다.)
3.중복될때마다 1씩 증가시켜서 가장 큰수 부터 메인에 순서별로 뿌려줍니다.
드디어 제 능력을 벗어나게 되네요.


지금 가장 큰 제 문제는 SELET 1,2,3......얼마가 될지 모르는 것과(왜냐면 계속 글이등록되고 있습니다.)
무슨 태그가 올지 모른다는 거죠....

혹시 위에 쓰신  
WITH travelstory(seq, tb_tab) AS(
SELECT 1, '여행, 낭만, 런던, 인도' FROM dual UNION ALL
SELECT 2, '여행, 서울, 런던' FROM dual UNION ALL
SELECT 3, '여행, 여행, 인도, 스위스, 미국, 일본' FROM dual
), couchsurfing(seq, cb_tab) AS(
SELECT 1, '숙소, 민박1, 파리1, 서울' FROM dual UNION ALL
SELECT 2, '숙소, 민박2, 파리2, 태국, 말레이시아' FROM dual UNION ALL
SELECT 3, '숙소, 민박3, 파리3' FROM dual
)
는 가상 테이블이라서 DB에 이런방식으로 하라 라고 알려주는 명령어 라면....

제가 제대로 아린 님을 답답하게 했네요...

늦은 시간에 답변 기다릴께요.
  

by 마농 [2013.06.26 18:50:11]

WITH 문은 그저 테스트용 데이터를 만들어 놓은 것 뿐입니다.
실제 적용할때 WITH 문을 쓰라는게 아니죠.


by neomegas [2013.06.26 20:54:07]
감사드려요.

큰 도움이 됬습니다.
저도 능력껏 실력을 공유할께요!

by 아린 [2013.06.26 19:55:14]
마농님 말씀 처럼

WITH 문 내용은 아래와 같이 테이블 만들고/데이터 입력하기 (귀찮아서 ㅡ.ㅡ)  
만든 테스트 데이터고요.

보통은 테이블 생성후/입력처리/조회를 하지요.

-- 테이블 생성
CREATE TABLE travelstory(
seq NUMBER
tb_tab VARCHAR2(100));

-- 데이터 입력
INSERT INTO travelstory VALUES (1, '여행, 낭만, 런던, 인도');
INSERT INTO travelstory VALUES (1, '여행, 서울, 런던');
INSERT INTO travelstory VALUES (1, '여행, 인도, 스위스, 미국, 일본');

-- 조회
SELECT * FROM travelstory;

by neomegas [2013.06.26 20:57:15]
아린님의 정보를 공유해 주셔서 감사합니다.

저도 남과 제 실력을 기부가 아닌, 공유하는 그날을 꿈꿉니다.
(실은 재능 기부보다는... 왜냐면 기부는 남과 동등한 입장이라는 생각이 안들고 약간 자신이 우월하다는 의미를 가지것 같아서 싫어해요!
공유나 남을 대등히 해줄 수 있는 용어를 좋아합니다!)
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입