maya.public

Parameters

Name	Type	Mode
_startdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
date_key	integer	OUT
iddevice	integer	OUT
serialnumber	character varying	OUT
idunit	integer	OUT
idclient	integer	OUT
iddriver	integer	OUT
movement_status	character varying	OUT
mov_group_num	integer	OUT
pos_quantity	integer	OUT
startdate	timestamp with time zone	OUT
duration	interval	OUT
enddate	timestamp with time zone	OUT
mvgroup_enddate	timestamp with time zone	OUT
mvgroup_distance	real	OUT
avg_speed	real	OUT
max_speed	real	OUT
mvgroup_duration	interval	OUT
adjacent_duration	interval	OUT
stopduration	integer	OUT
idle_time	integer	OUT
start_lon	real	OUT
start_lat	real	OUT
start_loc	character varying	OUT
end_lon	real	OUT
end_lat	real	OUT
end_loc	character varying	OUT
geofence	character varying	OUT
odometer	real	OUT
allowed_stop	boolean	OUT
dmodel	character varying	OUT
sum_km	real	OUT
row_number	integer	OUT
reason	character varying	OUT
speed	real	OUT
total_distance	real	OUT
global_max_speed	real	OUT
ems_consumed_fuel	real	OUT
ems_idle_fuel	real	OUT
ems_distance	real	OUT
ems_rpm	real	OUT
ems_over_rpm_time	real	OUT
ems_exceeded_speed_limit	integer	OUT
allowed_stops	integer	OUT
not_allowed_stops	integer	OUT
sudden_breaks	integer	OUT
total_stops	integer	OUT
mov_stop_time	real	OUT
mov_idle_time	real	OUT
mov_moving_time	real	OUT
avg_speed_global	real	OUT
row_number_device	integer	OUT

Definition

WITH q AS
  (SELECT e.iddevice,
          d.serialnumber,
          dm.name ||' - '||dm.description as dmodel,
          e.senddate,
          u.idunit,
          t.idclient,
          du.iddriver,
          lead(e.senddate) OVER w next_senddate,
          e.speed,
          vms.id vmsid,
          lag(vms.id) OVER w prev_vmsid,
          vms.description movement,
          e.otros->'idle_time' idle_time,
          e.otros->'stopduration' stopduration,
          e.longitude::real,
	  e.latitude::real,
          e.location,
          e.odometer::real,
          er.description reason,
          (row_number() over w)::int row_number_device
   FROM event e
   JOIN catalogs.vehicle_movementstatus vms ON vms.id = (e.otros->'vehicle_movement_status')::int
   JOIN event_reason er ON er.id = e.reason
   JOIN device d ON (d.iddevice = e.iddevice) JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount
   JOIN devicemodel dm ON dm.idmodeldevice = d.idmodeldevice
   JOIN unit u ON u.idunit = d.idunit JOIN driver_unit du ON (du.idunit = u.idunit AND du.enabled = true)
   WHERE e.senddate BETWEEN _startdate AND _enddate 
   WINDOW w AS (PARTITION BY e.iddevice ORDER BY e.senddate)
   ) ,
      q2 AS
  (SELECT iddevice,
	dmodel,
	idunit,
	serialnumber,
	idclient,
	iddriver,
	senddate,
	next_senddate,
	(next_senddate - senddate)::interval duration,
	speed,
	lag(odometer) over w prev_odometer,
	idle_time::int,
	stopduration::int ,
	movement,
	coalesce(sum((prev_vmsid <> vmsid)::int) over w,0) mov_group_num,
	longitude,
	latitude,
	location,
	odometer,
	reason,
	(max(odometer) over w1 - min(odometer) over w1)::real total_distance,
	max(speed) over w1 as global_max_speed,
	row_number_device
   FROM q window w AS (partition BY iddevice ORDER BY senddate),
    w1 AS (PARTITION by iddevice)
   ) ,
      q3 AS
  (SELECT iddevice,
          dmodel,
          count(*) over w pos_quantity,
          idunit,
          serialnumber,
          idclient,
          iddriver,
          movement,
          duration,
          mov_group_num,
          (odometer - prev_odometer)::real distance,
          ((avg(speed) over w))::real avg_speed,
          senddate,
          next_senddate,
          last_value(senddate) over w end_date,
	stopduration,
	idle_time,
	(last_value(senddate) over w - first_value(senddate) over w)::interval sum_mov_group,
	sum(duration) over w adjacent_duration,
	max(stopduration) over w max_stopduration,
	max(idle_time) over w max_idle_time,
	row_number() over(PARTITION BY iddevice,mov_group_num ORDER BY senddate) row_number
	, longitude
	, latitude
	, location
	, odometer
	, reason
	, speed
	, total_distance
	, global_max_speed
	, row_number_device
	   FROM q2 WINDOW w AS (partition BY iddevice, mov_group_num), w1 as (partition by iddevice order by senddate)),
      q4 AS
  (SELECT iddevice,
          dmodel,
          serialnumber,
          pos_quantity::int,
          idunit,
          idclient,
          iddriver,
          movement,
          duration,
          mov_group_num,
          senddate startdate,
                   last_value(senddate) over w enddate,
                   last_value(next_senddate) over w mvgroup_enddate,
                   (sum(distance) over w)::real distance,
                   sum(distance) over (PARTITION BY iddevice ORDER BY senddate) sum_km, 
                   distance _distance,
                   avg_speed,
                   max(speed) over w max_speed,
                   sum_mov_group mvgroup_duration,
                   adjacent_duration,
                   max_stopduration stopduration,
                   max_idle_time idle_time,
                   longitude start_lon,
                   latitude start_lat,
                   location start_loc,
                   last_value(longitude) over w end_lon,
                   last_value(latitude) over w end_lat,
                   last_value(location) over w end_loc,
                   location->'localidad' geofence,
                   odometer,
                   reason,
                   speed,
                   row_number,
                   total_distance
                   , global_max_speed
                   , row_number_device
   FROM q3 WINDOW w as (PARTITION BY iddevice,mov_group_num) )
SELECT 
       (extract(year from startdate)*10000 + extract(month from startdate)*100 + extract(days from startdate))::int date_key,
       iddevice,
       serialnumber,
       idunit,
       idclient,
       iddriver,
       movement,
       mov_group_num::int,
       pos_quantity,
       startdate,
       duration, /*individual event*/
       enddate,
       mvgroup_enddate,
       distance mvgroup_distance ,
       avg_speed, /*by group of events*/
       max_speed,/*by group of events*/
       mvgroup_duration, 
       adjacent_duration,
       stopduration,
       idle_time,
       start_lon,
       start_lat,
       substring(start_loc::varchar,1,10) start_loc,
       end_lon,
       end_lat,
       substring(end_loc::varchar,1,10) end_loc,
       nullif(geofence,'null') geofence,
       odometer::real,
       null::boolean allowed_stop,
       dmodel,
       sum_km::real,
       row_number::int,
       reason,
       speed,
       total_distance,
       global_max_speed,
       (random()*100)::real ems_consumed_fuel,
       (random()*100)::real ems_idle_fuel,
       (random()*100)::real ems_distance,
       (random()*100)::real ems_rpm,
       (random()*100)::real ems_over_rpm_time,
       (random()*100)::int ems_exceeded_speed_limit,
       (random()*30)::int allowed_stops,
       (random()*20)::int not_allowed_stops,
       (random()*20)::int sudden_breaks,
       (random()*20)::int total_stops,
       (random()*24)::real mov_stop_time,
       (random()*24)::real mov_idle_time,
       (random()*24)::real mov_moving_time,
       (avg(speed) over (partition by iddevice))::real avg_speed,
       row_number_device
FROM q4
;

dw_fact_activity(timestamp with time zone, timestamp with time zone)

Parameters

Definition