작성
·
298
0
with
temp_01 as (
select a.sess_id, a.page_path, hit_seq, hit_time
, lead(hit_time) over (partition by a.sess_id order by hit_seq) as next_hit_time
, row_number() over (partition by a.sess_id, page_path order by hit_seq) as rnum
from ga.ga_sess_hits a
join ga_sess b on a.sess_id = b.sess_id
where visit_stime >= (:current_date - interval '30 days') and visit_stime < :current_date
and a.hit_type = 'PAGE'
)
select page_path,count(*) as page_cnt
, count(case when rnum = 1 then '1' else null end) as unique_page_cnt
, round(avg(next_hit_time - hit_time)/1000.0, 2) as avg_elapsed_sec
from temp_01
group by page_path order by 2 desc;
>강의에서 설명한 위의 코드를 아래의 코드로 사용해도 괜찮을까요?
순페이지 조회수 구할 때 row_number로 안하고 distinct sess_id로 해도 괜찮을까요?
with
temp_01 as (
select a.sess_id, a.page_path, hit_seq, hit_time
, lead(hit_time) over (partition by a.sess_id order by hit_seq) as next_hit_time
from ga.ga_sess_hits a
join ga_sess b on a.sess_id = b.sess_id
where visit_stime >= (:current_date - interval '30 days') and visit_stime < :current_date
and a.hit_type = 'PAGE'
)
select page_path,count(*) as page_cnt
, count( distinct sess_id) as unique_page_cnt
, round(avg(next_hit_time - hit_time)/1000.0, 2) as avg_elapsed_sec
from temp_01
group by page_path order by 2 desc;