과거 30일간 페이지별 조회수와 순페이지 조회수 구하기

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;