1 2 3 4 5 6 7 8 9 10 11
| SELECT
H.title,
H.VisitID,
substring_index(substring_index(substring_index(substring_index(H.Page2View,'_ppt/',-1),'/',-10),'/',-1),'.',1) as nonslide,
substring_index(substring_index(substring_index(substring_index(H.Page2View,'_ppt/',-1),'/',-10),'/',-1),'.',-1) as typeslide,
RANK() OVER(PARTITION BY H.VisitID ORDER BY H.Timer asc) AS ordre,
cast(from_unixtime(H.timer)as time)as heure,
cast(from_unixtime(U.timestampDebut)as date) as datepresentation
FROM history H inner join usersonline U on H.VisitID=U.VisitID
where U.OwnerId>=100 and U.OwnerId<>105
and H.Page2View like('/temp/%') |
Partager