데이터베이스 질문과 답변
SQL질문.
0
3
1,278
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 ;
) 버튼을 클릭하여 작성 하시면 됩니다.