-- 当天的所有记录
with data_today_rn as (
select
t1.id
,t1.occur_time
,t1.device_id
,t1.event_type
,row_number() over(partition by t1.device_id order by t1.occur_time asc ,t1.id asc ) as rn_asc
,row_number() over(partition by t1.device_id order by t1.occur_time desc,t1.id desc) as rn_desc
from ODS_VIDEO_DEVICE_EVENT t1
where to_date(occur_time) = '2021-11-18'
and event_type in ('上线','离线')
-- and t1.device_id = '3a001506-9646-c4fd-825b-5b404eac6d47'
) -- 当天所有记录
,data_cnt_online_offline as (
select
device_id
,count(case when event_type = '上线' then 1 end) as cnt_online
,count(case when event_type = '离线' then 1 end) as cnt_offline
from data_today_rn
group by
device_id
) -- 统计当天上线和离线次数
,data_relevance_filter as (
select
t1.id
,t2.id as t2_id
,t1.occur_time
,t2.occur_time as t2_occur_time
,t1.device_id
,t2.device_id as t2_device_id
,t1.event_type
,t2.event_type as t2_event_type
,t1.rn_asc
,t2.rn_asc as t2_rn_asc
,t1.rn_desc
,t2.rn_desc as t2_rn_desc
,round((cast(cast(t2.occur_time as timestamp) as bigint) - cast(cast(t1.occur_time as timestamp) as bigint)) / 3600,4) as hour_diff
,round((cast(cast(t2.occur_time as timestamp) as bigint) - cast(cast(concat(to_date(t2.occur_time),' 00:00:00') as timestamp) as bigint)) / 3600,4) as hour_diff_first
,round((cast(cast(concat(date_add(to_date(t1.occur_time),1),' 00:00:00') as timestamp) as bigint) - cast(cast(t1.occur_time as timestamp) as bigint)) / 3600,4) as hour_diff_end
,case
when t2.rn_asc = 1 and t2.event_type = '离线' then '第一条是离线'
when t1.rn_desc = 1 and t1.event_type = '上线' then '最后一条是上线'
else '上线离线'
end as flag
from data_today_rn t1
full join data_today_rn t2
on t1.device_id = t2.device_id and (t1.rn_asc + 1 ) = t2.rn_asc
where (t1.event_type = '上线' and t2.event_type = '离线')
or (t2.rn_asc = 1 and t2.event_type = '离线') -- 第一条是离线
or (t1.rn_desc = 1 and t1.event_type = '上线') -- 最后一条是上线
) -- 将两条记录错位关联，并过滤数据
-- select * from data_relevance_filter order by nvl(device_id,t2_device_id),nvl(rn_asc,t2_rn_asc) ;
,data_the_day_etl as (
select
to_date(nvl(occur_time,t2_occur_time)) as day
,nvl(id,t2_id) as id
-- ,t2_id
-- ,occur_time
-- ,t2_occur_time
,nvl(device_id,t2_device_id) as device_id
-- ,event_type
-- ,t2_event_type
,nvl(rn_asc,0) as rn_asc
-- ,t2_rn_asc
-- ,nvl(rn_desc,0) as rn_desc
-- ,t2_rn_desc
,case
when flag = '第一条是离线' then hour_diff_first
when flag = '最后一条是上线' then hour_diff_end
else hour_diff
end as hour_diff_fix
-- ,hour_diff
-- ,hour_diff_first
-- ,hour_diff_end
,flag
from data_relevance_filter
) -- 根据标签计算出记录的上线时长
-- select * from data_the_day_etl order by device_id,rn_asc;
,data_the_day_cnt as (
select
day
,device_id
,sum(hour_diff_fix) as hour_diff_sum
from data_the_day_etl
group by
day
,device_id
) -- 统计当天记录的设备的上线时长
-- select * from data_the_day_cnt;
,data_the_day_befor as (
select
'2021-11-18' as day
,device_id
,case
when event_type = '上线' then 24
when event_type = '离线' then 0
end as hour_diff
from (
select
t1.id
,t1.occur_time
,t1.device_id
,t1.event_type
,row_number() over(partition by t1.device_id order by t1.occur_time desc) as rn
from ODS_VIDEO_DEVICE_EVENT t1
where to_date(occur_time) < '2021-11-18'
and event_type in ('上线','离线')
) t2
where t2.rn = 1
) -- 当天之前的最后一条记录
-- select * from data_the_day_befor;
,data_device_online_duration as (
select
nvl(t1.day,t2.day) as day
,nvl(t1.device_id,t2.device_id) as device_id
,nvl(t1.hour_diff_sum,t2.hour_diff) as hour_diff
from data_the_day_cnt t1
full join data_the_day_befor t2
on t1.device_id = t2.device_id and t1.day = t2.day
) -- 统计当天的上线时长
-- select * from data_device_online_duration limit 100;
,data_device_project_mapping as (
select
t2.id
,t2.device_id
,t2.proj_id
from (
select
t1.id
,t1.device_id
,t1.proj_id
,row_number() over(partition by t1.device_id order by t1.occur_time asc ,t1.id asc) as rn
from ODS_VIDEO_DEVICE_EVENT t1
where nvl(t1.proj_id,'') <> ''
) t2
where t2.rn = 1
) -- 设备项目映射表
-- select * from data_device_project_mapping;
insert overwrite table dwd_fact_video_device_event partition (etl_dt)
select
t1.day
,t1.device_id
,t1.hour_diff
,t3.cnt_online
,t3.cnt_offline
,t2.proj_id
,t2.proj_name
,t2.corp_id
,t2.corp_name
,current_timestamp() as etl_timestamp
,t1.day as etl_dt
from data_device_online_duration t1
left join (
select
t21.device_id
,t21.proj_id
,t22.name as proj_name
,t22.corp_id
,t23.name as corp_name
from data_device_project_mapping t21
inner join (select * from ODS_PROJECT where db_name = 'DB_NAME') t22
on t21.proj_id = t22.id
left join (select * from ODS_ORGANIZATION where db_name = 'DB_NAME') t23
on t22.corp_id = t23.id
) t2
on t1.device_id = t2.device_id
left join data_cnt_online_offline t3
on t1.device_id = t3.device_id
;