with t as
(
select '201611' as yyyy, '123' as area, 54 as dogdogVisit, 65 as catVisit from dual union all
select '201611' as yyyy, '234' as area, 23 as dogVisit, 13 as catVisit from dual union all
select '201611' as yyyy, '534' as area, 53 as dogVisit, 6 as catVisit from dual union all
select '201611' as yyyy, '234' as area, 12 as dogVisit, 3 as catVisit from dual union all
select '201611' as yyyy, '153' as area, 53 as dogVisit, 74 as catVisit from dual union all
select '201611' as yyyy, '643' as area, 123 as dogVisit, 14 as catVisit from dual union all
select '201611' as yyyy, '125' as area, 634 as dogVisit, 4 as catVisit from dual union all
select '201611' as yyyy, '125' as area, 12 as dogVisit, 104 as catVisit from dual union all
select '201611' as yyyy, '125' as area, 93 as dogVisit, 23 as catVisit from dual union all
select '201711' as yyyy, '123' as area, 231 as dogVisit, 53 as catVisit from dual union all
select '201711' as yyyy, '125' as area, 321 as dogVisit, 1 as catVisit from dual union all
select '201711' as yyyy, '125' as area, 85 as dogVisit, 53 as catVisit from dual union all
select '201711' as yyyy, '153' as area, 32 as dogVisit, 74 as catVisit from dual union all
select '201711' as yyyy, '456' as area, 75 as dogVisit, 85 as catVisit from dual union all
select '201711' as yyyy, '008' as area, 53 as dogVisit, 4 as catVisit from dual union all
)
비슷하게나마 가상테이블 구현해봤습니다.
해당월 데이터는 계속 쌓이는데 보기 편하게 하기 위해 전년도랑 이번년도 11월껄만 작성했구요
WHWERE area in (?, ?, ?) AND ( YYMM = '201603' OR YYMM = TO_CHAR(ADD_MONTHS(TO_DATE('201603', 'yyyymm'), -12), 'yyyymm')) group by yymm
이렇게 해서 전년도꺼 뽑았씁니다.
그리고 SUM을 하고 한번 묶고
ROUND(dogVisit / LAG(dogVisit) OVER(ORDER BY YYMM) * 100 , 1) dogPer 이렇게 해서 증감률은 구했는데 +인지 - 인지 구하려다 보니
decode( GREATEST( ) , ?, ? ) 이런식으로 해서 한번 더 묶으려고 하거든요.
그리고 위에 적진 않았지만 개랑 고양이 말고 요일별 방문수도 있는데 어느요일에 방문을 제일 많이 했는지도 뽑아야 하고.. 개랑 고양이랑 똑같이
설명이 조잡하게 되어있긴 한데.. 참조 URL이나 이거 쓰면 될것같다라는 함수명만 알려주심 제가 검색해서 해보겠습니다. ㅜㅜ