SELECT /* Fix for bug # 4751315 */
page.site_id site_id -- Required for join to the site dimension
,page.transaction_date transaction_date -- Required for join to the time dimension
,page.page_instance_id page_instance_id -- Required for join to the page dimension
,page.site_area_id site_area_id -- Required for join to the site area dimension
,page.visitant_id visitant_id -- Daily Unique Visitors is calculated
-- by doing distinct of visitant_id
-- because this metric is non additive across dimensions
,page.visit_id visit_id
,SUM(page.page_views) page_views
,SUM(page.page_view_duration) page_view_duration
,COUNT(page.page_views) d_page_views
,COUNT(page.page_view_duration ) d_page_view_duration_cnt
,grouping_sets_id
,(CASE
WHEN GROUPING (time.report_date_julian) = 0
THEN time.report_date_julian
WHEN GROUPING (time.week_id) = 0
THEN time.week_id
WHEN GROUPING (time.ent_period_id) = 0
THEN time.ent_period_id
WHEN GROUPING (time.ent_qtr_id) = 0
THEN time.ent_qtr_id
ELSE time.ent_year_id
END
) time_id
,(CASE
WHEN GROUPING (time.report_date_julian) = 0
THEN 1
WHEN GROUPING (time.week_id) = 0
THEN 16
WHEN GROUPING (time.ent_period_id) = 0
THEN 32
WHEN GROUPING (time.ent_qtr_id) = 0
THEN 64
ELSE 128
END
) period_type_id
,time.ent_year_id
,time.ent_qtr_id
,time.ent_period_id
,time.report_date_julian
,time.week_id
,GROUPING_ID (page.site_id
,page.transaction_date
,page.page_instance_id
,page.site_area_id
,page.visitant_id
,page.visit_id
,grouping_sets_id
,time.ent_year_id
,time.ent_qtr_id
,time.ent_period_id
,time.week_id
,time.report_date_julian
) d_gid
,COUNT(*) d_tot
FROM
ibw_page_sa_mv page
,FII.FII_TIME_DAY time
WHERE
page.transaction_date = time.report_date
GROUP BY
page.site_id
,page.transaction_date
,page.page_instance_id
,page.site_area_id
,page.visitant_id
,page.visit_id
,grouping_sets_id
,GROUPING SETS (time.ent_year_id,time.ent_qtr_id, time.ent_period_id, time.week_id,time.report_date_julian)
[Home] [Help]