gistfile1.txt

-- create or replace view vschedule4 as 

create view vwtf1 as 

WITH sched1 as (

 SELECT q.date AS trip_date, s.gos_number, bt.name AS bus_model, sh.name AS school_name, f_name_fmt(p.*) AS driver_name, t.name AS trip_name, g.name AS group_name, first_value(q.name) OVER w AS first_point, timezone('UTC'::text, first_value(q.start_time) OVER w)::timestamp without time zone AS first_point_plan, timezone('UTC'::text, first_value(q.time_actual) OVER w)::timestamp without time zone AS first_point_actual, last_value(q.name) OVER w1 AS last_point, timezone('UTC'::text, last_value(q.start_time) OVER w1)::timestamp without time zone AS last_point_plan, timezone('UTC'::text, last_value(q.time_actual) OVER w1)::timestamp without time zone AS last_point_actual, sum(q.fuckup_num) OVER w1 AS fuckup_num_sum, 
        CASE
            WHEN sum(q.fuckup_num) OVER w1 > 0 THEN 'да'::text
            ELSE 'нет'::text
        END AS trip_fuckup, 
        CASE
            WHEN sum(q.wout_num) OVER w1 > 0 THEN 'вне графика'::text
            ELSE ''::text
        END AS wout_fail, 
        CASE
            WHEN sum(q.vpath_num) OVER w1 > 0 THEN 'сход'::text
            ELSE ''::text
        END AS vpath_fail, 
        CASE
            WHEN sum(q.late_num) OVER w1 > 0 THEN 'опоздание'::text
            ELSE ''::text
        END AS late_fail, 
        sum(q.dist) OVER w1 AS dist_sum, 
        sum(q.dist_ok) OVER w1 AS dist_ok_sum, 
        sum(q.dist_fail) OVER w1 AS dist_fail_sum, 
        timezone('UTC'::text, first_value(q.alarm) OVER w1)::timestamp without time zone AS first_alarm, 
        timezone('UTC'::text, last_value(q.alarm) OVER w1)::timestamp without time zone AS last_alarm, 
        last_value(q.speed) OVER w1 AS last_speed, 
        timezone('UTC'::text, last_value(q.speed_time) OVER w1)::timestamp without time zone AS last_speed_time, 
        max(q.speed) OVER w1 AS max_speed,
        CASE
            WHEN q.rule_type = 'IN'::text THEN 'прибытие'::text
            ELSE 'отправление'::text
        END AS rule_name, 
        CASE
            WHEN q.fuckup THEN 'да'::text
            ELSE 'нет'::text
        END AS rule_fail, q.rule, q.object_id, q.trip_id, q.name, q.date, q.rule_type, q.wout, q.vpath, q.dist, timezone('UTC'::text, q.start_time)::timestamp without time zone AS start_time, timezone('UTC'::text, q.time_actual)::timestamp without time zone AS time_actual, timezone('UTC'::text, q.t1)::timestamp without time zone AS t1, timezone('UTC'::text, q.t2)::timestamp without time zone AS t2, timezone('UTC'::text, q.t11)::timestamp without time zone AS t11, timezone('UTC'::text, q.t22)::timestamp without time zone AS t22, q.fuckup, q.dist_ok, q.dist_fail, q.fuckup_num, q.wout_num, q.vpath_num, q.late_num, q.speed, timezone('UTC'::text, q.speed_time)::timestamp without time zone AS speed_time, timezone('UTC'::text, q.alarm)::timestamp without time zone AS alarm
   FROM ( SELECT f.rule, f.object_id, f.trip_id, f.name, f.date, f.rule_type, f.wout, f.vpath, f.dist, f.start_time, f.time_actual, f.t1, f.t2, f.t11, f.t22, f.speed, f.speed_time, f.alarm, f.time_actual IS NULL OR f.wout IS NOT NULL OR f.vpath IS NOT NULL AS fuckup, 
                CASE
                    WHEN f.time_actual IS NULL OR f.wout IS NOT NULL OR f.vpath IS NOT NULL THEN 0.00
                    ELSE f.dist
                END AS dist_ok, 
                CASE
                    WHEN f.time_actual IS NULL OR f.wout IS NOT NULL OR f.vpath IS NOT NULL THEN f.dist
                    ELSE 0.00
                END AS dist_fail, 
                CASE
                    WHEN f.time_actual IS NULL OR f.wout IS NOT NULL OR f.vpath IS NOT NULL THEN 1
                    ELSE 0
                END AS fuckup_num, 
                CASE
                    WHEN f.wout IS NOT NULL THEN 1
                    ELSE 0
                END AS wout_num, 
                CASE
                    WHEN f.vpath IS NOT NULL THEN 1
                    ELSE 0
                END AS vpath_num, 
                CASE
                    WHEN f.time_actual IS NULL THEN 1
                    ELSE 0
                END AS late_num
           FROM trip_rule_fired f
          WINDOW w AS (PARTITION BY f.trip_id, f.date ORDER BY f.start_time)) q
   JOIN school_bus s ON s.id = q.object_id
   JOIN object_group g ON s.group_id = g.id
   JOIN school sh ON sh.id = s.school_id
   JOIN bus_type bt ON s.bus_type_id = bt.id
   JOIN trip t ON t.id = q.trip_id
   LEFT JOIN person p ON p.id = s.person_id
   
  WINDOW w AS (PARTITION BY q.date, q.object_id, q.trip_id ORDER BY q.start_time), 
         w1 AS (PARTITION BY q.date, q.object_id, q.trip_id), 
         w2 AS (PARTITION BY q.date, q.object_id), w3 AS (PARTITION BY q.object_id)
  ORDER BY q.date, q.object_id, q.trip_id, q.start_time
 
),

trip_mileage as (

select date, object_id, trip_id,
       sum(q.sum_dist) over (partition by object_id order by date) sum_dist
from (

select date,
       trip_id,
       object_id,
       sum(dist) * (case when bool_or( time_actual IS NULL OR wout IS NOT NULL OR vpath IS NOT NULL ) then 0 else 1 end) as sum_dist,
       bool_and( time_actual IS NULL OR wout IS NOT NULL OR vpath IS NOT NULL ) as fuckup
from trip_rule_fired
group by date, trip_id, object_id) as q
order by date, object_id, trip_id
)

select 
       max(tm.sum_dist) over (partition by s1.object_id)  - min(tm.sum_dist) over (partition by s1.object_id) as dist_ok_total,
       sum(s1.dist) over (partition by s1.object_id) as dist_total,
       sum(s1.dist) over (partition by s1.object_id) - max(tm.sum_dist) over (partition by s1.object_id)  - min(tm.sum_dist) over (partition by s1.object_id) as dist_fail_total,
       s1.*
from sched1 s1 join trip_mileage tm on s1.trip_date = tm.date and tm.trip_id = s1.trip_id and s1.object_id = tm.object_id
where s1.object_id in (5,19,27)
--and s1.date < '2011-02-28'
and s1.date between '2011-02-01' and  '2011-02-28'
order by s1.date, s1.object_id, s1.trip_id, s1.start_time 

;

-- where object_id in (5, 19, 27) 
-- and date between '2011-02-11' and '2011-02-25' -- and fuckup_num_sum = 0
-- and first_point = name;