데이터베이스 질문과 답변
안녕하세요.
혼자 끙끙대다 도저히 답이 안나와 문의드립니다.
아래 형식의 데이터가 수십만 건이 있습니다. ㅠ.ㅠ
- idx : number
- cdate : date
- userid : varchar2
| idx | cdate | userid |
| 1 | 2015-01-16 오전 9:17:16 | 0202 |
| 2 | 2015-01-16 오전 9:17:19 | 0202 |
| 3 | 2015-01-16 오전 9:20:12 | 0103 |
| 4 | 2015-01-16 오전 10:20:14 | 0102 |
| 5 | 2015-01-16 오전 11:20:15 | 0103 |
| 6 | 2015-01-17 오전 9:17:16 | 0202 |
| 7 | 2015-01-17 오전 11:15:19 | 0103 |
| 8 | 2015-01-17 오후 1:20:15 | 0102 |
| 9 | 2015-01-17 오후 7:20:25 | 0101 |
원하는 출력물은 아래와 같이 날짜별 사용자로 추출하고자 하는데.
| cdate | cnt |
| 20150116 | 3 |
| 20150117 | 4 |
trunc(cdate) 로 group by 하면 결과는 나오는데 index를 이용하지 않아 속도가 엄청납니다..
날짜를 효율적으로 group by 하는 방법 없나요?
여러분의 도움 손길을 부탁 드립니다.
-- 기초 데이터 입력용
WITH t (idx, cdate, userid ) AS
(
select '1', to_date('20150116 091716','yyyymmdd hh24miss'), '0202' from dual union all
select '2', to_date('20150116 091719','yyyymmdd hh24miss'), '0202' from dual union all
select '3', to_date('20150116 092012','yyyymmdd hh24miss'), '0103' from dual union all
select '4', to_date('20150116 102014','yyyymmdd hh24miss'), '0102' from dual union all
select '5', to_date('20150116 112015','yyyymmdd hh24miss'), '0103' from dual union all
select '6', to_date('20150117 091716','yyyymmdd hh24miss'), '0202' from dual union all
select '7', to_date('20150117 111519','yyyymmdd hh24miss'), '0103' from dual union all
select '8', to_date('20150117 132015','yyyymmdd hh24miss'), '0102' from dual union all
select '9', to_date('20150117 192025','yyyymmdd hh24miss'), '0101' from dual
)
select * from t
인덱스를 이용하지 않아 속도가 엄청나다고 하셨는데요?
어차피 전체 대상 조회 아닌가요?
전체 대상이라면 느린 이유는 인덱스를 이용하지 않아서 느린게 아닙니다.
만약 특정 기간을 대상으로 한다면 인덱스가 유리하겠지요.
이 경우엔 (날짜 컬럼 + 유저ID) 인덱스를 만들어 사용하시면 되겠네요.
감사합니다. 마농님 힌트에 힘입어...
전체를 못 뽑고 년 단위로 뽑아보니 그나마 빨리 나오네요.
아래 SQL을 나름 만들어 봤는데 혹시 손볼데 있을까요?
WITH t (idx, cdate, userid ) AS
(
select '1', to_date('20150116 091716','yyyymmdd hh24miss'), '0202' from dual union all
select '2', to_date('20150116 091719','yyyymmdd hh24miss'), '0202' from dual union all
select '3', to_date('20150116 092012','yyyymmdd hh24miss'), '0103' from dual union all
select '4', to_date('20150116 102014','yyyymmdd hh24miss'), '0102' from dual union all
select '5', to_date('20150116 112015','yyyymmdd hh24miss'), '0103' from dual union all
--
select '6', to_date('20150117 091716','yyyymmdd hh24miss'), '0202' from dual union all
select '7', to_date('20150117 111519','yyyymmdd hh24miss'), '0103' from dual union all
select '8', to_date('20150117 132015','yyyymmdd hh24miss'), '0102' from dual union all
select '9', to_date('20150117 192025','yyyymmdd hh24miss'), '0101' from dual
)
select cdate, count(*) cnt
from (
SELECT TRUNC(cdate) cdate, userid
, row_number() OVER(PARTITION BY TRUNC(cdate),userid ORDER BY userid) rn
FROM t
WHERE cdate >= TO_DATE('2015','yyyy')
AND cdate < TO_DATE('2016','yyyy')
)
where rn = 1
group by cdate
1. TO_DATE('2015', 'yyyy') 사용이 틀렸습니다.
연도만 줄 경우 월은 1월이 아닌 현재월이 기본값입니다.
현재월이 1월이라 우연하게 맞게 나왓을 뿐 2월부터는 어뚱한 결과가 나오겠네요.
2. Count(Distinct ) 를 이용해 보세요.
SELECT TRUNC(cdate) cdate
, COUNT(DISTINCT userid) cnt
FROM t
WHERE cdate >= TO_DATE('2015'||'01', 'yyyymm')
AND cdate < TO_DATE('2016'||'01', 'yyyymm')
GROUP BY TRUNC(cdate)
ORDER BY cdate
;
앗... 큰일날뻔 했군요.
sql 저렇게 심플하게 바뀔 수가 있었군요.
감사합니다!!!!!
) 버튼을 클릭하여 작성 하시면 됩니다.