maya.public

Parameters

Name	Type	Mode
_startdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
iddevice	integer	OUT
pos_quantity	integer	OUT
movement	character varying	OUT
startdate	timestamp with time zone	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

Definition

WITH q AS
  (SELECT iddevice,
          senddate,
          lead(senddate) OVER w next_senddate,
          odometer,
          speed,
          vms.id vmsid,
          lag(vms.id) OVER w prev_vmsid,
          vms.description movement,
          otros->'idle_time' idle_time,
          otros->'stopduration' stopduration,
          longitude::real,
	  latitude::real,
          location
   FROM event e
   JOIN catalogs.vehicle_movementstatus vms ON vms.id = (e.otros->'vehicle_movement_status')::int
   WHERE e.senddate BETWEEN _startdate AND _enddate
   WINDOW w AS (PARTITION BY e.iddevice ORDER BY e.senddate)
   ) ,
      q2 AS
  (SELECT iddevice,
          senddate,
          next_senddate,
          (next_senddate - senddate)::interval duration,
          odometer,
          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
   FROM q window w AS (partition BY iddevice
                       ORDER BY senddate)) ,
      q3 AS
  (SELECT iddevice,
          count(*) over w pos_quantity,
          movement,
          mov_group_num,
          (odometer - prev_odometer)::real distance,
          speed,
          ((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
   FROM q2 WINDOW w AS (partition BY iddevice, mov_group_num), w1 as (partition by iddevice order by senddate)),
      q4 AS
  (SELECT iddevice,
          pos_quantity::int,
          movement,
          senddate startdate,
                   last_value(senddate) over w enddate,
                   last_value(next_senddate) over w mvgroup_enddate,
                   (sum(distance) over w)::real distance,
                   distance _distance,
                   speed,
                   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,
                   row_number
   FROM q3 WINDOW w as (PARTITION BY iddevice,mov_group_num) )
SELECT iddevice,
       pos_quantity,
       movement,
       startdate,
       enddate,
       mvgroup_enddate,
       distance mvgroup_distance,
       avg_speed,
       max_speed,
       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
FROM q4
;

dw_movement_activity(timestamp with time zone, timestamp with time zone)

Parameters

Definition