· 8 years ago · Feb 05, 2018, 05:00 PM
1SET temp_buffers = '10000MB';
2
3DROP TABLE IF EXISTS ocr.ocr_raw_temp;
4
5CREATE TABLE IF NOT EXISTS ocr.ocr_raw_temp (
6 event_id BIGSERIAL,
7 event_timestamp TIMESTAMP NULL DEFAULT NULL,
8 equip_id_new INT NULL DEFAULT NULL,
9 sentido VARCHAR(10) NULL DEFAULT NULL,
10 faixa INT NULL DEFAULT NULL,
11 vehicle_id VARCHAR(7) NULL DEFAULT NULL,
12 trip_id INT NULL DEFAULT NULL,
13 segment_duration INTERVAL NULL DEFAULT NULL,
14 event_id_next BIGINT NULL DEFAULT NULL,
15 equip_id_new_next INT NULL DEFAULT NULL,
16 sentido_next VARCHAR(10) NULL DEFAULT NULL
17 --event_id_prev BIGINT NULL DEFAULT NULL
18)
19WITH (OIDS = FALSE);
20
21INSERT INTO ocr.ocr_raw_temp (
22 event_id,
23 event_timestamp,
24 equip_id_new,
25 sentido,
26 faixa,
27 vehicle_id,
28 trip_id,
29 segment_duration,
30 event_id_next,
31 equip_id_new_next,
32 sentido_next
33)
34WITH q AS (
35 SELECT
36 s.*,
37 h.cod_rod as cod_rod,
38 h.km_corr as km_corr,
39 i.cod_rod as cod_rod_prev,
40 i.km_corr as km_corr_prev,
41 j.cod_rod as cod_rod_next,
42 j.km_corr as km_corr_next
43 FROM ocr.ocr_raw_sample s
44 LEFT JOIN ocr.radar_equipments h ON s.equip_id_new = h.equip_id_new
45 LEFT JOIN ocr.radar_equipments i ON s.equip_id_new_prev = i.equip_id_new
46 LEFT JOIN ocr.radar_equipments j ON s.equip_id_new_next = j.equip_id_new
47),
48u AS (
49 SELECT
50 q.*,
51 CASE WHEN q.segment_duration IS NULL THEN null ELSE
52 COUNT(*) FILTER( WHERE
53 q.segment_duration IS NOT NULL
54 AND q.event_id_prev IS NULL
55 OR (
56 q.equip_id_new_prev = q.equip_id_new_next
57 OR q.equip_id_new_prev = q.equip_id_new
58 )
59 OR (
60 (q.cod_rod_prev = q.cod_rod_next OR q.cod_rod_prev = q.cod_rod)
61 AND (q.km_corr_prev >= q.km_corr AND q.km_corr_next >= q.km_corr)
62 )
63 OR (
64 (q.cod_rod_prev = q.cod_rod_next OR q.cod_rod_prev = q.cod_rod)
65 AND (q.km_corr_prev <= q.km_corr AND q.km_corr_next <= q.km_corr)
66 )
67 ) OVER w
68 END AS trip_id
69 FROM q WHERE segment_duration IS NOT NULL WINDOW w AS (PARTITION BY q.vehicle_id ORDER BY q.event_timestamp)
70),
71v AS (
72SELECT
73 u.*,
74 CASE WHEN u.trip_id IS NULL THEN null ELSE row_number() OVER w END AS equip_id_new_dup1
75 FROM u WINDOW w AS (PARTITION BY u.vehicle_id, u.trip_id, u.equip_id_new ORDER BY u.event_timestamp)
76),
77x AS (
78SELECT
79 v.*,
80 CASE WHEN v.trip_id IS NULL THEN null ELSE
81 max(v.equip_id_new_dup1) OVER w
82 END AS equip_id_new_dup2
83 FROM v WINDOW w AS (PARTITION BY v.vehicle_id, v.trip_id ORDER BY v.event_timestamp ROWS UNBOUNDED PRECEDING)
84),
85y AS (
86SELECT
87 x.*,
88 CASE WHEN x.trip_id IS NULL THEN null ELSE
89 x.equip_id_new_dup2 - lag(x.equip_id_new_dup2) OVER w
90 END AS equip_id_new_dup_max
91 FROM x WINDOW w AS (PARTITION BY x.vehicle_id, x.trip_id ORDER BY x.event_timestamp ROWS UNBOUNDED PRECEDING)
92)
93SELECT
94 y.event_id,
95 y.event_timestamp,
96 y.equip_id_new,
97 y.sentido,
98 y.faixa,
99 y.vehicle_id,
100 CASE WHEN y.trip_id IS NULL THEN null ELSE
101 COUNT(*) FILTER( WHERE
102 y.segment_duration IS NOT NULL
103 AND y.event_id_prev IS NULL
104 OR (
105 y.equip_id_new_prev = y.equip_id_new_next
106 OR y.equip_id_new_prev = y.equip_id_new
107 )
108 OR (
109 (y.cod_rod_prev = y.cod_rod_next OR y.cod_rod_prev = y.cod_rod)
110 AND (y.km_corr_prev >= y.km_corr AND y.km_corr_next >= y.km_corr)
111 )
112 OR (
113 (y.cod_rod_prev = y.cod_rod_next OR y.cod_rod_prev = y.cod_rod)
114 AND (y.km_corr_prev <= y.km_corr AND y.km_corr_next <= y.km_corr)
115 )
116 OR y.equip_id_new_dup_max = 1
117 ) OVER w
118 END AS trip_id,
119 y.segment_duration,
120 y.event_id_next,
121 y.equip_id_new_next,
122 y.sentido_next
123FROM y WINDOW w AS (PARTITION BY y.vehicle_id ORDER BY y.event_timestamp) ORDER by y.vehicle_id, y.event_timestamp;
124
125ALTER TABLE ocr.ocr_raw_temp RENAME TO ocr_raw_trip2;