· 7 years ago · Nov 21, 2018, 12:32 AM
1DROP VIEW IF EXISTS tmp;
2/*DROP TABLE IF EXISTS trajectoriesTest;
3DROP TABLE IF EXISTS categoriesTest;
4
5CREATE TABLE categoriesTest (
6 venueid TEXT NOT NULL,
7 venuecategory TEXT,
8 latitude DOUBLE PRECISION,
9 longitude DOUBLE PRECISION,
10 cattype TEXT,
11 PRIMARY KEY (venueid)
12);
13CREATE TABLE trajectoriesTest (
14 userid INTEGER,
15 venueid TEXT,
16 utctimestamp TIMESTAMP(6) WITHOUT TIME ZONE,
17 tpos BIGINT
18);
19
20INSERT INTO categoriesTest (venueid, venuecategory, latitude, longitude, cattype)
21VALUES ('0', 'McDonalds', 0, 0, 'Restaurant');
22INSERT INTO categoriesTest
23VALUES ('1', 'Juan', 0, 0, 'Home');
24INSERT INTO categoriesTest
25VALUES ('2', 'Aeroparque', 0, 0, 'Airport');
26INSERT INTO categoriesTest
27VALUES ('3', 'Ezeiza', 0, 0, 'Airport');
28INSERT INTO categoriesTest
29VALUES ('4', 'Hilton', 0, 0, 'Hotel');
30INSERT INTO categoriesTest
31VALUES ('5', 'Superchino', 0, 0, 'Market');
32INSERT INTO categoriesTest
33VALUES ('6', 'Mati', 0, 0, 'Home');
34INSERT INTO categoriesTest
35VALUES ('7', 'YPF', 0, 0, 'Station');
36
37INSERT INTO trajectoriesTest
38VALUES (1, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
39INSERT INTO trajectoriesTest
40VALUES (1, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
41INSERT INTO trajectoriesTest
42VALUES (1, '5', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
43INSERT INTO trajectoriesTest
44VALUES (1, '3', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
45INSERT INTO trajectoriesTest
46VALUES (1, '6', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 5);
47INSERT INTO trajectoriesTest
48VALUES (1, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 6);
49INSERT INTO trajectoriesTest
50VALUES (1, '1', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 7);
51INSERT INTO trajectoriesTest
52VALUES (1, '0', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 8);
53INSERT INTO trajectoriesTest
54VALUES (1, '5', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 9);
55INSERT INTO trajectoriesTest
56VALUES (1, '3', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 10);
57INSERT INTO trajectoriesTest
58VALUES (1, '6', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 11);
59INSERT INTO trajectoriesTest
60VALUES (1, '1', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 12);
61INSERT INTO trajectoriesTest
62VALUES (2, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
63INSERT INTO trajectoriesTest
64VALUES (2, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
65INSERT INTO trajectoriesTest
66VALUES (2, '5', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
67INSERT INTO trajectoriesTest
68VALUES (2, '2', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
69INSERT INTO trajectoriesTest
70VALUES (2, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 5);
71INSERT INTO trajectoriesTest
72VALUES (3, '2', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 6);
73INSERT INTO trajectoriesTest
74VALUES (3, '4', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 7);
75INSERT INTO trajectoriesTest
76VALUES (3, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 8);
77INSERT INTO trajectoriesTest
78VALUES (3, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 9);
79INSERT INTO trajectoriesTest
80VALUES (3, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 10);
81INSERT INTO trajectoriesTest
82VALUES (3, '4', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 11);
83INSERT INTO trajectoriesTest
84VALUES (3, '3', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 12);
85INSERT INTO trajectoriesTest
86VALUES (3, '4', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 13);
87INSERT INTO trajectoriesTest
88VALUES (4, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 10);
89INSERT INTO trajectoriesTest
90VALUES (4, '6', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 11);
91INSERT INTO trajectoriesTest
92VALUES (4, '3', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 12);
93INSERT INTO trajectoriesTest
94VALUES (5, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
95INSERT INTO trajectoriesTest
96VALUES (5, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
97INSERT INTO trajectoriesTest
98VALUES (5, '6', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
99INSERT INTO trajectoriesTest
100VALUES (5, '3', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
101INSERT INTO trajectoriesTest
102VALUES (6, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
103INSERT INTO trajectoriesTest
104VALUES (6, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
105INSERT INTO trajectoriesTest
106VALUES (6, '3', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
107INSERT INTO trajectoriesTest
108VALUES (6, '7', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
109INSERT INTO trajectoriesTest
110VALUES (7, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
111INSERT INTO trajectoriesTest
112VALUES (7, '1', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
113INSERT INTO trajectoriesTest
114VALUES (7, '7', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
115INSERT INTO trajectoriesTest
116VALUES (7, '3', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
117INSERT INTO trajectoriesTest
118VALUES (7, '0', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 5);
119INSERT INTO trajectoriesTest
120VALUES (8, '0', to_timestamp('2018-11-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
121INSERT INTO trajectoriesTest
122VALUES (8, '1', to_timestamp('2018-11-11 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
123INSERT INTO trajectoriesTest
124VALUES (8, '7', to_timestamp('2018-12-01 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
125INSERT INTO trajectoriesTest
126VALUES (8, '3', to_timestamp('2018-12-21 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
127INSERT INTO trajectoriesTest
128VALUES (9, '0', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
129INSERT INTO trajectoriesTest
130VALUES (9, '1', to_timestamp('2018-12-03 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
131INSERT INTO trajectoriesTest
132VALUES (9, '7', to_timestamp('2018-12-04 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
133INSERT INTO trajectoriesTest
134VALUES (9, '3', to_timestamp('2018-12-05 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);
135INSERT INTO trajectoriesTest
136VALUES (9, '1', to_timestamp('2018-12-03 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 5);
137INSERT INTO trajectoriesTest
138VALUES (9, '7', to_timestamp('2018-12-04 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 6);
139INSERT INTO trajectoriesTest
140VALUES (9, '3', to_timestamp('2018-12-05 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 7);
141INSERT INTO trajectoriesTest
142VALUES (9, '1', to_timestamp('2018-12-04 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 8);
143INSERT INTO trajectoriesTest
144VALUES (9, '3', to_timestamp('2018-12-05 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 9);
145INSERT INTO trajectoriesTest
146VALUES (10, '0', to_timestamp('2018-12-02 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 1);
147INSERT INTO trajectoriesTest
148VALUES (10, '1', to_timestamp('2018-12-03 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 2);
149INSERT INTO trajectoriesTest
150VALUES (10, '6', to_timestamp('2018-12-04 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 3);
151INSERT INTO trajectoriesTest
152VALUES (10, '3', to_timestamp('2018-12-04 11:11:11', 'YYYY-MM-DD hh24:mi:ss'), 4);*/
153
154CREATE VIEW tmp AS (SELECT t.userid, t.venueid, t.tpos, utctimestamp, c.venuecategory, c.cattype
155 FROM trajectoriesTest AS t
156 LEFT JOIN categoriesTest as c ON t.venueid = c.venueid);
157/* QUERY 1*/
158/*WITH RECURSIVE q1 AS (SELECT userid, ARRAY[cattype] AS cattypePath, utctimestamp, tpos, ARRAY[tpos] AS tposPath
159 FROM tmp
160
161 UNION
162 SELECT t.userid, t.cattype || cattypePath, t.utctimestamp, t.tpos, t.tpos || tposPath
163 FROM tmp as t
164 INNER JOIN q1 AS q ON t.userid = q.userid
165 WHERE (q.tpos - 1 = t.tpos))
166
167SELECT q1.userid, q1.tposPath
168FROM q1
169WHERE q1.cattypePath = ARRAY['Home','Station','Airport'];*/
170
171/* QUERY 2*/
172
173/*WITH RECURSIVE q2 AS (SELECT userid, ARRAY[venuecategory] AS path, ARRAY[cattype] AS cattypePath, utctimestamp, tpos
174 FROM tmp
175
176 UNION
177 SELECT t.userid, t.venuecategory || path, t.cattype || cattypePath, t.utctimestamp, t.tpos
178 FROM tmp as t
179 INNER JOIN q2 AS q ON t.userid = q.userid
180 WHERE DATE_PART('day', t.utctimestamp - q.utctimestamp) = 0
181 AND (q.tpos - 1 = t.tpos)
182 )
183
184SELECT q2.userid, q2.utctimestamp, max(q2.cattypePath) AS cattypePath
185FROM q2
186WHERE ARRAY['Home', 'Airport'] <@ q2.cattypePath
187 AND array_position(q2.cattypePath, 'Home') < array_position(q2.cattypePath, 'Airport')
188GROUP BY q2.userid, q2.utctimestamp;*/
189
190/* QUERY 3*/
191
192WITH RECURSIVE q3 AS (SELECT userid, ARRAY[venueid] AS path, utctimestamp, tpos, array_length(ARRAY[venueid], 1) as len
193 FROM tmp
194
195 UNION
196
197 SELECT t.userid, t.venueid || path, t.utctimestamp, t.tpos, array_length(t.venueid || path, 1) as len
198 FROM tmp as t
199 INNER JOIN q3 AS q ON t.userid = q.userid
200 WHERE DATE_PART('day', t.utctimestamp - q.utctimestamp) = 0
201 AND (q.tpos - 1 = t.tpos)),
202 maximals AS (SELECT userid, utctimestamp, MAX(len) as max_len
203 FROM q3
204 WHERE array_length(path, 1) > 1
205 GROUP BY userid, utctimestamp)
206
207SELECT q.userid, q.utctimestamp, q.path, m.max_len
208FROM q3 as q
209 LEFT JOIN maximals as m on m.userid = q.userid AND m.utctimestamp = q.utctimestamp
210WHERE q.len = m.max_len
211 AND path [ 1 ] = path [ array_length(path, 1) ];
212
213/* QUERY 4*/
214
215/*WITH RECURSIVE q4 AS (SELECT userid, ARRAY[venuecategory] AS path, utctimestamp, tpos
216 FROM tmp
217
218 UNION
219 SELECT t.userid, t.venuecategory || path, t.utctimestamp, t.tpos
220 FROM tmp as t
221 INNER JOIN q4 AS q ON t.userid = q.userid
222 WHERE DATE_PART('day', t.utctimestamp - q.utctimestamp) = 0
223 AND (q.tpos - 1 = t.tpos))
224
225SELECT q4.userid, q4.utctimestamp, q4.path [ 1 ] as StopInicial, q4.path [ traylength ] as StopFinal
226FROM q4
227 INNER JOIN (SELECT q4.userid, q4.utctimestamp, max(array_length(q4.path, 1)) as traylength
228 FROM q4
229 GROUP BY q4.userid, q4.utctimestamp) AS innerq4 ON q4.userid = innerq4.userid
230WHERE array_length(q4.path, 1) = innerq4.traylength AND q4.utctimestamp = innerq4.utctimestamp
231ORDER BY q4.userid;*/