1USE tpch1; 2DROP TABLE IF EXISTS `pv_facts`; 3CREATE TABLE `pv_facts` ( 4`page_view_id` varchar(35) NOT NULL, 5`visit_date_key` date NOT NULL, 6`visit_time_key` varchar(6) DEFAULT '000000', 7`visitor_key` int unsigned NOT NULL, 8`user_key` int unsigned NOT NULL, 9`referring_url_key` int unsigned NOT NULL, 10`url_key` int unsigned NOT NULL, 11`publication_key` int unsigned NOT NULL, 12`eref_key` int unsigned NOT NULL, 13`iref_key` int unsigned NOT NULL, 14`content_key` int unsigned NOT NULL, 15`device_key` int unsigned NOT NULL, 16`visit_time` decimal(18,5) NOT NULL, 17`visit_id` varchar(100) NOT NULL, 18`ip_address` bigint NOT NULL, 19`visit_type` varchar(20) NOT NULL, 20`page_type` varchar(20) NOT NULL, 21`page_id` varchar(255) DEFAULT NULL, 22`first_byte` int unsigned DEFAULT NULL, 23`dom_interactive` int unsigned DEFAULT NULL, 24`dns_lookup` int unsigned DEFAULT NULL, 25`dom_done` int unsigned DEFAULT NULL, 26`small_screen` int unsigned DEFAULT '0', 27`view_width` int unsigned DEFAULT '0', 28`view_height` int unsigned DEFAULT '0' 29) ENGINE=Columnstore DEFAULT CHARSET=utf8; 30insert into pv_facts values ('U1mQdAoBCjUAAHbgZcsAAABS','2014-04-24',183012,47544380,0,0,1,3,1,1,0,18,1398378612.15000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); 31insert into pv_facts values ('U1mQgwoBCjUAAHbcVCMAAAAm','2014-04-24',183027,47544380,0,1,6,3,1,588,0,18,1398378627.16000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); 32insert into pv_facts values ('U1mRDQoBCjcAACltU4MAAAAO','2014-04-24',183245,47544380,0,0,1,3,1,588,0,18,1398378765.38000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); 33insert into pv_facts values ('U1mRHAoBCjUAAHfsPr4AAAAI','2014-04-24',183301,47544380,0,1,338,3,1,12967,0,18,1398378780.92000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',747,0,0,0,0,0,1280,652); 34insert into pv_facts values ('U1mRuQoBCn0AAD1LRyAAAAA7','2014-04-24',183538,47544380,0,338,1065,3,1,7490,0,18,1398378937.80000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',646,0,0,0,0,0,1280,652); 35insert into pv_facts values ('U1mS1goBCjgAAFPpujQAAAAq','2014-04-24',184022,47544380,0,5530,31,3,1,17208,0,18,1398379222.31000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',49,0,0,0,0,0,1280,652); 36insert into pv_facts values ('U1mSoQoBCjQAAGlPc44AAABa','2014-04-24',183930,47544380,0,1065,5530,3,1,7490,0,18,1398379169.63000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',646,0,0,0,0,0,1280,652); 37insert into pv_facts values ('U1mT8woBCjQAAGzYKq4AAABH','2014-04-24',184508,47544380,0,1743587,3734,3,1,15557,0,18,1398379507.53000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',280,0,0,0,0,0,1280,652); 38insert into pv_facts values ('U1mTAQoBCn0AADG@5EoAAAAq','2014-04-24',184105,47544380,0,31,96,3,1,17208,2211498,18,1398379265.39000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','FEATURE_ARTICLE',1035489,0,0,0,0,0,1280,652); 39insert into pv_facts values ('U1mTUAoBCjcAABpjRKEAAABE','2014-04-24',184225,47544380,0,96,6,3,1,16465,0,18,1398379344.69000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); 40insert into pv_facts values ('U1mTdwoBCjcAAB30SZMAAAAp','2014-04-24',184303,47544380,0,6,254,3,1,17332,0,18,1398379383.19000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',489,0,0,0,0,0,1280,652); 41insert into pv_facts values ('U1mTwQoBCjYAAB9mJv0AAAAd','2014-04-24',184418,47544380,0,254,1743587,3,1,17332,2211589,18,1398379457.75000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1035618,0,0,0,0,0,1280,652); 42insert into pv_facts values ('U1mU5AoBCjgAAFgaWngAAAA1','2014-04-24',184909,47544380,0,189,1793585,3,1,4553,2212159,18,1398379748.68000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1036245,0,0,0,0,0,1280,652); 43insert into pv_facts values ('U1mU8goBCjUAAHKCH3QAAABJ','2014-04-24',184922,47544380,0,1793585,1808578,3,1,2702,2212384,18,1398379762.04000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1036401,0,0,0,0,0,1280,652); 44insert into pv_facts values ('U1mUZwoBCjYAAB73G9UAAABg','2014-04-24',184704,47544380,0,0,1,3,1,15557,0,18,1398379623.69000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); 45insert into pv_facts values ('U1mUcQoBCjQAAG1Uc9YAAAAK','2014-04-24',184714,47544380,0,1,189,3,1,4553,0,18,1398379633.67000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',280,0,0,0,0,0,1280,652); 46insert into pv_facts values ('U1mVWgoBCjMAAEh2BRcAAABP','2014-04-24',185106,47544380,0,1808578,6,3,1,2137,0,18,1398379866.03000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); 47select distinct * from ( 48select 49visit_id, 50first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 51first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 52first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 53first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 54first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 55first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 56first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 57first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 58first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 59first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 60count(*) over (partition by visit_id ) as pvs, 610 as clicks 62from pv_facts 63where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 64visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 65U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 66select distinct * from ( 67select 68visit_id, 69first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 70first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 71first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 72first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 73first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 74first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 75first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 76first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 77first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 78first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 79count(*) over (partition by visit_id ) as pvs, 800 as clicks 81from pv_facts 82where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 83visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 84U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 85select distinct * from ( 86select 87visit_id, 88first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 89first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 90first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 91first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 92first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 93first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 94first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 95first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 96first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 97first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 98count(*) over (partition by visit_id ) as pvs, 990 as clicks 100from pv_facts 101where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 102visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 103U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 104select distinct * from ( 105select 106visit_id, 107first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 108first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 109first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 110first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 111first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 112first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 113first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 114first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 115first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 116first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 117count(*) over (partition by visit_id ) as pvs, 1180 as clicks 119from pv_facts 120where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 121visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 122U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 123select distinct * from ( 124select 125visit_id, 126first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 127first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 128first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 129first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 130first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 131first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 132first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 133first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 134first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 135first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 136count(*) over (partition by visit_id ) as pvs, 1370 as clicks 138from pv_facts 139where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 140visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 141U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 142select distinct * from ( 143select 144visit_id, 145first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 146first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 147first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 148first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 149first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 150first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 151first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 152first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 153first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 154first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 155count(*) over (partition by visit_id ) as pvs, 1560 as clicks 157from pv_facts 158where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 159visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 160U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 161select distinct * from ( 162select 163visit_id, 164first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 165first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 166first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 167first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 168first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 169first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 170first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 171first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 172first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 173first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 174count(*) over (partition by visit_id ) as pvs, 1750 as clicks 176from pv_facts 177where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 178visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 179U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 180select distinct * from ( 181select 182visit_id, 183first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 184first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 185first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 186first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 187first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 188first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 189first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 190first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 191first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 192first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 193count(*) over (partition by visit_id ) as pvs, 1940 as clicks 195from pv_facts 196where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 197visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 198U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 199select distinct * from ( 200select 201visit_id, 202first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 203first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 204first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 205first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 206first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 207first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 208first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 209first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 210first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 211first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 212count(*) over (partition by visit_id ) as pvs, 2130 as clicks 214from pv_facts 215where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 216visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 217U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 218select distinct * from ( 219select 220visit_id, 221first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 222first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 223first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 224first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 225first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 226first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 227first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 228first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 229first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 230first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 231count(*) over (partition by visit_id ) as pvs, 2320 as clicks 233from pv_facts 234where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 235visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 236U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 237select distinct * from ( 238select 239visit_id, 240first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 241first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 242first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 243first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 244first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 245first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 246first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 247first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 248first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 249first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 250count(*) over (partition by visit_id ) as pvs, 2510 as clicks 252from pv_facts 253where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 254visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 255U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 256select distinct * from ( 257select 258visit_id, 259first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 260first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 261first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 262first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 263first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 264first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 265first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 266first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 267first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 268first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 269count(*) over (partition by visit_id ) as pvs, 2700 as clicks 271from pv_facts 272where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 273visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 274U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 275select distinct * from ( 276select 277visit_id, 278first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 279first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 280first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 281first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 282first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 283first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 284first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 285first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 286first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 287first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 288count(*) over (partition by visit_id ) as pvs, 2890 as clicks 290from pv_facts 291where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 292visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 293U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 294select distinct * from ( 295select 296visit_id, 297first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 298first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 299first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 300first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 301first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 302first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 303first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 304first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 305first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 306first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 307count(*) over (partition by visit_id ) as pvs, 3080 as clicks 309from pv_facts 310where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 311visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 312U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 313select distinct * from ( 314select 315visit_id, 316first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 317first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 318first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 319first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 320first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 321first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 322first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 323first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 324first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 325first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 326count(*) over (partition by visit_id ) as pvs, 3270 as clicks 328from pv_facts 329where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 330visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 331U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 332select distinct * from ( 333select 334visit_id, 335first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, 336first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, 337first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, 338first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, 339first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, 340first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, 341first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, 342first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, 343first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, 344first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, 345count(*) over (partition by visit_id ) as pvs, 3460 as clicks 347from pv_facts 348where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; 349visit_id start_time end_time landing_eref_key landing_iref_key landing_page_id landing_page_type exit_eref_key exit_iref_key exit_page_id exit_page_type pvs clicks 350U1mQdAoBCjUAAHbgZcsAAABS 1398378612.15000 1398379866.03000 1 1 existing_user INDEX 1 2137 Editors' Choice MISC 17 0 351DROP TABLE `pv_facts`; 352