꿈꾸는 개발자, DBA 커뮤니티 구루비

SQL질문.

by 오일사마 [2015.11.05 11:18:32]

USER_ID값이 GROUP BY 되어서 하나의 USER_ID값만 나오도록 하려고 합니다.

SRCHWRD 값은 여러개일시 최대 5개행만 구분자로 구분되어 하나의 필드로 만들려고 하구요.

작성한 SQL말고 효율적인 방법이 있을까요?

--TRACE
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        2    0.000        0.001          0          0          0          0
Execute      2    0.010        0.004          0          0          0          0
Fetch       37   10.280       17.149      15386       2785         20       3537
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       41   10.290       17.154      15386       2785         20       3537

Misses in library cache during parse   : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
   3537  SORT GROUP BY (cr=2785 pr=15386 pw=15386 time=17145691 us cost=9604 size=2115142 card=10471)
3468079   COUNT  (cr=2785 pr=0 pw=0 time=5275328 us)
3468079    HASH JOIN  (cr=2785 pr=0 pw=0 time=3998811 us cost=2487 size=88689716 card=439058)
  22600     TABLE ACCESS FULL INFO (cr=253 pr=0 pw=0 time=5823 us cost=68 size=316400 card=22600)
6016568     HASH JOIN  (cr=2532 pr=0 pw=0 time=2631691 us cost=2416 size=82542904 card=439058)
 101747      TABLE ACCESS FULL EVENT_LOG (cr=1266 pr=0 pw=0 time=26663 us cost=343 size=2172588 card=98754)
  24522      VIEW  (cr=1266 pr=0 pw=0 time=79554 us cost=1077 size=16393164 card=98754)
  24522       HASH UNIQUE (cr=1266 pr=0 pw=0 time=73038 us cost=1077 size=2567604 card=98754)
 101747        TABLE ACCESS FULL EVENT_LOG (cr=1266 pr=0 pw=0 time=27965 us cost=343 size=2567604 card=98754)

********************************************************************************


--SQL
SELECT A.USER_ID,
       A.PAGE_CODE,
       (LISTAGG(CASE WHEN ROWNUM <=5 THEN C.SRCHWRD ELSE NULL
                 END, '|') WITHIN
         GROUP (ORDER BY A.EVENT_DATE)) AS SRCHWRD
  FROM LOG A,
       INFO B,
       (select distinct 
               user_id,
               page_code,
               SRCHWRD
          from LOG 
        )C
 where A.PAGE_CODE=B.DEFAULT_AUTH
   and A.USER_ID = B.USER_ID
   and A.USER_ID = C.user_id
   and A.PAGE_CODE = C.PAGE_CODE
 GROUP BY A.USER_ID, A.PAGE_CODE
 order by USER_ID asc;

by 마농 [2015.11.06 15:33:29]

SELECT user_id, page_code
     , LISTAGG(srchwrd, '|') WITHIN GROUP(ORDER BY rn) srchwrd
  FROM (SELECT user_id, page_code, srchwrd, event_date
             , ROW_NUMBER() OVER(PARTITION BY user_id, page_code ORDER BY event_date) rn
          FROM log
        )
 WHERE rn <= 5
 GROUP BY user_id, page_code
 ORDER BY user_id, page_code
;

by 오일사마 [2015.11.11 17:02:16]

마농님 감사합니다.

한가지더 질문이 있습니다.

SRCHWRD 값을 중복문자 제거후 출력 하기 위해선 어떤 부분을 수정해야 하나요?

--sample

콘도|콘도|콘도|콘도 => 콘도

by 마농 [2015.11.11 17:55:28]

SELECT user_id, page_code
     , LISTAGG(srchwrd, '|') WITHIN GROUP(ORDER BY rn) srchwrd
  FROM (SELECT user_id, page_code, srchwrd
             , ROW_NUMBER() OVER(PARTITION BY user_id, page_code ORDER BY MIN(event_date)) rn
          FROM log
         GROUP BY user_id, page_code, srchwrd
        )
 WHERE rn <= 5
 GROUP BY user_id, page_code
 ORDER BY user_id, page_code
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변